#At file:///Users/mattiasj/clones/bzrroot/topush-60-5129rc/
2844 Mattias Jonsson 2008-10-01 [merge]
merge (basically a null merge, since it contains a backport of bug#33479 as a fix for Bug#38804)
removed:
mysql-test/suite/parts/inc/partition_auto_increment.inc
mysql-test/suite/parts/r/partition_auto_increment_innodb.result
mysql-test/suite/parts/r/partition_auto_increment_myisam.result
mysql-test/suite/parts/t/partition_auto_increment_innodb.test
mysql-test/suite/parts/t/partition_auto_increment_myisam.test
added:
mysql-test/suite/parts/inc/partition_auto_increment.inc
mysql-test/suite/parts/r/partition_auto_increment_archive.result
mysql-test/suite/parts/r/partition_auto_increment_blackhole.result
mysql-test/suite/parts/r/partition_auto_increment_innodb.result
mysql-test/suite/parts/r/partition_auto_increment_memory.result
mysql-test/suite/parts/r/partition_auto_increment_myisam.result
mysql-test/suite/parts/r/partition_auto_increment_ndb.result
mysql-test/suite/parts/t/partition_auto_increment_archive.test
mysql-test/suite/parts/t/partition_auto_increment_blackhole.test
mysql-test/suite/parts/t/partition_auto_increment_innodb.test
mysql-test/suite/parts/t/partition_auto_increment_memory.test
mysql-test/suite/parts/t/partition_auto_increment_myisam.test
mysql-test/suite/parts/t/partition_auto_increment_ndb.test
modified:
mysql-test/suite/parts/r/partition_auto_increment_falcon.result
mysql-test/suite/parts/t/disabled.def
sql/ha_partition.cc
sql/ha_partition.h
per-file messages:
mysql-test/suite/parts/t/disabled.def
Enabled partition_auto_increment_falcon, since it now works, disabled parttion_auto_increment_ndb since it have changed behavior which should be investigated
sql/ha_partition.cc
Bug#38804
merge of fixes when backporting bug#33479 to 5.1
sql/ha_partition.h
Bug#38804
merge of fixes when backporting bug#33479 to 5.1
=== added file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc 2008-09-08 13:30:01 +0000
@@ -0,0 +1,600 @@
+# inc/partition_auto_increment.inc
+#
+# auto_increment test
+# used variables: $engine
+#
+
+-- disable_warnings
+DROP TABLE IF EXISTS t1;
+-- enable_warnings
+
+-- echo # test without partitioning for reference
+eval CREATE TABLE t1 (
+ c1 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c1))
+ENGINE=$engine;
+SHOW CREATE TABLE t1;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (0);
+-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (5), (16);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_update)
+{
+ UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+ UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+ -- error 0, ER_BAD_NULL_ERROR
+ UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+ INSERT INTO t1 VALUES (NULL);
+ INSERT INTO t1 VALUES (NULL);
+}
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+eval CREATE TABLE t1 (
+ c1 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c1))
+ENGINE=$engine;
+SHOW CREATE TABLE t1;
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+if (!$skip_delete)
+{
+DELETE FROM t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+if (!$skip_truncate)
+{
+TRUNCATE TABLE t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Simple test with NULL
+eval CREATE TABLE t1 (
+ c1 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c1))
+ENGINE=$engine
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;
+
+-- echo # Test with sql_mode and first insert as 0
+eval CREATE TABLE t1 (
+ c1 INT,
+ c2 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c2))
+ENGINE=$engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (1, NULL);
+let $old_sql_mode = `select @@session.sql_mode`;
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 0);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+SELECT * FROM t1 ORDER BY c1, c2;
+DROP TABLE t1;
+eval CREATE TABLE t1 (
+ c1 INT,
+ c2 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c2))
+ENGINE=$engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (1, 0);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (1, 1), (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (4, 7);
+INSERT INTO t1 VALUES (1, NULL);
+SELECT * FROM t1 ORDER BY c1, c2;
+eval SET @@session.sql_mode = '$old_sql_mode';
+DROP TABLE t1;
+
+
+-- echo # Simple test with NULL, 0 and explicit values both incr. and desc.
+eval CREATE TABLE t1 (
+ c1 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c1))
+ENGINE=$engine
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (5), (16);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (17), (19), (NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (NULL), (9);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (59), (55);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_update)
+{
+ UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+ UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+ -- error 0, ER_BAD_NULL_ERROR
+ UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+ INSERT INTO t1 VALUES (NULL);
+ INSERT INTO t1 VALUES (NULL);
+}
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Test with auto_increment_increment and auto_increment_offset.
+eval CREATE TABLE t1 (
+ c1 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c1))
+ENGINE=$engine
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+let $old_increment = `SELECT @@session.auto_increment_increment`;
+let $old_offset = `SELECT @@session.auto_increment_offset`;
+SET @@session.auto_increment_increment = 10;
+SET @@session.auto_increment_offset = 5;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SET @@session.auto_increment_increment = 5;
+SET @@session.auto_increment_offset = 3;
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 1);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 2);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 3);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 4);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 5);
+INSERT INTO t1 VALUES (NULL);
+let $new_val = `SELECT LAST_INSERT_ID()`;
+eval INSERT INTO t1 VALUES ($new_val + 6);
+INSERT INTO t1 VALUES (NULL);
+eval SET @@session.auto_increment_increment = $old_increment;
+eval SET @@session.auto_increment_offset = $old_offset;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+
+-- echo # Test reported auto_increment value
+eval CREATE TABLE t1 (
+ c1 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c1))
+ENGINE=$engine
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (10);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+SELECT * FROM t1 ORDER BY c1;
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+INSERT INTO t1 VALUES (NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (15);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+INSERT INTO t1 VALUES (NULL);
+if (!$skip_delete)
+{
+DELETE FROM t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+if (!$skip_truncate)
+{
+TRUNCATE TABLE t1;
+}
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Test with two threads
+connection default;
+-- echo # con default
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ ENGINE = $engine
+ PARTITION BY HASH(c1)
+ PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+connect(con1, localhost, root,,);
+connection con1;
+-- echo # con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+connection default;
+-- echo # con default
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+disconnect con1;
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Test with two threads + start transaction NO PARTITIONING
+connect(con1, localhost, root,,);
+connection default;
+-- echo # con default
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ ENGINE = $engine;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+connection con1;
+-- echo # con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+connection default;
+-- echo # con default
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+disconnect con1;
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Test with two threads + start transaction
+connect(con1, localhost, root,,);
+connection default;
+-- echo # con default
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ ENGINE = $engine
+ PARTITION BY HASH(c1)
+ PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+connection con1;
+-- echo # con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+connection default;
+-- echo # con default
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (16);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+-- echo # con1
+connection con1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+disconnect con1;
+connection default;
+-- echo # con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+if (!$only_ai_pk)
+{
+-- echo # Test with another column after
+eval CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = $engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
+INSERT INTO t1 VALUES (NULL, 3);
+INSERT INTO t1 VALUES (2, 0), (NULL, 2);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (NULL, 2);
+SELECT * FROM t1 ORDER BY c1,c2;
+DROP TABLE t1;
+}
+
+-- echo # Test with another column before
+eval CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = $engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, 1);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 2);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+DROP TABLE t1;
+
+-- echo # Test with auto_increment on secondary column in multi-column-index
+-- disable_abort_on_error
+eval CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = $engine
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+-- enable_abort_on_error
+-- disable_query_log
+eval SET @my_errno= $mysql_errno ;
+let $run = `SELECT @my_errno = 0`;
+# ER_WRONG_AUTO_KEY is 1075
+let $ER_WRONG_AUTO_KEY= 1075;
+if (`SELECT @my_errno NOT IN (0,$ER_WRONG_AUTO_KEY)`)
+{
+ -- echo # Unknown error code, exits
+ exit;
+}
+-- enable_query_log
+if ($run)
+{
+INSERT INTO t1 VALUES (1, 0);
+-- error 0, ER_DUP_KEY, ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, 1);
+if (!$mysql_errno)
+{
+ echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (3, NULL);
+INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 VALUES (2, 2);
+if (!$mysql_errno)
+{
+echo # ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY;
+}
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+DROP TABLE t1;
+}
+
+-- echo # Test AUTO_INCREMENT in CREATE
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ ENGINE = $engine
+ AUTO_INCREMENT = 15
+ PARTITION BY HASH(c1)
+ PARTITIONS 2;
+SHOW CREATE TABLE t1;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (4);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+
+-- echo # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+let $old_sql_mode = `select @@session.sql_mode`;
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+-- error 0, ER_DUP_KEY
+INSERT INTO t1 (c1) VALUES (0);
+if ($mysql_errno)
+{
+ echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno;
+}
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+eval SET @@session.sql_mode = '$old_sql_mode';
+DROP TABLE t1;
+
+-- echo # Test SET INSERT_ID
+eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ ENGINE = $engine
+ PARTITION BY HASH(c1)
+ PARTITIONS 2;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
+-- echo # Testing with FLUSH TABLE
+eval CREATE TABLE t1 (
+ c1 INT NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (c1))
+ ENGINE=$engine
+ PARTITION BY HASH(c1)
+ PARTITIONS 2;
+SHOW CREATE TABLE t1;
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+SELECT * FROM t1 ORDER BY c1;
+DROP TABLE t1;
+
=== removed file 'mysql-test/suite/parts/inc/partition_auto_increment.inc'
--- a/mysql-test/suite/parts/inc/partition_auto_increment.inc 2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc 1970-01-01 00:00:00 +0000
@@ -1,466 +0,0 @@
-# inc/partition_auto_increment.inc
-#
-# auto_increment test
-# used variables: $engine
-#
-
--- disable_warnings
-DROP TABLE IF EXISTS t1;
--- enable_warnings
-
--- echo # test without partitioning for reference
-eval CREATE TABLE t1 (
- c1 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c1))
-ENGINE=$engine;
-SHOW CREATE TABLE t1;
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (2);
-INSERT INTO t1 VALUES (4);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (0);
--- error ER_DUP_ENTRY
-INSERT INTO t1 VALUES (5), (16);
-INSERT INTO t1 VALUES (17);
-INSERT INTO t1 VALUES (19), (NULL);
-INSERT INTO t1 VALUES (NULL), (10), (NULL);
-INSERT INTO t1 VALUES (NULL);
-SET INSERT_ID = 30;
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-eval CREATE TABLE t1 (
- c1 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c1))
-ENGINE=$engine;
-SHOW CREATE TABLE t1;
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (4);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-DELETE FROM t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-TRUNCATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Simple test with NULL
-eval CREATE TABLE t1 (
- c1 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c1))
-ENGINE=$engine
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1;
-DROP TABLE t1;
-
--- echo # Test with sql_mode and first insert as 0
-eval CREATE TABLE t1 (
- c1 INT,
- c2 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c2))
-ENGINE=$engine
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, NULL);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (1, 1), (99, 99);
-INSERT INTO t1 VALUES (1, NULL);
-let $old_sql_mode = `select @@session.sql_mode`;
-SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-INSERT INTO t1 VALUES (1, 0);
-SELECT * FROM t1 ORDER BY c1, c2;
-DROP TABLE t1;
-eval CREATE TABLE t1 (
- c1 INT,
- c2 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c2))
-ENGINE=$engine
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1), (1, NULL);
-INSERT INTO t1 VALUES (2, NULL), (4, 7);
-INSERT INTO t1 VALUES (1, NULL);
-SELECT * FROM t1 ORDER BY c1, c2;
-eval SET @@session.sql_mode = '$old_sql_mode';
-DROP TABLE t1;
-
-
--- echo # Simple test with NULL, 0 and explicit values both incr. and desc.
-eval CREATE TABLE t1 (
- c1 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c1))
-ENGINE=$engine
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (2), (4), (NULL);
-INSERT INTO t1 VALUES (0);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (5), (16);
-INSERT INTO t1 VALUES (17), (19), (NULL);
-INSERT INTO t1 VALUES (NULL), (10), (NULL);
-INSERT INTO t1 VALUES (NULL), (9);
-INSERT INTO t1 VALUES (59), (55);
-INSERT INTO t1 VALUES (NULL), (90);
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with auto_increment_increment and auto_increment_offset.
-eval CREATE TABLE t1 (
- c1 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c1))
-ENGINE=$engine
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-let $old_increment = `SELECT @@session.auto_increment_increment`;
-let $old_offset = `SELECT @@session.auto_increment_offset`;
-SET @@session.auto_increment_increment = 10;
-SET @@session.auto_increment_offset = 5;
-INSERT INTO t1 VALUES (1);
-INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
-SET @@session.auto_increment_increment = 5;
-SET @@session.auto_increment_offset = 3;
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 1);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 2);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 3);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 4);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 5);
-INSERT INTO t1 VALUES (NULL);
-let $new_val = `SELECT LAST_INSERT_ID()`;
-eval INSERT INTO t1 VALUES ($new_val + 6);
-INSERT INTO t1 VALUES (NULL);
-eval SET @@session.auto_increment_increment = $old_increment;
-eval SET @@session.auto_increment_offset = $old_offset;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
-
--- echo # Test reported auto_increment value
-eval CREATE TABLE t1 (
- c1 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c1))
-ENGINE=$engine
-PARTITION BY HASH (c1)
-PARTITIONS 2;
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (2);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (4);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (17);
-INSERT INTO t1 VALUES (19);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (10);
-SELECT * FROM t1 ORDER BY c1;
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (15);
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-INSERT INTO t1 VALUES (NULL);
-DELETE FROM t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-TRUNCATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with two threads
-connect(con1, localhost, root,,);
-connection default;
--- echo # con default
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
- ENGINE = $engine
- PARTITION BY HASH(c1)
- PARTITIONS 2;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-connection con1;
--- echo # con1
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (10);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (19);
-INSERT INTO t1 (c1) VALUES (21);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (16);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-disconnect con1;
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with two threads + start transaction NO PARTITIONING
-connect(con1, localhost, root,,);
-connection default;
--- echo # con default
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
- ENGINE = $engine;
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-connection con1;
--- echo # con1
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (10);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (19);
-INSERT INTO t1 (c1) VALUES (21);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (16);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-disconnect con1;
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with two threads + start transaction
-connect(con1, localhost, root,,);
-connection default;
--- echo # con default
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
- ENGINE = $engine
- PARTITION BY HASH(c1)
- PARTITIONS 2;
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-connection con1;
--- echo # con1
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (NULL), (10);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
-INSERT INTO t1 (c1) VALUES (21);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (16);
--- echo # con1
-connection con1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-disconnect con1;
-connection default;
--- echo # con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Test with another column after
-eval CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-c2 INT,
-PRIMARY KEY (c1,c2))
-ENGINE = $engine
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
-INSERT INTO t1 VALUES (NULL, 3);
-INSERT INTO t1 VALUES (2, 0), (NULL, 2);
-INSERT INTO t1 VALUES (2, 2);
-INSERT INTO t1 VALUES (2, 22);
-INSERT INTO t1 VALUES (NULL, 2);
-SELECT * FROM t1 ORDER BY c1,c2;
-DROP TABLE t1;
-
--- echo # Test with another column before
-eval CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c2))
-ENGINE = $engine
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
-INSERT INTO t1 VALUES (2, NULL);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (2, 2);
-INSERT INTO t1 VALUES (2, 22);
-INSERT INTO t1 VALUES (2, NULL);
-SELECT * FROM t1 ORDER BY c1,c2;
-DROP TABLE t1;
-
--- echo # Test with auto_increment on secondary column in multi-column-index
--- disable_abort_on_error
-eval CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1,c2))
-ENGINE = $engine
-PARTITION BY HASH(c2)
-PARTITIONS 2;
--- enable_abort_on_error
--- disable_query_log
-eval SET @my_errno= $mysql_errno ;
-let $run = `SELECT @my_errno = 0`;
-# ER_WRONG_AUTO_KEY is 1075
-let $ER_WRONG_AUTO_KEY= 1075;
-if (`SELECT @my_errno NOT IN (0,$ER_WRONG_AUTO_KEY)`)
-{
- -- echo # Unknown error code, exits
- exit;
-}
--- enable_query_log
-if ($run)
-{
-INSERT INTO t1 VALUES (1, 0);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (2, NULL);
-INSERT INTO t1 VALUES (3, NULL);
-INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
--- error ER_DUP_KEY
-INSERT INTO t1 VALUES (2, 2);
-INSERT INTO t1 VALUES (2, 22), (2, NULL);
-SELECT * FROM t1 ORDER BY c1,c2;
-DROP TABLE t1;
-}
-
--- echo # Test AUTO_INCREMENT in CREATE
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
- ENGINE = $engine
- AUTO_INCREMENT = 15
- PARTITION BY HASH(c1)
- PARTITIONS 2;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (4);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (0);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-
--- echo # Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
-let $old_sql_mode = `select @@session.sql_mode`;
-SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-INSERT INTO t1 (c1) VALUES (300);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (0);
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-eval SET @@session.sql_mode = '$old_sql_mode';
-DROP TABLE t1;
-
--- echo # Test SET INSERT_ID
-eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
- ENGINE = $engine
- PARTITION BY HASH(c1)
- PARTITIONS 2;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1;
-SET INSERT_ID = 23;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
--- echo # Testing with FLUSH TABLE
-eval CREATE TABLE t1 (
- c1 INT NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (c1))
- ENGINE=$engine
- PARTITION BY HASH(c1)
- PARTITIONS 2;
-SHOW CREATE TABLE t1;
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (4);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-SELECT * FROM t1 ORDER BY c1;
-DROP TABLE t1;
-
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_archive.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_archive.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_archive.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,747 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+30
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+6
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+6
+7
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 1
+1 2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (1, 1), (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (4, 7);
+INSERT INTO t1 VALUES (1, NULL);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17), (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL), (9);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (59), (55);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+59
+60
+90
+91
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SET @@session.auto_increment_increment = 10;
+SET @@session.auto_increment_offset = 5;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SET @@session.auto_increment_increment = 5;
+SET @@session.auto_increment_offset = 3;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+# ERROR (only OK if Archive) mysql_errno: 1022
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+25
+26
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+17
+19
+20
+21
+22
+23
+24
+25
+26
+27
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# ERROR (only OK if Archive) mysql_errno: 1022
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Archive'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+# ERROR (only OK if Archive) mysql_errno: 1022
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Archive'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ARCHIVE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_blackhole.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_blackhole.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,570 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1), (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (4, 7);
+INSERT INTO t1 VALUES (1, NULL);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (17), (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL), (9);
+INSERT INTO t1 VALUES (59), (55);
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SET @@session.auto_increment_increment = 10;
+SET @@session.auto_increment_offset = 5;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SET @@session.auto_increment_increment = 5;
+SET @@session.auto_increment_offset = 3;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
+INSERT INTO t1 VALUES (NULL, 3);
+INSERT INTO t1 VALUES (2, 0), (NULL, 2);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (NULL, 2);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+# ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (3, NULL);
+INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+# ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+DROP TABLE t1;
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Blackhole'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=BLACKHOLE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+DROP TABLE t1;
=== modified file 'mysql-test/suite/parts/r/partition_auto_increment_falcon.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_falcon.result 2008-05-08 13:41:16 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_falcon.result 2008-10-01 12:00:30 +0000
@@ -27,13 +27,17 @@ AUTO_INCREMENT
6
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (5), (16);
-ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
INSERT INTO t1 VALUES (17);
INSERT INTO t1 VALUES (19), (NULL);
INSERT INTO t1 VALUES (NULL), (10), (NULL);
INSERT INTO t1 VALUES (NULL);
SET INSERT_ID = 30;
INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
2
@@ -41,13 +45,15 @@ c1
5
6
10
-17
-19
20
21
22
23
30
+50
+51
+52
+53
DROP TABLE t1;
CREATE TABLE t1 (
c1 INT NOT NULL AUTO_INCREMENT,
@@ -133,7 +139,6 @@ PARTITION BY HASH(c2)
PARTITIONS 2;
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t1 VALUES (1, 1), (99, 99);
-ERROR 23000: Can't write; duplicate key in table 't1'
INSERT INTO t1 VALUES (1, NULL);
SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
INSERT INTO t1 VALUES (1, 0);
@@ -174,13 +179,17 @@ PARTITIONS 2;
INSERT INTO t1 VALUES (2), (4), (NULL);
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES (5), (16);
-ERROR 23000: Can't write; duplicate key in table 't1'
INSERT INTO t1 VALUES (17), (19), (NULL);
INSERT INTO t1 VALUES (NULL), (10), (NULL);
INSERT INTO t1 VALUES (NULL), (9);
INSERT INTO t1 VALUES (59), (55);
INSERT INTO t1 VALUES (NULL), (90);
INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
SELECT * FROM t1 ORDER BY c1;
c1
2
@@ -189,8 +198,6 @@ c1
6
9
10
-17
-19
20
21
22
@@ -200,6 +207,10 @@ c1
60
90
91
+150
+151
+152
+153
DROP TABLE t1;
# Test with auto_increment_increment and auto_increment_offset.
CREATE TABLE t1 (
@@ -547,12 +558,10 @@ PARTITION BY HASH(c2)
PARTITIONS 2;
INSERT INTO t1 VALUES (1, 0);
INSERT INTO t1 VALUES (1, 1);
-ERROR 23000: Can't write; duplicate key in table 't1'
INSERT INTO t1 VALUES (1, NULL);
INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
INSERT INTO t1 VALUES (2, NULL);
INSERT INTO t1 VALUES (2, 2);
-ERROR 23000: Can't write; duplicate key in table 't1'
INSERT INTO t1 VALUES (2, 22);
INSERT INTO t1 VALUES (2, NULL);
SELECT * FROM t1 ORDER BY c1,c2;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_innodb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,747 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+20
+22
+23
+25
+30
+31
+32
+50
+51
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+6
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1), (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (4, 7);
+INSERT INTO t1 VALUES (1, NULL);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17), (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL), (9);
+INSERT INTO t1 VALUES (59), (55);
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+9
+10
+20
+21
+22
+23
+55
+59
+60
+90
+91
+150
+151
+152
+153
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SET @@session.auto_increment_increment = 10;
+SET @@session.auto_increment_offset = 5;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SET @@session.auto_increment_increment = 5;
+SET @@session.auto_increment_offset = 3;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+15
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+26
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
+INSERT INTO t1 VALUES (NULL, 3);
+INSERT INTO t1 VALUES (2, 0), (NULL, 2);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (NULL, 2);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 0
+1 1
+2 0
+2 1
+2 2
+2 22
+3 2
+4 3
+5 3
+6 2
+7 2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+0
+4
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='InnoDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== removed file 'mysql-test/suite/parts/r/partition_auto_increment_innodb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_innodb.result 1970-01-01 00:00:00 +0000
@@ -1,738 +0,0 @@
-DROP TABLE IF EXISTS t1;
-# test without partitioning for reference
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB';
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-1
-INSERT INTO t1 VALUES (2);
-INSERT INTO t1 VALUES (4);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-6
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-6
-INSERT INTO t1 VALUES (0);
-INSERT INTO t1 VALUES (5), (16);
-ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
-INSERT INTO t1 VALUES (17);
-INSERT INTO t1 VALUES (19), (NULL);
-INSERT INTO t1 VALUES (NULL), (10), (NULL);
-INSERT INTO t1 VALUES (NULL);
-SET INSERT_ID = 30;
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-10
-17
-19
-20
-22
-23
-25
-30
-DROP TABLE t1;
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB';
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1
-INSERT INTO t1 VALUES (4);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
-INSERT INTO t1 VALUES (NULL);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
-DELETE FROM t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
-SELECT * FROM t1 ORDER BY c1;
-c1
-6
-TRUNCATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
-SELECT * FROM t1 ORDER BY c1;
-c1
-1
-DROP TABLE t1;
-# Simple test with NULL
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1;
-c1
-1
-DROP TABLE t1;
-# Test with sql_mode and first insert as 0
-CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c2))
-ENGINE='InnoDB'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (1, 1), (99, 99);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (1, NULL);
-SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-INSERT INTO t1 VALUES (1, 0);
-SELECT * FROM t1 ORDER BY c1, c2;
-c1 c2
-1 0
-1 1
-1 2
-DROP TABLE t1;
-CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c2))
-ENGINE='InnoDB'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1), (1, NULL);
-INSERT INTO t1 VALUES (2, NULL), (4, 7);
-INSERT INTO t1 VALUES (1, NULL);
-SELECT * FROM t1 ORDER BY c1, c2;
-c1 c2
-1 0
-1 1
-1 2
-1 8
-2 3
-4 7
-SET @@session.sql_mode = '';
-DROP TABLE t1;
-# Simple test with NULL, 0 and explicit values both incr. and desc.
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (2), (4), (NULL);
-INSERT INTO t1 VALUES (0);
-INSERT INTO t1 VALUES (5), (16);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (17), (19), (NULL);
-INSERT INTO t1 VALUES (NULL), (10), (NULL);
-INSERT INTO t1 VALUES (NULL), (9);
-INSERT INTO t1 VALUES (59), (55);
-INSERT INTO t1 VALUES (NULL), (90);
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-9
-10
-17
-19
-20
-21
-22
-23
-55
-59
-60
-90
-91
-DROP TABLE t1;
-# Test with auto_increment_increment and auto_increment_offset.
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-SET @@session.auto_increment_increment = 10;
-SET @@session.auto_increment_offset = 5;
-INSERT INTO t1 VALUES (1);
-INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
-SET @@session.auto_increment_increment = 5;
-SET @@session.auto_increment_offset = 3;
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (33 + 1);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (38 + 2);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (43 + 3);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (48 + 4);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (53 + 5);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (63 + 6);
-INSERT INTO t1 VALUES (NULL);
-SET @@session.auto_increment_increment = 1;
-SET @@session.auto_increment_offset = 1;
-SELECT * FROM t1 ORDER BY c1;
-c1
-1
-5
-15
-25
-33
-34
-38
-40
-43
-46
-48
-52
-53
-58
-63
-69
-73
-DROP TABLE t1;
-# Test reported auto_increment value
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB'
-PARTITION BY HASH (c1)
-PARTITIONS 2;
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-1
-INSERT INTO t1 VALUES (2);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-3
-INSERT INTO t1 VALUES (4);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-6
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (17);
-INSERT INTO t1 VALUES (19);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-22
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-22
-INSERT INTO t1 VALUES (10);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-10
-17
-19
-20
-21
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-23
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (15);
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-10
-15
-17
-19
-20
-21
-22
-23
-24
-INSERT INTO t1 VALUES (NULL);
-DELETE FROM t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-26
-TRUNCATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-1
-DROP TABLE t1;
-# Test with two threads
-# con default
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'InnoDB'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (10);
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (19);
-INSERT INTO t1 (c1) VALUES (21);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-# con default
-INSERT INTO t1 (c1) VALUES (16);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-DROP TABLE t1;
-# Test with two threads + start transaction NO PARTITIONING
-# con default
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'InnoDB';
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-# con1
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (10);
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (19);
-INSERT INTO t1 (c1) VALUES (21);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-# con default
-INSERT INTO t1 (c1) VALUES (16);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-5
-10
-22
-23
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-5
-10
-22
-23
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-DROP TABLE t1;
-# Test with two threads + start transaction
-# con default
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'InnoDB'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-# con1
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (NULL), (10);
-# con default
-INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
-INSERT INTO t1 (c1) VALUES (21);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-# con default
-INSERT INTO t1 (c1) VALUES (16);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-5
-10
-22
-23
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-5
-10
-22
-23
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-DROP TABLE t1;
-# Test with another column after
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-c2 INT,
-PRIMARY KEY (c1,c2))
-ENGINE = 'InnoDB'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
-INSERT INTO t1 VALUES (NULL, 3);
-INSERT INTO t1 VALUES (2, 0), (NULL, 2);
-INSERT INTO t1 VALUES (2, 2);
-INSERT INTO t1 VALUES (2, 22);
-INSERT INTO t1 VALUES (NULL, 2);
-SELECT * FROM t1 ORDER BY c1,c2;
-c1 c2
-1 0
-1 1
-2 0
-2 1
-2 2
-2 22
-3 2
-4 3
-5 3
-6 2
-7 2
-DROP TABLE t1;
-# Test with another column before
-CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c2))
-ENGINE = 'InnoDB'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
-INSERT INTO t1 VALUES (2, NULL);
-INSERT INTO t1 VALUES (2, 2);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (2, 22);
-INSERT INTO t1 VALUES (2, NULL);
-SELECT * FROM t1 ORDER BY c1,c2;
-c1 c2
-1 1
-1 2
-2 3
-2 13
-2 14
-2 22
-2 23
-3 11
-3 12
-DROP TABLE t1;
-# Test with auto_increment on secondary column in multi-column-index
-CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1,c2))
-ENGINE = 'InnoDB'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
-# Test AUTO_INCREMENT in CREATE
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'InnoDB'
-AUTO_INCREMENT = 15
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (4);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (0);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-4
-15
-16
-# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
-SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-INSERT INTO t1 (c1) VALUES (300);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (0);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-0
-4
-15
-16
-300
-301
-SET @@session.sql_mode = '';
-DROP TABLE t1;
-# Test SET INSERT_ID
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'InnoDB'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1;
-c1
-1
-SET INSERT_ID = 23;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-1
-23
-DROP TABLE t1;
-# Testing with FLUSH TABLE
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='InnoDB'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 VALUES (4);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 VALUES (NULL);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-4
-5
-DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_memory.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_memory.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_memory.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,775 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+20
+21
+22
+23
+30
+50
+51
+52
+53
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+6
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1), (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (4, 7);
+INSERT INTO t1 VALUES (1, NULL);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17), (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL), (9);
+INSERT INTO t1 VALUES (59), (55);
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+9
+10
+20
+21
+22
+23
+55
+59
+60
+90
+91
+150
+151
+152
+153
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SET @@session.auto_increment_increment = 10;
+SET @@session.auto_increment_offset = 5;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SET @@session.auto_increment_increment = 5;
+SET @@session.auto_increment_offset = 3;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+15
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+26
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+27
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
+INSERT INTO t1 VALUES (NULL, 3);
+INSERT INTO t1 VALUES (2, 0), (NULL, 2);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (NULL, 2);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 0
+1 1
+2 0
+2 1
+2 2
+2 22
+3 2
+4 3
+5 3
+6 2
+7 2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'Memory'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+ERROR 42000: Incorrect table definition; there can be only one auto column and it must be defined as a key
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+0
+4
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='Memory'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,794 @@
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+20
+21
+22
+23
+30
+50
+51
+52
+53
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+6
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1), (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (4, 7);
+INSERT INTO t1 VALUES (1, NULL);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17), (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL), (9);
+INSERT INTO t1 VALUES (59), (55);
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+9
+10
+20
+21
+22
+23
+55
+59
+60
+90
+91
+150
+151
+152
+153
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SET @@session.auto_increment_increment = 10;
+SET @@session.auto_increment_offset = 5;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SET @@session.auto_increment_increment = 5;
+SET @@session.auto_increment_offset = 3;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+15
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+26
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+27
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
+INSERT INTO t1 VALUES (NULL, 3);
+INSERT INTO t1 VALUES (2, 0), (NULL, 2);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (NULL, 2);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 0
+1 1
+2 0
+2 1
+2 2
+2 22
+3 2
+4 3
+5 3
+6 2
+7 2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (3, NULL);
+INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 1
+2 2
+2 3
+2 22
+2 23
+3 1
+3 2
+DROP TABLE t1;
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+0
+4
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='MyISAM'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== removed file 'mysql-test/suite/parts/r/partition_auto_increment_myisam.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_myisam.result 1970-01-01 00:00:00 +0000
@@ -1,787 +0,0 @@
-DROP TABLE IF EXISTS t1;
-# test without partitioning for reference
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM';
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-1
-INSERT INTO t1 VALUES (2);
-INSERT INTO t1 VALUES (4);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-6
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-6
-INSERT INTO t1 VALUES (0);
-INSERT INTO t1 VALUES (5), (16);
-ERROR 23000: Duplicate entry '5' for key 'PRIMARY'
-INSERT INTO t1 VALUES (17);
-INSERT INTO t1 VALUES (19), (NULL);
-INSERT INTO t1 VALUES (NULL), (10), (NULL);
-INSERT INTO t1 VALUES (NULL);
-SET INSERT_ID = 30;
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-10
-17
-19
-20
-21
-22
-23
-30
-DROP TABLE t1;
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM';
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-INSERT INTO t1 VALUES (4);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
-INSERT INTO t1 VALUES (NULL);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
-DELETE FROM t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
-SELECT * FROM t1 ORDER BY c1;
-c1
-6
-TRUNCATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
-SELECT * FROM t1 ORDER BY c1;
-c1
-1
-DROP TABLE t1;
-# Simple test with NULL
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1;
-c1
-1
-DROP TABLE t1;
-# Test with sql_mode and first insert as 0
-CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c2))
-ENGINE='MyISAM'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (1, 1), (99, 99);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (1, NULL);
-SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-INSERT INTO t1 VALUES (1, 0);
-SELECT * FROM t1 ORDER BY c1, c2;
-c1 c2
-1 0
-1 1
-1 2
-DROP TABLE t1;
-CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c2))
-ENGINE='MyISAM'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1), (1, NULL);
-INSERT INTO t1 VALUES (2, NULL), (4, 7);
-INSERT INTO t1 VALUES (1, NULL);
-SELECT * FROM t1 ORDER BY c1, c2;
-c1 c2
-1 0
-1 1
-1 2
-1 8
-2 3
-4 7
-SET @@session.sql_mode = '';
-DROP TABLE t1;
-# Simple test with NULL, 0 and explicit values both incr. and desc.
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (2), (4), (NULL);
-INSERT INTO t1 VALUES (0);
-INSERT INTO t1 VALUES (5), (16);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (17), (19), (NULL);
-INSERT INTO t1 VALUES (NULL), (10), (NULL);
-INSERT INTO t1 VALUES (NULL), (9);
-INSERT INTO t1 VALUES (59), (55);
-INSERT INTO t1 VALUES (NULL), (90);
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-9
-10
-17
-19
-20
-21
-22
-23
-55
-59
-60
-90
-91
-DROP TABLE t1;
-# Test with auto_increment_increment and auto_increment_offset.
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-SET @@session.auto_increment_increment = 10;
-SET @@session.auto_increment_offset = 5;
-INSERT INTO t1 VALUES (1);
-INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
-SET @@session.auto_increment_increment = 5;
-SET @@session.auto_increment_offset = 3;
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (33 + 1);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (38 + 2);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (43 + 3);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (48 + 4);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (53 + 5);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (63 + 6);
-INSERT INTO t1 VALUES (NULL);
-SET @@session.auto_increment_increment = 1;
-SET @@session.auto_increment_offset = 1;
-SELECT * FROM t1 ORDER BY c1;
-c1
-1
-5
-15
-25
-33
-34
-38
-40
-43
-46
-48
-52
-53
-58
-63
-69
-73
-DROP TABLE t1;
-# Test reported auto_increment value
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM'
-PARTITION BY HASH (c1)
-PARTITIONS 2;
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-1
-INSERT INTO t1 VALUES (2);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-3
-INSERT INTO t1 VALUES (4);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-6
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (17);
-INSERT INTO t1 VALUES (19);
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-22
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-22
-INSERT INTO t1 VALUES (10);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-10
-17
-19
-20
-21
-INSERT INTO t1 VALUES (NULL);
-SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
-AND TABLE_NAME='t1';
-AUTO_INCREMENT
-23
-INSERT INTO t1 VALUES (NULL);
-INSERT INTO t1 VALUES (15);
-INSERT INTO t1 VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-6
-10
-15
-17
-19
-20
-21
-22
-23
-24
-INSERT INTO t1 VALUES (NULL);
-DELETE FROM t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-26
-TRUNCATE TABLE t1;
-INSERT INTO t1 VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-27
-DROP TABLE t1;
-# Test with two threads
-# con default
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'MyISAM'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (10);
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (19);
-INSERT INTO t1 (c1) VALUES (21);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-# con default
-INSERT INTO t1 (c1) VALUES (16);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-DROP TABLE t1;
-# Test with two threads + start transaction NO PARTITIONING
-# con default
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'MyISAM';
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-# con1
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (10);
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (NULL);
-INSERT INTO t1 (c1) VALUES (19);
-INSERT INTO t1 (c1) VALUES (21);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-# con default
-INSERT INTO t1 (c1) VALUES (16);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-DROP TABLE t1;
-# Test with two threads + start transaction
-# con default
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'MyISAM'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (2);
-INSERT INTO t1 (c1) VALUES (4);
-# con1
-START TRANSACTION;
-INSERT INTO t1 (c1) VALUES (NULL), (10);
-# con default
-INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
-INSERT INTO t1 (c1) VALUES (21);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-# con default
-INSERT INTO t1 (c1) VALUES (16);
-# con1
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-# con default
-INSERT INTO t1 (c1) VALUES (NULL);
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-COMMIT;
-SELECT * FROM t1 ORDER BY c1;
-c1
-2
-4
-5
-10
-11
-12
-16
-19
-21
-22
-23
-24
-DROP TABLE t1;
-# Test with another column after
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-c2 INT,
-PRIMARY KEY (c1,c2))
-ENGINE = 'MyISAM'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
-INSERT INTO t1 VALUES (NULL, 3);
-INSERT INTO t1 VALUES (2, 0), (NULL, 2);
-INSERT INTO t1 VALUES (2, 2);
-INSERT INTO t1 VALUES (2, 22);
-INSERT INTO t1 VALUES (NULL, 2);
-SELECT * FROM t1 ORDER BY c1,c2;
-c1 c2
-1 0
-1 1
-2 0
-2 1
-2 2
-2 22
-3 2
-4 3
-5 3
-6 2
-7 2
-DROP TABLE t1;
-# Test with another column before
-CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c2))
-ENGINE = 'MyISAM'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
-INSERT INTO t1 VALUES (2, NULL);
-INSERT INTO t1 VALUES (2, 2);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (2, 22);
-INSERT INTO t1 VALUES (2, NULL);
-SELECT * FROM t1 ORDER BY c1,c2;
-c1 c2
-1 1
-1 2
-2 3
-2 13
-2 14
-2 22
-2 23
-3 11
-3 12
-DROP TABLE t1;
-# Test with auto_increment on secondary column in multi-column-index
-CREATE TABLE t1 (
-c1 INT,
-c2 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1,c2))
-ENGINE = 'MyISAM'
-PARTITION BY HASH(c2)
-PARTITIONS 2;
-INSERT INTO t1 VALUES (1, 0);
-INSERT INTO t1 VALUES (1, 1);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (1, NULL);
-INSERT INTO t1 VALUES (2, NULL);
-INSERT INTO t1 VALUES (3, NULL);
-INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
-INSERT INTO t1 VALUES (2, 2);
-ERROR 23000: Can't write; duplicate key in table 't1'
-INSERT INTO t1 VALUES (2, 22), (2, NULL);
-SELECT * FROM t1 ORDER BY c1,c2;
-c1 c2
-1 1
-1 2
-2 1
-2 2
-2 3
-2 22
-2 23
-3 1
-3 2
-DROP TABLE t1;
-# Test AUTO_INCREMENT in CREATE
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'MyISAM'
-AUTO_INCREMENT = 15
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (4);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (0);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=17 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-4
-15
-16
-# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
-SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-INSERT INTO t1 (c1) VALUES (300);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (0);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=301 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=302 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-0
-4
-15
-16
-300
-301
-SET @@session.sql_mode = '';
-DROP TABLE t1;
-# Test SET INSERT_ID
-CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
-ENGINE = 'MyISAM'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1;
-c1
-1
-SET INSERT_ID = 23;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 (c1) VALUES (NULL);
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=24 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-1
-23
-DROP TABLE t1;
-# Testing with FLUSH TABLE
-CREATE TABLE t1 (
-c1 INT NOT NULL AUTO_INCREMENT,
-PRIMARY KEY (c1))
-ENGINE='MyISAM'
-PARTITION BY HASH(c1)
-PARTITIONS 2;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 VALUES (4);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-INSERT INTO t1 VALUES (NULL);
-FLUSH TABLE;
-SHOW CREATE TABLE t1;
-Table Create Table
-t1 CREATE TABLE `t1` (
- `c1` int(11) NOT NULL AUTO_INCREMENT,
- PRIMARY KEY (`c1`)
-) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
-SELECT * FROM t1 ORDER BY c1;
-c1
-4
-5
-DROP TABLE t1;
=== added file 'mysql-test/suite/parts/r/partition_auto_increment_ndb.result'
--- a/mysql-test/suite/parts/r/partition_auto_increment_ndb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/r/partition_auto_increment_ndb.result 2008-09-08 13:30:01 +0000
@@ -0,0 +1,769 @@
+SET new=on;
+DROP TABLE IF EXISTS t1;
+# test without partitioning for reference
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL);
+SET INSERT_ID = 30;
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 50 WHERE c1 = 17;
+UPDATE t1 SET c1 = 51 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+20
+21
+22
+23
+30
+50
+51
+52
+53
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB';
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+6
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Simple test with NULL
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+DROP TABLE t1;
+# Test with sql_mode and first insert as 0
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (1, 1), (99, 99);
+INSERT INTO t1 VALUES (1, NULL);
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 VALUES (1, 0);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 100
+DROP TABLE t1;
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE='NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1), (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (4, 7);
+INSERT INTO t1 VALUES (1, NULL);
+SELECT * FROM t1 ORDER BY c1, c2;
+c1 c2
+1 0
+1 1
+1 2
+1 8
+2 3
+4 7
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Simple test with NULL, 0 and explicit values both incr. and desc.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (2), (4), (NULL);
+INSERT INTO t1 VALUES (0);
+INSERT INTO t1 VALUES (5), (16);
+INSERT INTO t1 VALUES (17), (19), (NULL);
+INSERT INTO t1 VALUES (NULL), (10), (NULL);
+INSERT INTO t1 VALUES (NULL), (9);
+INSERT INTO t1 VALUES (59), (55);
+INSERT INTO t1 VALUES (NULL), (90);
+INSERT INTO t1 VALUES (NULL);
+UPDATE t1 SET c1 = 150 WHERE c1 = 17;
+UPDATE t1 SET c1 = 151 WHERE c1 = 19;
+UPDATE t1 SET c1 = NULL WHERE c1 = 4;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+9
+10
+20
+21
+22
+23
+55
+59
+60
+90
+91
+150
+151
+152
+153
+DROP TABLE t1;
+# Test with auto_increment_increment and auto_increment_offset.
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SET @@session.auto_increment_increment = 10;
+SET @@session.auto_increment_offset = 5;
+INSERT INTO t1 VALUES (1);
+INSERT INTO t1 VALUES (NULL), (NULL), (NULL);
+SET @@session.auto_increment_increment = 5;
+SET @@session.auto_increment_offset = 3;
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (33 + 1);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (38 + 2);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (43 + 3);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (48 + 4);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (53 + 5);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (63 + 6);
+INSERT INTO t1 VALUES (NULL);
+SET @@session.auto_increment_increment = 1;
+SET @@session.auto_increment_offset = 1;
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+5
+15
+25
+33
+34
+38
+40
+43
+46
+48
+52
+53
+58
+63
+69
+73
+DROP TABLE t1;
+# Test reported auto_increment value
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH (c1)
+PARTITIONS 2;
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+1
+INSERT INTO t1 VALUES (2);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+3
+INSERT INTO t1 VALUES (4);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+6
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (17);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+22
+INSERT INTO t1 VALUES (10);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+17
+19
+20
+21
+INSERT INTO t1 VALUES (NULL);
+SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test'
+AND TABLE_NAME='t1';
+AUTO_INCREMENT
+23
+INSERT INTO t1 VALUES (NULL);
+INSERT INTO t1 VALUES (15);
+INSERT INTO t1 VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+6
+10
+15
+17
+19
+20
+21
+22
+23
+24
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+26
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+DROP TABLE t1;
+# Test with two threads
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction NO PARTITIONING
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB';
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (NULL);
+INSERT INTO t1 (c1) VALUES (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with two threads + start transaction
+# con default
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (2);
+INSERT INTO t1 (c1) VALUES (4);
+# con1
+START TRANSACTION;
+INSERT INTO t1 (c1) VALUES (NULL), (10);
+# con default
+INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19);
+INSERT INTO t1 (c1) VALUES (21);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+# con default
+INSERT INTO t1 (c1) VALUES (16);
+# con1
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+5
+10
+22
+23
+# con default
+INSERT INTO t1 (c1) VALUES (NULL);
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+COMMIT;
+SELECT * FROM t1 ORDER BY c1;
+c1
+2
+4
+5
+10
+11
+12
+16
+19
+21
+22
+23
+24
+DROP TABLE t1;
+# Test with another column after
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+c2 INT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2), (NULL, 3);
+INSERT INTO t1 VALUES (NULL, 3);
+INSERT INTO t1 VALUES (2, 0), (NULL, 2);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (NULL, 2);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 0
+1 1
+2 0
+2 1
+2 2
+2 22
+3 2
+4 3
+5 3
+6 2
+7 2
+DROP TABLE t1;
+# Test with another column before
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c2))
+ENGINE = 'NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL), (3, 11), (3, NULL), (2, 0);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+INSERT INTO t1 VALUES (2, 22);
+INSERT INTO t1 VALUES (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 3
+2 13
+2 14
+2 22
+2 23
+3 11
+3 12
+DROP TABLE t1;
+# Test with auto_increment on secondary column in multi-column-index
+CREATE TABLE t1 (
+c1 INT,
+c2 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1,c2))
+ENGINE = 'NDB'
+PARTITION BY HASH(c2)
+PARTITIONS 2;
+INSERT INTO t1 VALUES (1, 0);
+INSERT INTO t1 VALUES (1, 1);
+INSERT INTO t1 VALUES (1, NULL);
+INSERT INTO t1 VALUES (2, NULL);
+INSERT INTO t1 VALUES (3, NULL);
+INSERT INTO t1 VALUES (3, NULL), (2, 0), (2, NULL);
+INSERT INTO t1 VALUES (2, 2);
+# ERROR (only OK if Blackhole/NDB) should give ER_DUP_KEY or ER_DUP_ENTRY
+INSERT INTO t1 VALUES (2, 22), (2, NULL);
+SELECT * FROM t1 ORDER BY c1,c2;
+c1 c2
+1 1
+1 2
+2 2
+2 3
+2 6
+2 7
+2 22
+2 23
+3 4
+3 5
+DROP TABLE t1;
+# Test AUTO_INCREMENT in CREATE
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+AUTO_INCREMENT = 15
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (4);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+15
+16
+# Test sql_mode 'NO_AUTO_VALUE_ON_ZERO'
+SET @@session.sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
+INSERT INTO t1 (c1) VALUES (300);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (0);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+0
+4
+15
+16
+300
+301
+SET @@session.sql_mode = '';
+DROP TABLE t1;
+# Test SET INSERT_ID
+CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1))
+ENGINE = 'NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1;
+c1
+1
+SET INSERT_ID = 23;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 (c1) VALUES (NULL);
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+1
+23
+DROP TABLE t1;
+# Testing with FLUSH TABLE
+CREATE TABLE t1 (
+c1 INT NOT NULL AUTO_INCREMENT,
+PRIMARY KEY (c1))
+ENGINE='NDB'
+PARTITION BY HASH(c1)
+PARTITIONS 2;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (4);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+INSERT INTO t1 VALUES (NULL);
+FLUSH TABLE;
+SHOW CREATE TABLE t1;
+Table Create Table
+t1 CREATE TABLE `t1` (
+ `c1` int(11) NOT NULL AUTO_INCREMENT,
+ PRIMARY KEY (`c1`)
+) ENGINE=ndbcluster DEFAULT CHARSET=latin1 /*!50100 PARTITION BY HASH (c1) PARTITIONS 2 */
+SELECT * FROM t1 ORDER BY c1;
+c1
+4
+5
+DROP TABLE t1;
=== modified file 'mysql-test/suite/parts/t/disabled.def'
--- a/mysql-test/suite/parts/t/disabled.def 2008-09-11 08:01:28 +0000
+++ b/mysql-test/suite/parts/t/disabled.def 2008-10-01 12:00:30 +0000
@@ -7,7 +7,6 @@ partition_syntax_ndb : Bug#367
partition_value_innodb : Bug#30581 partition_value tests use disallowed CAST() function
partition_value_myisam : Bug#30581 partition_value tests use disallowed CAST() function
partition_value_ndb : Bug#30581 partition_value tests use disallowed CAST() function
-partition_auto_increment_falcon : Bug#36595 2008-05-08 test crashes
ndb_dd_backuprestore : Bug#32659 2008-07-14 alik Disabled to make 6.0 greaner (the test fails too often)
partition_mgm_lc0_archive : Bug#38751 - add partition deletes all rows
partition_mgm_lc1_archive : Bug#38751 - add partition deletes all rows
@@ -15,3 +14,4 @@ partition_mgm_lc2_archive : Bug#387
partition_mgm_lc0_ndb : Bug#38778 - master1 crashes
partition_mgm_lc1_ndb : Bug#38778 - master1 crashes
partition_mgm_lc2_ndb : Bug#38778 - master1 crashes
+partition_auto_increment_ndb : Bug#39773 - auto_increment for NDB changed behavior
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_archive.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_archive.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_archive.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,40 @@
+################################################################################
+# t/partition_auto_increment_archive.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# Archive branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-09-02 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_archive.inc
+# Archve does not support delete
+let $skip_delete= 1;
+let $skip_truncate= 1;
+let $skip_update= 1;
+let $only_ai_pk= 1;
+
+##### Storage engine to be tested
+let $engine= 'Archive';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_blackhole.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_blackhole.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,35 @@
+################################################################################
+# t/partition_auto_increment_blackhole.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# Blackhole branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-09-02 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_blackhole.inc
+
+##### Storage engine to be tested
+let $engine= 'Blackhole';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_innodb.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_innodb.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,35 @@
+################################################################################
+# t/partition_auto_increment_innodb.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# InnoDB branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-02-12 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'InnoDB';
+--source include/have_innodb.inc
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== removed file 'mysql-test/suite/parts/t/partition_auto_increment_innodb.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_innodb.test 2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_innodb.test 1970-01-01 00:00:00 +0000
@@ -1,35 +0,0 @@
-################################################################################
-# t/partition_auto_increment_innodb.test #
-# #
-# Purpose: #
-# Tests around auto increment column #
-# InnoDB branch #
-# #
-#------------------------------------------------------------------------------#
-# Original Author: MattiasJ #
-# Original Date: 2008-02-12 #
-# Change Author: #
-# Change Date: #
-# Change: #
-################################################################################
-
-#
-# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
-# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
-# THE SOURCED FILES ONLY.
-#
-
-# The server must support partitioning.
---source include/have_partition.inc
-
-#------------------------------------------------------------------------------#
-# Engine specific settings and requirements
-
-##### Storage engine to be tested
-let $engine= 'InnoDB';
---source include/have_innodb.inc
-
-#------------------------------------------------------------------------------#
-# Execute the tests to be applied to all storage engines
---source suite/parts/inc/partition_auto_increment.inc
-
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_memory.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_memory.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_memory.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,34 @@
+################################################################################
+# t/partition_auto_increment_memory.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# Memory branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-02-12 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'Memory';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_myisam.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_myisam.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_myisam.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,34 @@
+################################################################################
+# t/partition_auto_increment_myisam.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# MyISAM branch #
+# #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-02-12 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+
+##### Storage engine to be tested
+let $engine= 'MyISAM';
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== removed file 'mysql-test/suite/parts/t/partition_auto_increment_myisam.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_myisam.test 2008-04-25 11:37:58 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_myisam.test 1970-01-01 00:00:00 +0000
@@ -1,34 +0,0 @@
-################################################################################
-# t/partition_auto_increment_myisam.test #
-# #
-# Purpose: #
-# Tests around auto increment column #
-# MyISAM branch #
-# #
-#------------------------------------------------------------------------------#
-# Original Author: MattiasJ #
-# Original Date: 2008-02-12 #
-# Change Author: #
-# Change Date: #
-# Change: #
-################################################################################
-
-#
-# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
-# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
-# THE SOURCED FILES ONLY.
-#
-
-# The server must support partitioning.
---source include/have_partition.inc
-
-#------------------------------------------------------------------------------#
-# Engine specific settings and requirements
-
-##### Storage engine to be tested
-let $engine= 'MyISAM';
-
-#------------------------------------------------------------------------------#
-# Execute the tests to be applied to all storage engines
---source suite/parts/inc/partition_auto_increment.inc
-
=== added file 'mysql-test/suite/parts/t/partition_auto_increment_ndb.test'
--- a/mysql-test/suite/parts/t/partition_auto_increment_ndb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/parts/t/partition_auto_increment_ndb.test 2008-09-08 13:30:01 +0000
@@ -0,0 +1,41 @@
+################################################################################
+# t/partition_auto_increment_ndb.test #
+# #
+# Purpose: #
+# Tests around auto increment column #
+# NDB branch #
+# #
+# Note: NDB behavior for auto_increment on secondary column in #
+# multi-column-index is NOT like MyISAM, instead it uses the same #
+# behavior as if it was the primary column. #
+#------------------------------------------------------------------------------#
+# Original Author: MattiasJ #
+# Original Date: 2008-09-02 #
+# Change Author: #
+# Change Date: #
+# Change: #
+################################################################################
+
+#
+# NOTE: PLEASE DO NOT ADD NOT MYISAM SPECIFIC TESTCASES HERE !
+# TESTCASES WHICH MUST BE APPLIED TO ALL STORAGE ENGINES MUST BE ADDED IN
+# THE SOURCED FILES ONLY.
+#
+
+# The server must support partitioning.
+--source include/have_partition.inc
+
+#------------------------------------------------------------------------------#
+# Engine specific settings and requirements
+--source include/have_ndb.inc
+
+##### Storage engine to be tested
+let $engine= 'NDB';
+connection default;
+#enable hash partitioning
+SET new=on;
+
+#------------------------------------------------------------------------------#
+# Execute the tests to be applied to all storage engines
+--source suite/parts/inc/partition_auto_increment.inc
+
=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc 2008-09-20 07:22:37 +0000
+++ b/sql/ha_partition.cc 2008-10-01 12:00:30 +0000
@@ -2515,7 +2515,10 @@ int ha_partition::open(const char *name,
alloc_root(&table_share->mem_root,
sizeof(HA_DATA_PARTITION));
if (!ha_data)
+ {
+ pthread_mutex_unlock(&table_share->LOCK_ha_data);
goto err_handler;
+ }
DBUG_PRINT("info", ("table_share->ha_data 0x%p", ha_data));
bzero(ha_data, sizeof(HA_DATA_PARTITION));
}
@@ -2933,7 +2936,7 @@ int ha_partition::write_row(uchar * buf)
tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
error= m_file[part_id]->ha_write_row(buf);
if (have_auto_increment && !table->s->next_number_keypart)
- set_auto_increment_if_higher();
+ set_auto_increment_if_higher(table->next_number_field->val_int());
reenable_binlog(thd);
exit:
table->timestamp_field_type= orig_timestamp_type;
@@ -2960,13 +2963,6 @@ exit:
Keep in mind that the server can do updates based on ordering if an
ORDER BY clause was used. Consecutive ordering is not guarenteed.
- Currently new_data will not have an updated auto_increament record, or
- and updated timestamp field. You can do these for partition by doing these:
- if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
- table->timestamp_field->set_time();
- if (table->next_number_field && record == table->record[0])
- update_auto_increment();
-
Called from sql_select.cc, sql_acl.cc, sql_update.cc, and sql_insert.cc.
new_data is always record[0]
old_data is normally record[1] but may be anything
@@ -3009,10 +3005,12 @@ int ha_partition::update_row(const uchar
table_share->ha_data->next_auto_inc_val if needed.
(not to be used if auto_increment on secondary field in a multi-
column index)
+ mysql_update does not set table->next_number_field, so we use
+ table->found_next_number_field instead.
*/
- if (table->next_number_field && new_data == table->record[0] &&
+ if (table->found_next_number_field && new_data == table->record[0] &&
!table->s->next_number_keypart)
- set_auto_increment_if_higher();
+ set_auto_increment_if_higher(table->found_next_number_field->val_int());
reenable_binlog(thd);
goto exit;
}
@@ -3022,6 +3020,9 @@ int ha_partition::update_row(const uchar
old_part_id, new_part_id));
tmp_disable_binlog(thd); /* Do not replicate the low-level changes. */
error= m_file[new_part_id]->ha_write_row(new_data);
+ if (table->found_next_number_field && new_data == table->record[0] &&
+ !table->s->next_number_keypart)
+ set_auto_increment_if_higher(table->found_next_number_field->val_int());
reenable_binlog(thd);
if (error)
goto exit;
@@ -5989,6 +5990,7 @@ void ha_partition::get_auto_increment(ul
if (first_value_part == ~(ulonglong)(0)) // error in one partition
{
*first_value= first_value_part;
+ /* log that the error was between table/partition handler */
sql_print_error("Partition failed to reserve auto_increment value");
unlock_auto_increment();
DBUG_VOID_RETURN;
=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h 2008-09-20 07:22:37 +0000
+++ b/sql/ha_partition.h 2008-10-01 12:00:30 +0000
@@ -881,9 +881,8 @@ private:
auto_increment_lock= FALSE;
}
}
- virtual void set_auto_increment_if_higher()
+ virtual void set_auto_increment_if_higher(const ulonglong nr)
{
- ulonglong nr= table->next_number_field->val_int();
HA_DATA_PARTITION *ha_data= (HA_DATA_PARTITION*) table_share->ha_data;
lock_auto_increment();
/* must check when the mutex is taken */
| Thread |
|---|
| • bzr commit into mysql-6.0 branch (mattias.jonsson:2844) Bug#33479Bug#38804 | Mattias Jonsson | 1 Oct |
