Never use SET GLOBAL sql_slave_skip_counter with a value higher than 1

Updated . Posted . Visible to the public.

If you have a replication error with MySQL and you know the "error" is okay (e.g. you've executed the same statement at the same time on 2 masters which sync each other), you can skip this error and continue with the replication without having to set up the slave from the ground up.

stop slave;
set global sql_slave_skip_counter = 1;
start slave;

But what if you have multiple errors which you want to skip? (e.g. you've executed multiple statement at the same time on 2 masters which sync each other)
Still do not use a value higher than 1 for sql_slave_skip_counter! Why? Because you can't be sure (without looking into the binlog directly) that your second statement is really the next one which would get executed by the MySQL server.

Example: you've executed drop database foobar; and drop database blubber; in a row on both MySQL server of a master/master setup.

It's not guaranteed that these two statements follow each other in the binlog. If the binlog e.g. looks like this:

# simplified, that's not how binlogs look like
Event 1000: drop database foobar;
Event 1001: update asdf.users set admin = 0 where id = 23;
Event 1002: drop database blubber;

and you execute:

stop slave;
set global sql_slave_skip_counter = 2;
start slave;

The Event 1000 and 1001 will get skipped which could lead to data inconsistency on this MySQL servers. Also, your replication would break again at Event 1003.

It's "sql_slave_skip_counter", not "sql_slave_skip_ERROR_counter". It skips statements/events, not errors.

There are even more problems with set global sql_slave_skip_counter which are explained here: https://www.percona.com/blog/2013/07/23/another-reason-why-sql_slave_skip_counter-is-bad-in-mysql/ Show archive.org snapshot

Last edit
Arne Hartherz
License
Source code in this card is licensed under the MIT License.
Posted by Kim Klotz to makandra dev (2017-02-01 16:25)