MySQL Bugs: #32055: Cluster does not handle auto inc correctly with insert ignore statement

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	CPU Architecture:	Any

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;

Pushed into 5.1.23-rc

Pushed into 5.0.56

Pushed into 6.0.5-alpha

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.