This was just a suggestion to store the insert id
TRUNCATE TABLE table_name;
...
insert into insert_id (insert_id) select last_insert_id();
...
insert into new_table
select t.*, i.insert_id
from table1 t, insert_id i
<where table1 condition is met>
-----Original Message-----
From: Philippe Poelvoorde
To: Victor Pendleton
Cc: ''mysql@stripped ' '
Sent: 7/27/04 9:56 AM
Subject: Re: Replication script pb
I feed the table with :
INSERT INTO table ( SELECT @backup_id, col1. col2 FROM table_to_backup)
How can i do with a temp table ?
Victor Pendleton wrote:
> Ugly, but you could try using a temp table to store the
last_insert_id().
>
> -----Original Message-----
> From: Philippe Poelvoorde
> To: 'mysql@stripped '
> Sent: 7/27/04 5:54 AM
> Subject: Re: Replication script pb
>
> Paul DuBois wrote:
>
>>At 8:20 -0500 7/26/04, Victor Pendleton wrote:
>>
>>
>>>Have you tried using the last insert id function instead?
>>>SET @backup_id = last_insert_id()
>>
>>
>>That'll give him the same result. I suspect the problem might be
>>that user variables are not replicated well in MySQL 4.0.x.
>
>
> Yes, same error.
>
>
>>Philippe, what version of MySQL are you using? If 4.0.x, you might
>>try skipping the SET statement and just refer to LAST_INSERT_ID()
>>or @@LAST_INSERT_ID() directly in your second INSERT statement.
>
>
> the version is 4.0.15.
> the pb is that I have two INSERT to do with the same id... any
> workaround for that ?
>
>
>>>-----Original Message-----
>>>From: Philippe Poelvoorde
>>>To: mysql@stripped
>>>Sent: 7/26/04 7:03 AM
>>>Subject: Replication script pb
>>>
>>>Hi,
>>>
>>>We have an environnment with a master and a slave. We run a script
>
> every
>
>>>hour (on the master only) that does something like this to backup
>
> some
>
>>>parameters :
>>>insert into backup(NULL,NULL) VALUES(NULL,NOW())
>>>SET @backup_id = @@LAST_INSERT_ID
>>>INSERT INTO backup_param ( SELECT @backup_id, col1, col2 FROM param)
>>>It works perfectly on the master but the slave stop due to duplicate
>>>entries. the @backup_id do not pass the replication...
>>>any solution to have that script working ?
>>
>>
>>
>
>
--
Philippe Poelvoorde
COS Trading Ltd.
+44.(0)20.7376.2401