MySQL Bugs: #62436: auto increment in MySQL Cluster always increases

Bug #62436	auto increment in MySQL Cluster always increases - even on insert error!
Submitted:	14 Sep 2011 20:47	Modified:	23 Jan 2012 8:22
Reporter:	J H	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.1.56-ndb-7.1.15a	OS:	Linux
Assigned to:	Martin Skold	CPU Architecture:	Any
Tags:	auto increment, id, insert, ndb

Description:
auto_increment will ALWAYS increment, even if the insert returns an error, or INSERT IGNORE INTO finds a duplicate key.

auto_increment_increment and auto_increment_offset do not help; error still exists

How to repeat:
#
# create quick test table
#
CREATE DATABASE test;
USE test;
CREATE TABLE t1 (
  id int not null primary key auto_increment,
  word varchar(20) not null default '',
  UNIQUE INDEX(word),
  created datetime
) engine=ndb;

#
# insert something (this should give id=1)
INSERT INTO t1(word,created) VALUES ('test',NOW());
#
# try it again (gives error)
INSERT INTO t1(word,created) VALUES ('test',NOW());
#
# and  again (another error)
INSERT INTO t1(word,created) VALUES ('test',NOW());
#
# insert something else
# expected: id=2 (since this is the second successful entry into table)
# actual: id=4 (both failed inserts above apparently got IDs??)
INSERT INTO t1(word,created) VALUES ('test2',NOW());

#
# same thing with INSERT IGNORE INTO, except no errors (because of "IGNORE")
#
CREATE TABLE t2 (
  id int not null primary key auto_increment,
  word varchar(20) not null default '',
  UNIQUE INDEX(word),
  created datetime
) engine=ndb;
INSERT IGNORE INTO t2(word,created) VALUES ('test',NOW()); #good, id=1
INSERT IGNORE INTO t2(word,created) VALUES ('test',NOW()); #duplicate key
INSERT IGNORE INTO t2(word,created) VALUES ('test',NOW()); #duplicate key
INSERT IGNORE INTO t2(word,created) VALUES ('test2',NOW()); #inserts, but we get id=4, not 2

#
# same thing with auto_increment_increment or auto_increment_offset
#
set @@auto_increment_increment=10;
CREATE TABLE t3 (
  id int not null primary key auto_increment,
  word varchar(20) not null default '',
  UNIQUE INDEX(word),
  created datetime
) engine=ndb;
INSERT IGNORE INTO t3(word,created) VALUES ('test',NOW()); #good, id=1
INSERT IGNORE INTO t3(word,created) VALUES ('test',NOW()); #duplicate key
INSERT IGNORE INTO t3(word,created) VALUES ('test',NOW()); #duplicate key
INSERT IGNORE INTO t3(word,created) VALUES ('test2',NOW()); #inserts, but we get id=31, not 11

Suggested fix:

I have not had time to check the source code, but it seems as though AUTO_INCREMENT is being incremented on clustered tables when the INSERT statement is called.  A fix should have AUTO_INCREMENT increment ONLY when a row is successfully inserted.

It does not make sense to get an error from an INSERT statement, have not data inserted into the table, but only later find out that you have huge gaps in your AUTO_INCREMENT columns.

INSERT IGNORE INTO is a great feature of MySQL; doing a SELECT to check EVERY unique key BEFORE inserting is *NOT* a workaround. If I have 10 servers clustered, each receiving thousands of connections, the time difference between the SELECT and INSERT commands leaves a possibility of the row I'm about to INSERT being inserted from another server in-between the two calls.

It *MAY* be possible (ie I have not tested it while receiving thousands of connections, so I give no testament to it's validity) to remove "auto_increment", and use "max(id)+1", but again, this is not preferable

Forgot to mention: another acceptable solution may be to make this work with auto_increment_offset, because each node can individually reserve given id space, allowing it to 'release' the 'reserved' increment (or "roll-back" or whatever). In other words, with auto_increment_offset, each node can control given increment values, and therefore there should be no need to talk to other nodes.

Just a thought; obviously not a perfect (or even preferred) one, but still a thought.

Is anyone even looking at this?  A simple, "here is where we are at" will do.... But seriously, four months with no response?  This is sad....

There is no claim that auto_increment should work the same in all engines. The only thing we guarantee is that it is an a monotonically increasing number series. No guarantees that all values are used or that there will not be holes.
Auto_increment in cluster is much more complex than in local non-distributed storage engines since it is a distributed counter with prefetch of several values.
This might be fixed in the future, but it is really a feature request.