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
Triage: Needs Triage: D2 (Serious)

[20 Jun 2014 13:23] Van Stokes
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.
[20 Jun 2014 13:27] Van Stokes
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.
[20 Jun 2014 13:29] Van Stokes
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?
[17 Jul 2014 19:03] Sveta Smirnova
Thank you for the report.

Verified as described.