Understanding and Testing sql_slave_skip_counter in MySQL Replication

The author, a MySQL DBA, encountered a replication failure caused by improper use of the sql_slave_skip_counter parameter, which can lead to data inconsistency between master and slave.

Background

MySQL replication is logical; the slave applies events from the relay log. The sql_slave_skip_counter variable allows skipping a number of events, but incorrect usage may cause mismatched data.

Common Errors

write_rows : Duplicate entry (error 1062) – primary‑key conflict.

update_rows : Can't find record (error 1032) – missing row for update.

Parameter Explanation

According to the official documentation, sql_slave_skip_counter skips events in units of event groups . For transactional tables, one event group equals one transaction; for non‑transactional tables, one event group equals one SQL statement. An event group may contain multiple events.

References: MySQL 5.6 , MySQL 8.0

Testing the Impact of sql_slave_skip_counter

Using MySQL 8.0.23 with ROW‑based logging, the following steps were performed.

2.1 Prepare Data

Configure master and slave with the following settings:

-- Close GTID
mysql> show global variables like '%gtid%';
+----------------------------------------+----------+
| Variable_name                           | Value    |
+----------------------------------------+----------+
| binlog_gtid_simple_recovery             | OFF      |
| enforce_gtid_consistency                | OFF      |
| gtid_executed                           |          |
| gtid_mode                               | OFF      |
| gtid_owned                              |          |
| gtid_purged                              |          |
| simplified_binlog_gtid_recovery          | OFF      |
+----------------------------------------+----------+

-- Disable read_only
mysql> show global variables like 'read_only';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| read_only        | OFF   |
+------------------+-------+

-- Set ROW format
mysql> show global variables like 'binlog_format';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| binlog_format | ROW      |
+---------------+----------+

Create one InnoDB (transactional) table and one MyISAM (non‑transactional) table, then insert a row with id=1 into each.

-- Create test tables on master
create table replica_innodb (id int, msg char(32)) engine=innodb;
create table replica_myisam (id int, msg char(32)) engine=myisam;

-- Insert initial rows
insert into replica_innodb values(1,'innodb_001');
insert into replica_myisam values(1,'myisam_001');

2.2 Simulate a Failure and Skip It

1) On the slave, delete the row from the non‑transactional table:

set sql_log_bin=0;
delete from replica_myisam where id=1;
set sql_log_bin=1;

2) On the master, update the same row (which no longer exists on the slave) and insert additional rows via a stored procedure:

-- Stored procedure to insert rows 2‑5
delimiter $$
create procedure p_insert()
begin
  declare i int;
  set i = 2;
  while i < 6 do
    insert into replica_innodb values (i, concat('innodb_', repeat('0',2), i));
    insert into replica_myisam values (i, concat('myisam_', repeat('0',2), i));
    set i = i + 1;
  end while;
end$$
delimiter ;

-- Update non‑transactional row and call procedure
begin;
update replica_myisam set msg='my01' where id=1;  -- triggers error 1032 on slave
call p_insert();
commit;

3) Check slave status – the SQL thread stops with error 1032.

Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table testdb.replica_myisam; Can't find record in 'replica_myisam'; ...

4) Skip the error by setting a large counter and restarting the SQL thread:

set global sql_slave_skip_counter=10000;
start slave sql_thread;

After the skip, SHOW SLAVE STATUS shows both IO and SQL threads running and Skip_Counter decreasing as replication proceeds.

Event Types Observed

Under ROW format, each transaction generates at least four events: Rows_query (when enabled), Table_map, Write_rows/Update_rows, and Query(begin) / Xid(commit). The table below summarizes the events and their purposes.

Event

Purpose

Query

Records transaction metadata, DDL, BEGIN, etc.

Rows_query

Logs DML statements (INSERT, DELETE, UPDATE).

Table_map

Maps event to database/table identifiers.

Write_rows

Contains data for INSERT operations.

Update_rows

Contains before/after images for UPDATE operations.

Xid

Marks the end of a transaction with a unique transaction ID.

2.3 Resetting sql_slave_skip_counter to Zero

To avoid lingering effects, the counter should be cleared after the error is skipped. Three methods are described:

Restart replication (effective in MySQL 5.7/8.0, not in 5.6):

stop slave;
start slave;
show global variables like 'sql_slave_skip_counter'\G
Variable_name: sql_slave_skip_counter
Value: 0

Manually set to zero while the slave is stopped:

stop slave;
set global sql_slave_skip_counter=0;
start slave;
show variables like 'sql_slave_skip_counter'\G
Variable_name: sql_slave_skip_counter
Value: 0

Restart the slave server (works for all versions).

Conclusion

Improper use of sql_slave_skip_counter can skip unrelated events and cause data divergence. For small databases (<~60 GB) rebuilding replication is advisable; for larger ones, inspect the binlog event groups before skipping and try to repair the offending rows (errors 1032, 1062) so that the slave can apply the changes.

Enabling GTID‑based replication is recommended for faster consistency checks and stronger fault recovery, though GTID cannot be enabled online on MySQL 5.6.