Bug #33029 5.0 to 5.1 replication fails on dup key when inserting using a trig in SP
Submitted: 6 Dec 2007 0:59 Modified: 5 Apr 2008 7:34
Reporter: Omer Barnir (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.0.52 / 5.1.22 OS:Any
Assigned to: Zhenxing He
Triage: D1 (Critical)

[6 Dec 2007 0:59] Omer Barnir
Description:
When replicating from a 5.0 system to a 5.1 system the slave 'mixes' up auto_increment values of tables , resulting in duplicate key erros that cause the SQL slave thread to stop with an error message:
  Last_SQL_Errno: 1062
  Last_SQL_Error: Error 'Duplicate entry '10' for key 'PRIMARY'' on query. 
                  Default database: 'test'. Query: 'DELETE FROM tbl_a 
                  WHERE id =  NAME_CONST('del_count',10)'

- table_a and table b have auto increment columns.
- data is inserted and deleted into/from table_a using a stored procedure
- table_a has a 'before delete' trigger that inserts rows to table_a.

The problem is that when a row is inserted into table_b (by the trigger), the value of the auto_increment column in table_b *ON THE SLAVE* is set to the value of the last auto_increment value of table_a and not that of table_b.

This results in a duplicate key error once a second row is deleted from table_a.

Note:
1) There is no problem on the master and values there are inserted correctly
2) This does not happen when master ans slave are of the same version (either 
   5.0 or 5.1)
3) The following example is with innodb but the same happens with myisam 
   as well
4) The error of the SQL thread reports of a duplicate key when deleting from
   table_a but the actual duplication is caused by the insert to table_b.

How to repeat:
1) Using mysql-test-run.pl start a 5.0 mysql server with replication
2) Using mysql-test-run.pl start a 5.1 mysql server with replication
   (make sure you use different port numbers and vardir locations for both systems
    this can be done by using the --vardir --master-port and --slave-port options)
3) Using the mysql client log into the 5.1 slave and point it to the 5.1 master:
   - stop slave;
   - change master to master_port=<port of 5.0 master>;
   - start slave
4) Using the mysqladmin tool shutdown the 5.1 master and the 5.0 slave
>>> You now have a system with a 5.1 master pointing to a 5.1 slave

5) Using the mysql client log into the 5.0 master.
6) Run the attached sql script
7) from the client select * from tbl_a and select * from tbl_b;
>>> You will note that the id values in tbl_b are 1..10

5) Using the mysql client log into the 5.1 slave;
6) use test; and select * from tbl_a and select * from tbl_b;
>> You will notice that tbl_b has only two rows with id of 5 and 10.
   These correspond to the auto increment values of tbl_a after the first
   insert (the delete of row 3 and after the second 5 row insert (the delete
   of row 7). The third delete (happening after no further inserts to tbl_a,
   generats the error and stops the SQL thread.
7) running show slave status will return the above error.
 
Start a 5.0 mysql system with replication

Suggested fix:
Replication should not fail between 5.0 master and 5.1 slave
[6 Dec 2007 10:13] Susanne Ebrecht
Many thanks for reporting a bug.
[6 Dec 2007 18:48] Susanne Ebrecht
Verified as described
[23 Jan 2008 17:12] Andrei Elkin
The issue is certainly a duplicate of Bug #19630 stored function inserting into two auto_increment breaks statement-based binlog.

Generated by  <= 5.0 version query event that inserted implicitly in a table with a auto-increment column via a sp or a trigger can not be always reliably applied on a slave.

Bug#19630 fixed the issue for 5.1 and higher versions.
[24 Jan 2008 20:08] Omer Barnir
Andrei,

This happens *only* wen replicating from 5.0 (master) to 5.1 slave, and not when both master and slave are of the same version. 

Are you sure this is the same thing as Bug #19630 - the use case and impact on upgrade is competently different
[28 Jan 2008 19:00] Omer Barnir
sql test case

Attachment: bug33029.sql (text/x-sql), 1.67 KiB.

[28 Jan 2008 19:02] Omer Barnir
Added the missing test case. 
Note that when replication is done from 5.0 to 5.1 tab_b gets rows 1,2,3 on the master while on the slave the rows are inserted with 5,10,fail... the autoincrement values of tab_a at the time
[26 Feb 2008 14:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/42995

ChangeSet@1.2534, 2008-02-26 22:43:27+08:00, hezx@mail.hezx.com +7 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  When replicating from 5.0 to 5.1, using trigger or function in
  stored procedure will cause the replication break.
  
  The reason of the bug is because when run a statement that calls
  function or trigger in a SP, For 5.1 masters, if the statement or
  functions/triggers used generate auto_increment values, the first
  such used value will be logged in binlog, if the statement do not
  used such values, the auto_increment value used by
  functions/triggers will be logged, and the value will later be
  used by 5.1 slaves. However this is not the case for 5.0 masters,
  it's always the next auto_increment value of the statement that
  will be logged, and at the same time, 5.0 slaves will not use the
  value for functions/triggers. If we replicate from 5.0 to 5.1,
  the auto_increment value of the statement will be use as the
  value for functions/triggers, and then cause the break.
  
  This patch fixes this bug by making 5.1 slaves checking for 5.0
  masters, and if it is, don't use the saved auto_increment value
  for functions/triggers to be compatible with the 5.0 master.
[11 Mar 2008 5:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43715

ChangeSet@1.2583, 2008-03-11 13:09:13+08:00, hezx@mail.hezx.com +4 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  Prior to MySQL 5.1.12 (including 5.0.x), when a stored routine or
  trigger caused an INSERT into an AUTO_INCREMENT column, the
  generated AUTO_INCREMENT value should not be written into the binary
  log, which means if a statement does not generate AUTO_INCREMENT
  valule itself, there will be no Intvar event (SET INSERT_ID)
  associated with it even if one of the stored routine or trigger
  caused generation of such a value. And meanwhile, when executing a
  stored routine or trigger, it would ignore the INSERT_ID value even
  if there is a INSERT_ID value available set by a SET INSERT_ID
  statement.
  
  After MySQL 5.1.12, the generated AUTO_INCREMENT value is written
  into the binary log, and the value will be used if availabe when
  executing the stored routine or trigger.
  
  Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
  (referenced as the buggy versions in the text below), when one
  statement that generates AUTO_INCREMENT value by itself was executed
  in the body of a SP, all statements in the SP after this statement
  would be treated as if they had generated AUTO_INCREMENT by itself.
  When a statement that did not generate AUTO_INCREMENT value by
  itself but by a function/trigger called by it, an erroenous Intvar
  event would be associated with the statement, this erroenous
  INSERT_ID value wouldn't cause problem when replicating between
  masters and slaves of 5.0.x or prior 5.1.12, because the erroneous
  INSERT_ID value was not used when executing functions/triggers. But
  when replicating from buggy versions to 5.1.12 or newer, which will
  use the INSERT_ID value in functions/triggers, the erronous value
  will be used.
  
  The patch for 5.0 fixed it not to generate the erroneous Intvar
  event, another patch for 5.1 fixed it to ignore the SET INSERT_ID
  value when executing functions/triggers if it is replicating from
  a master of buggy versions.
[11 Mar 2008 5:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43716

ChangeSet@1.2534, 2008-03-11 13:25:26+08:00, hezx@mail.hezx.com +11 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  Prior to MySQL 5.1.12 (including 5.0.x), when a stored routine or
  trigger caused an INSERT into an AUTO_INCREMENT column, the
  generated AUTO_INCREMENT value should not be written into the binary
  log, which means if a statement does not generate AUTO_INCREMENT
  valule itself, there will be no Intvar event (SET INSERT_ID)
  associated with it even if one of the stored routine or trigger
  caused generation of such a value. And meanwhile, when executing a
  stored routine or trigger, it would ignore the INSERT_ID value even
  if there is a INSERT_ID value available set by a SET INSERT_ID
  statement.
  
  After MySQL 5.1.12, the generated AUTO_INCREMENT value is written
  into the binary log, and the value will be used if availabe when
  executing the stored routine or trigger.
  
  Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
  (referenced as the buggy versions in the text below), when one
  statement that generates AUTO_INCREMENT value by itself was executed
  in the body of a SP, all statements in the SP after this statement
  would be treated as if they had generated AUTO_INCREMENT by itself.
  When a statement that did not generate AUTO_INCREMENT value by
  itself but by a function/trigger called by it, an erroenous Intvar
  event would be associated with the statement, this erroenous
  INSERT_ID value wouldn't cause problem when replicating between
  masters and slaves of 5.0.x or prior 5.1.12, because the erroneous
  INSERT_ID value was not used when executing functions/triggers. But
  when replicating from buggy versions to 5.1.12 or newer, which will
  use the INSERT_ID value in functions/triggers, the erronous value
  will be used.
  
  The patch for 5.1 fixed it to ignore the SET INSERT_ID value when
  executing functions/triggers if it is replicating from a master
  of buggy versions, another patch for 5.0 fixed it not to generate
  the erroneous Intvar event.
[12 Mar 2008 7:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43803

ChangeSet@1.2534, 2008-03-12 15:20:11+08:00, hezx@mail.hezx.com +11 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  For all 5.0 and up to 5.1.12 exclusive, when a stored routine or
  trigger caused an INSERT into an AUTO_INCREMENT column, the
  generated AUTO_INCREMENT value should not be written into the binary
  log, which means if a statement does not generate AUTO_INCREMENT
  valule itself, there will be no Intvar event (SET INSERT_ID)
  associated with it even if one of the stored routine or trigger
  caused generation of such a value. And meanwhile, when executing a
  stored routine or trigger, it would ignore the INSERT_ID value even
  if there is a INSERT_ID value available set by a SET INSERT_ID
  statement.
  
  Starting from MySQL 5.1.12, the generated AUTO_INCREMENT value is written
  into the binary log, and the value will be used if availabe when
  executing the stored routine or trigger.
  
  Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
  (referenced as the buggy versions in the text below), when a statement
  that generates AUTO_INCREMENT value by the top statement was executed
  in the body of a SP, all statements in the SP after this statement
  would be treated as if they had generated AUTO_INCREMENT by the top statement.
  When a statement that did not generate AUTO_INCREMENT value by
  the top statement but by a function/trigger called by it, an erroenous Intvar
  event would be associated with the statement, this erroenous
  INSERT_ID value wouldn't cause problem when replicating between
  masters and slaves of 5.0.x or prior 5.1.12, because the erroneous
  INSERT_ID value was not used when executing functions/triggers. But
  when replicating from buggy versions to 5.1.12 or newer, which will
  use the INSERT_ID value in functions/triggers, the erronous value
  will be used, which would cause duplicate entry error and cause the
  slave to stop.
  
  The patch for 5.1 fixed it to ignore the SET INSERT_ID value when
  executing functions/triggers if it is replicating from a master
  of buggy versions, another patch for 5.0 fixed it not to generate
  the erroneous Intvar event.
[12 Mar 2008 7:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43804

ChangeSet@1.2534, 2008-03-12 15:07:41+08:00, hezx@mail.hezx.com +11 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  Prior to MySQL 5.1.12 (including 5.0.x), when a stored routine or
  trigger caused an INSERT into an AUTO_INCREMENT column, the
  generated AUTO_INCREMENT value should not be written into the binary
  log, which means if a statement does not generate AUTO_INCREMENT
  valule itself, there will be no Intvar event (SET INSERT_ID)
  associated with it even if one of the stored routine or trigger
  caused generation of such a value. And meanwhile, when executing a
  stored routine or trigger, it would ignore the INSERT_ID value even
  if there is a INSERT_ID value available set by a SET INSERT_ID
  statement.
  
  After MySQL 5.1.12, the generated AUTO_INCREMENT value is written
  into the binary log, and the value will be used if availabe when
  executing the stored routine or trigger.
  
  Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
  (referenced as the buggy versions in the text below), when one
  statement that generates AUTO_INCREMENT value by itself was executed
  in the body of a SP, all statements in the SP after this statement
  would be treated as if they had generated AUTO_INCREMENT by itself.
  When a statement that did not generate AUTO_INCREMENT value by
  itself but by a function/trigger called by it, an erroenous Intvar
  event would be associated with the statement, this erroenous
  INSERT_ID value wouldn't cause problem when replicating between
  masters and slaves of 5.0.x or prior 5.1.12, because the erroneous
  INSERT_ID value was not used when executing functions/triggers. But
  when replicating from buggy versions to 5.1.12 or newer, which will
  use the INSERT_ID value in functions/triggers, the erronous value
  will be used.
  
  The patch for 5.1 fixed it to ignore the SET INSERT_ID value when
  executing functions/triggers if it is replicating from a master
  of buggy versions, another patch for 5.0 fixed it not to generate
  the erroneous Intvar event.
[12 Mar 2008 8:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43809

ChangeSet@1.2583, 2008-03-12 16:46:34+08:00, hezx@mail.hezx.com +4 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  For all 5.0 and up to 5.1.12 exclusive, when a stored routine or
  trigger caused an INSERT into an AUTO_INCREMENT column, the
  generated AUTO_INCREMENT value should not be written into the
  binary log, which means if a statement does not generate
  AUTO_INCREMENT valule itself, there will be no Intvar event (SET
  INSERT_ID) associated with it even if one of the stored routine
  or trigger caused generation of such a value. And meanwhile, when
  executing a stored routine or trigger, it would ignore the
  INSERT_ID value even if there is a INSERT_ID value available set
  by a SET INSERT_ID statement.
  
  Starting from MySQL 5.1.12, the generated AUTO_INCREMENT value is
  written into the binary log, and the value will be used if
  availabe when executing the stored routine or trigger.
  
  Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
  (referenced as the buggy versions in the text below), when a
  statement that generates AUTO_INCREMENT value by the top
  statement was executed in the body of a SP, all statements in the
  SP after this statement would be treated as if they had generated
  AUTO_INCREMENT by the top statement.  When a statement that did
  not generate AUTO_INCREMENT value by the top statement but by a
  function/trigger called by it, an erroenous Intvar event would be
  associated with the statement, this erroenous INSERT_ID value
  wouldn't cause problem when replicating between masters and
  slaves of 5.0.x or prior 5.1.12, because the erroneous INSERT_ID
  value was not used when executing functions/triggers. But when
  replicating from buggy versions to 5.1.12 or newer, which will
  use the INSERT_ID value in functions/triggers, the erronous value
  will be used, which would cause duplicate entry error and cause
  the slave to stop.
  
  The patch for 5.0 fixed it not to generate the erroneous Intvar
  event, another patch for 5.1 fixed it to ignore the SET INSERT_ID
  value when executing functions/triggers if it is replicating from
  a master of buggy versions.
[14 Mar 2008 2:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43970

ChangeSet@1.2583, 2008-03-14 10:03:01+08:00, hezx@mail.hezx.com +4 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  For all 5.0 and up to 5.1.12 exclusive, when a stored routine or
  trigger caused an INSERT into an AUTO_INCREMENT column, the
  generated AUTO_INCREMENT value should not be written into the
  binary log, which means if a statement does not generate
  AUTO_INCREMENT value itself, there will be no Intvar event (SET
  INSERT_ID) associated with it even if one of the stored routine
  or trigger caused generation of such a value. And meanwhile, when
  executing a stored routine or trigger, it would ignore the
  INSERT_ID value even if there is a INSERT_ID value available set
  by a SET INSERT_ID statement.
  
  Starting from MySQL 5.1.12, the generated AUTO_INCREMENT value is
  written into the binary log, and the value will be used if
  available when executing the stored routine or trigger.
  
  Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
  (referenced as the buggy versions in the text below), when a
  statement that generates AUTO_INCREMENT value by the top
  statement was executed in the body of a SP, all statements in the
  SP after this statement would be treated as if they had generated
  AUTO_INCREMENT by the top statement.  When a statement that did
  not generate AUTO_INCREMENT value by the top statement but by a
  function/trigger called by it, an erroneous Intvar event would be
  associated with the statement, this erroneous INSERT_ID value
  wouldn't cause problem when replicating between masters and
  slaves of 5.0.x or prior 5.1.12, because the erroneous INSERT_ID
  value was not used when executing functions/triggers. But when
  replicating from buggy versions to 5.1.12 or newer, which will
  use the INSERT_ID value in functions/triggers, the erroneous
  value will be used, which would cause duplicate entry error and
  cause the slave to stop.
  
  The patch for 5.0 fixed it not to generate the erroneous Intvar
  event, another patch for 5.1 fixed it to ignore the SET INSERT_ID
  value when executing functions/triggers if it is replicating from
  a master of buggy versions.
[14 Mar 2008 3:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/43972

ChangeSet@1.2534, 2008-03-14 11:35:41+08:00, hezx@mail.hezx.com +10 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  For all 5.0 and up to 5.1.12 exclusive, when a stored routine or
  trigger caused an INSERT into an AUTO_INCREMENT column, the
  generated AUTO_INCREMENT value should not be written into the
  binary log, which means if a statement does not generate
  AUTO_INCREMENT value itself, there will be no Intvar event (SET
  INSERT_ID) associated with it even if one of the stored routine
  or trigger caused generation of such a value. And meanwhile, when
  executing a stored routine or trigger, it would ignore the
  INSERT_ID value even if there is a INSERT_ID value available set
  by a SET INSERT_ID statement.
  
  Starting from MySQL 5.1.12, the generated AUTO_INCREMENT value is
  written into the binary log, and the value will be used if
  available when executing the stored routine or trigger.
  
  Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
  (referenced as the buggy versions in the text below), when a
  statement that generates AUTO_INCREMENT value by the top
  statement was executed in the body of a SP, all statements in the
  SP after this statement would be treated as if they had generated
  AUTO_INCREMENT by the top statement.  When a statement that did
  not generate AUTO_INCREMENT value by the top statement but by a
  function/trigger called by it, an erroneous Intvar event would be
  associated with the statement, this erroneous INSERT_ID value
  wouldn't cause problem when replicating between masters and
  slaves of 5.0.x or prior 5.1.12, because the erroneous INSERT_ID
  value was not used when executing functions/triggers. But when
  replicating from buggy versions to 5.1.12 or newer, which will
  use the INSERT_ID value in functions/triggers, the erroneous
  value will be used, which would cause duplicate entry error and
  cause the slave to stop.
  
  The patch for 5.1 fixed it to ignore the SET INSERT_ID value when
  executing functions/triggers if it is replicating from a master
  of buggy versions, another patch for 5.0 fixed it not to generate
  the erroneous Intvar event.
[20 Mar 2008 14:00] Chuck Bell
Patches approved.
[27 Mar 2008 5:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44493

ChangeSet@1.2602, 2008-03-27 13:42:34+08:00, hezx@mail.hezx.com +6 -0
  post fix after push of BUG#33029
[27 Mar 2008 5:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44494

ChangeSet@1.2548, 2008-03-27 13:48:04+08:00, hezx@mail.hezx.com +2 -0
  post fix after push of bug#33029
[28 Mar 2008 14:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/44587

ChangeSet@1.2549, 2008-03-28 16:07:50+02:00, aelkin@mysql1000.(none) +1 -0
  BUG#33029
  
  refining a test for embedded env.
[31 Mar 2008 7:56] Bugs System
Pushed into 5.1.24-rc
[1 Apr 2008 14:11] Bugs System
Pushed into 5.0.60
[3 Apr 2008 13:02] Bugs System
Pushed into 6.0.5-alpha
[5 Apr 2008 7:34] Jon Stephens
Documented bugfix in the 5.0.60, 5.1.24, and 6.0.5 changelogs as follows:

        When a stored routine or trigger, running on a master that used any 
        MySQL 5.0 version or MySQL 5.1.11 or earlier, performed an insert on an 
        AUTO_INCREMENT column, the INSERT_ID value was not replicated correctly 
        to a slave running MySQL 5.1.12 or later (including any MySQL 6.0 
        release).

Also added item discussing this issue to "Replication and AUTO_INCREMENT" (in the 5.1 Manual, this is http://dev.mysql.com/doc/refman/5.1/en/replication-features-autoincid.html - also added the note to the 5.0 and 6.0 versions of this page, with slightly different wording to fit the version).