MySQL Bugs: #81254: Using INSERT_ID local variable, makes REPLACE fail when there is a duplicate key

Bug #81254	Using INSERT_ID local variable, makes REPLACE fail when there is a duplicate key
Submitted:	1 May 2016 7:53	Modified:	2 May 2016 10:08
Reporter:	Miguel Angel Nieto	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6, 5.6.30, 5.7.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If you use INSERT_ID to specify the next AUTO INCREMENT value, REPLACE fails when it finds a duplicate key instead of updating the row. This is a problem, because replication relies on INSERT_ID when no PK is specified in the query. So, a REPLACE that works on the master (deleting the old row and insert the new one) will make the replication fail with duplicate key error.

It affects REPLACE and LOAD DATA LOCAL INFILE ... REPLACE INTO TABLE .

How to repeat:
mysql> select * from test_import_data;
+----+--------+------+------------------------------+---------+-----------------------+
| id | idFile | line | email                        | status  | reason                |
+----+--------+------+------------------------------+---------+-----------------------+
|  4 |    106 | 9001 | miguel@a.com                 | Success |                       |
|  5 |    106 | 9002 | angel@b.com                  | Success |                       |
|  6 |    106 | 9057 | nieto@c.com;;;;;;;;;;;;;;;;; | Failed  | Invalid email format. |
+----+--------+------+------------------------------+---------+-----------------------+

mysql> SET INSERT_ID=6;
mysql> REPLACE INTO `test_import_data` (`idFile`, `line`, `email`, `status`, `reason`) VALUES(106,9057,"a@c.com;;;;;;;;;;;;;;;;;","Failed","Invalid email format.");
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

Suggested fix:
REPLACE should always work as documented:

"MySQL uses the following algorithm for REPLACE (and LOAD DATA ... REPLACE):

- Try to insert the new row into the table
- While the insertion fails because a duplicate-key error occurs for a primary key or unique index:
- Delete from the table the conflicting row that has the duplicate key value
- Try again to insert the new row into the table"

regardless of if you use INSERT_ID or not. Because it breaks the replication (among other things).

In order to make the replication fail, you need to add some data inconsistency. For example:

Master's PK goes from 1 to 3. Slaves' PK goes from 1 to 4.

If you run a REPLACE on the master, it will be logged with SET SESSION_ID=4, and that will make slave to fail (when it should just DELETE and INSERT).

To make the REPLACE fail without replication, just set INSERT_ID and it will behave as it was a normal INSERT.

CREATE TABLE `test_import_data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`idFile` int(11) NOT NULL,
`line` int(11) NOT NULL,
`email` varchar(255) NOT NULL,
`status` enum('Failed','Success','Duplicated') NOT NULL DEFAULT 'Failed',
`reason` text NOT NULL,
PRIMARY KEY (`id`),
KEY `idFile` (`idFile`)
) ENGINE=InnoDB;

Hello Miguel,

Thank you for the report and test case.
Observed this with 5.6.30/5.7.12.

Thanks,
Umesh