enterprise-support team mailing list archive

[Miguel Angel Nieto <1577709@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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@xxxxx                 | Success |                       |
|  5 |    106 | 9002 | angel@xxxxx                  | Success |                       |
|  6 |    106 | 9057 | nieto@xxxxx;;;;;;;;;;;;;;;;; | Failed  | Invalid email format. |
+----+--------+------+------------------------------+---------+-----------------------+

mysql> SET INSERT_ID=6;
mysql> REPLACE INTO `test_import_data` (`idFile`, `line`, `email`, `status`, `reason`) VALUES(106,9057,"a@xxxxx;;;;;;;;;;;;;;;;;","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;

** Affects: mysql-server
     Importance: Unknown
         Status: Unknown

** Affects: percona-server
     Importance: Undecided
         Status: New


** Tags: i67350

** Bug watch added: MySQL Bug System #81254
   http://bugs.mysql.com/bug.php?id=81254

** Also affects: mysql-server via
   http://bugs.mysql.com/bug.php?id=81254
   Importance: Unknown
       Status: Unknown

** Tags added: i67350

-- 
You received this bug notification because you are a member of Ubuntu
Server/Client Support Team, which is subscribed to MySQL.
Matching subscriptions: Ubuntu Server/Client Support Team
https://bugs.launchpad.net/bugs/1577709

Title:
  Using INSERT_ID local variable, makes REPLACE fail when there is a
  duplicate key

To manage notifications about this bug go to:
https://bugs.launchpad.net/mysql-server/+bug/1577709/+subscriptions