Bug #39773 auto_increment for NDB changed behavior from mysql-5.1 to mysql-6.0
Submitted: 1 Oct 2008 11:50 Modified: 7 May 2009 12:56
Reporter: Mattias Jonsson Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:mysql-6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Triaged: D2 (Serious) / R6 (Needs Assessment) / E6 (Needs Assessment)

[1 Oct 2008 11:50] Mattias Jonsson
Description:
The behavior of auto_increment in NDB has changed from 5.1 (works as expected) to 6.0:

auto_increment_increment/auto_increment_offset is not consistent

When inserting into a auto_increment table, the rows are not seen until they are committed.

How to repeat:
use parts.partition_auto_increment_myisam.test and change engine to ndb and run it in 5.1 and 6.0. Or use the parts.partition_auto_increment_ndb.test which will be in the 5.1.29 trees.
[1 Oct 2008 12:45] Jon Stephens
NDB is not supported in 6.0. IMO -> !Bug.
[8 Oct 2008 20:44] Sveta Smirnova
Thank you for the report.

If I just copy parts.partition_auto_increment_myisam.test to 5.1 tree test fails with "
mysqltest: In included file "./suite/parts/inc/partition_auto_increment.inc": At line 396: query 'INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL)' failed: 1467: Failed to read auto-increment value from storage engine"

If I try to create simple test case like:

--source include/have_ndb.inc

create table t1(id int not null auto_increment primary key) engine=ndb;

SET @@session.auto_increment_increment = 5;
SET @@session.auto_increment_offset = 3;

begin;

insert into t1 values();
insert into t1 values();

select * from t1;

commit;

select * from t1;

I get same results with 5.1 and 6.0

Please provide working test case.
[8 Oct 2008 20:45] Sveta Smirnova
Jon,

if NDB is not supported in 6.0 it should be disabled.
[8 Oct 2008 23:59] Mattias Jonsson
Sorry for not being clear enough.

The parts.partition_auto_increment_ndb is now in mysql-5.1-bugteam and mysql-6.0-bugteam and will demonstrate the difference between 5.1 and 6.0.

Here is a smaller set which will find a third bug (even worse) on the auto_increment handling in NDB in 6.0, it is able to dead lock (possible because it is not unique auto_increment values between threads?)

--source include/have_ndb.inc
create table t1(id int not null auto_increment primary key) engine=ndb;

SET @@session.auto_increment_increment = 5;
SET @@session.auto_increment_offset = 3;

begin;

insert into t1 values();
let $new_val= `select last_insert_id()`;
--echo # last id was $new_val, inserting $new_val + 1
eval insert into t1 values($new_val + 1);
insert into t1 values();
let $new_val= `select last_insert_id()`;
--echo # last id was $new_val, inserting $new_val + 2
eval insert into t1 values($new_val + 2);
insert into t1 values();
let $new_val= `select last_insert_id()`;
--echo # last id was $new_val, inserting $new_val + 3
eval insert into t1 values($new_val + 3);
--echo # in 6.0 this will skip an interval
insert into t1 values();
let $new_val= `select last_insert_id()`;
--echo # last id was $new_val, inserting $new_val + 4
eval insert into t1 values($new_val + 4);
insert into t1 values();
select * from t1;

connect(con1,localhost,root,,);
--echo # con1
begin;
--echo # Concurrent transaction, will not see its on insert in 6.0 until commit
insert into t1 values();
insert into t1 values();
--echo # dependent of the inserts in the other thread, this will either deadlock or not show anything
select * from t1;

connection default;
--echo # con default
insert into t1 values();
select * from t1;
commit;
select * from t1;
connection con1;
--echo # con1
select * from t1;
commit;
select * from t1;
[9 Oct 2008 6:46] Sveta Smirnova
Thank you for the feedback.

Verified as described: deadlock occurred in my case with 6.0 while 5.1 works fine. Bug was introduced after 6.0.6
[4 Sep 2009 1:54] Roel Van de Paar
See bug #47118
[13 Mar 2014 13:36] Omer Barnir
This bug is not scheduled to be fixed at this time.