MySQL Bugs: #39773: auto_increment for NDB changed behavior from mysql-5.1 to mysql-6.0

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

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.

NDB is not supported in 6.0. IMO -> !Bug.

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.

Jon,

if NDB is not supported in 6.0 it should be disabled.

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;

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

See bug #47118

This bug is not scheduled to be fixed at this time.