| 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 | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.7.11 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[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 >
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*/;