diff options
Diffstat (limited to 'mysql-test/suite/parts/inc/partition_auto_increment.inc')
| -rw-r--r-- | mysql-test/suite/parts/inc/partition_auto_increment.inc | 925 |
1 files changed, 925 insertions, 0 deletions
diff --git a/mysql-test/suite/parts/inc/partition_auto_increment.inc b/mysql-test/suite/parts/inc/partition_auto_increment.inc new file mode 100644 index 00000000..567733ed --- /dev/null +++ b/mysql-test/suite/parts/inc/partition_auto_increment.inc @@ -0,0 +1,925 @@ +# inc/partition_auto_increment.inc +# +# auto_increment test +# used variables: $engine +# +--disable_query_log +set sql_mode=""; +--enable_query_log + +-- 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; + echo # mysql_errno: $mysql_errno; +} +INSERT INTO t1 VALUES (17); +INSERT INTO t1 VALUES (19), (NULL); +-- error 0, ER_DUP_ENTRY, 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); +SET INSERT_ID = 29; +-- error 0, ER_DUP_ENTRY, ER_DUP_KEY +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +if (!$mysql_errno) +{ + echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY; + echo # mysql_errno: $mysql_errno; +} +INSERT INTO t1 VALUES (NULL); +if (!$skip_update) +{ +# InnoDB Does not handle this correctly, see bug#14793, bug#21641 + UPDATE t1 SET c1 = 50 WHERE c1 = 17; + UPDATE t1 SET c1 = 51 WHERE c1 = 19; + FLUSH TABLES; + UPDATE t1 SET c1 = 40 WHERE c1 = 50; + SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' + AND TABLE_NAME='t1'; + UPDATE t1 SET c1 = NULL WHERE c1 = 4; + 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; +INSERT INTO t1 VALUES (100); +INSERT INTO t1 VALUES (NULL); +if (!$skip_delete) +{ +DELETE FROM t1 WHERE c1 >= 100; +} +# InnoDB does reset auto_increment on OPTIMIZE, Bug#18274 +# Archive does reset auto_increment on OPTIMIZE, Bug#40216 +OPTIMIZE TABLE t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +if (!$skip_update) +{ +eval CREATE TABLE t1 +(a INT NULL AUTO_INCREMENT, + UNIQUE KEY (a)) +ENGINE=$engine; +SET LAST_INSERT_ID = 999; +SET INSERT_ID = 0; +INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +UPDATE t1 SET a = 1 WHERE a IS NULL; +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +UPDATE t1 SET a = NULL WHERE a = 1; +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +DROP TABLE t1; +SET INSERT_ID = 1; +} + +-- 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, ER_DUP_ENTRY +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; + echo # mysql_errno: $mysql_errno; +} +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_ENTRY, 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_ENTRY, 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, 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; + echo # mysql_errno: $mysql_errno; +} +INSERT INTO t1 VALUES (17), (19), (NULL); +-- error 0, ER_DUP_ENTRY, 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_ENTRY, 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_ENTRY, 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; + FLUSH TABLES; + UPDATE t1 SET c1 = 140 WHERE c1 = 150; + SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test' + AND TABLE_NAME='t1'; + UPDATE t1 SET c1 = NULL WHERE c1 = 4; + 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_ENTRY, 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_ENTRY, 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; +INSERT INTO t1 VALUES (100); +INSERT INTO t1 VALUES (NULL); +if (!$skip_delete) +{ +DELETE FROM t1 WHERE c1 >= 100; +} +# InnoDB does reset auto_increment on OPTIMIZE, Bug#18274 +OPTIMIZE TABLE t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +-- echo # Test with two threads +connection 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; +INSERT INTO t1 (c1) VALUES (NULL); +INSERT INTO t1 (c1) VALUES (10); +connection 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); +connection con1; +INSERT INTO t1 (c1) VALUES (NULL); +connection default; +-- error 0, ER_DUP_ENTRY, ER_DUP_KEY +INSERT INTO t1 (c1) VALUES (16); +if ($mysql_errno) +{ + echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno; +} +connection con1; +INSERT INTO t1 (c1) VALUES (NULL); +disconnect con1; +connection 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; +eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) + ENGINE = $engine; +if ($engine == "'InnoDB'") +{ +# MDEV-515 takes X-lock on the table for the first insert. +# So concurrent insert won't happen on the table +INSERT INTO t1(c1) VALUES(100); +} +START TRANSACTION; +INSERT INTO t1 (c1) VALUES (2); +INSERT INTO t1 (c1) VALUES (4); +connection con1; +START TRANSACTION; +INSERT INTO t1 (c1) VALUES (NULL); +INSERT INTO t1 (c1) VALUES (10); +connection 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); +connection con1; +INSERT INTO t1 (c1) VALUES (NULL); +connection 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; +} +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; +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; +eval CREATE TABLE t1 (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (c1)) + ENGINE = $engine + PARTITION BY HASH(c1) + PARTITIONS 2; +IF ($engine == "'InnoDB'") +{ +# MDEV-515 takes X-lock on the table for the first insert. +# So concurrent insert won't happen on the table +INSERT INTO t1 (c1) VALUES (100); +INSERT INTO t1 (c1) VALUES (101); +} +START TRANSACTION; +INSERT INTO t1 (c1) VALUES (2); +INSERT INTO t1 (c1) VALUES (4); +connection con1; +START TRANSACTION; +INSERT INTO t1 (c1) VALUES (NULL), (10); +connection default; +INSERT INTO t1 (c1) VALUES (NULL), (NULL), (19); +INSERT INTO t1 (c1) VALUES (21); +connection con1; +INSERT INTO t1 (c1) VALUES (NULL); +connection default; +-- error 0, ER_DUP_ENTRY, ER_DUP_KEY +INSERT INTO t1 (c1) VALUES (16); +if ($mysql_errno) +{ + echo # ERROR (only OK if Archive) mysql_errno: $mysql_errno; +} +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; +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; + echo # mysql_errno: $mysql_errno; +} +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; + echo # mysql_errno: $mysql_errno; +} +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; + echo # mysql_errno: $mysql_errno; +} +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_ENTRY, ER_DUP_KEY +INSERT INTO t1 VALUES (2, 2); +if (!$mysql_errno) +{ + echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY; + echo # mysql_errno: $mysql_errno; +} +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_ENTRY, 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_ENTRY, 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; +SET INSERT_ID = 22; +-- error 0, ER_DUP_ENTRY, ER_DUP_KEY +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +if (!$mysql_errno) +{ + echo # ERROR (only OK if Blackhole) should give ER_DUP_KEY or ER_DUP_ENTRY; + echo # mysql_errno: $mysql_errno; +} +INSERT INTO t1 VALUES (NULL); +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; + +if (!$skip_negative_auto_inc) +{ +--echo ############################################################################# +--echo # Bug #45823 - Assertion failure in file row/row0mysql.c line 1386 +--echo # Bug #43988 - AUTO_INCREMENT errors with partitioned InnoDB tables in 5.1.31 +--echo ############################################################################## + +--echo # Inserting negative autoincrement values into a partition table (partitions >= 4) + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Reading from a partition table (partitions >= 2 ) after inserting a negative +--echo # value into the auto increment column + + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2; + +INSERT INTO t VALUES (-2,-20); +INSERT INTO t(c2) VALUES (30); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Inserting negative auto increment value into a partition table (partitions >= 2) +--echo # auto increment value > 2. + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 2; + +INSERT INTO t VALUES (-4,-20); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t(c2) VALUES (40); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Inserting -1 into autoincrement column of a partition table (partition >= 4) + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); + +SELECT * FROM t ORDER BY c1 ASC; + +INSERT INTO t(c2) VALUES (30); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Deleting from an auto increment table after inserting negative values + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); +INSERT INTO t VALUES (-3,-20); +INSERT INTO t(c2) VALUES (40); + +SELECT * FROM t ORDER BY c1 ASC; + +if (!$skip_delete) +{ +DELETE FROM t WHERE c1 > 1; +} + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Inserting a positive value that exceeds maximum allowed value for an +--echo # Auto Increment column (positive maximum) + +eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); + +--error ER_DUP_ENTRY +INSERT INTO t VALUES (128,50); +--error ER_DUP_ENTRY +INSERT INTO t VALUES (129,60); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Inserting a negative value that goes below minimum allowed value for an +--echo # Auto Increment column (negative minimum) + +eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-127,30); +INSERT INTO t VALUES (-128,40); + +--error ER_DUP_ENTRY +INSERT INTO t VALUES (-129,50); +--error ER_DUP_ENTRY +INSERT INTO t VALUES (-130,60); + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Updating the partition table with a negative Auto Increment value + +eval CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (-1,-10); +INSERT INTO t(c2) VALUES (30); + +SELECT * FROM t ORDER BY c1 ASC; + +if (!$skip_update) +{ +UPDATE t SET c1 = -6 WHERE c1 = 2; +} + +SELECT * FROM t ORDER BY c1 ASC; + +INSERT INTO t(c2) VALUES (40); +INSERT INTO t(c2) VALUES (50); + +if (!$skip_update) +{ +UPDATE t SET c1 = -6 WHERE c1 = 2; +} + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +--echo # Updating the partition table with a value that crosses the upper limits +--echo # on both the positive and the negative side. + +eval CREATE TABLE t (c1 TINYINT NOT NULL AUTO_INCREMENT, PRIMARY KEY(c1), + c2 INT) ENGINE=$engine PARTITION BY HASH(c1) PARTITIONS 4; + +INSERT INTO t(c2) VALUES (10); +INSERT INTO t(c2) VALUES (20); +INSERT INTO t VALUES (126,30); +INSERT INTO t VALUES (127,40); + +SELECT * FROM t ORDER BY c1 ASC; + +if (!$skip_update) +{ +UPDATE t SET c1 = 130 where c1 = 127; +} + +SELECT * FROM t ORDER BY c1 ASC; + +if (!$skip_update) +{ +UPDATE t SET c1 = -140 where c1 = 126; +} + +SELECT * FROM t ORDER BY c1 ASC; + +DROP TABLE t; + +if (!$skip_update) +{ +eval CREATE TABLE t1 +(a INT NULL AUTO_INCREMENT, + UNIQUE KEY (a)) +ENGINE=$engine +PARTITION BY KEY(a) PARTITIONS 2; +SET LAST_INSERT_ID = 999; +SET INSERT_ID = 0; +INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +INSERT INTO t1 SET a = 1 ON DUPLICATE KEY UPDATE a = NULL; +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +UPDATE t1 SET a = 1 WHERE a IS NULL; +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +UPDATE t1 SET a = NULL WHERE a = 1; +SELECT LAST_INSERT_ID(); +SELECT * FROM t1; +DROP TABLE t1; +} +--echo ############################################################################## +} + +if (!$skip_update) +{ +--echo # +--echo # MDEV-19622 Assertion failures in +--echo # ha_partition::set_auto_increment_if_higher upon UPDATE on Aria table +--echo # +eval CREATE OR REPLACE TABLE t1 (pk INT AUTO_INCREMENT, a INT, KEY(pk)) ENGINE=$engine PARTITION BY HASH(a); +INSERT INTO t1 VALUES (1,1),(2,2); +UPDATE t1 SET pk = 0; +DROP TABLE t1; + +--echo # +--echo # MDEV-29636 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' +--echo # failed in ha_partition::set_auto_increment_if_higher upon REPLACE +--echo # with partition pruning +--echo # +eval CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine +PARTITION BY RANGE (a) ( + PARTITION p0 VALUES LESS THAN (10), + PARTITION pn VALUES LESS THAN MAXVALUE +); +REPLACE INTO t1 PARTITION (p0) SELECT 1; +DROP TABLE t1; +} + +if (!$skip_delete) +{ +--echo # +--echo # MDEV-21027 Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' +--echo # ha_partition::set_auto_increment_if_higher +--echo # +eval CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine PARTITION BY HASH (a) PARTITIONS 3; +REPLACE INTO t1 PARTITION (p0) VALUES (3); +DROP TABLE t1; +} + +if (!$skip_truncate) +{ +--echo # +--echo # MDEV-21310 AUTO_INCREMENT column throws range error on INSERT in partitioned table | +--echo # Assertion `part_share->auto_inc_initialized || !can_use_for_auto_inc_init()' failed. +--echo # +eval CREATE TABLE t1 (c INT AUTO_INCREMENT KEY) ENGINE=$engine PARTITION BY LIST (c) (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2)); +ALTER TABLE t1 TRUNCATE PARTITION p1; +INSERT INTO t1 PARTITION (p1) (c) SELECT 1; +DROP TABLE t1; +} |