Bug #26342 auto_increment_increment AND auto_increment_offset REALLY REALLY anger NDB clust
Submitted: 13 Feb 2007 20:23 Modified: 19 Jun 2007 12:37
Reporter: Joshua Butcher Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.0.38 | mysql-5.1-telco OS:Linux (CentOS 4.4 | Fedora)
Assigned to: Martin Skold
Tags: auto_increment_increment, auto_increment_offset, ndb

[13 Feb 2007 20:23] Joshua Butcher
Description:
When using auto_increment_increment and/or auto_increment_offset with NDB cluster, it angers it something fierce.  It results in random duplicate key (1022) errors indicating it can't write the data because of a duplicate key, but the table supposedly throwing the error isn't even involved in the update.

How to repeat:
In my.cnf file, specify:

#auto_increment_increment        = 10
#auto_increment_offset           = 2

CREATE DATABASE db;

CREATE TABLE `db`.`test` (
  `idx` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
  `x` VARCHAR(2) NOT NULL,
  PRIMARY KEY(`idx`)
)
ENGINE = ndbcluster;

Insert into db.test (x) VALUES('aa');
Insert into db.test (x) VALUES('aa');
Insert into db.test (x) VALUES('aa');
Insert into db.test (x) VALUES('aa');
Insert into db.test (x) VALUES('aa');
Insert into db.test (x) VALUES('aa');
Insert into db.test (x) VALUES('aa');

Simply use MySQL QB or your app to perform the insert statements you will get RANDOM failures, they are not consistent, and if you wait long enough in between each insert, it will NOT happen, but repeated assault on any combination of tables with auto inc values will cause this error to happen.

The error goes away when you remove the values mentioned above in the my.cnf file, and restart the MySQL server.

Suggested fix:
Make it so this doesnt happen.
[15 Mar 2007 3:03] Adam Dixon
Verified for Ver 5.0.38-debug from bk tree. using Linux 2.6.19-1.2911.6.5.fc6

Not a bug for 5.1 due to auto_increment_increment and auto_increment_offset noted as not being supported in http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-limitations.html
[30 Mar 2007 1:30] Jonathan Miller
Using the following in a my.cnf file causes the issues listed below
* auto_increment_increment=10
* auto_increment_offset=3

mysql> select * from t1;
+----+------+---------+------+------+-------------------+
| c1 | c2   | c3      | c4   | c5   | c6                |
+----+------+---------+------+------+-------------------+
|  3 | txt  | 3.00008 |  145 |      | Testing Disk Data |
+----+------+---------+------+------+-------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');

ERROR 1582 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '13' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '23' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
ERROR 1582 (23000): Duplicate entry '33' for key 'PRIMARY'
mysql> INSERT INTO t1 VALUES(NULL ,'txt',3.00008,145.00, b'1111','Testing Disk Data');
Query OK, 1 row affected (0.01 sec)
[9 May 2007 12:14] 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/commits/26374

ChangeSet@1.2471, 2007-05-09 14:14:27+02:00, mskold@mysql.com +7 -0
  Bug#26342 auto_increment_increment AND auto_increment_offset REALLY REALLY anger NDB cluster, implemented support for auto_increment_offset and auto_increment_increment for Ndb
[9 May 2007 15:09] 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/commits/26394

ChangeSet@1.2512, 2007-05-09 17:09:06+02:00, mskold@mysql.com +3 -0
  Ndb.hpp, Ndb.cpp, ha_ndbcluster.cc:
    Bug#26342 auto_increment_increment AND auto_increment_offset REALLY REALLY anger NDB cluster, implemented support for auto_increment_offset and auto_increment
[14 Jun 2007 14:02] 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/commits/28758

ChangeSet@1.2504, 2007-06-14 16:01:51+02:00, mskold@mysql.com +1 -0
  Bug#26342 auto_increment_increment AND auto_increment_offset REALLY REALLY anger NDB cluster, implemented support for auto_increment_offset and auto_increment_increment for Ndb, post review fix
[18 Jun 2007 7:48] Bugs System
Pushed into 5.1.20-beta
[18 Jun 2007 7:50] Bugs System
Pushed into 5.0.44
[19 Jun 2007 10:17] 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.44 and 5.1.20 changelogs; noted lifting of limitation in Cluster chapter of 5.0 and 5.1 Manuals.
[19 Jun 2007 10:25] Daniel Fischer
Didn't make it into 5.0.44, will be in 5.0.46.
[19 Jun 2007 12:37] Jon Stephens
Moved 5.0 changelog entry to 5.0.46 changelog. Thanks for the heads-up.
[3 Jul 2007 4:46] Jon Stephens
Also documented for telco-6.2.3 release.
[4 Sep 2009 1:53] Roel Van de Paar
See bug #47118