Bug #80478 Replication is failing as CASCADE clause is not working as expected in 5.7
Submitted: 23 Feb 2016 12:52 Modified: 24 Feb 2016 0:01
Reporter: Ramesh Sivaraman (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.7.11 OS:CentOS
Assigned to: CPU Architecture:Any

[23 Feb 2016 12:52] Ramesh Sivaraman
Description:
Replication is failing as DELETE CASCADE clause is not working as expected in 5.7. Master is not detecting cascade clause in 5.7. But in 5.6 it is working

How to repeat:
Testcase

CREATE TABLE t0 (f0 INT PRIMARY KEY);
CREATE TABLE t1 (f1 INT PRIMARY KEY,f0 INTEGER,FOREIGN KEY (f0) REFERENCES t0(f0) ON DELETE CASCADE);
INSERT INTO t0 VALUES (0), (1);
INSERT INTO t1 VALUES (0, 0);
INSERT INTO t1 VALUES (1, 0);
DELETE t0.*,t1.*  FROM t0, t1 WHERE t0.f0 = 0 AND t1.f1 = 0;

Slave error

               Last_SQL_Error: Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 1910

Relay log info

SET TIMESTAMP=1456227510/*!*/;
BEGIN
/*!*/;
# at 1948
#160223  6:38:30 server id 101  end_log_pos 1780 CRC32 0x9148e0c0       Table_map: `test`.`t0` mapped to number 108
# at 1993
#160223  6:38:30 server id 101  end_log_pos 1826 CRC32 0x09c373f5       Table_map: `test`.`t1` mapped to number 109
# at 2039
#160223  6:38:30 server id 101  end_log_pos 1866 CRC32 0x6ae5736f       Delete_rows: table id 108
# at 2079
#160223  6:38:30 server id 101  end_log_pos 1910 CRC32 0x712c942a       Delete_rows: table id 109 flags: STMT_END_F
### DELETE FROM `test`.`t0`
### WHERE
###   @1=0
### DELETE FROM `test`.`t1`
### WHERE
###   @1=0
###   @2=0
# at 2123
#160223  6:38:30 server id 101  end_log_pos 1941 CRC32 0x17f22080       Xid = 50
COMMIT/*!*/;
# at 2154
#160223  6:46:35 server id 102  end_log_pos 2177 CRC32 0xe1cba2fa       Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[24 Feb 2016 0:01] MySQL Verification Team
Thank you for the bug report.

c:\dbs>5.7s\bin\mysql -uroot -pmiguel --port=3307 --prompt="slave > "
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.12 Source distribution PULL: 2016-FEB-19

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

slave > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 528
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 87911824-d9bb-11e5-a482-0a0027000000
             Master_Info_File: c:\dbs\5.7s\data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

slave > show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repli
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 1848
               Relay_Log_File: slave-relay-bin.000003
                Relay_Log_Pos: 1670
        Relay_Master_Log_File: master-bin.000002
             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: 1032
                   Last_Error: Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's m
aster log master-bin.000002, end_log_pos 1817
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1503
              Relay_Log_Space: 2222
              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: 1032
               Last_SQL_Error: Could not execute Delete_rows event on table test.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's m
aster log master-bin.000002, end_log_pos 1817
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: 87911824-d9bb-11e5-a482-0a0027000000
             Master_Info_File: c:\dbs\5.7s\data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 160223 20:58:08
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

slave >