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:
None 
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

[12 Oct 2007 3:01] Matthew Montgomery
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.
[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.