Bug #73065 GTID slave using replicate-do-db attempting to execute DDL, cannot skip
Description:
OS: Windows Server 2008 x64
MySQL Server: 5.6.19 x64 (MySQL distro)
Slave Error No: 0
Slave Error Message:
Query caused different errors on master and slave. Error on master: message (format)='Cannot truncate a table referenced in a foreign key constraint (%.192s)' error code=1701 ; Error on slave: actual message='no error', error code=0. Default database: 'eci_tracking_test'. Query: 'truncate messages'
This server is an end-point slave. It replicates only certain databases from it's master. The slave is attempting to execute a DDL statement for a database that it should be ignoring. Furthermore, I cannot use standard GTID practice to skip the transaction.
The my.ini:
gtid-mode = on
enforce-gtid-consistency = true
replicate-do-db = eci_edi_eculine
replicate-do-db = eci_elite_prod
replicate-do-db = oti_elite_prod
How to repeat:
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.27.101.247
Master_User: rs_1101
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000352
Read_Master_Log_Pos: 611642767
Relay_Log_File: slave-relay-bin.000557
Relay_Log_Pos: 2518005
Relay_Master_Log_File: master-bin.000344
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB: eci_edi_eculine,eci_elite_prod,oti_elite_prod
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error: Query caused different errors on master and slave. Error on master: message (format)='Cannot truncate a table referenced in a foreign key constraint (%.192s)' error code=1701 ; Error on slave: actual message='no error', error code=0. Default database: 'eci_tracking_test'. Query: 'truncate messages'
Skip_Counter: 0
Exec_Master_Log_Pos: 751622290
Relay_Log_Space: 8499913596
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: 0
Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Cannot truncate a table referenced in a foreign key constraint (%.192s)' error code=1701 ; Error on slave: actual message='no error', error code=0. Default database: 'eci_tracking_test'. Query: 'truncate messages'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1002
Master_UUID: 708bb615-d393-11e3-a682-003048c3ab22
Master_Info_File: mysql.slave_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: 140620 09:06:15
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 69cf02cd-1731-11e3-9a19-002590854928:51337729-53119964,
708bb615-d393-11e3-a682-003048c3ab22:12359097-12646949,
819c985c-d384-11e3-a621-00259002979a:1077016-1112043,
9204e764-d379-11e3-a5d9-0013726268ea:2403-2404
Executed_Gtid_Set: 22f1e449-03ba-11e3-9b29-003048f2f703:1-53959,
69cf02cd-1731-11e3-9a19-002590854928:1-51337792,
708bb615-d393-11e3-a682-003048c3ab22:1-12362045,
819c985c-d384-11e3-a621-00259002979a:1-1077041,
9204e764-d379-11e3-a5d9-0013726268ea:1-2402
Auto_Position: 1
1 row in set (0.07 sec)
mysql> SHOW relaylog EVENTS IN 'slave-relay-bin.000557' FROM 2518005 LIMIT 3\G
*************************** 1. row ***************************
Log_name: slave-relay-bin.000557
Pos: 2518005
Event_type: Gtid
Server_id: 1002
End_log_pos: 751622338
Info: SET @@SESSION.GTID_NEXT= '708bb615-d393-11e3-a682-003048c3ab22:12362044'
*************************** 2. row ***************************
Log_name: slave-relay-bin.000557
Pos: 2518053
Event_type: Query
Server_id: 1002
End_log_pos: 751622442
Info: use `eci_tracking_test`; truncate messages
*************************** 3. row ***************************
Log_name: slave-relay-bin.000557
Pos: 2518157
Event_type: Gtid
Server_id: 1002
End_log_pos: 751622490
Info: SET @@SESSION.GTID_NEXT= '708bb615-d393-11e3-a682-003048c3ab22:12362045'
3 rows in set (0.07 sec)
Suggested fix:
I executed this to try and skip the transaction:
STOP SLAVE;
SET GTID_NEXT="708bb615-d393-11e3-a682-003048c3ab22:12362044";
BEGIN;
COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;
however that does not work. I cannot seem to get past the
use `eci_tracking_test`; truncate messages
statement.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: OS: Windows Server 2008 x64 MySQL Server: 5.6.19 x64 (MySQL distro) Slave Error No: 0 Slave Error Message: Query caused different errors on master and slave. Error on master: message (format)='Cannot truncate a table referenced in a foreign key constraint (%.192s)' error code=1701 ; Error on slave: actual message='no error', error code=0. Default database: 'eci_tracking_test'. Query: 'truncate messages' This server is an end-point slave. It replicates only certain databases from it's master. The slave is attempting to execute a DDL statement for a database that it should be ignoring. Furthermore, I cannot use standard GTID practice to skip the transaction. The my.ini: gtid-mode = on enforce-gtid-consistency = true replicate-do-db = eci_edi_eculine replicate-do-db = eci_elite_prod replicate-do-db = oti_elite_prod How to repeat: mysql> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.27.101.247 Master_User: rs_1101 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000352 Read_Master_Log_Pos: 611642767 Relay_Log_File: slave-relay-bin.000557 Relay_Log_Pos: 2518005 Relay_Master_Log_File: master-bin.000344 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: eci_edi_eculine,eci_elite_prod,oti_elite_prod Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Query caused different errors on master and slave. Error on master: message (format)='Cannot truncate a table referenced in a foreign key constraint (%.192s)' error code=1701 ; Error on slave: actual message='no error', error code=0. Default database: 'eci_tracking_test'. Query: 'truncate messages' Skip_Counter: 0 Exec_Master_Log_Pos: 751622290 Relay_Log_Space: 8499913596 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: 0 Last_SQL_Error: Query caused different errors on master and slave. Error on master: message (format)='Cannot truncate a table referenced in a foreign key constraint (%.192s)' error code=1701 ; Error on slave: actual message='no error', error code=0. Default database: 'eci_tracking_test'. Query: 'truncate messages' Replicate_Ignore_Server_Ids: Master_Server_Id: 1002 Master_UUID: 708bb615-d393-11e3-a682-003048c3ab22 Master_Info_File: mysql.slave_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: 140620 09:06:15 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 69cf02cd-1731-11e3-9a19-002590854928:51337729-53119964, 708bb615-d393-11e3-a682-003048c3ab22:12359097-12646949, 819c985c-d384-11e3-a621-00259002979a:1077016-1112043, 9204e764-d379-11e3-a5d9-0013726268ea:2403-2404 Executed_Gtid_Set: 22f1e449-03ba-11e3-9b29-003048f2f703:1-53959, 69cf02cd-1731-11e3-9a19-002590854928:1-51337792, 708bb615-d393-11e3-a682-003048c3ab22:1-12362045, 819c985c-d384-11e3-a621-00259002979a:1-1077041, 9204e764-d379-11e3-a5d9-0013726268ea:1-2402 Auto_Position: 1 1 row in set (0.07 sec) mysql> SHOW relaylog EVENTS IN 'slave-relay-bin.000557' FROM 2518005 LIMIT 3\G *************************** 1. row *************************** Log_name: slave-relay-bin.000557 Pos: 2518005 Event_type: Gtid Server_id: 1002 End_log_pos: 751622338 Info: SET @@SESSION.GTID_NEXT= '708bb615-d393-11e3-a682-003048c3ab22:12362044' *************************** 2. row *************************** Log_name: slave-relay-bin.000557 Pos: 2518053 Event_type: Query Server_id: 1002 End_log_pos: 751622442 Info: use `eci_tracking_test`; truncate messages *************************** 3. row *************************** Log_name: slave-relay-bin.000557 Pos: 2518157 Event_type: Gtid Server_id: 1002 End_log_pos: 751622490 Info: SET @@SESSION.GTID_NEXT= '708bb615-d393-11e3-a682-003048c3ab22:12362045' 3 rows in set (0.07 sec) Suggested fix: I executed this to try and skip the transaction: STOP SLAVE; SET GTID_NEXT="708bb615-d393-11e3-a682-003048c3ab22:12362044"; BEGIN; COMMIT; SET GTID_NEXT="AUTOMATIC"; START SLAVE; however that does not work. I cannot seem to get past the use `eci_tracking_test`; truncate messages statement.