Detailed explanation of slave_exec_mode parameter in MySQL

Today I accidentally saw the parameter slave_exec_mode. From the description in the manual, I know that this parameter is related to MySQL replication and is a variable that can be modified dynamically. The default mode is STRICT (strict mode), and the optional value is IDEMPOTENT mode (idempotent mode). Setting the slave to IDEMPOTENT mode can prevent errors 1032 (key that does not exist on the slave) and 1062 (duplicate key, primary key or unique key must exist). This mode is only effective in binlog mode of ROW EVENT and is invalid in binlog mode of STATEMENT EVENT. The IDEMPOTENT mode is mainly used in multi-master replication and NDB CLUSTER situations and is not recommended in other situations. From the above introduction, this parameter allows the slave library to skip the specified errors, so the question is:

1: What are the benefits compared to sql_slave_skip_counter?

2: Compared with slave-skip-errors = N, what are the benefits?

With these two questions, this article will conduct relevant tests and explanations.

environment:

MySQL version: Percona MySQL 5.7

Replication mode: ROW, GTID not enabled

test:

① 1062 Error: Could not execute ... event on table db.x; Duplicate entry 'xx' for key 'PRIMARY', Error_code: 1062;

Test table structure on master and slave:

CREATE TABLE `x` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Table records on the master and slave:

M:

select * from x;
+----+
|id|
+----+
| 2 |
| 3 |
+----+
2 rows in set (0.01 sec)

S:

select * from x;
+----+
|id|
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

The table records on the master and slave are inconsistent, and the record with id=1 is missing on the master.

At this time, slave_exec_mode on the slave is the default STRICT mode:

show variables like 'slave_exec_mode';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| slave_exec_mode | STRICT |
+-----------------+--------+
1 row in set (0.00 sec)

The binlog mode on M is:

show variables like 'binlog_format'; +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)

Execute on M:

insert into x values(1),(4),(5);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

Because the record with id=1 already exists on the slave, the slave replication reports error 1062:

Last_SQL_Errno: 1062
Last_SQL_Error: Could not execute Write_rows event on table dba_test.x; Duplicate entry '1' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin-3306.000006, end_log_pos 7124

When this error occurs, the consistent approach is to execute: sql_slave_skip_counter=N.

1. set global sql_slave_skip_counter=N where N means skipping N events
2. The best thing to remember is that when N is set to 1, the effect is to skip the next transaction.
3. After skipping the Nth event, if the position happens to fall within a transaction, the entire transaction will be skipped. 4. An insert/update/delete does not necessarily correspond to only one event, which is determined by the engine and log format.

The unit of sql_slave_skip_counter is "event". Many people think that the unit of this parameter is "transaction", which is actually wrong, because a transaction contains multiple events, and skipping N events may still be in the same transaction. For the error 1062 above, setting N to 1 to 4 has the same effect, which is to skip a transaction. Because the executed SQL generates 4 events:

show binlog events in 'mysql-bin-3306.000006' from 6950;
+-----------------------+------+------------+-----------+-------------+---------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------------+------+------------+-----------+-------------+---------------------------------+
| mysql-bin-3306.000006 | 6950 | Query | 169 | 7026 | BEGIN |
| mysql-bin-3306.000006 | 7026 | Table_map | 169 | 7074 | table_id: 707 (dba_test.x) |
| mysql-bin-3306.000006 | 7074 | Write_rows | 169 | 7124 | table_id: 707 flags: STMT_END_F |
| mysql-bin-3306.000006 | 7124 | Xid | 169 | 7155 | COMMIT /* xid=74803 */ |
+-----------------------+------+------------+-----------+-------------+---------------------------------+
4 rows in set (0.00 sec)

So the ways to handle this error are:

1: skip_slavesql_slave_skip_counter

stop slave; Query OK, 0 rows affected (0.00 sec)
set global sql_slave_skip_counter=[1-4];
Query OK, 0 rows affected (0.00 sec)
start slave;
Query OK, 0 rows affected (0.00 sec)

2: Specify slave-skip-errors=1062 in the configuration file (requires restart)

Both methods can restore replication to normal, but will cause inconsistency between the master and slave data (use with caution), causing the slave database to lose records with id=4 and 5. And the second method also requires restarting the database, at which point the slave_exec_mode parameter introduced in this article comes in handy. Set this parameter on the slave library:

set global slave_exec_mode='IDEMPOTENT';
Query OK, 0 rows affected (0.00 sec)
stop slave; Query OK, 0 rows affected (0.00 sec)
start slave;
Query OK, 0 rows affected (0.00 sec)

Also execute on the master:

insert into x values(1),(4),(5);

It is surprising to find that the master and slave data are synchronized, and there are no replication anomalies:

M:
select * from x; +----+
|id|
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.00 sec)

S:
select * from x; +----+
|id|
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+----+
5 rows in set (0.01 sec)

From the above test, we can see that after setting the parameter to slave_exec_mode='IDEMPOTENT', an error event can be skipped.

② 1032 error: Could not execute ... event on table db.x; Can't find record in 'x', Error_code: 1032;

This error occurs because replication in ROW mode has strict requirements on data consistency.

Test table structure on master and slave:

CREATE TABLE `x` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

Table records on the master and slave:

M:

select * from x; +----+
|id|
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)

S:

select * from x;
+----+
|id|
+----+
| 1 |
| 3 |
+----+
2 rows in set (0.00 sec)

The table records on the master and slave are inconsistent, and the record with id=2 is missing on the slave. At this time, slave_exec_mode on the slave is the default STRICT mode:

show variables like 'slave_exec_mode';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| slave_exec_mode | STRICT |
+-----------------+--------+
1 row in set (0.00 sec)

The binlog mode on M is:

show variables like 'binlog_format'; +---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)

Execute on M:

BEGIN;
INSERT INTO x SELECT 4;
DELETE FROM x WHERE id = 2;
INSERT INTO x SELECT 5;
COMMIT;

Because the record with id=2 does not exist on the slave, the slave replication reports error 1032:

Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table dba_test.x; Can't find record in 'x', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin-3306.000006, end_log_pos 12102

Similarly, the two methods described in the above test can make the replication work, but the data will be lost. The records with id=4 and 5 are lost. Continue to set the parameter on the slave library:

set global slave_exec_mode='IDEMPOTENT';
Query OK, 0 rows affected (0.00 sec)
stop slave; Query OK, 0 rows affected (0.00 sec)
start slave;
Query OK, 0 rows affected (0.00 sec)

Do the same on M:

BEGIN;
INSERT INTO x SELECT 4;
DELETE FROM x WHERE id = 2;
INSERT INTO x SELECT 5;
COMMIT;

You may also be pleasantly surprised to find that the master-slave data is synchronized and there are no replication anomalies.

Note: slave_exec_mode='IDEMPOTENT' cannot make DDL operations idempotent, and cannot make errors caused by different field lengths idempotent, such as changing the id field type of the slave table in the example from int to bigint. It can only be used when binlog_format is ROW mode, and can only be used in idempotent mode for 1032 and 1062.

Summarize:

For the test summary above, for the slave_exec_mode parameter, it can skip errors 1062 and 1032 and does not affect the normal data execution in the same transaction. If the transaction consists of multiple SQL statements, the problematic event can be skipped.

This parameter looks good, but the manual states that it is not recommended to enable it in a normal replication environment. For storage engines other than NDB , IDEMPOTENT mode should be used only when you are sure that duplicate-key errors and no-key errors can be safely ignored. This parameter is specially designed for NBD Cluster. In NBD Cluster mode, this parameter can only be set to IDEMPOTENT mode. So you have to decide based on your own application scenario. Under normal circumstances, the master and slave are consistent, and any error will be reported. However, it can be temporarily enabled when doing special processing.

In addition, sql_slave_skip_counter is not supported in replication in GTID mode. Replication in this mode can be tested by yourself.