| 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: | |
| Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
| Version: | 5.0.52 / 5.1.22 | OS: | Any |
| Assigned to: | Zhenxing He | CPU Architecture: | Any |
[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).
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