MySQL "replace in" pit self-increasing id, ready for opportunities to have problems, this problem will not appear on MySQL 5.7.17
The following is online information.
Source: http://www.cnblogs.com/monian/archive/2014/10/09/4013784.html
MySQL "replace in" pit
MySQL has many extensions to SQL, some of which are convenient to use, but some of them have performance problems after misuse, and some unexpected side effects, such as REPLACE INTO.
For example, there is a table like this:
CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB
The auto table has a self-increasing id field as the primary key, and the field k has UNIQUE KEY as the uniqueness constraint. This is what happens when several records are written:
xupeng@diggle7:3600(dba_m) [dba] mysql> INSERT INTO auto (k, v, extra) VALUES (1, '1', 'extra 1'), (2, '2', 'extra 2'), (3, '3', 'extra 3'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.01 sec)
xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 1 | 1 | 1 | extra 1 | | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | +----+---+------+---------+ 3 rows in set (0.00 sec)
The slave node is consistent with the master:
xupeng@diggle8:3600(dba_s) [dba] mysql> SELECT * FROM auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 1 | 1 | 1 | extra 1 | | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | +----+---+------+---------+ 3 rows in set (0.00 sec) xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
As you can see, after writing three records, the AUTO_INCREMENT of the auto table increases to 4, which means that the next record that does not specify a value for id manually will have a value of 4 for the id field.
Next, REPLACE INTO is used to write a record:
xupeng@diggle7:3600(dba_m) [dba] mysql> REPLACE INTO auto (k, v) VALUES (1, '1-1'); Query OK, 2 rows affected (0.01 sec) xupeng@diggle7:3600(dba_m) [dba] mysql> SELECT * FROM auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 1 | 1-1 | NULL | +----+---+------+---------+ 3 rows in set (0.00 sec)
xupeng@diggle7:3600(dba_m) [dba] mysql> SHOW CREATE TABLE auto\G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
You can see that MySQL says "2 rows affected", but it clearly only writes a record, why? This is because MySQL first tries INSERT INTO auto (k) VALUES (1) when executing REPLACE INTO auto (k) VALUES (1), but duplicate key error occurs because a record of k=1 already exists, so MySQL deletes the existing record of k=1, i.e. id=1, and then writes a new record again.
At this time, there is a weird problem on slave:
xupeng@diggle8:3600(dba_s) [dba] mysql> SHOW CREATE TABLE auto\G *************************** 1. row *************************** Table: auto Create Table: CREATE TABLE `auto` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL, `v` varchar(100) DEFAULT NULL, `extra` varchar(200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
As you can see, the maximum value of the data ID field in the current table is 4, AUTO_INCREMENT should be 5, but AUTO_INCREMENT has not been updated on slave. What's the problem? After upgrading this slave to master, because AUTO_INCREMENT is smaller than the actual next id, duplicate key error will occur when new records are written. After each conflict, AUTO_INCREMENT += 1 will not return to normal until it grows to Max (id) +1:
xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (4, '4'); ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY' xupeng@diggle8:3600(dba_s) [dba] mysql> REPLACE INTO auto (k, v) VALUES (5, '5'); Query OK, 1 row affected (0.00 sec)
xupeng@diggle8:3600(dba_s) [dba] mysql> SELECT * FROM auto; +----+---+------+---------+ | id | k | v | extra | +----+---+------+---------+ | 2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | extra 3 | | 4 | 1 | 1-1 | NULL | | 5 | 5 | 5 | NULL | +----+---+------+---------+ 4 rows in set (0.00 sec)
It is not anticipated that MySQL actually deletes old records and writes new records when data conflicts occur. This is the biggest misunderstanding when using REPLACE INTO. For example, after executing REPLACE INTO auto (k, v) VALUES (1,'1-1'), the value of the original record extrafield is "lost" because the value of the original record extrafield is not specified when new records are written. Usually this is not what business expects, but the more common requirement is that when there is a record of k=1, the value of the V field is updated to'1-1', while other unspecified fields remain unchanged. MySQL dialect to meet this requirement is INSERT INTO auto (k, v) VALUES (1,'1-1') ON DUPLICATE KEY UPDATE v=VALUES(v);
In view of this, many scenarios using REPLACE INTO actually require INSERT INTO... ON DUPLICATE KEY UPDATE, on the premise of correctly understanding the behavior and side effects of REPLACE INTO, carefully use REPLACE INTO.