| Bug #31552 | Replication breaks when deleting rows from out-of-sync table without PK | ||
|---|---|---|---|
| Submitted: | 12 Oct 2007 3:01 | Modified: | 6 Feb 2008 9:55 |
| Reporter: | Matthew Montgomery | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S2 (Serious) |
| Version: | 5.1.22-ndb-6.3.2-telco-log | OS: | Any |
| Assigned to: | Andrei Elkin | CPU Architecture: | Any |
| Tags: | bfsm_2007_10_18, idempotant slave mode, replication | ||
[17 Oct 2007 13:19]
Lars Thalmann
See also BUG#31609.
[9 Nov 2007 10:03]
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/37418 ChangeSet@1.2616, 2007-11-09 11:02:51+01:00, mats@kindahl-laptop.dnsalias.net +4 -0 BUG#31552 (Replication breaks when deleting rows from out-of-sync table without PK): Pushing test case for bug only. Bug already fixed as a result of the patch for BUG#19958.
[9 Nov 2007 10:03]
Mats Kindahl
Duplicate of BUG#19958
[19 Nov 2007 14:41]
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/38065 ChangeSet@1.2610, 2007-11-19 16:40:45+02:00, aelkin@koti.dsl.inet.fi +32 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported although the slave sql thread halted. Second, execution was different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which control the slave sql thread behaviour. The new variable allows further extension to mimic sql_mode session/server variable. To address the 4th, the new bug#32468 has to be fixed as it was staying in the way. changes due to bug#31552/31609 idemptency is not default any longer delete rows event on a table with foreign key constraint fails changes to link the command line option with the new global sys var.
[19 Nov 2007 15:59]
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/38075 ChangeSet@1.2610, 2007-11-19 17:58:26+02:00, aelkin@koti.dsl.inet.fi +34 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[19 Nov 2007 21:00]
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/38095 ChangeSet@1.2610, 2007-11-19 22:59:56+02:00, aelkin@koti.dsl.inet.fi +34 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[20 Nov 2007 13:16]
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/38131 ChangeSet@1.2610, 2007-11-20 15:15:49+02:00, aelkin@koti.dsl.inet.fi +34 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[26 Nov 2007 19:28]
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/38560 ChangeSet@1.2610, 2007-11-26 21:28:03+02:00, aelkin@koti.dsl.inet.fi +35 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[27 Nov 2007 10:34]
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/38609 ChangeSet@1.2610, 2007-11-27 12:34:23+02:00, aelkin@koti.dsl.inet.fi +35 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[27 Nov 2007 10:51]
Bugs System
Pushed into 5.1.23-rc
[27 Nov 2007 10:54]
Bugs System
Pushed into 6.0.4-alpha
[29 Nov 2007 17:23]
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/38859 ChangeSet@1.2610, 2007-11-29 19:22:47+02:00, aelkin@koti.dsl.inet.fi +35 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[30 Nov 2007 11:49]
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/38951 ChangeSet@1.2610, 2007-11-30 13:49:33+02:00, aelkin@koti.dsl.inet.fi +35 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[5 Dec 2007 11: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/39306 ChangeSet@1.2610, 2007-12-05 13:09:59+02:00, aelkin@koti.dsl.inet.fi +35 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[7 Dec 2007 11:08]
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/39512 ChangeSet@1.2610, 2007-12-07 13:07:58+02:00, aelkin@koti.dsl.inet.fi +31 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idemptency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotant applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[7 Dec 2007 12:46]
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/39519 ChangeSet@1.2610, 2007-12-07 14:46:05+02:00, aelkin@koti.dsl.inet.fi +29 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idempotency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotent applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[12 Dec 2007 10:15]
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/39765 ChangeSet@1.2610, 2007-12-12 12:14:59+02:00, aelkin@koti.dsl.inet.fi +30 -0 Bug#31552 Replication breaks when deleting rows from out-of-sync table without PK Bug#31609 Not all RBR slave errors reported as errors bug#32468 delete rows event on a table with foreign key constraint fails The first two bugs comprise idempotency issues. First, there was no error code reported under conditions of the bug description although the slave sql thread halted. Second, executions were different with and without presence of prim key in the table. Third, there was no way to instruct the slave whether to ignore an error and skip to the following event or to halt. Fourth, there are handler errors which might happen due to idempotent applying of binlog but those were not listed among the "idempotent" error list. All the named issues are addressed. Wrt to the 3rd, there is the new global system variable, changeble at run time, which controls the slave sql thread behaviour. The new variable allows further extensions to mimic the sql_mode session/global variable. To address the 4th, the new bug#32468 had to be fixed as it was staying in the way.
[12 Dec 2007 19:13]
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/39804 ChangeSet@1.2653, 2007-12-12 20:12:29+01:00, aelkin@dl145j.mysql.com +8 -0 bug#31552 manual merge and post-make-test-run changes.
[13 Dec 2007 8:32]
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/39861 ChangeSet@1.2654, 2007-12-13 09:32:23+01:00, aelkin@dl145j.mysql.com +1 -0 bug#31552 fixing a compilation issue in the debugless build.
[5 Feb 2008 13:03]
Bugs System
Pushed into 5.1.24-rc
[5 Feb 2008 13:08]
Bugs System
Pushed into 6.0.5-alpha
[6 Feb 2008 9:55]
Jon Stephens
Documented in the 5.1.24 and 6.0.5 changelogs as follows:
When using row-based replication, the slave stopped when
attempting to delete non-existent rows from a slave table
without a primary key. In addition, no error was reported when
this occurred.
[6 Mar 2008 9:50]
Jon Stephens
Also documented for 5.1.23-ndb-6.2.14.
[30 Mar 2008 20:21]
Jon Stephens
Also documented fix for 5.1.23-ndb-6.3.11.
Description: Hello, Using row based replication, the slave stops when attempting to delete non-existent rows from a slave table without a primary key. Last_Errno: 0 Last_Error: Error in Delete_rows event: error during transaction execution on table test.t1. In earlier versions (5.1.14) Last_Errno: 137 was presented. There is no error presented when deleting non-existent rows over replication when the table has a primary key. This is storage engine independent. Tested against MyISAM, InnoDB and NDB How to repeat: Configure master/slave RBR replication. master> reset master; slave> change master to master_host='matt-desktop', master_user='repl', master_port=3351, master_password='repl', master_log_file='matt-desktop-bin.000001', master_log_pos=0; slave> slave start; slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: matt-desktop Master_User: repl Master_Port: 3351 Connect_Retry: 60 Master_Log_File: matt-desktop-bin.000001 Read_Master_Log_Pos: 106 Relay_Log_File: sandbox-relay-bin.000002 Relay_Log_Pos: 258 Relay_Master_Log_File: matt-desktop-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... master> use test; master> create table t1 (a varchar(20), b varchar(20)) engine=myisam; master> insert into t1 (a,b) values('abc','def'),('ghi','jkl'); slave> delete from t1 where a='abc'; master> delete from t1; slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: matt-desktop Master_User: repl Master_Port: 3351 Connect_Retry: 60 Master_Log_File: matt-desktop-bin.000001 Read_Master_Log_Pos: 415 Relay_Log_File: sandbox-relay-bin.000002 Relay_Log_Pos: 474 Relay_Master_Log_File: matt-desktop-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: No ... Last_Errno: 0 Last_Error: Error in Delete_rows event: error during transaction execution on table test.t1. ... slave> select * from t1; +------+------+ | a | b | +------+------+ | ghi | jkl | +------+------+ 1 row in set (0.00 sec) Compare table with PK... master> reset master; slave> slave stop; slave> change master to master_host='matt-desktop', master_user='repl', master_port=3351, master_password='repl', master_log_file='matt-desktop-bin.000001', master_log_pos=0; slave> slave start; master> create table t2 (a int auto_increment primary key, b varchar(20), c varchar(20)) engine=myisam; master> insert into t2 (b,c) values('abc','def'),('ghi','jkl'); slave> delete from t2 where b='abc'; master> delete from t2; slave> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: matt-desktop Master_User: repl Master_Port: 3351 Connect_Retry: 60 Master_Log_File: matt-desktop-bin.000001 Read_Master_Log_Pos: 467 Relay_Log_File: sandbox-relay-bin.000002 Relay_Log_Pos: 619 Relay_Master_Log_File: matt-desktop-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes ... Last_Errno: 0 Last_Error: ... slave> select * from t2; Empty set (0.00 sec) Suggested fix: I'd like to see some error code returned on any delete which appears in the binlog but does not succeed in the storage engine. Under 5.1.14 "MySQL error code 137: No more records (read after end of file)" appears during this operation on tables without a PK. This gives me a better indication that the master and slave are out of sync. Though a more intuitive error message would be better. If I want to ignore these differences between the master & slave I can skip the event and/or add --slave-skip-errors=137 to the slave and carry on replication at my own risk. At a minimum, errors on missing slave rows deletions should behave the same weather or not a PK exists on the table.