Shulou(Shulou.com)06/01 Report--

The pit of MySQL "replace into" adds id. There is a problem with the standby opportunity. This problem will not appear on mysql5.7.17.

The following is the online information

Source: http://www.cnblogs.com/monian/archive/2014/10/09/4013784.html

The pit of MySQL "replace into"

MySQL has many extensions to SQL, some of which are easy to use, but some can have performance problems after being misused, and there will be 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, `room`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. After writing a few records, it looks like this:

Xupeng@diggle7:3600 (dba_m) [dba] mysql > INSERT INTO auto (k, v, extra) VALUES (1, '1mm,' extra 1'), (2, '2mm,' extra 2'), (3, '3Qing,' extra 3') Query OK, 3 rows affected (0.01sec) Records: 3 Duplicates: 0 Warnings: 0xupeng@diggle7:3600 (dba_m) [dba] mysql > SHOW CREATE TABLE auto\ Graph * 1. Row * * Table: autoCreate Table: CREATE TABLE `auto` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT `k` int (10) unsigned NOT NULL, `v` varchar (100) DEFAULT NULL, `room`varchar (200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_ k` (`k`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin11 row in set (0.01sec) xupeng@diggle7:3600 (dba_m) [dba] mysql > SELECT * FROM auto +-1 | 1 | 1 | extra 1 | | 2 | 2 | extra 2 | | 3 | 3 | extra 3 | +-+-- + 3 rows in set (0.00 sec)

Is consistent with master on the slave node:

Xupeng@diggle8:3600 (dba_s) [dba] mysql > SELECT * FROM auto +-1 | 1 | 1 | extra 1 | | 2 | 2 | extra 2 | | 3 | 3 | extra 3 | +-+ 3 rows In set (0.00 sec) xupeng@diggle8:3600 (dba_s) [dba] mysql > SHOW CREATE TABLE auto\ Graph * 1. Row * * Table: autoCreate Table: CREATE TABLE `auto` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT `k` int (10) unsigned NOT NULL, `v` varchar (100) DEFAULT NULL, `room` varchar (200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_ k` (`k`) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin11 row in set (0.00 sec)

As you can see, after writing three records, the AUTO_INCREMENT of the auto table grows to 4, which means that for the next record that does not manually specify a value for id, the value of the id field will be 4.

Next, use REPLACE INTO 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 +-2 | 2 | 2 | extra 2 | | 3 | 3 | 3 | 3 | 1 | 1 | NULL | +-+ 3 Rows in set (0.00 sec) xupeng@diggle7:3600 (dba_m) [dba] mysql > SHOW CREATE TABLE auto\ Graph * 1. Row * * Table: autoCreate Table: CREATE TABLE `auto` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT `k` int (10) unsigned NOT NULL, `v` varchar (100) DEFAULT NULL, `room` varchar (200) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_ k` (`k`) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin11 row in set (0.00 sec)

You can see that MySQL says "2 rows affected", but obviously only writes one record. Why? This is because MySQL first tries INSERT INTO auto (k) VALUES (1) when executing REPLACE INTO auto (k) VALUES (1), but because there is already a record of kryp1, duplicate key error occurs, so MySQL first deletes the existing record of kumb1, that is, id=1, and then rewrites a new record.

At this time, a strange problem appeared on slave:

Xupeng@diggle8:3600 (dba_s) [dba] mysql > SHOW CREATE TABLE auto\ Graph * 1. Row * * Table: autoCreate Table: CREATE TABLE `auto` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `k` int (10) unsigned NOT NULL, `v`varchar (100th) DEFAULT NULL, `room`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 id field in the current table is 4, but the AUTO_INCREMENT has not been updated on slave, so what's the problem? After the slave is promoted to master, because the AUTO_INCREMENT is smaller than the actual next id, duplicate key error occurs when a new record is written. After each conflict, AUTO_INCREMENT + = 1, and cannot 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 +-extra 2 | 3 | 3 | 3 | 3 | 4 | 1 | 1-1 | NULL | 5 | 5 | NULL | +-+ -+ 4 rows in set (0.00 sec)

It is not expected that MySQL actually deletes the old record and writes the new record in the event of data conflict, which is the biggest misunderstanding when using REPLACE INTO. Take the previous example, after the execution of REPLACE INTO auto (k, v) VALUES (1,'1-1'), the value of the extra field of the original record is "lost" because no value is specified for the extra field when the new record is written, which is usually not what the business expects. A more common requirement is actually to update the value of the v field to'1-1 'when there is a record of Kroom1, leaving the other unspecified fields as they are, and the MySQL dialect that meets 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 that use REPLACE INTO actually need INSERT INTO. ON DUPLICATE KEY UPDATE, under the premise of correctly understanding the behavior and side effects of REPLACE INTO, use REPLACE INTO carefully.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.