MySQL Bugs: #17086: rpl_auto_increment.test fix for ndb

Bug #17086	rpl_auto_increment.test fix for ndb
Submitted:	3 Feb 2006 0:39	Modified:	3 Jul 2006 14:42
Reporter:	Jonathan Miller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	4.1 ->	OS:	Linux (Linux 32 Bit OS)
Assigned to:	Tomas Ulin	CPU Architecture:	Any

Description:
This one took me a while to track down, but I think it is a good one.

Fixing rpl_auto_increment.test to work with NDB I cp it to rpl_ndb_auto_inc.test and switch the innodb enigine for ndb. Running the test with ndb produces duplicate key error.

So I used MyISAM to help break down the test case and results.

NOTE: This clone was pulled fresh and built this morning.

*** auto_increment_increment=100; ***

NDB Results:
! set @@session.auto_increment_increment=100;
! show variables like "%auto_inc%";
! Variable_name Value
! auto_increment_increment      100
! auto_increment_offset 1
! Create table t2 (a int not null auto_increment, primary key (a)) engine=NDB;
! insert into t2 values (NULL);
! insert into t2 values (NULL);
! select * from t2;
  a
! 101
! 1

MyISAM Results:
+ set @@session.auto_increment_increment=100;
+ show variables like "%auto_inc%";
+ Variable_name Value
+ auto_increment_increment      100
+ auto_increment_offset 1
+ Create table t2 (a int not null auto_increment, primary key (a)) engine=myisam;
+ insert into t2 values (NULL);
+ insert into t2 values (NULL);
+ select * from t2;
+ a
+ 1
+ 101

**** auto_increment_offset=10;*****
NDB Results:
! set @@session.auto_increment_offset=10;
! show variables like "%auto_inc%";
! Variable_name Value
! auto_increment_increment      1
! auto_increment_offset 10
! Create table t2 (a int not null auto_increment, primary key (a)) engine=NDB;
! insert into t2 values (NULL);
! insert into t2 values (NULL);
! select * from t2;
  a
! 1
! 2

MyISAM results:
+ set @@session.auto_increment_offset=10;
+ show variables like "%auto_inc%";
+ Variable_name Value
+ auto_increment_increment      1
+ auto_increment_offset 10
+ Create table t2 (a int not null auto_increment, primary key (a)) engine=myisam;
+ insert into t2 values (NULL);
+ insert into t2 values (NULL);
+ select * from t2;
+ a
+ 1
+ 2

***** Using both setting together ****
NDB Results:
Errors are (from /home/ndbdev/jmiller/clones/mysql-5.1-new/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 12: query 'insert into t2 values (NULL)' failed: 1062: Duplicate entry '10' for key 1
(the last lines may be the most important ones)

MyISAM Results:
+ set @@session.auto_increment_increment=100;
+ set @@session.auto_increment_offset=10;
+ show variables like "%auto_inc%";
+ Variable_name Value
+ auto_increment_increment      100
+ auto_increment_offset 10
+ Create table t2 (a int not null auto_increment, primary key (a)) engine=myisam;
+ insert into t2 values (NULL);
+ insert into t2 values (NULL);
+ select * from t2;
+ a
+ 10
+ 110

How to repeat:
1) vi ./t/rpl_ndb_1.test and added test case listed below
2) touch ./r/rpl_ndb_1.result
3) run test:
./mysql-test-run --force --ndb-extra-test --with-ndbcluster-all --mysqld=--default-storage-engine=ndb --do-test=rpl_ndb_1

Note: You can swtich out NDB and MyISAM in the ENGINE= section and comment out the session.auto_increment_increment and auto_increment_offset as need to recreate results above.

TEST CASE:
-- source include/master-slave.inc
--disable_warnings
DROP TABLE IF EXISTS t2;
--enable_warnings
set @@session.auto_increment_increment=100;
set @@session.auto_increment_offset=10;
show variables like "%auto_inc%";

Create table t2 (a int not null auto_increment, primary key (a)) engine=NDB;
insert into t2 values (NULL);
insert into t2 values (NULL);
select * from t2;
DROP TABLE t2;

Suggested fix:
should act just like MyISAM

Seems to be unclear semantics even for MyISAM,
if auto_increment_increment = 1 the
auto_increment_offset doesn't seem to  have any
effect.
Also will never work exactly like in MyISAM since cluster
prefetches auto_increment values in batches, so
the behavior can only be made the same in one
connection with no interleaving from other connections.

changed title to reflect the bug

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Documented in Cluster Replication and Server System Variables sections of 5.1 Manual. Closed.

Re-opening, test case is still disabled.

Martin: regarding your post of Apr 8, http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html says:
" If the value of auto_increment_offset is greater than that of auto_increment_increment, the value of auto_increment_offset is ignored."

test fixed and enabled