From: Date: November 5 2007 8:18pm Subject: bk commit into 5.0 tree (mskold:1.2491) BUG#31956 List-Archive: http://lists.mysql.com/commits/37127 X-Bug: 31956 Message-Id: <200711051918.lA5JIPIV023746@quadfish.ndb.mysql.com> Below is the list of changes that have just been committed into a local 5.0 repository of marty. When marty does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-11-05 20:18:22+01:00, mskold@stripped +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 mysql-test/r/ndb_auto_increment.result@stripped, 2007-11-05 18:15:18+01:00, mskold@stripped +445 -0 BitKeeper file /home/marty/MySQL/mysql-5.0-ndb/mysql-test/r/ndb_auto_increment.result mysql-test/r/ndb_auto_increment.result@stripped, 2007-11-05 18:15:18+01:00, mskold@stripped +0 -0 mysql-test/r/ndb_insert.result@stripped, 2007-11-05 18:16:18+01:00, mskold@stripped +0 -169 Moved auto_increment tests to ndb_auto_increment.test mysql-test/t/ndb_auto_increment.test@stripped, 2007-11-05 18:15:18+01:00, mskold@stripped +294 -0 BitKeeper file /home/marty/MySQL/mysql-5.0-ndb/mysql-test/t/ndb_auto_increment.test mysql-test/t/ndb_auto_increment.test@stripped, 2007-11-05 18:15:18+01:00, mskold@stripped +0 -0 mysql-test/t/ndb_insert.test@stripped, 2007-11-05 18:15:54+01:00, mskold@stripped +0 -138 Moved auto_increment tests to ndb_auto_increment.test sql/ha_ndbcluster.cc@stripped, 2007-11-05 18:11:55+01:00, mskold@stripped +62 -22 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 sql/ha_ndbcluster.h@stripped, 2007-11-05 18:09:13+01:00, mskold@stripped +3 -0 Bug #31956 auto increment bugs in MySQL Cluster: Added utility method and constant for internal prefetch default sql/mysqld.cc@stripped, 2007-11-05 18:04:13+01:00, mskold@stripped +2 -2 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) # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: mskold # Host: quadfish.(none) # Root: /home/marty/MySQL/mysql-5.0-ndb --- 1.609/sql/mysqld.cc 2007-05-02 14:25:09 +02:00 +++ 1.610/sql/mysqld.cc 2007-11-05 18:04:13 +01:00 @@ -5282,8 +5282,8 @@ {"ndb-autoincrement-prefetch-sz", OPT_NDB_AUTOINCREMENT_PREFETCH_SZ, "Specify number of autoincrement values that are prefetched.", (gptr*) &global_system_variables.ndb_autoincrement_prefetch_sz, - (gptr*) &global_system_variables.ndb_autoincrement_prefetch_sz, - 0, GET_ULONG, REQUIRED_ARG, 32, 1, 256, 0, 0, 0}, + (gptr*) &max_system_variables.ndb_autoincrement_prefetch_sz, + 0, GET_ULONG, REQUIRED_ARG, 1, 1, 256, 0, 0, 0}, {"ndb-force-send", OPT_NDB_FORCE_SEND, "Force send of buffers to ndb immediately without waiting for " "other threads.", --- 1.12/mysql-test/r/ndb_insert.result 2007-05-09 14:14:12 +02:00 +++ 1.13/mysql-test/r/ndb_insert.result 2007-11-05 18:16:18 +01:00 @@ -657,172 +657,3 @@ 2 NULL 3 NULL drop table t1; -CREATE TABLE t1 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER; -CREATE TABLE t2 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=MYISAM; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -pk b c -1 1 0 -11 2 1 -21 3 2 -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -COUNT(t1.pk) -3 -TRUNCATE t1; -TRUNCATE t2; -SET @@session.auto_increment_offset=5; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); -SELECT * FROM t1 ORDER BY pk; -pk b c -5 1 0 -15 2 1 -25 3 2 -27 4 3 -35 5 4 -99 6 5 -105 7 6 -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -COUNT(t1.pk) -7 -TRUNCATE t1; -TRUNCATE t2; -SET @@session.auto_increment_increment=2; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -pk b c -1 1 0 -3 2 1 -5 3 2 -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -COUNT(t1.pk) -3 -DROP TABLE t1, t2; -CREATE TABLE t1 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; -CREATE TABLE t2 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 7; -SET @@session.auto_increment_offset=1; -SET @@session.auto_increment_increment=1; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -pk b c -7 1 0 -8 2 1 -9 3 2 -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -COUNT(t1.pk) -3 -DROP TABLE t1, t2; -CREATE TABLE t1 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3; -CREATE TABLE t2 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 3; -SET @@session.auto_increment_offset=5; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -pk b c -5 1 0 -15 2 1 -25 3 2 -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -COUNT(t1.pk) -3 -DROP TABLE t1, t2; -CREATE TABLE t1 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; -CREATE TABLE t2 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 7; -SET @@session.auto_increment_offset=5; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -pk b c -15 1 0 -25 2 1 -35 3 2 -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -COUNT(t1.pk) -3 -DROP TABLE t1, t2; -CREATE TABLE t1 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5; -CREATE TABLE t2 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 5; -SET @@session.auto_increment_offset=5; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -pk b c -5 1 0 -15 2 1 -25 3 2 -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -COUNT(t1.pk) -3 -DROP TABLE t1, t2; -CREATE TABLE t1 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100; -CREATE TABLE t2 ( -pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, -b INT NOT NULL, -c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 100; -SET @@session.auto_increment_offset=5; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -pk b c -105 1 0 -115 2 1 -125 3 2 -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -COUNT(t1.pk) -3 -DROP TABLE t1, t2; --- 1.14/mysql-test/t/ndb_insert.test 2007-05-09 14:14:12 +02:00 +++ 1.15/mysql-test/t/ndb_insert.test 2007-11-05 18:15:54 +01:00 @@ -638,142 +638,4 @@ insert ignore into t1 values (1,0), (2,0), (2,null), (3,null); select * from t1 order by a; drop table t1; - -# Bug#26342 auto_increment_increment AND auto_increment_offset REALLY REALLY anger NDB cluster - -CREATE TABLE t1 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER; - -CREATE TABLE t2 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=MYISAM; - -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -TRUNCATE t1; -TRUNCATE t2; -SET @@session.auto_increment_offset=5; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); -SELECT * FROM t1 ORDER BY pk; -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -TRUNCATE t1; -TRUNCATE t2; -SET @@session.auto_increment_increment=2; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -DROP TABLE t1, t2; - -CREATE TABLE t1 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; - -CREATE TABLE t2 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 7; - -SET @@session.auto_increment_offset=1; -SET @@session.auto_increment_increment=1; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -DROP TABLE t1, t2; - -CREATE TABLE t1 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3; - -CREATE TABLE t2 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 3; - -SET @@session.auto_increment_offset=5; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -DROP TABLE t1, t2; - -CREATE TABLE t1 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; - -CREATE TABLE t2 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 7; - -SET @@session.auto_increment_offset=5; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -DROP TABLE t1, t2; - -CREATE TABLE t1 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5; - -CREATE TABLE t2 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 5; - -SET @@session.auto_increment_offset=5; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -DROP TABLE t1, t2; - -CREATE TABLE t1 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100; - -CREATE TABLE t2 ( - pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, - b INT NOT NULL, - c INT NOT NULL UNIQUE -) ENGINE=MYISAM AUTO_INCREMENT = 100; - -SET @@session.auto_increment_offset=5; -SET @@session.auto_increment_increment=10; -INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); -INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); -SELECT * FROM t1 ORDER BY pk; -SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; -DROP TABLE t1, t2; - # End of 4.1 tests --- 1.320/sql/ha_ndbcluster.cc 2007-10-25 10:54:21 +02:00 +++ 1.321/sql/ha_ndbcluster.cc 2007-11-05 18:11:55 +01:00 @@ -2259,6 +2259,25 @@ DBUG_RETURN(next_result(buf)); } +int +ha_ndbcluster::set_auto_inc(Field *field) +{ + Ndb *ndb= get_ndb(); + Uint64 next_val= (Uint64) field->val_int() + 1; + DBUG_ENTER("ha_ndbcluster::set_auto_inc"); +#ifndef DBUG_OFF + char buff[22]; + DBUG_PRINT("info", + ("Trying to set next auto increment value to %s", + llstr(next_val, buff))); +#endif + if (ndb->setAutoIncrementValue((const NDBTAB *) m_table, next_val, TRUE) + == -1) + ERR_RETURN(ndb->getNdbError()); + DBUG_RETURN(0); +} + + /* Insert one record into NDB */ @@ -2413,17 +2432,11 @@ } if ((has_auto_increment) && (m_skip_auto_increment)) { - Ndb *ndb= get_ndb(); - Uint64 next_val= (Uint64) table->next_number_field->val_int() + 1; -#ifndef DBUG_OFF - char buff[22]; - DBUG_PRINT("info", - ("Trying to set next auto increment value to %s", - llstr(next_val, buff))); -#endif - if (ndb->setAutoIncrementValue((const NDBTAB *) m_table, next_val, TRUE) - == -1) - ERR_RETURN(ndb->getNdbError()); + int ret_val; + if ((ret_val= set_auto_inc(table->next_number_field))) + { + DBUG_RETURN(ret_val); + } } m_skip_auto_increment= TRUE; @@ -2476,6 +2489,7 @@ NdbScanOperation* cursor= m_active_cursor; NdbOperation *op; uint i; + int auto_res; bool pk_update= (table->s->primary_key != MAX_KEY && key_cmp(table->s->primary_key, old_data, new_data)); DBUG_ENTER("update_row"); @@ -2531,6 +2545,16 @@ // Insert new row DBUG_PRINT("info", ("delete succeded")); m_primary_key_update= TRUE; + /* + If we are updating a primary key with auto_increment + then we need to update the auto_increment counter + */ + if (table->found_next_number_field && + table->found_next_number_field->query_id == thd->query_id && + (auto_res= set_auto_inc(table->found_next_number_field))) + { + DBUG_RETURN(auto_res); + } insert_res= write_row(new_data); m_primary_key_update= FALSE; if (insert_res) @@ -2553,7 +2577,16 @@ DBUG_PRINT("info", ("delete+insert succeeded")); DBUG_RETURN(0); } - + /* + If we are updating a unique key with auto_increment + then we need to update the auto_increment counter + */ + if (table->found_next_number_field && + table->found_next_number_field->query_id == thd->query_id && + (auto_res= set_auto_inc(table->found_next_number_field))) + { + DBUG_RETURN(auto_res); + } if (cursor) { /* @@ -3841,9 +3874,11 @@ // store thread specific data first to set the right context m_force_send= thd->variables.ndb_force_send; m_ha_not_exact_count= !thd->variables.ndb_use_exact_count; - m_autoincrement_prefetch= - (ha_rows) thd->variables.ndb_autoincrement_prefetch_sz; - + m_autoincrement_prefetch= + (thd->variables.ndb_autoincrement_prefetch_sz > + NDB_DEFAULT_AUTO_PREFETCH) ? + (ha_rows) thd->variables.ndb_autoincrement_prefetch_sz + : (ha_rows) NDB_DEFAULT_AUTO_PREFETCH; m_active_trans= thd_ndb->all ? thd_ndb->all : thd_ndb->stmt; DBUG_ASSERT(m_active_trans); // Start of transaction @@ -4868,8 +4903,9 @@ { int cache_size; Uint64 auto_value; - Uint64 step= current_thd->variables.auto_increment_increment; - Uint64 start= current_thd->variables.auto_increment_offset; + THD *thd= current_thd; + Uint64 step= thd->variables.auto_increment_increment; + Uint64 start= thd->variables.auto_increment_offset; DBUG_ENTER("get_auto_increment"); DBUG_PRINT("enter", ("m_tabname: %s", m_tabname)); Ndb *ndb= get_ndb(); @@ -4879,11 +4915,15 @@ /* We guessed too low */ m_rows_to_insert+= m_autoincrement_prefetch; } + int remaining= m_rows_to_insert - m_rows_inserted; + int min_prefetch= + (remaining < thd->variables.ndb_autoincrement_prefetch_sz) ? + thd->variables.ndb_autoincrement_prefetch_sz + : remaining; cache_size= - (int) ((m_rows_to_insert - m_rows_inserted < m_autoincrement_prefetch) ? - m_rows_to_insert - m_rows_inserted : - ((m_rows_to_insert > m_autoincrement_prefetch) ? - m_rows_to_insert : m_autoincrement_prefetch)); + (int) ((remaining < m_autoincrement_prefetch) ? + min_prefetch + : remaining); uint retries= NDB_AUTO_INCREMENT_RETRIES; int retry_sleep= 30; /* 30 milliseconds, transaction */ for (;;) @@ -4953,7 +4993,7 @@ m_dupkey((uint) -1), m_ha_not_exact_count(FALSE), m_force_send(TRUE), - m_autoincrement_prefetch((ha_rows) 32), + m_autoincrement_prefetch((ha_rows) NDB_DEFAULT_AUTO_PREFETCH), m_transaction_on(TRUE), m_cond(NULL), m_multi_cursor(NULL) --- 1.117/sql/ha_ndbcluster.h 2007-10-02 13:35:47 +02:00 +++ 1.118/sql/ha_ndbcluster.h 2007-11-05 18:09:13 +01:00 @@ -27,6 +27,8 @@ #include #define NDB_HIDDEN_PRIMARY_KEY_LENGTH 8 +#define NDB_DEFAULT_AUTO_PREFETCH 32 + /* Forward declarations */ class Ndb; class NdbOperation; @@ -271,6 +273,7 @@ int full_table_scan(byte * buf); int fetch_next(NdbScanOperation* op); int next_result(byte *buf); + int set_auto_inc(Field *field); int define_read_attrs(byte* buf, NdbOperation* op); int filtered_scan(const byte *key, uint key_len, byte *buf, --- New file --- +++ mysql-test/r/ndb_auto_increment.result 07/11/05 18:15:18 DROP TABLE IF EXISTS t1,t2; DROP TABLE IF EXISTS t1; set @old_auto_increment_offset = @@session.auto_increment_offset; set @old_auto_increment_increment = @@session.auto_increment_increment; set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz; flush status; create table t1 (a int not null auto_increment primary key) engine ndb; insert into t1 values (NULL); select * from t1 order by a; a 1 update t1 set a = 5 where a = 1; insert into t1 values (NULL); select * from t1 order by a; a 5 6 insert into t1 values (7); insert into t1 values (NULL); select * from t1 order by a; a 5 6 7 8 insert into t1 values (2); insert into t1 values (NULL); select * from t1 order by a; a 2 5 6 7 8 9 update t1 set a = 4 where a = 2; insert into t1 values (NULL); select * from t1 order by a; a 4 5 6 7 8 9 10 delete from t1 where a = 10; insert into t1 values (NULL); select * from t1 order by a; a 4 5 6 7 8 9 11 replace t1 values (NULL); select * from t1 order by a; a 4 5 6 7 8 9 11 12 replace t1 values (15); select * from t1 order by a; a 4 5 6 7 8 9 11 12 15 replace into t1 values (NULL); select * from t1 order by a; a 4 5 6 7 8 9 11 12 15 16 replace t1 values (15); select * from t1 order by a; a 4 5 6 7 8 9 11 12 15 16 insert ignore into t1 values (NULL); select * from t1 order by a; a 4 5 6 7 8 9 11 12 15 16 17 insert ignore into t1 values (15), (NULL); select * from t1 order by a; a 4 5 6 7 8 9 11 12 15 16 17 18 insert into t1 values (15) on duplicate key update a = 20; insert into t1 values (NULL); select * from t1 order by a; a 4 5 6 7 8 9 11 12 16 17 18 20 21 insert into t1 values (NULL) on duplicate key update a = 30; select * from t1 order by a; a 4 5 6 7 8 9 11 12 16 17 18 20 21 22 insert into t1 values (30) on duplicate key update a = 40; select * from t1 order by a; a 4 5 6 7 8 9 11 12 16 17 18 20 21 22 30 insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL); select * from t1 order by a; a 4 5 6 7 8 9 11 12 16 17 18 20 21 22 30 600 601 602 610 611 drop table t1; create table t1 (a int not null primary key, b int not null unique auto_increment) engine ndb; insert into t1 values (1, NULL); insert into t1 values (3, NULL); update t1 set b = 3 where a = 3; insert into t1 values (4, NULL); select * from t1 order by a; a b 1 1 3 3 4 4 drop table t1; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; pk b c 1 1 0 11 2 1 21 3 2 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; COUNT(t1.pk) 3 TRUNCATE t1; TRUNCATE t2; SET @@session.auto_increment_offset=5; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); SELECT * FROM t1 ORDER BY pk; pk b c 5 1 0 15 2 1 25 3 2 27 4 3 35 5 4 99 6 5 105 7 6 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; COUNT(t1.pk) 7 TRUNCATE t1; TRUNCATE t2; SET @@session.auto_increment_increment=2; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; pk b c 1 1 0 3 2 1 5 3 2 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; COUNT(t1.pk) 3 DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 7; SET @@session.auto_increment_offset=1; SET @@session.auto_increment_increment=1; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; pk b c 7 1 0 8 2 1 9 3 2 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; COUNT(t1.pk) 3 DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 3; SET @@session.auto_increment_offset=5; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; pk b c 5 1 0 15 2 1 25 3 2 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; COUNT(t1.pk) 3 DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 7; SET @@session.auto_increment_offset=5; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; pk b c 15 1 0 25 2 1 35 3 2 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; COUNT(t1.pk) 3 DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 5; SET @@session.auto_increment_offset=5; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; pk b c 5 1 0 15 2 1 25 3 2 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; COUNT(t1.pk) 3 DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 100; SET @@session.auto_increment_offset=5; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; pk b c 105 1 0 115 2 1 125 3 2 SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; COUNT(t1.pk) 3 DROP TABLE t1, t2; SET @@session.auto_increment_offset=1; SET @@session.auto_increment_increment=1; set ndb_autoincrement_prefetch_sz = 32; drop table if exists t1; SET @@session.auto_increment_offset=1; SET @@session.auto_increment_increment=1; set ndb_autoincrement_prefetch_sz = 32; create table t1 (a int not null auto_increment primary key) engine ndb; insert into t1 values (NULL); insert into t1 values (NULL); select * from t1 order by a; a 1 33 insert into t1 values (20); insert into t1 values (NULL); select * from t1 order by a; a 1 20 33 34 insert into t1 values (35); insert into t1 values (NULL); insert into t1 values (NULL); ERROR 23000: Duplicate entry '35' for key 1 select * from t1 order by a; a 1 20 21 33 34 35 insert into t1 values (100); insert into t1 values (NULL); insert into t1 values (NULL); select * from t1 order by a; a 1 20 21 22 33 34 35 100 101 set auto_increment_offset = @old_auto_increment_offset; set auto_increment_increment = @old_auto_increment_increment; set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz; drop table t1; --- New file --- +++ mysql-test/t/ndb_auto_increment.test 07/11/05 18:15:18 -- source include/have_ndb.inc -- source include/have_multi_ndb.inc -- source include/not_embedded.inc --disable_warnings connection server1; DROP TABLE IF EXISTS t1,t2; connection server2; DROP TABLE IF EXISTS t1; connection server1; --enable_warnings set @old_auto_increment_offset = @@session.auto_increment_offset; set @old_auto_increment_increment = @@session.auto_increment_increment; set @old_ndb_autoincrement_prefetch_sz = @@session.ndb_autoincrement_prefetch_sz; 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: Vefify INSERT IGNORE (bug#32055) insert ignore into t1 values(600),(NULL),(NULL),(610),(NULL); select * from t1 order by a; drop table t1; #Step 13: Verify auto_increment of unique key create table t1 (a int not null primary key, b int not null unique auto_increment) engine ndb; insert into t1 values (1, NULL); insert into t1 values (3, NULL); update t1 set b = 3 where a = 3; insert into t1 values (4, NULL); select * from t1 order by a; drop table t1; #Step 14: Verify that auto_increment_increment and auto_increment_offset # work as expected CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; TRUNCATE t1; TRUNCATE t2; SET @@session.auto_increment_offset=5; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t1 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (pk,b,c) VALUES (27,4,3),(NULL,5,4),(99,6,5),(NULL,7,6); SELECT * FROM t1 ORDER BY pk; SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; TRUNCATE t1; TRUNCATE t2; SET @@session.auto_increment_increment=2; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 7; SET @@session.auto_increment_offset=1; SET @@session.auto_increment_increment=1; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 3; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 3; SET @@session.auto_increment_offset=5; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 7; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 7; SET @@session.auto_increment_offset=5; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 5; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 5; SET @@session.auto_increment_offset=5; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; DROP TABLE t1, t2; CREATE TABLE t1 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=NDBCLUSTER AUTO_INCREMENT = 100; CREATE TABLE t2 ( pk INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b INT NOT NULL, c INT NOT NULL UNIQUE ) ENGINE=MYISAM AUTO_INCREMENT = 100; SET @@session.auto_increment_offset=5; SET @@session.auto_increment_increment=10; INSERT INTO t1 (b,c) VALUES (1,0),(2,1),(3,2); INSERT INTO t2 (b,c) VALUES (1,0),(2,1),(3,2); SELECT * FROM t1 ORDER BY pk; SELECT COUNT(t1.pk) FROM t1, t2 WHERE t1.pk = t2.pk AND t1.b = t2.b AND t1.c = t1.c; DROP TABLE t1, t2; #Step 15: 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. --disable_warnings connection server2; SET @@session.auto_increment_offset=1; SET @@session.auto_increment_increment=1; set ndb_autoincrement_prefetch_sz = 32; drop table if exists t1; connection server1; SET @@session.auto_increment_offset=1; SET @@session.auto_increment_increment=1; set ndb_autoincrement_prefetch_sz = 32; --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 order by a; #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 order by a; 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_ENTRY insert into t1 values (NULL); select * from t1 order by a; insert into t1 values (100); insert into t1 values (NULL); connection server1; insert into t1 values (NULL); select * from t1 order by a; set auto_increment_offset = @old_auto_increment_offset; set auto_increment_increment = @old_auto_increment_increment; set ndb_autoincrement_prefetch_sz = @old_ndb_autoincrement_prefetch_sz; drop table t1;