MySQL – DROP TABLE Replicates to Slaves but Not with USE Command

drop-tableMySQLmysql-5.6replication

I am currently looking to setup automatic failover for my MySQL 5.6.21 Master on CentOS 6.6. I have mysqlfailover set up in my lab, and it is working well enough. My question is not about that, but rather, a peculiarity I noticed while testing. After a failover, I need to drop a table on my master to resume normal operation of mysqlfailover. The table is mysql.failover_console. Perhaps this is not the best practice, but that is not the issue at question. I run the following command on my master:

DROP TABLE mysql.failover_console;

I am ignoring mysql in the binlog and on the slave's relay log. To my surprise, my slave (MySQL 5.6.21 on CentOS 6.6) stop replicating with the following message:

Last_SQL_Errno: 1051
Last_SQL_Error: Error 'Unknown table 'mysql.failover_console'' on query. Default database: ''. Query: 'DROP TABLE `mysql`.`failover_console` /* generated by server */'

The peculiar part is that the following two commands do not cause any issues and perform what is functionally the same task.

USE mysql;
DROP TABLE mysql.failover_console;

Here is the output of SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysqld-bin.000002
         Position: 373
     Binlog_Do_DB:
 Binlog_Ignore_DB: mysql
Executed_Gtid_Set: f9b71d72-70f9-11e4-a8e4-00155d022404:1-9
1 row in set (0.00 sec)

Here is the output of SHOW SLAVE STATUS\G after the failed command.

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.3.2
                  Master_User: replicant
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000002
          Read_Master_Log_Pos: 373
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 403
        Relay_Master_Log_File: mysqld-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1051
                   Last_Error: Error 'Unknown table 'mysql.failover_console'' on query. Default database: ''. Query: 'DROP TABLE `mysql`.`failover_console` /* generated by server */'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 191
              Relay_Log_Space: 2656
              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: 1051
               Last_SQL_Error: Error 'Unknown table 'mysql.failover_console'' on query. Default database: ''. Query: 'DROP TABLE `mysql`.`failover_console` /* generated by server */'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: f9b71d72-70f9-11e4-a8e4-00155d022404
             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: 141211 11:54:53
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: f9b71d72-70f9-11e4-a8e4-00155d022404:1-9
            Executed_Gtid_Set: d5794f6a-7fd6-11e4-89cf-00155d022408:1,
f9b71d72-70f9-11e4-a8e4-00155d022404:1-8
                Auto_Position: 1
1 row in set (0.00 sec)

So why does the USE command seem to prevent replication as designed, but running a single command does not allow MySQL to ignore?

Thanks.

Best Answer

Replication filtering rules will only work on the situations where you do 

USE mysql;
DROP TABLE failover_console;

When you do DROP TABLE mysql.failover_console;, that will slip through the cracks. Why?

The problem stems from you using Binlog_Ignore_DB: mysql. This looks for mysql to the the default database. Still, you explicitly use mysql. in front of the table name failover_console. Please note how this happens as explained in the MySQL Documentation:

When using statement-based logging, the following example does not work as you might expect. Suppose that the server is started with --binlog-ignore-db=sales and you issue the following statements:

USE prices;
UPDATE sales.january SET amount=amount+1000;

The UPDATE statement is logged in such a case because --binlog-ignore-db applies only to the default database (determined by the USE statement). Because the sales database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based logging, the UPDATE statement's effects are not written to the binary log, which means that no changes to the sales.january table are logged; in this instance, --binlog-ignore-db=sales causes all changes made to tables in the master's copy of the sales database to be ignored for purposes of binary logging.

Next time, you should do one of two things:

Either

USE mysql
DROP TABLE failover_console;

or

DROP TABLE IF EXISTS mysql.failover_console;

GIVE IT A TRY !!!

Related Question