233 lines
7.0 KiB
Plaintext
233 lines
7.0 KiB
Plaintext
#---------------------------
|
|
# auto_increment_offset
|
|
#---------------------------
|
|
SET auto_increment_offset = 200;
|
|
CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=xengine;
|
|
INSERT INTO t1 (a,b) VALUES (NULL,'a'),(NULL,'b'),(NULL,'c');
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
1
|
|
SELECT a,b FROM t1 ORDER BY a;
|
|
a b
|
|
1 a
|
|
2 b
|
|
3 c
|
|
#---------------------------
|
|
# auto_increment_increment
|
|
#---------------------------
|
|
SET auto_increment_increment = 300;
|
|
INSERT INTO t1 (a,b) VALUES (NULL,'d'),(NULL,'e'),(NULL,'f');
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
200
|
|
SELECT a,b FROM t1 ORDER BY a;
|
|
a b
|
|
1 a
|
|
2 b
|
|
3 c
|
|
200 d
|
|
500 e
|
|
800 f
|
|
SET auto_increment_increment = 50;
|
|
INSERT INTO t1 (a,b) VALUES (NULL,'g'),(NULL,'h'),(NULL,'i');
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
850
|
|
SELECT a,b FROM t1 ORDER BY a;
|
|
a b
|
|
1 a
|
|
2 b
|
|
3 c
|
|
200 d
|
|
500 e
|
|
800 f
|
|
850 g
|
|
900 h
|
|
950 i
|
|
DROP TABLE t1;
|
|
#---------------------------
|
|
# offset is greater than the max value
|
|
#---------------------------
|
|
SET auto_increment_increment = 500;
|
|
SET auto_increment_offset = 300;
|
|
CREATE TABLE t1 (a TINYINT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine;
|
|
set @orig_sql_mode=@@session.sql_mode;
|
|
set session sql_mode="";
|
|
INSERT INTO t1 (a) VALUES (NULL);
|
|
Warnings:
|
|
Warning 1264 Out of range value for column 'a' at row 1
|
|
set session sql_mode=@orig_sql_mode;
|
|
SELECT LAST_INSERT_ID();
|
|
LAST_INSERT_ID()
|
|
127
|
|
SELECT a FROM t1 ORDER BY a;
|
|
a
|
|
127
|
|
DROP TABLE t1;
|
|
SET auto_increment_increment = 1;
|
|
SET auto_increment_offset = 1;
|
|
CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=xengine;
|
|
INSERT INTO t1 VALUES (18446744073709551613, 'a');
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` char(8) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES (NULL, 'b');
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` char(8) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES (NULL, 'c');
|
|
ERROR HY000: Failed to read auto-increment value from storage engine
|
|
SELECT * FROM t1;
|
|
a b
|
|
18446744073709551613 a
|
|
18446744073709551614 b
|
|
DROP TABLE t1;
|
|
SET auto_increment_increment = 300;
|
|
CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=xengine;
|
|
INSERT INTO t1 VALUES (18446744073709551613, 'a');
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` char(8) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES (NULL, 'b');
|
|
ERROR HY000: Failed to read auto-increment value from storage engine
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` char(8) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES (NULL, 'c');
|
|
ERROR HY000: Failed to read auto-increment value from storage engine
|
|
SELECT * FROM t1;
|
|
a b
|
|
18446744073709551613 a
|
|
DROP TABLE t1;
|
|
SET auto_increment_offset = 200;
|
|
CREATE TABLE t1 (a BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, b CHAR(8)) ENGINE=xengine;
|
|
INSERT INTO t1 VALUES (18446744073709551613, 'a');
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` char(8) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=18446744073709551614 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES (NULL, 'b');
|
|
ERROR HY000: Failed to read auto-increment value from storage engine
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
|
|
`b` char(8) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 VALUES (NULL, 'c');
|
|
ERROR HY000: Failed to read auto-increment value from storage engine
|
|
SELECT * FROM t1;
|
|
a b
|
|
18446744073709551613 a
|
|
DROP TABLE t1;
|
|
#----------------------------------
|
|
# Issue #792 Crash in autoincrement
|
|
#----------------------------------
|
|
CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY,C2 CHAR) ENGINE=xengine;
|
|
Warnings:
|
|
Warning 3856 AUTO_INCREMENT support for FLOAT/DOUBLE columns is deprecated and will be removed in a future release. Consider removing AUTO_INCREMENT from column 'C1'.
|
|
INSERT INTO t1 VALUES(2177,0);
|
|
DROP TABLE t1;
|
|
CREATE TABLE t0(c0 BLOB) ENGINE=xengine;
|
|
INSERT INTO t0 VALUES(0);
|
|
ALTER TABLE t0 AUTO_INCREMENT=0;
|
|
DROP TABLE t0;
|
|
set auto_increment_offset = 1;
|
|
set auto_increment_increment = 1;
|
|
CREATE TABLE t1 (pk INT AUTO_INCREMENT, a INT, PRIMARY KEY(pk)) ENGINE=xengine;
|
|
INSERT INTO t1 (a) VALUES (1);
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`pk` int(11) NOT NULL AUTO_INCREMENT,
|
|
`a` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`pk`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
select * from t1;
|
|
pk a
|
|
1 1
|
|
UPDATE t1 SET pk = 100;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`pk` int(11) NOT NULL AUTO_INCREMENT,
|
|
`a` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`pk`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 (a) VALUES (2);
|
|
SELECT * FROM t1;
|
|
pk a
|
|
100 1
|
|
101 2
|
|
ALTER TABLE t1 AUTO_INCREMENT 2;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`pk` int(11) NOT NULL AUTO_INCREMENT,
|
|
`a` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`pk`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=102 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 (a) VALUES (3);
|
|
select * from t1;
|
|
pk a
|
|
100 1
|
|
101 2
|
|
102 3
|
|
ALTER TABLE t1 AUTO_INCREMENT 200;
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`pk` int(11) NOT NULL AUTO_INCREMENT,
|
|
`a` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`pk`)
|
|
) ENGINE=XENGINE AUTO_INCREMENT=200 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
INSERT INTO t1 (a) VALUES (4);
|
|
select * from t1;
|
|
pk a
|
|
100 1
|
|
101 2
|
|
102 3
|
|
200 4
|
|
DROP TABLE t1;
|
|
#----------------------------------
|
|
# Issue #902 Debug assert in autoincrement with small field type
|
|
#----------------------------------
|
|
SET auto_increment_increment=100, auto_increment_offset=10;
|
|
CREATE TABLE t1(i INT AUTO_INCREMENT PRIMARY KEY) ENGINE=xengine AUTO_INCREMENT=18446744073709551615;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
ERROR HY000: Failed to read auto-increment value from storage engine
|
|
SELECT * FROM t1;
|
|
i
|
|
ALTER TABLE t1 AUTO_INCREMENT=1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT * FROM t1;
|
|
i
|
|
10
|
|
ALTER TABLE t1 AUTO_INCREMENT=18446744073709551615;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
ERROR HY000: Failed to read auto-increment value from storage engine
|
|
SELECT * FROM t1;
|
|
i
|
|
10
|
|
DROP TABLE t1;
|