640 lines
22 KiB
Plaintext
640 lines
22 KiB
Plaintext
|
-- source include/have_innodb.inc
|
||
|
|
# embedded server ignores 'delayed', so skip this
|
||
|
|
-- source include/not_embedded.inc
|
||
|
|
|
||
|
|
--disable_warnings
|
||
|
|
drop table if exists t1;
|
||
|
|
--enable_warnings
|
||
|
|
|
||
|
|
#
|
||
|
|
# Bug #34335
|
||
|
|
#
|
||
|
|
CREATE TABLE t1 (c1 BIGINT PRIMARY KEY AUTO_INCREMENT, c2 VARCHAR(10)) ENGINE=InnoDB;
|
||
|
|
INSERT INTO t1 VALUES (9223372036854775807, null);
|
||
|
|
-- error ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 ER_DUP_ENTRY,1062
|
||
|
|
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 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);
|
||
|
|
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 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;
|
||
|
|
# 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 ER_WARN_DATA_OUT_OF_RANGE
|
||
|
|
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;
|
||
|
|
|
||
|
|
#
|
||
|
|
# 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');
|
||
|
|
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;
|
||
|
|
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 ER_DUP_ENTRY,1467
|
||
|
|
INSERT INTO t2 SELECT NULL FROM t1;
|
||
|
|
DROP TABLE t1;
|
||
|
|
DROP TABLE t2;
|
||
|
|
|
||
|
|
# 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 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;
|
||
|
|
#
|
||
|
|
# 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 INTO t1 VALUES (-1, 'innodb');
|
||
|
|
INSERT 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 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;
|
||
|
|
#
|
||
|
|
# 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 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;
|
||
|
|
#
|
||
|
|
# 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 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;
|
||
|
|
#
|
||
|
|
# 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;
|