Bug #39393 slave-skip-errors does not work when using ROW based replication
Submitted: 11 Sep 2008 13:13 Modified: 7 May 2009 13:15
Reporter: Cyril SCETBON Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.1.26, 5.1, 6.0 bzr OS:Linux (debian etch)
Assigned to: Alfranio Junior CPU Architecture:Any

[11 Sep 2008 13:13] Cyril SCETBON
Description:
When using SBR slave-skip-errors works but not in RBR mode

How to repeat:
on the master :

master [localhost] {msandbox} (test) > SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

master [localhost] {msandbox} (test) > delete from toto where id in (103072);
Query OK, 1 row affected (0.06 sec)

master [localhost] {msandbox} (test) > delete from toto where id in (359181);
Query OK, 1 row affected (0.06 sec)

master [localhost] {msandbox} (test) > delete from toto where id in (545417,43442,430833);
Query OK, 3 rows affected (0.03 sec)

master [localhost] {msandbox} (test) > select count(*) from toto;
+----------+
| count(*) |
+----------+
|     4812 |
+----------+
1 row in set (0.01 sec)

master [localhost] {msandbox} (test) > Bye
MySQL:/usr/local/mysql/sandbox_dir# ../5.1.26/bin/mysql --defaults-file=/usr/local/mysql/sandbox_dir/rsandbox_5_1_26/master/my.sandbox.cnf test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.26-rc-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

master [localhost] {msandbox} (test) > select count(*) from toto;
+----------+
| count(*) |
+----------+
|     4812 |
+----------+
1 row in set (0.00 sec)

master [localhost] {msandbox} (test) > insert into toto values(103072);
Query OK, 1 row affected (0.00 sec)

on the slave :

slave1 [localhost] {msandbox} (test) > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: msandbox
                  Master_Port: 21341
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 320
               Relay_Log_File: mysql_sandbox21342-relay-bin.000013
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table test.toto; Duplicate entry '103072' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000005, end_log_pos 251
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 106
              Relay_Log_Space: 676
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table test.toto; Duplicate entry '103072' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000005, end_log_pos 251
1 row in set (0.01 sec)

slave1 [localhost] {msandbox} (test) > show global variables like 'slave_skip_errors';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| slave_skip_errors | ALL   | 
+-------------------+-------+
1 row in set (0.01 sec)
[11 Sep 2008 18:57] Sveta Smirnova
Thank you for the report.

I can not repeat described behavior. Please provide output of SHOW CREATE TABLE toto, configuration files for both master and slave and slave error log.
[11 Sep 2008 19:29] Sveta Smirnova
Thanks again for the report.

No feedback needed. Verified as described using following test case.

$cat rpl_bug39393-slave.opt
--slave_skip_errors=all

$cat rpl_bug39393.test
--source include/master-slave.inc

set binlog_format='row';

create table t1(id int not null primary key);

insert into t1 values(1);
SET SQL_LOG_BIN=0;
delete from t1;
SET SQL_LOG_BIN=1;
insert into t1 values(1);

flush logs;

sleep 1;

connection slave;

--vertical_results
show slave status;

select * from t1;
[29 Oct 2008 21:08] Lars Thalmann
There is an idempotency mode one can use as a work-around.

I think RBR should support the slave skip errors, 
unless Mats has some more input on this.
[20 Mar 2009 12: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/69910

2802 Alfranio Correia	2009-03-20
      BUG#39393 slave-skip-errors does not work when using ROW based replication
                  
      RBR was not considering the option --slave-skip-errors.
                  
      To fix the problem, we are reporting the ignored ERROR(s) as warnings thus avoiding 
      stopping the SQL Thread. Besides, it fixes the output of "SHOW VARIABLES LIKE 
      'slave_skip_errors'" which was showing nothing when the value "all" was assigned 
      to --slave-skip-errors.
      
      @include/my_sys.h
      @mysys/my_error.c
        added functions to create messages in a buffer without printing them out.
      @sql/ha_ndbcluster.h
      @sql/ha_ndbcluster.cc
        refactored its interface so specific errors in the cluster are easily
        reported through the handler.
      @sql/ha_partition.cc
      @sql/ha_partition.h
      @sql/partition_info.cc
      @sql/partition_info.h
        refactored its interface so specific errors in the cluster engine 
        are easily reported through the handler.
      @sql/handler.h
      @sql/handler.cc
        refactored its interface so errors can be easily checked and reported
        through its interfaces.
      @sql/log_event.cc
        skipped rbr errors when the option skip-slave-errors is set.
      @sql/slave.cc
        fixed the output of for SHOW VARIABLES LIKE 'slave_skip_errors'".
[28 Mar 2009 11: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/70766

2802 Alfranio Correia	2009-03-28
      BUG#39393 slave-skip-errors does not work when using ROW based replication
                        
      RBR was not considering the option --slave-skip-errors.
                        
      To fix the problem, we are reporting the ignored ERROR(s) as warnings thus avoiding 
      stopping the SQL Thread. Besides, it fixes the output of "SHOW VARIABLES LIKE 
      'slave_skip_errors'" which was showing nothing when the value "all" was assigned 
      to --slave-skip-errors.
            
      @sql/log_event.cc
        skipped rbr errors when the option skip-slave-errors is set.
      @sql/slave.cc
        fixed the output of for SHOW VARIABLES LIKE 'slave_skip_errors'"
      @test-cases
        fixed the output of rpl.rpl_idempotency
        create a test case rpl_skiperrors_rbr
[31 Mar 2009 7: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/70859

2840 Alfranio Correia	2009-03-31
      BUG#39393 slave-skip-errors does not work when using ROW based replication
                              
      RBR was not considering the option --slave-skip-errors.
                              
      To fix the problem, we are reporting the ignored ERROR(s) as warnings thus avoiding 
      stopping the SQL Thread. Besides, it fixes the output of "SHOW VARIABLES LIKE 
      'slave_skip_errors'" which was showing nothing when the value "all" was assigned 
      to --slave-skip-errors.
                  
      @sql/log_event.cc
        skipped rbr errors when the option skip-slave-errors is set.
      @sql/slave.cc
        fixed the output of for SHOW VARIABLES LIKE 'slave_skip_errors'"
      @test-cases
        fixed the output of rpl.rpl_idempotency
        updated the test case rpl_skip_error
[5 Apr 2009 12: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/71398

2840 Alfranio Correia	2009-04-05
      BUG#39393 slave-skip-errors does not work when using ROW based replication
                                    
      RBR was not considering the option --slave-skip-errors.
                                    
      To fix the problem, we are reporting the ignored ERROR(s) as warnings thus avoiding 
      stopping the SQL Thread. Besides, it fixes the output of "SHOW VARIABLES LIKE 
      'slave_skip_errors'" which was showing nothing when the value "all" was assigned 
      to --slave-skip-errors.
                        
      @sql/log_event.cc
        skipped rbr errors when the option skip-slave-errors is set.
      @sql/slave.cc
        fixed the output of for SHOW VARIABLES LIKE 'slave_skip_errors'"
      @test-cases
        fixed the output of rpl.rpl_idempotency
        updated the test case rpl_skip_error
[7 Apr 2009 9:39] Simone Pieri
I've applied 69910 patch, now I must apply 70766, 70859 and 71398 or only 71398 ?

If I redo all, I must apply only 71398 ?

Thanks a lot

Simone Pieri
[8 Apr 2009 21:02] 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/71682

2856 Alfranio Correia	2009-04-08
      BUG#39393. Post-fix for test rpl_skip_error.
      
      The result set for multi-row statements is not the same between STMT and
      RBR and among different versions. Thus to avoid test failures, we are not
      printing out such result sets. Note, however, that this does not have
      impact on coverage and accuracy since the execution is able to continue
      without further issues when an error is found on the master and such error
      is set to be skipped.
[5 May 2009 19:38] Bugs System
Pushed into 5.1.35 (revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (version source revid:davi.arnaut@sun.com-20090505190206-9xmh7dlc6kom8exp) (merge vers: 5.1.35) (pib:6)
[6 May 2009 14:09] Bugs System
Pushed into 6.0.12-alpha (revid:svoj@sun.com-20090506125450-yokcmvqf2g7jhujq) (version source revid:zhenxing.he@sun.com-20090409063157-pnao43h5tde42c7q) (merge vers: 6.0.11-alpha) (pib:6)
[7 May 2009 13:15] Jon Stephens
Documented bugfix in the 5.1.35 and 6.0.12 changelogs as follows:

        The --slave-skip errors option had no effect when using row-based
        logging format.

Also noted in replication-options section of 5.1 and 6.0 Manual.
[15 Jun 2009 8:25] Bugs System
Pushed into 5.1.35-ndb-6.3.26 (revid:jonas@mysql.com-20090615074202-0r5r2jmi83tww6sf) (version source revid:jonas@mysql.com-20090615070837-9pccutgc7repvb4d) (merge vers: 5.1.35-ndb-6.3.26) (pib:6)
[15 Jun 2009 9:04] Bugs System
Pushed into 5.1.35-ndb-7.0.7 (revid:jonas@mysql.com-20090615074335-9hcltksp5cu5fucn) (version source revid:jonas@mysql.com-20090615072714-rmfkvrbbipd9r32c) (merge vers: 5.1.35-ndb-7.0.7) (pib:6)
[15 Jun 2009 9:45] Bugs System
Pushed into 5.1.35-ndb-6.2.19 (revid:jonas@mysql.com-20090615061520-sq7ds4yw299ggugm) (version source revid:jonas@mysql.com-20090615054654-ebgpz7elwu1xj36j) (merge vers: 5.1.35-ndb-6.2.19) (pib:6)