We have replication running from our master server to a slave.
binlog_format is the default;
We have noticed something weird and are not sure whether it's a bug or intended behaviour. Certain statements are being ignored.
DELETE FROM schema_name.table_name WHERE id = 123;
USE schema_name; DELETE FROM schema_name.table_name WHERE id = 123;
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.