Posted over 5 years ago. Visible to the public.

Never use SET GLOBAL sql_slave_skip_counter with a value higher than 1

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.

Copy
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:

Copy
# 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:

Copy
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/ Archive

makandra has been working exclusively with Ruby on Rails since 2007. Our laser focus on a single technology has made us a leader in this space.

Owner of this card:

Avatar
Kim Jahn
Last edit:
over 4 years ago
by Marius Schuller
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Kim Jahn to makandra dev
This website uses short-lived cookies to improve usability.
Accept or learn more