Mysql – In MySQL replication, why aren’t queries run on the slave when there was no USE statement on master

MySQLreplication

We have replication running from our master server to a slave. binlog_format is the default; mixed.

We have noticed something weird and are not sure whether it's a bug or intended behaviour. Certain statements are being ignored.

Ignored

DELETE FROM schema_name.table_name WHERE id = 123;

Works

USE schema_name;
DELETE FROM schema_name.table_name WHERE id = 123;

Also works!

USE different_schema_name;
DELETE FROM schema_name.table_name WHERE id = 123;

When I delete something without previously selecting a database on master, the query is ignored on the slave. But when I issue a USE schema_name on the master before the statement, even if it is a different schema, then it does run on the slave.

Regardless of the USE and whether they run on the slave, they always appear in the binary logs on master.

We noticed this as we have a script that tidies up old shopping carts that loops over a list of databases and issues DELETE FROM schema_name.carts WHERE created_at ..., but replication was breaking when an INSERT INTO carts SET cart_number = 'xxx' call took place on the slave; the cart number (unique column) no longer existed on master (after a successful check to validate such) but on the slave it still existed so it threw the error and broke replication.

Is this intended behaviour? What's the best method to ensure this kind of behaviour doesn't happen?

For reference and possibly meaningful, master is MariaDB (in a drbd cluster) and slave is Percona.

Best Answer

Please provide the relevant my.cnf entries.

If you have

binlog_do_db = B
-- Note:  That implies ignoring other dbs

and you do

USE A;  -- either explicitly, or due to the connection parameters
UPDATE B.tbl ...;

That UPDATE will be ignored.

Related Question