diff options
Diffstat (limited to 'mysql-test/suite/innodb/t/innodb-autoinc.test')
| -rw-r--r-- | mysql-test/suite/innodb/t/innodb-autoinc.test | 783 |
1 files changed, 783 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/t/innodb-autoinc.test b/mysql-test/suite/innodb/t/innodb-autoinc.test new file mode 100644 index 00000000..15846055 --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb-autoinc.test @@ -0,0 +1,783 @@ +--source include/have_innodb.inc + +# +# Bug #34335 +# +CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (9223372036854775807, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; +# +## Test AUTOINC overflow +## + +# TINYINT +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (127, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (255, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; +# +# SMALLINT +# +CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (32767, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (65535, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; +# +# MEDIUMINT +# +CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (8388607, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (16777215, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; +# +# INT +# +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2147483647, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (4294967295, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; +# +# BIGINT +# +CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (9223372036854775807, null); +-- error 167 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (18446744073709551615, null); +-- error ER_AUTOINC_READ_FAILED,1467 +INSERT INTO t1 (c2) VALUES ('innodb'); +SELECT * FROM t1; +DROP TABLE t1; + +# +# Bug 37531 +# After truncate, auto_increment behaves incorrectly for InnoDB +# +CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +SELECT c1 FROM t1; +SHOW CREATE TABLE t1; +TRUNCATE TABLE t1; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +SELECT c1 FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# +# Deleting all records should not reset the AUTOINC counter. +# +CREATE TABLE t1(c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +SELECT c1 FROM t1; +SHOW CREATE TABLE t1; +DELETE FROM t1; +SHOW CREATE TABLE t1; +INSERT INTO t1 VALUES (1), (2), (3); +INSERT INTO t1 VALUES (NULL), (NULL), (NULL); +SELECT c1 FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +# +# Bug 38839 +# Reset the last value generated at end of statement +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL, 1); +DELETE FROM t1 WHERE c1 = 1; +INSERT INTO t1 VALUES (2,1); +INSERT INTO t1 VALUES (NULL,8); +SELECT * FROM t1; +DROP TABLE t1; +# Bug 38839 -- same as above but for multi value insert +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL, 1); +DELETE FROM t1 WHERE c1 = 1; +INSERT INTO t1 VALUES (2,1), (NULL, 8); +INSERT INTO t1 VALUES (NULL,9); +SELECT * FROM t1; +DROP TABLE t1; + +# +# Test changes to AUTOINC next value calculation +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL),(5),(NULL); +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# Test with SIGNED INT column, by inserting a 0 for the first column value +# 0 is treated in the same was NULL. +# Reset the AUTOINC session variables +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(0); +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +INSERT INTO t1 VALUES (-1), (NULL),(2),(NULL); +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +SET @@INSERT_ID=400; +# Duplicate error expected here for autoinc_lock_mode != TRADITIONAL +-- error ER_DUP_ENTRY,1062 +INSERT INTO t1 VALUES(NULL),(NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# Test with SIGNED INT column +# Reset the AUTOINC session variables +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(-1); +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "auto_inc%"; +INSERT INTO t1 VALUES (-2), (NULL),(2),(NULL); +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL),(NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# Test with UNSIGNED INT column, single insert +# The sign in the value is ignored and a new column value is generated +# Reset the AUTOINC session variables +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT IGNORE INTO t1 VALUES(-1); +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "auto_inc%"; +INSERT IGNORE INTO t1 VALUES (-2); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (2); +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (250); +INSERT INTO t1 VALUES (NULL); +SELECT * FROM t1; +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES(NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# Test with UNSIGNED INT column, multi-value inserts +# The sign in the value is ignored and a new column value is generated +# Reset the AUTOINC session variables +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +INSERT IGNORE INTO t1 VALUES(-1); +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=100, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "auto_inc%"; +INSERT IGNORE INTO t1 VALUES (-2),(NULL),(2),(NULL); +INSERT INTO t1 VALUES (250),(NULL); +SELECT * FROM t1; +INSERT INTO t1 VALUES (1000); +SET @@INSERT_ID=400; +# Duplicate error expected here for autoinc_lock_mode != TRADITIONAL +-- error ER_DUP_ENTRY,1062 +INSERT INTO t1 VALUES(NULL),(NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# +# Check for overflow handling when increment is > 1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +# TODO: Fix the autoinc init code +# We have to do this because of a bug in the AUTOINC init code. +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (9223372036854775794); #-- 2^63 - 14 +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "auto_inc%"; +# This should just fit +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# +# Check for overflow handling when increment and offser are > 1 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +# TODO: Fix the autoinc init code +# We have to do this because of a bug in the AUTOINC init code. +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13 +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=2, @@SESSION.AUTO_INCREMENT_OFFSET=10; +SHOW VARIABLES LIKE "auto_inc%"; +--error ER_AUTOINC_READ_FAILED +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# +# Check for overflow handling when increment and offset are odd numbers +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +# TODO: Fix the autoinc init code +# We have to do this because of a bug in the AUTOINC init code. +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551603); #-- 2^64 - 13 +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=5, @@SESSION.AUTO_INCREMENT_OFFSET=7; +SHOW VARIABLES LIKE "auto_inc%"; +--error ER_AUTOINC_READ_FAILED +INSERT INTO t1 VALUES (NULL),(NULL), (NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# Check for overflow handling when increment and offset are odd numbers +# and check for large -ve numbers +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 BIGINT AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +# TODO: Fix the autoinc init code +# We have to do this because of a bug in the AUTOINC init code. +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES(-9223372036854775806); #-- -2^63 + 2 +INSERT INTO t1 VALUES(-9223372036854775807); #-- -2^63 + 1 +INSERT INTO t1 VALUES(-9223372036854775808); #-- -2^63 +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=3, @@SESSION.AUTO_INCREMENT_OFFSET=3; +SHOW VARIABLES LIKE "auto_inc%"; +INSERT INTO t1 VALUES (NULL),(NULL), (NULL); +SELECT * FROM t1; +DROP TABLE t1; +# +# Check for overflow handling when increment and offset are very +# large numbers 2^60 +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 BIGINT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(c1)) ENGINE=InnoDB; +# TODO: Fix the autoinc init code +# We have to do this because of a bug in the AUTOINC init code. +INSERT INTO t1 VALUES(NULL); +INSERT INTO t1 VALUES (18446744073709551610); #-- 2^64 - 2 +SELECT * FROM t1; +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1152921504606846976, @@SESSION.AUTO_INCREMENT_OFFSET=1152921504606846976; +SHOW VARIABLES LIKE "auto_inc%"; +--error HA_ERR_AUTOINC_ERANGE +INSERT INTO t1 VALUES (NULL),(NULL); +SELECT * FROM t1; +DROP TABLE t1; + +# +# Check for floating point autoinc column handling +# +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SET @@INSERT_ID=1; +SHOW VARIABLES LIKE "auto_inc%"; +CREATE TABLE t1 (c1 DOUBLE NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL, 1); +INSERT INTO t1 VALUES(NULL, 2); +SELECT * FROM t1; +ALTER TABLE t1 CHANGE c1 c1 SERIAL; +SELECT * FROM t1; +INSERT INTO t1 VALUES(NULL, 3); +INSERT INTO t1 VALUES(NULL, 4); +SELECT * FROM t1; +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 FLOAT NOT NULL AUTO_INCREMENT, c2 INT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES(NULL, 1); +INSERT INTO t1 VALUES(NULL, 2); +SELECT * FROM t1; +ALTER TABLE t1 CHANGE c1 c1 SERIAL; +SELECT * FROM t1; +INSERT INTO t1 VALUES(NULL, 3); +INSERT INTO t1 VALUES(NULL, 4); +SELECT * FROM t1; +DROP TABLE t1; + +--echo # +--echo # Bug#15851528 DUPLICATE KEY ERROR ON AUTO-INC PK WITH MIXED AUTO_INCREMENT_INCREMENT CLIENTS +--echo # +--echo # This test shows that the next record to be inserted is not affected +--echo # by a change in auto_increment_increment. +--echo # In addition, current value of auto_increment_increment by the client +--echo # that uses the existing autoinc value with be used to set next autoinc +--echo # value, which will be used by next client reguardless of its own session +--echo # setting for auto_increment_increment. +--echo # + +--connection default +--echo # Client 1: Insert a record with auto_increment_increment=2 +CREATE TABLE t( + a SERIAL PRIMARY KEY, + b VARCHAR(200)) ENGINE=InnoDB; +SET SESSION auto_increment_increment=2; +SHOW CREATE TABLE t; +INSERT INTO t(b) VALUES('S1'); +SELECT a,b FROM t; +--connect(con1,localhost,root,,) + +--connection con1 +--echo # Client 2: Insert records with auto_increment_increment 2,1 +SET SESSION auto_increment_increment=2; +SHOW CREATE TABLE t; +INSERT INTO t(b) VALUES('S2'); +SELECT a,b FROM t; +SET SESSION auto_increment_increment=1; +SHOW CREATE TABLE t; +INSERT INTO t(b) VALUES('S2'); +SELECT a,b FROM t; +disconnect con1; + +--connection default +--echo # Client 1: Insert a record with auto_increment_increment=1 +SET SESSION auto_increment_increment=1; +SHOW CREATE TABLE t; +INSERT INTO t(b) VALUES('S1'); +SELECT a,b FROM t; +DROP TABLE t; + +--echo # Autoincrement behaviour with mixed insert. +CREATE TABLE t( + a TINYINT AUTO_INCREMENT PRIMARY KEY, + b VARCHAR(200)) ENGINE=InnoDB; +SET SESSION auto_increment_increment=10; +SHOW CREATE TABLE t; +INSERT INTO t(b) VALUES('S0'),('S1'); +SHOW CREATE TABLE t; +INSERT INTO t(a,b) VALUES(28,'S2'); +SET SESSION auto_increment_increment=1; +SHOW CREATE TABLE t; +INSERT INTO t(b) VALUES('S3'); +SHOW CREATE TABLE t; +INSERT INTO t(b) VALUES('S4'); +SELECT * FROM t; +SHOW CREATE TABLE t; +DROP TABLE t; + +# +# Bug# 42714: AUTOINC column calculated next value not greater than highest +# value stored in table. +# +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=5; +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +CREATE TABLE t1 ( + a INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, + b INT(10) UNSIGNED NOT NULL, + c ENUM('FALSE','TRUE') DEFAULT NULL, + PRIMARY KEY (a)) ENGINE = InnoDB; +CREATE TABLE t2 ( + m INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, + n INT(10) UNSIGNED NOT NULL, + o enum('FALSE','TRUE') DEFAULT NULL, + PRIMARY KEY (m)) ENGINE = InnoDB; +INSERT INTO t2 (n,o) VALUES + (1 , 'true'), (1 , 'false'), (2 , 'true'), (2 , 'false'), (3 , 'true'), + (3 , 'false'), (4 , 'true'), (4 , 'false'), (5 , 'true'), (5 , 'false'); +SELECT * FROM t2; +SHOW CREATE TABLE t2; +INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; +SHOW CREATE TABLE t1; +INSERT INTO t1 (b,c) SELECT n,o FROM t2 ; +SELECT * FROM t1; +SHOW CREATE TABLE t1; +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SELECT * FROM t1; +SHOW CREATE TABLE t1; +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SELECT * FROM t1; +SHOW CREATE TABLE t1; +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SHOW CREATE TABLE t1; +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SHOW CREATE TABLE t1; +INSERT INTO t1 (b,c) SELECT n,o FROM t2 WHERE o = 'false'; +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +DROP TABLE t2; +# +# 43203: Overflow from auto incrementing causes server segv +# + +DROP TABLE IF EXISTS t1; +DROP TABLE IF EXISTS t2; +SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; +CREATE TABLE t1( + c1 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT + PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL); +CREATE TABLE t2( + c1 TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT + PRIMARY KEY) ENGINE=InnoDB; +-- error ER_DUP_ENTRY,1062 +INSERT INTO t2 SELECT c1 FROM t1; +-- error 167 +INSERT INTO t2 SELECT NULL FROM t1; +DROP TABLE t1; +DROP TABLE t2; +SET sql_mode = DEFAULT; + +# If the user has specified negative values for an AUTOINC column then +# InnoDB should ignore those values when setting the table's max value. +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SHOW VARIABLES LIKE "auto_inc%"; +# TINYINT +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-127, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT IGNORE INTO t1 VALUES (-1, 'innodb'); +INSERT IGNORE INTO t1 VALUES (-127, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# SMALLINT +# +CREATE TABLE t1 (c1 SMALLINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-32767, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT IGNORE INTO t1 VALUES (-1, 'innodb'); +INSERT IGNORE INTO t1 VALUES (-32757, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# MEDIUMINT +# +CREATE TABLE t1 (c1 MEDIUMINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-8388607, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 MEDIUMINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT IGNORE INTO t1 VALUES (-1, 'innodb'); +INSERT IGNORE INTO t1 VALUES (-8388607, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# INT +# +CREATE TABLE t1 (c1 INT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-2147483647, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT IGNORE INTO t1 VALUES (-1, 'innodb'); +INSERT IGNORE INTO t1 VALUES (-2147483647, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# BIGINT +# +CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT INTO t1 VALUES (-1, 'innodb'); +INSERT INTO t1 VALUES (-9223372036854775807, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (c1 BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +INSERT IGNORE INTO t1 VALUES (-1, 'innodb'); +INSERT IGNORE INTO t1 VALUES (-9223372036854775807, 'innodb'); +INSERT INTO t1 VALUES (NULL, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; +# +# End negative number check + +## +# 47125: auto_increment start value is ignored if an index is created +# and engine=innodb +# +CREATE TABLE t1 (c1 INT AUTO_INCREMENT, c2 INT, PRIMARY KEY(c1)) AUTO_INCREMENT=10 ENGINE=InnoDB; +CREATE INDEX i1 on t1(c2); +SHOW CREATE TABLE t1; +INSERT INTO t1 (c2) values (0); +SELECT * FROM t1; +DROP TABLE t1; + +## +# 49032: Use the correct function to read the AUTOINC column value +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +# Restart the server +-- source include/restart_mysqld.inc +INSERT INTO t1(C2) VALUES ('innodb'); +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1(C1 FLOAT AUTO_INCREMENT KEY, C2 CHAR(10)) ENGINE=InnoDB; +INSERT INTO t1(C1, C2) VALUES (1, 'innodb'), (3, 'innodb'); +# Restart the server +-- source include/restart_mysqld.inc +INSERT INTO t1(C2) VALUES ('innodb'); +SHOW CREATE TABLE t1; +DROP TABLE t1; + +## +# 47720: REPLACE INTO Autoincrement column with negative values +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 SET c1 = 1; +SHOW CREATE TABLE t1; +INSERT INTO t1 SET c1 = 2; +INSERT INTO t1 SET c1 = -1; +SELECT * FROM t1; +-- error ER_DUP_ENTRY,1062 +INSERT INTO t1 SET c1 = -1; +SHOW CREATE TABLE t1; +REPLACE INTO t1 VALUES (-1); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +## +# 49497: Error 1467 (ER_AUTOINC_READ_FAILED) on inserting a negative value +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (1), (NULL), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-685113344), (2), (NULL), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (NULL); +INSERT INTO t1 VALUES (4), (5), (6), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL), (2), (-685113344), (5); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; +CREATE TABLE t1 (c1 INTEGER AUTO_INCREMENT, PRIMARY KEY (c1)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1), (2), (-685113344), (NULL); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +## +# 55277: Failing assertion: auto_inc > 0 +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1(c1 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (NULL); +INSERT INTO t1 VALUES (18446744073709551615); +# Restart the server +-- source include/restart_mysqld.inc +SHOW CREATE TABLE t1; +DROP TABLE t1; + + +# Check if we handle offset > column max value properly +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=256; +SHOW VARIABLES LIKE "auto_inc%"; +# TINYINT +CREATE TABLE t1 (c1 TINYINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, NULL); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +# Check if we handle the case where a current value is greater than the max +# of the column. IMO, this should not be allowed and the assertion that fails +# is actually an invariant. +SET @@SESSION.AUTO_INCREMENT_INCREMENT=1, @@SESSION.AUTO_INCREMENT_OFFSET=1; +SHOW VARIABLES LIKE "auto_inc%"; +# TINYINT +CREATE TABLE t1 (c1 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (2147483648, 'a'); +SHOW CREATE TABLE t1; +SELECT * FROM t1; +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +ALTER TABLE t1 CHANGE c1 c1 INT default 0; +SHOW CREATE TABLE t1; +INSERT INTO t1(c2) VALUES('b'); +SELECT * FROM t1; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # Bug #14049391 INNODB MISCALCULATES AUTO-INCREMENT +--echo # AFTER CHANGING AUTO_INCREMENT_INCREMEMENT +--echo # +CREATE TABLE t ( i INT AUTO_INCREMENT, KEY(i) ) ENGINE=InnoDB; +SET auto_increment_increment = 300; +INSERT INTO t VALUES (NULL), (NULL); +SHOW CREATE TABLE t; +SET auto_increment_increment = 50; +INSERT INTO t VALUES (NULL); +SELECT * FROM t; +SHOW CREATE TABLE t; +DROP TABLE t; +SET auto_increment_increment = DEFAULT; + + +--echo # +--echo # MDEV-14008 Assertion failing: `!is_set() || (m_status == DA_OK_BULK && is_bulk_op()) +--echo # + +SET sql_mode=STRICT_ALL_TABLES; +CREATE TABLE t1 ( + c1 DOUBLE NOT NULL PRIMARY KEY AUTO_INCREMENT +) ENGINE=InnoDB AUTO_INCREMENT=10000000000000000000; +INSERT INTO t1 VALUES (); +SELECT * FROM t1; +DROP TABLE t1; + +CREATE TABLE t1 (a DOUBLE PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB; +INSERT INTO t1 VALUES (-1); +SELECT * FROM t1; +DROP TABLE t1; + + +--echo # +--echo # MDEV-15352 AUTO_INCREMENT breaks after updating a column value to a negative number +--echo # + +SET @engine='INNODB'; +--source include/autoinc_mdev15353.inc + +--echo # +--echo # MDEV-28416 Incorrect AUTO_INCREMENT may be issued +--echo # + +SET @aii=@@auto_increment_increment; +SET auto_increment_increment=300; +CREATE TABLE t1 (a SERIAL) ENGINE=innodb; +INSERT INTO t1 VALUES (18446744073709551613); +SHOW CREATE TABLE t1; +--error HA_ERR_AUTOINC_ERANGE +INSERT INTO t1 VALUES (NULL); +SHOW CREATE TABLE t1; +DROP TABLE t1; +SET auto_increment_increment=@aii; + +--echo # End of 10.2 tests |