MySQL Bugs: #73065: GTID slave using replicate-do-db attempting to execute DDL, cannot skip

Bug #73065	GTID slave using replicate-do-db attempting to execute DDL, cannot skip
Submitted:	20 Jun 2014 13:23	Modified:	17 Jul 2014 19:03
Reporter:	Van Stokes	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.6.19	OS:	Any (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	DDL, GTID, replicate-do-db, replication

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.

BTW, I also executed this to try and skip the transaction (incremented past 12362044 - the truncate statement) :

STOP SLAVE;
SET GTID_NEXT="708bb615-d393-11e3-a682-003048c3ab22:12362045";
BEGIN;
COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;

but that didn't work either.

Restarting the SQL server got passed the error.

Why couldn't this be done while the server was running? Why did we have to restart the MySQL server?

Thank you for the report.

Verified as described.