Detailed description of slave_exec_mode parameters in MySQL,

Detailed description of slave_exec_mode parameters in MySQL,

I have no intention of seeing the slave_exec_mode parameter today. From the instruction in the manual, we can see that this parameter is related to MySQL replication. It is a variable that can be dynamically modified. The default value is STRICT mode ), the optional values include the IDEMPOTENT mode (IDEMPOTENT mode ). Setting it to IDEMPOTENT mode allows the slave database to avoid errors such as 1032 (keys that do not exist in the slave database) and 1062 (duplicate keys, which require primary keys or unique keys, this mode takes effect only in the binlog mode of the row event and is invalid in the binlog mode of the statement event. IDEMPOTENT mode is mainly used for multi-master replication and ndb cluster. It is not recommended in other cases. From the above introduction, the problem is as follows:

1: What are the advantages of SQL _slave_skip_counter?

2: What are the advantages of slave-skip-errors = N?

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

Environment:

MySQL version: Percona MySQL 5.7

Replication mode: ROW. GTID is not enabled.

Test:

① 1062 error: cocould not execute... event on table db. x; Duplicate entry 'xx' for key 'Primary ', Error_code: 1062;

Test Table Structure on the master and slave nodes:

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

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 databases are inconsistent. The master database does not have the id = 1 record.

In this case, the default STRICT mode is selected from the above slave_exec_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)

Run the following command on M:

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

Because there is already a record with id = 1 from above, the error 1062 is reported from the replication:

Last_SQL_Errno: 1062Last_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 practice is to execute: SQL _slave_skip_counter = N.

1. In set global SQL _slave_skip_counter = N, N indicates skipping N event2. The best note is that when N is set to 1, the effect skips the next transaction. 3. After skipping the nth event, if the position falls inside a transaction, the entire transaction will be skipped. 4. An insert/update/delete operation does not necessarily correspond to only one event, 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 incorrect because a transaction contains multiple events, skipping N may still be in the same transaction. For the above error 1062, set N to 1 ~ 4. The results are the same. All transactions are skipped. Because the executed SQL generates four 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)

The methods to handle this error include:

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: In the configuration file, specify slave-skip-errors = 1062 (restart required)

Both methods can restore replication to normal, but will make the Master/Slave Data inconsistent (use with caution), so that the slave database will lose the records with id = 4 and 5. In addition, the database needs to be restarted in the 2nd methods. The slave_exec_mode parameter described in this article is useful. Set this parameter on the slave database:

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)

Similarly, run the following command on the master node:

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

We can be pleasantly surprised to find that the Master/Slave Data is synchronized without the replication exception:

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)

The test above shows that after the parameter is set to slave_exec_mode = 'idempotent', an error event can be skipped.

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

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

Test Table Structure on the master and slave nodes:

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

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 databases are inconsistent. The table records with id = 2 are missing from the master and slave databases. In this case, the default STRICT mode is selected from the above slave_exec_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)

Run the following command on M:

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

Because there is no record with id = 2 from above, the error 1032 is reported in the copy process:

Last_SQL_Errno: 1032Last_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 test above can make the replication normal, but the data will also be lost. If records with id = 4 and 5 are lost, set this parameter on the slave database:

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)

Perform the same operation on M:

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

You can also be pleasantly surprised to find that the Master/Slave Data is synchronized without a replication exception.

Note: slave_exec_mode = 'idempotent' cannot perform DDL operation idempotence or perform idempotence on errors caused by different field lengths, for example, change the id field type int of the slave database table in the example to bigint. It can only be used in binlog_format ROW mode, and can only perform idempotent mode on 1032 and 1062.

Summary:

For the test summary above, for the slave_exec_mode parameter, it can skip errors 1062 and 1032 without affecting normal data execution in the same transaction. If it is a transaction composed of multiple SQL statements, you can skip the problematic event.

This parameter looks good, but it is not recommended to enable it in a common replication environment as described in the Manual. For storage engines other than NDB, the IDEMPOTENT mode should be used only when it is determined that duplicate key errors and no key errors can be safely ignored. This parameter is specially designed for the NBD Cluster. In the NBD Cluster mode, this parameter can only be set to the IDEMPOTENT mode. Therefore, it depends on your application scenario. Under normal circumstances, the master and slave nodes are consistent. If any errors occur, an error is reported. However, you can enable them temporarily during special processing.

In addition, SQL _slave_skip_counter is not supported for replication in GTID mode. You can test the replication in this mode.