MySQL Bugs: #31956: auto increment bugs in MySQL Cluster

Bug #31956	auto increment bugs in MySQL Cluster
Submitted:	30 Oct 2007 15:49	Modified:	15 Dec 2007 9:08
Reporter:	Mikael Ronström	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.1	OS:	Any
Assigned to:	Martin Skold	CPU Architecture:	Any
Tags:	auto_increment, cluster, ndb, ndb_autoincrement_prefetch_sz

Description:
Autoincrement has problems with
1) Updates of autoincrement doesn't update the autoincrement counter
2) Autoincrements are not prefetched beyond statement borders

How to repeat:
-- source include/have_multi_ndb.inc
-- source include/not_embedded.inc

--disable_warnings
connection server2;
DROP TABLE IF EXISTS t1;
connection server1;
DROP TABLE IF EXISTS t1;
--enable_warnings

flush status;

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

# Step 1: Verify simple insert
insert into t1 values (NULL);
select * from t1 order by a;

# Step 2: Verify simple update with higher than highest value causes
#         next insert to use updated_value + 1
update t1 set a = 5 where a = 1;
insert into t1 values (NULL);
select * from t1 order by a;

# Step 3: Verify insert that inserts higher than highest value causes
#         next insert to use inserted_value + 1
insert into t1 values (7);
insert into t1 values (NULL);
select * from t1 order by a;

# Step 4: Verify that insert into hole, lower than highest value doesn't
#         affect next insert
insert into t1 values (2);
insert into t1 values (NULL);
select * from t1 order by a;

# Step 5: Verify that update into hole, lower than highest value doesn't
#         affect next insert
update t1 set a = 4 where a = 2;
insert into t1 values (NULL);
select * from t1 order by a;

# Step 6: Verify that delete of highest value doesn't cause the next
#         insert to reuse this value
delete from t1 where a = 10;
insert into t1 values (NULL);
select * from t1 order by a;

# Step 7: Verify that REPLACE has the same effect as INSERT
replace t1 values (NULL);
select * from t1 order by a;
replace t1 values (15);
select * from t1 order by a;
replace into t1 values (NULL);
select * from t1 order by a;

# Step 8: Verify that REPLACE has the same effect as UPDATE
replace t1 values (15);
select * from t1 order by a;

# Step 9: Verify that IGNORE doesn't affect auto_increment
insert ignore into t1 values (NULL);
select * from t1 order by a;
insert ignore into t1 values (15), (NULL);
select * from t1 order by a;

# Step 10: Verify that on duplicate key as UPDATE behaves as an
#          UPDATE
insert into t1 values (15)
on duplicate key update a = 20;
insert into t1 values (NULL);
select * from t1 order by a;

# Step 11: Verify that on duplicate key as INSERT behaves as INSERT
insert into t1 values (NULL) on duplicate key update a = 30;
select * from t1 order by a;
insert into t1 values (30) on duplicate key update a = 40;
select * from t1 order by a;

#Step 12: Now verify that behaviour on multiple MySQL Servers behave
#         properly. Start by dropping table and recreating it to start
#         counters and id caches from zero again.
drop table t1;
--disable_warnings
connection server2;
drop table if exists t1;
connection server1;
--enable_warnings

create table t1 (a int not null auto_increment primary key) engine ndb;
# Basic test, ensure that the second server gets a new range.
#Generate record with key = 1
insert into t1 values (NULL);
connection server2;
#Generate record with key = 33
insert into t1 values (NULL);
connection server1;
select * from t1;

#This insert should not affect the range of the second server
insert into t1 values (20);
connection server2;
insert into t1 values (NULL);
select * from t1;

connection server1;
#This insert should remove cached values but also skip values already
#taken by server2, given that there is no method of communicating with
#the other server it should also cause a conflict
connection server1;
insert into t1 values (35);
insert into t1 values (NULL);
connection server2;
--error ER_DUP_KEY
insert into t1 values (NULL);
select * from t1;

insert into t1 values (100);
insert into t1 values (NULL);
connection server1;
insert into t1 values (NULL);
select * from t1;

drop table t1;

Suggested fix:
Remove if-statement that checks ha_rows(rows) == 0 and 
always assign m_rows_to_insert to prefetch value

setAutoincrementValue when updating primary key with
autoincrement field.

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/37127

ChangeSet@1.2491, 2007-11-05 20:18:22+01:00, mskold@mysql.com +7 -0
  ha_ndbcluster.h:
    Bug #31956       auto increment bugs in MySQL Cluster: Added utility method and constant for internal prefetch default
  ndb_auto_increment.result:
    BitKeeper file /home/marty/MySQL/mysql-5.0-ndb/mysql-test/r/ndb_auto_increment.result
  mysqld.cc:
    Bug #25176  	Trying to set ndb_autoincrement_prefetch_sz always fails: Changed pointer to max value
    Bug #31956  	auto increment bugs in MySQL Cluster: Changed meaning of ndb_autoincrement_prefetch_sz to specify prefetch between statements, changed default to 1 (with internal prefetch to at least 32 inside a statement)
  ndb_insert.test, ndb_insert.result:
    Moved auto_increment tests to ndb_auto_increment.test
  ndb_auto_increment.test:
    BitKeeper file /home/marty/MySQL/mysql-5.0-ndb/mysql-test/t/ndb_auto_increment.test
  ha_ndbcluster.cc:
    Bug #31956       auto increment bugs in MySQL Cluster: Changed meaning of ndb_autoincrement_prefetch_sz to specify prefetch between statements, changed default to 1 (with internal prefetch to at least 32 inside a statement), added handling of updates of pk/unique key with auto_increment
    Bug #32055  	Cluster does not handle auto inc correctly with insert ignore statement

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/37168

ChangeSet@1.2580, 2007-11-06 10:57:49+01:00, mskold@mysql.com +3 -0
  ndb_auto_increment.result:
    Rename: mysql-test/r/ndb_auto_increment.result -> mysql-test/suite/ndb/r/ndb_auto_increment.result
  ndb_auto_increment.test, ndb_auto_increment.result:
    Bug #31956       auto increment bugs in MySQL Cluster: Adapted test cases
  ha_ndbcluster.cc:
    Bug #31956       auto increment bugs in MySQL Cluster: Merging from 5.0
  ndb_auto_increment.test:
    Rename: mysql-test/t/ndb_auto_increment.test -> mysql-test/suite/ndb/t/ndb_auto_increment.test

Documented bugfix in 5.1.22-ndb-6.2.10 and 5.1.22-ndb-6.3.7 changelogs, behaviour change for ndb_autoincrement_prefetch_sz. Set status to PQ for mainline pushes.

Pushed into 5.1.23-rc

Pushed into 5.0.56

Pushed into 6.0.5-alpha

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

Bugfix and behaviour change also now documented for 5.0.56, 5.1.23, and 6.0.5. Closed.