MySQL replace operation causes inconsistency between primary and secondary self incrementing primary keys
A problem encountered online today is the inconsistency of master-slave self increasing primary keys caused by replace syntax. Here I simulate it and the problem can be reproduced stably. I hope you don't step on the pit in the follow-up process
01
Problem restore
Environment introduction:
MySQL version 5.7.18
Introduction to key parameters:
binlog_format: row
binlog_row_image:full
Main library operation
Create a table test on the main database and insert some data:
copy# Create table create table test1 ( id int not null auto_increment primary key, # Primary key name varchar(10) unique, # unique index age int ); Query OK, 0 rows affected (0.13 sec) # insert data insert into test1 values (1,'aaa',1),(2,'bbb',2); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0 # Insert data with replace replace into test1 values (3,'ccc',3); Query OK, 1 row affected (0.04 sec) # View data select * from test1; +----+------+------+ | id | name | age | +----+------+------+ | 1 | aaa | 1 | | 2 | bbb | 2 | | 3 | ccc | 3 | +----+------+------+ 3 rows in set (0.03 sec)
At this time, it can be seen that when replacing inserts data with id=3, it can be inserted normally. The return is 1 row affected, which means that 1 row of data has been affected.
Then we use the replace syntax to insert the column with id=4 again:
copyreplace into test1 values (4,'aaa',4); Query OK, 2 rows affected (0.03 sec) select * from test1; +----+------+------+ | id | name | age | +----+------+------+ | 2 | bbb | 2 | | 3 | ccc | 3 | | 4 | aaa | 4 | +----+------+------+ 3 rows in set (0.04 sec)
Note that the return value here is 2 lines. Why 2 lines? This is explained by the words in the official documents:
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
From the above description, it is not difficult to see that when replacing encounters primary key conflict or unique key conflict, it executes delete first and then insert.
Therefore, when we look at the contents of the table, we can see that since the name column with id=4 is' aaa ', there is a unique key conflict with the name column with id=1, the record with id=1 does not exist, and the record with id=4 is replaced.
At this point, let's view the table structure of the main library:
show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4
1 row in set (0.14 sec)
We can see that since the last operation was deleted and then inserted, the auto of the main library_ Increment has become 5.
Operation from library
Let's take another look at the data records and self increasing id values from the database;
select * from test1;
+----+------+------+
| id | name | age |
+----+------+------+
| 2 | bbb | 2 |
| 3 | ccc | 3 |
| 4 | aaa | 4 |
+----+------+------+
3 rows in set (0.13 sec)
show create table test1\G
*************************** 1. row ***************************
Table: test1
Create Table: CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
1 row in set (0.13 sec)
As you can see, auto from the library_ The increment value is 4.
You can see two questions:
1. Auto from library_ Increment value and main library Auto_ Inconsistent increment value.
2,AUTO_ The value of increment represents the default ID of the next record inserted into the table, but there is already a record with id=4 in our slave database
02
Cause analysis
In fact, the essential reason for this problem is that MySQL combines the delete and insert operations of the replace statement into an update statement in binlog, and the update statement only updates the value of the id column in the record without actively updating auto_ The value of increment (please understand this sentence). When we parse the corresponding binlog log, we can see:
copyreplace id=3 Recorded, recorded binlog yes insert operation BEGIN /*!*/; # at 139995629 #220413 20:22:45 server id 2157944972 end_log_pos 139995681 CRC32 0xea7d7120 Table_map: `test`.`test1` mapped to number 153 # at 139995681 #220413 20:22:45 server id 2157944972 end_log_pos 139995729 CRC32 0x5b8b0ebc Write_rows: table id 153 flags: STMT_END_F ### INSERT INTO `test`.`test1` ### SET ### @1=3 /* INT meta=0 nullable=0 is_null=0 */ ### @2='ccc' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */ ### @3=3 /* INT meta=0 nullable=1 is_null=0 */ # at 139995729 #220413 20:22:45 server id 2157944972 end_log_pos 139995760 CRC32 0xc69ecc26 Xid = 12340849656 COMMIT/*!*/; replace id=4 Recorded, recorded binlog yes update operation BEGIN /*!*/; # at 139996477 #220413 20:22:58 server id 2157944972 end_log_pos 139996529 CRC32 0xb8805056 Table_map: `test`.`test1` mapped to number 153 # at 139996529 #220413 20:22:58 server id 2157944972 end_log_pos 139996591 CRC32 0x07d4f31b Update_rows: table id 153 flags: STMT_END_F ### UPDATE `test`.`test1` ### WHERE ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2='aaa' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */ ### @3=1 /* INT meta=0 nullable=1 is_null=0 */ ### SET ### @1=4 /* INT meta=0 nullable=0 is_null=0 */ ### @2='aaa' /* VARSTRING(40) meta=40 nullable=1 is_null=0 */ ### @3=4 /* INT meta=0 nullable=1 is_null=0 */ # at 139996591 #220413 20:22:58 server id 2157944972 end_log_pos 139996622 CRC32 0xe0cf7229 Xid = 12340849733 COMMIT/*!*/;
In the process of this experiment, I tested mysql8 Version 0 and MySQL 5.0 Version 7, found mysql8 0. Although the binlog content is consistent, auto is updated_ Value of increment.
This phenomenon can be understood as a bug in MySQL version 5.7.
03
potential impact
You may think that if the master database inserts a new data record without conflict by using the replace operation, the self increment of the slave database will be synchronized again. It doesn't seem to have any impact on the whole architecture???
This idea is wrong. Imagine such a scenario. If the master-slave switch occurs at this time point, the slave library will be promoted to a new master library. Take our example above:
At this time, the data records in the new main database are as follows:
copyselect * from test1; +----+------+------+ | id | name | age | +----+------+------+ | 2 | bbb | 2 | | 3 | ccc | 3 | | 4 | aaa | 4 | +----+------+------+ 3 rows in set (0.13 sec)
But the new main library's auto_ The increment value is 4, which means that the next insert statement in the new main database that does not specify the self increment ID will report a primary key conflict. Because it will generate a record with self incrementing id=4 by default, but this record already exists in the table.
04
summary
Since the above Auto_ Inconsistent increment values will bring hidden dangers after master-slave switching. How should we eliminate them?
1. Upgrade MySQL version to version 8.0.
2. The business side shall eliminate the non-standard SQL syntax of replace and use the business logic to judge the data conflict.
3. Detect the inconsistency of self increment ID, configure corresponding monitoring, find and solve the problem at the first time.