Bug #11080 Multi-row REPLACE fails on a duplicate key error on an AUTO-INC column
Submitted: 3 Jun 2005 17:32 Modified: 7 Jun 2005 10:52
Reporter: Heikki Tuuri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.7 OS:Linux (Linux/x86)
Assigned to: Michael Widenius CPU Architecture:Any

[3 Jun 2005 17:32] Heikki Tuuri
Description:
Hi!

The semantics of a multi-row REPLACE for MyISAM differs from the same REPLACEs issued row-by-row. Note that the bug is not present in 4.1.13.

Regards,

Heikki

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t` (
    ->   `a` int(11) NOT NULL auto_increment,
    ->   `b` int(11) default NULL,
    ->   PRIMARY KEY  (`a`),
    ->   UNIQUE KEY `b` (`b`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t(b) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> replace into t(b) values (2), (1), (3);
ERROR 1062 (23000): Duplicate entry '3' for key 1
mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t` (
    ->   `a` int(11) NOT NULL auto_increment,
    ->   `b` int(11) default NULL,
    ->   PRIMARY KEY  (`a`),
    ->   UNIQUE KEY `b` (`b`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t(b) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> replace into t(b) values (2);
Query OK, 1 row affected (0.00 sec)

mysql> replace into t(b) values (1);
Query OK, 2 rows affected (0.00 sec)

mysql> replace into t(b) values (3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
| 2 |    2 |
| 4 |    3 |
+---+------+
3 rows in set (0.00 sec)

mysql>

In 4.1.13:

heikki@hundin:~/mysql-5.0/client> ./mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.13-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table t;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE `t` (
    ->   `a` int(11) NOT NULL auto_increment,
    ->   `b` int(11) default NULL,
    ->   PRIMARY KEY  (`a`),
    ->   UNIQUE KEY `b` (`b`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t(b) values (1);
Query OK, 1 row affected (0.01 sec)

mysql> replace into t(b) values (4), (1), (5);
Query OK, 4 rows affected (0.00 sec)
Records: 3  Duplicates: 1  Warnings: 0

mysql> select * from t;
+---+------+
| a | b    |
+---+------+
| 3 |    1 |
| 2 |    4 |
| 4 |    5 |
+---+------+
3 rows in set (0.02 sec)

mysql>

How to repeat:
See above.
[3 Jun 2005 17:52] MySQL Verification Team
Thank you for the bug report.
[6 Jun 2005 17:29] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 5.0.6. Note that the fix only fixes MyISAM tables. For this to work with InnoDB tables we have to fix that InnoDB stores max used auto-increment value for UPDATES.
See bug #11005 for more details regarding this
[6 Jun 2005 17:29] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/25663
[7 Jun 2005 10:39] Heikki Tuuri
Hi!

I pushed the fix to bug #11005 a few minutes ago, and it will fix also this REPLACE bug for InnoDB. But I did NOT change the InnoDB behavior in an UPDATE. It will remain different from MyISAM.

The fix will probably be in 5.0.6.

Regards,

Heikki
[7 Jun 2005 10:52] Heikki Tuuri
Of course I meant the fix will probably be in 5.0.7.
--Heikki