Bug #32055 Cluster does not handle auto inc correctly with insert ignore statement
Submitted: 2 Nov 2007 16:33 Modified: 14 Dec 2007 20:38
Reporter: Jonathan Miller Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:mysql-5.1-new-ndb OS:Linux (64)
Assigned to: Martin Skold
Triage: D2 (Serious)

[2 Nov 2007 16:33] Jonathan Miller
Description:
rpl_ndb_auto_inc 

Cluster returns:

+ values(600),(NULL),(NULL),(610),(NULL);
+ select * from t1 ORDER BY a;
+ a
+ 1
+ 5
+ 6
+ 7
+ 500
+ 501
+ 502
+ 503
+ 600
+ 610
+ 611

MyISAM returns:

mysql>  select * from t1;
+-----+
| a   |
+-----+
|   1 |
|   5 |
|   6 |
|   7 |
| 500 |
| 501 |
| 502 |
| 503 |
| 600 |
| 601 |
| 602 |
| 610 |
| 611 |
+-----+

How to repeat:
mysql> use test
Database changed
mysql> create table t1 (a int not null auto_increment, primary key (a
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values (NULL),(5),(NULL),(NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (500),(NULL),(502),(NULL),(600);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>  insert into t1 values(600),(NULL),(NULL);
ERROR 1062 (23000): Duplicate entry '600' for key 'PRIMARY'
mysql> insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL);
Query OK, 4 rows affected (0.00 sec)
Records: 5  Duplicates: 1  Warnings: 0

mysql>  select * from t1;
[14 Dec 2007 17:08] Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 17:09] Bugs System
Pushed into 5.0.56
[14 Dec 2007 17:09] Bugs System
Pushed into 6.0.5-alpha
[14 Dec 2007 20:38] Jon Stephens
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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.0.56, 5.1.23, 6.0.5 changelogs as:

        Tables using the NDBCLUSTER storage engine
        did not handle AUTO_INCREMENT values
        correctly with INSERT IGNORE statements.