1

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.

Improve this question

1 Answer 1

Reset to default
0

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.

Improve this answer
2
  • In our case both tables in question are replicated, and not ignored. Your example seems to be the opposite way round - when we have USE, even a different schema, it works. But without, even when specifying the schema in-line, it doesn’t. I’ll post relevant config tomorrow when I can get on the server though for a closer look.
    – Leonard Challis
    Commented Dec 9, 2018 at 0:32
  • @LeonardChallis - You are right; my example was backward.
    – Rick James
    Commented Dec 9, 2018 at 1:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.