mirror of
https://github.com/MariaDB/server.git
synced 2025-11-03 20:36:16 +01:00
1010 lines
25 KiB
Text
1010 lines
25 KiB
Text
ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
|
|
#
|
|
# MDEV-6076 Persistent AUTO_INCREMENT for InnoDB
|
|
#
|
|
# WL#6204 InnoDB persistent max value for autoinc columns
|
|
#
|
|
# Most of this test case is copied from the test innodb.autoinc_persist
|
|
# that was introduced in MySQL 8.0.0. The observable behaviour
|
|
# of MDEV-6076 is equivalent to WL#6204, with the exception that
|
|
# there is less buffering taking place and redo log checkpoints
|
|
# are not being treated specially.
|
|
# Due to less buffering, there is no debug instrumentation testing
|
|
# for MDEV-6076.
|
|
#
|
|
# Pre-create several tables
|
|
SET SQL_MODE='STRICT_ALL_TABLES';
|
|
CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t1 VALUES(0), (0), (0), (0), (-1), (-10), (0),
|
|
(20), (30), (31);
|
|
SELECT * FROM t1;
|
|
a
|
|
-10
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
20
|
|
30
|
|
31
|
|
CREATE TABLE t2(a TINYINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t2 VALUES(-5);
|
|
ERROR 22003: Out of range value for column 'a' at row 1
|
|
INSERT INTO t2 VALUES(0), (0), (0), (0), (8), (10), (0),
|
|
(20), (30), (31);
|
|
SELECT * FROM t2;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
8
|
|
10
|
|
11
|
|
20
|
|
30
|
|
31
|
|
CREATE TABLE t3(a SMALLINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t3 VALUES(0), (0), (0), (0), (-1), (-10), (0),
|
|
(20), (30), (31), (1024), (4096);
|
|
SELECT * FROM t3;
|
|
a
|
|
-10
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
20
|
|
30
|
|
31
|
|
1024
|
|
4096
|
|
CREATE TABLE t4(a SMALLINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t4 VALUES(-5);
|
|
ERROR 22003: Out of range value for column 'a' at row 1
|
|
INSERT INTO t4 VALUES(0), (0), (0), (0), (8), (10), (0),
|
|
(20), (30), (31), (1024), (4096);
|
|
SELECT * FROM t4;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
8
|
|
10
|
|
11
|
|
20
|
|
30
|
|
31
|
|
1024
|
|
4096
|
|
CREATE TABLE t5(a MEDIUMINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t5 VALUES(0), (0), (0), (0), (-1), (-10), (0),
|
|
(20), (30), (31), (1000000), (1000005);
|
|
SELECT * FROM t5;
|
|
a
|
|
-10
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
20
|
|
30
|
|
31
|
|
1000000
|
|
1000005
|
|
CREATE TABLE t6(a MEDIUMINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t6 VALUES(-5);
|
|
ERROR 22003: Out of range value for column 'a' at row 1
|
|
INSERT INTO t6 VALUES(0), (0), (0), (0), (8), (10), (0),
|
|
(20), (30), (31), (1000000), (1000005);
|
|
SELECT * FROM t6;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
8
|
|
10
|
|
11
|
|
20
|
|
30
|
|
31
|
|
1000000
|
|
1000005
|
|
CREATE TABLE t7(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t7 VALUES(0), (0), (0), (0), (-1), (-10), (0),
|
|
(20), (30), (31), (100000000), (100000008);
|
|
SELECT * FROM t7;
|
|
a
|
|
-10
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
20
|
|
30
|
|
31
|
|
100000000
|
|
100000008
|
|
CREATE TABLE t8(a INT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t8 VALUES(-5);
|
|
ERROR 22003: Out of range value for column 'a' at row 1
|
|
INSERT INTO t8 VALUES(0), (0), (0), (0), (8), (10), (0),
|
|
(20), (30), (31), (100000000), (100000008);
|
|
SELECT * FROM t8;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
8
|
|
10
|
|
11
|
|
20
|
|
30
|
|
31
|
|
100000000
|
|
100000008
|
|
CREATE TABLE t9(a BIGINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t9 VALUES(0), (0), (0), (0), (-1), (-10), (0),
|
|
(20), (30), (31), (100000000000), (100000000006);
|
|
SELECT * FROM t9;
|
|
a
|
|
-10
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
20
|
|
30
|
|
31
|
|
100000000000
|
|
100000000006
|
|
CREATE TABLE t10(a BIGINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t10 VALUES(-5);
|
|
ERROR 22003: Out of range value for column 'a' at row 1
|
|
INSERT INTO t10 VALUES(0), (0), (0), (0), (8), (10), (0),
|
|
(20), (30), (31), (100000000000), (100000000006);
|
|
SELECT * FROM t10;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
8
|
|
10
|
|
11
|
|
20
|
|
30
|
|
31
|
|
100000000000
|
|
100000000006
|
|
CREATE TABLE t11(a FLOAT AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t11 VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
|
|
SELECT * FROM t11;
|
|
a
|
|
-10
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
20
|
|
30
|
|
31
|
|
CREATE TABLE t11u(a FLOAT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t11u VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
|
|
ERROR 22003: Out of range value for column 'a' at row 5
|
|
INSERT INTO t11u VALUES(0), (0), (0), (0), (0), (20), (30), (31);
|
|
SELECT * FROM t11u;
|
|
a
|
|
11
|
|
12
|
|
13
|
|
14
|
|
15
|
|
20
|
|
30
|
|
31
|
|
CREATE TABLE t12(a DOUBLE AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t12 VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
|
|
SELECT * FROM t12;
|
|
a
|
|
-10
|
|
-1
|
|
1
|
|
2
|
|
3
|
|
4
|
|
5
|
|
20
|
|
30
|
|
31
|
|
CREATE TABLE t12u(a DOUBLE UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t12u VALUES(0), (0), (0), (0), (-1), (-10), (0), (20), (30), (31);
|
|
ERROR 22003: Out of range value for column 'a' at row 5
|
|
INSERT INTO t12u VALUES(0), (0), (0), (0), (0), (20), (30), (31);
|
|
SELECT * FROM t12u;
|
|
a
|
|
11
|
|
12
|
|
13
|
|
14
|
|
15
|
|
20
|
|
30
|
|
31
|
|
# Scenario 1: Normal restart, to test if the counters are persisted
|
|
# Scenario 2: Delete some values, to test the counters should not be the
|
|
# one which is the largest in current table
|
|
DELETE FROM t1 WHERE a > 30;
|
|
SELECT MAX(a) AS `Expect 30` FROM t1;
|
|
Expect 30
|
|
30
|
|
DELETE FROM t3 WHERE a > 2000;
|
|
SELECT MAX(a) AS `Expect 2000` FROM t3;
|
|
Expect 2000
|
|
1024
|
|
DELETE FROM t5 WHERE a > 1000000;
|
|
SELECT MAX(a) AS `Expect 1000000` FROM t5;
|
|
Expect 1000000
|
|
1000000
|
|
DELETE FROM t7 WHERE a > 100000000;
|
|
SELECT MAX(a) AS `Expect 100000000` FROM t7;
|
|
Expect 100000000
|
|
100000000
|
|
DELETE FROM t9 WHERE a > 100000000000;
|
|
SELECT MAX(a) AS `Expect 100000000000` FROM t9;
|
|
Expect 100000000000
|
|
100000000000
|
|
CREATE TABLE t13(a INT AUTO_INCREMENT KEY) ENGINE = InnoDB,
|
|
AUTO_INCREMENT = 1234;
|
|
# restart
|
|
SHOW CREATE TABLE t13;
|
|
Table Create Table
|
|
t13 CREATE TABLE `t13` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=1234 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t13 VALUES(0);
|
|
SELECT a AS `Expect 1234` FROM t13;
|
|
Expect 1234
|
|
1234
|
|
DROP TABLE t13;
|
|
INSERT INTO t1 VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 33` FROM t1;
|
|
Expect 33
|
|
33
|
|
INSERT INTO t3 VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 4098` FROM t3;
|
|
Expect 4098
|
|
4098
|
|
INSERT INTO t5 VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 1000007` FROM t5;
|
|
Expect 1000007
|
|
1000007
|
|
INSERT INTO t7 VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 100000010` FROM t7;
|
|
Expect 100000010
|
|
100000010
|
|
INSERT INTO t9 VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 100000000008` FROM t9;
|
|
Expect 100000000008
|
|
100000000008
|
|
# Scenario 3: Insert some bigger counters, the next counter should start
|
|
# from there
|
|
INSERT INTO t1 VALUES(40), (0);
|
|
INSERT INTO t1 VALUES(42), (0);
|
|
SELECT a AS `Expect 43, 42` FROM t1 ORDER BY a DESC LIMIT 4;
|
|
Expect 43, 42
|
|
43
|
|
42
|
|
41
|
|
40
|
|
INSERT INTO t3 VALUES(5000), (0);
|
|
INSERT INTO t3 VALUES(5010), (0);
|
|
SELECT a AS `Expect 5011, 5010` FROM t3 ORDER BY a DESC LIMIT 4;
|
|
Expect 5011, 5010
|
|
5011
|
|
5010
|
|
5001
|
|
5000
|
|
INSERT INTO t5 VALUES(1000010), (0);
|
|
INSERT INTO t5 VALUES(1000020), (0);
|
|
SELECT a AS `Expect 1000021, 1000020` FROM t5 ORDER BY a DESC LIMIT 4;
|
|
Expect 1000021, 1000020
|
|
1000021
|
|
1000020
|
|
1000011
|
|
1000010
|
|
INSERT INTO t7 VALUES(100000020), (0);
|
|
INSERT INTO t7 VALUES(100000030), (0);
|
|
SELECT a AS `Expect 100000031, 100000030` FROM t7 ORDER BY a DESC LIMIT 4;
|
|
Expect 100000031, 100000030
|
|
100000031
|
|
100000030
|
|
100000021
|
|
100000020
|
|
INSERT INTO t9 VALUES(100000000010), (0);
|
|
INSERT INTO t9 VALUES(100000000020), (0);
|
|
SELECT a AS `Expect 100000000021, 100000000020` FROM t9 ORDER BY a DESC LIMIT 4;
|
|
Expect 100000000021, 100000000020
|
|
100000000021
|
|
100000000020
|
|
100000000011
|
|
100000000010
|
|
# Scenario 4: Update some values, to test the counters should be updated
|
|
# to the bigger value, but not smaller value.
|
|
INSERT INTO t1 VALUES(50), (55);
|
|
UPDATE t1 SET a = 105 WHERE a = 5;
|
|
UPDATE t1 SET a = 100 WHERE a = 55;
|
|
# This should insert 102, 106, 107, and make next counter 109.
|
|
INSERT INTO t1 VALUES(102), (0), (0);
|
|
SELECT a AS `Expect 107, 106` FROM t1 ORDER BY a DESC LIMIT 2;
|
|
Expect 107, 106
|
|
107
|
|
106
|
|
DELETE FROM t1 WHERE a > 105;
|
|
INSERT INTO t1 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 109` FROM t1;
|
|
Expect 109
|
|
109
|
|
# Test the same things on t3, t5, t7, t9, to test if DDTableBuffer would
|
|
# be updated accordingly
|
|
INSERT INTO t3 VALUES(60), (65);
|
|
UPDATE t3 SET a = 6005 WHERE a = 5;
|
|
UPDATE t3 SET a = 6000 WHERE a = 60;
|
|
# This should insert 6002, 6006, 6007, and make next counter 6009.
|
|
INSERT INTO t3 VALUES(6002), (0), (0);
|
|
SELECT a AS `Expect 6007, 6006` FROM t3 ORDER BY a DESC LIMIT 2;
|
|
Expect 6007, 6006
|
|
6007
|
|
6006
|
|
DELETE FROM t3 WHERE a > 6005;
|
|
INSERT INTO t3 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 6009` FROM t3;
|
|
Expect 6009
|
|
6009
|
|
INSERT INTO t5 VALUES(100), (200);
|
|
UPDATE t5 SET a = 1000105 WHERE a = 5;
|
|
UPDATE t5 SET a = 1000100 WHERE a = 100;
|
|
# This should insert 1000102, 1000106, 1000107, and make next counter
|
|
# 1000109.
|
|
INSERT INTO t5 VALUES(1000102), (0), (0);
|
|
SELECT a AS `Expect 1000107, 1000106` FROM t5 ORDER BY a DESC LIMIT 2;
|
|
Expect 1000107, 1000106
|
|
1000107
|
|
1000106
|
|
DELETE FROM t5 WHERE a > 1000105;
|
|
INSERT INTO t5 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 1000109` FROM t5;
|
|
Expect 1000109
|
|
1000109
|
|
INSERT INTO t7 VALUES(100), (200);
|
|
UPDATE t7 SET a = 100000105 WHERE a = 5;
|
|
UPDATE t7 SET a = 100000100 WHERE a = 100;
|
|
# This should insert 100000102, 1100000106, 100000107, and make next
|
|
# counter 100000109.
|
|
INSERT INTO t7 VALUES(100000102), (0), (0);
|
|
SELECT a AS `Expect 100000107, 100000106` FROM t7 ORDER BY a DESC LIMIT 2;
|
|
Expect 100000107, 100000106
|
|
100000107
|
|
100000106
|
|
DELETE FROM t7 WHERE a > 100000105;
|
|
INSERT INTO t7 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 100000109` FROM t7;
|
|
Expect 100000109
|
|
100000109
|
|
INSERT INTO t9 VALUES(100), (200);
|
|
UPDATE t9 SET a = 100000000105 WHERE a = 5;
|
|
UPDATE t9 SET a = 100000000100 WHERE a = 100;
|
|
# This should insert 100000000102, 100000000106, 100000000107, and make
|
|
# next counter 100000000109.
|
|
INSERT INTO t9 VALUES(100000000102), (0), (0);
|
|
SELECT a AS `Expect 100000000107, 100000000106` FROM t9 ORDER BY a DESC LIMIT 2;
|
|
Expect 100000000107, 100000000106
|
|
100000000107
|
|
100000000106
|
|
DELETE FROM t9 WHERE a > 100000000105;
|
|
INSERT INTO t9 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 100000000109` FROM t9;
|
|
Expect 100000000109
|
|
100000000109
|
|
# restart
|
|
INSERT INTO t1 VALUES(0), (0);
|
|
SELECT a AS `Expect 110, 111` FROM t1 ORDER BY a DESC LIMIT 2;
|
|
Expect 110, 111
|
|
111
|
|
110
|
|
INSERT INTO t3 VALUES(0), (0);
|
|
SELECT a AS `Expect 6010, 6011` FROM t3 ORDER BY a DESC LIMIT 2;
|
|
Expect 6010, 6011
|
|
6011
|
|
6010
|
|
INSERT INTO t5 VALUES(0), (0);
|
|
SELECT a AS `Expect 1100111, 1100110` FROM t5 ORDER BY a DESC LIMIT 2;
|
|
Expect 1100111, 1100110
|
|
1000111
|
|
1000110
|
|
INSERT INTO t7 VALUES(0), (0);
|
|
SELECT a AS `Expect 100000111, 100000110` FROM t7 ORDER BY a DESC LIMIT 2;
|
|
Expect 100000111, 100000110
|
|
100000111
|
|
100000110
|
|
INSERT INTO t9 VALUES(0), (0);
|
|
SELECT a AS `Expect 100000000111, 100000000110` FROM t9 ORDER BY a DESC LIMIT 2;
|
|
Expect 100000000111, 100000000110
|
|
100000000111
|
|
100000000110
|
|
# Scenario 5: Test kill the server
|
|
INSERT INTO t1 VALUES(125);
|
|
DELETE FROM t1 WHERE a = 125;
|
|
INSERT INTO t3 VALUES(6100);
|
|
DELETE FROM t3 WHERE a = 6100;
|
|
INSERT INTO t5 VALUES(1100200);
|
|
DELETE FROM t5 WHERE a = 1100200;
|
|
INSERT INTO t7 VALUES(100000200);
|
|
DELETE FROM t7 WHERE a = 100000200;
|
|
# Ensure that all changes before the server is killed are persisted.
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
INSERT INTO t9 VALUES(100000000200);
|
|
DELETE FROM t9 WHERE a = 100000000200;
|
|
# restart
|
|
INSERT INTO t1 VALUES(0);
|
|
SELECT a AS `Expect 126` FROM t1 ORDER BY a DESC LIMIT 1;
|
|
Expect 126
|
|
126
|
|
INSERT INTO t3 VALUES(0);
|
|
SELECT a AS `Expect 6101` FROM t3 ORDER BY a DESC LIMIT 1;
|
|
Expect 6101
|
|
6101
|
|
INSERT INTO t5 VALUES(0);
|
|
SELECT a AS `Expect 1100201` FROM t5 ORDER BY a DESC LIMIT 1;
|
|
Expect 1100201
|
|
1100201
|
|
INSERT INTO t7 VALUES(0);
|
|
SELECT a AS `Expect 100000201` FROM t7 ORDER BY a DESC LIMIT 1;
|
|
Expect 100000201
|
|
100000201
|
|
INSERT INTO t9 VALUES(0);
|
|
SELECT a AS `Expect 100000000201` FROM t9 ORDER BY a DESC LIMIT 1;
|
|
Expect 100000000201
|
|
100000000201
|
|
# Scenario 6: Test truncate will reset the counters to 0
|
|
TRUNCATE TABLE t1;
|
|
TRUNCATE TABLE t3;
|
|
TRUNCATE TABLE t5;
|
|
TRUNCATE TABLE t7;
|
|
TRUNCATE TABLE t9;
|
|
INSERT INTO t1 VALUES(0), (0);
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
2
|
|
INSERT INTO t3 VALUES(0), (0);
|
|
SELECT * FROM t3;
|
|
a
|
|
1
|
|
2
|
|
INSERT INTO t5 VALUES(0), (0);
|
|
SELECT * FROM t5;
|
|
a
|
|
1
|
|
2
|
|
INSERT INTO t7 VALUES(0), (0);
|
|
SELECT * FROM t7;
|
|
a
|
|
1
|
|
2
|
|
INSERT INTO t9 VALUES(0), (0);
|
|
SELECT * FROM t9;
|
|
a
|
|
1
|
|
2
|
|
# Ensure that all changes before the server is killed are persisted.
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
TRUNCATE TABLE t1;
|
|
TRUNCATE TABLE t3;
|
|
TRUNCATE TABLE t5;
|
|
TRUNCATE TABLE t7;
|
|
TRUNCATE TABLE t9;
|
|
# Scenario 7: Test explicit rename table won't change the counter
|
|
RENAME TABLE t9 to t19;
|
|
INSERT INTO t19 VALUES(0), (0);
|
|
SELECT * FROM t19;
|
|
a
|
|
1
|
|
2
|
|
# restart
|
|
INSERT INTO t1 VALUES(0), (0);
|
|
SELECT * FROM t1;
|
|
a
|
|
1
|
|
2
|
|
INSERT INTO t3 VALUES(0), (0);
|
|
SELECT * FROM t3;
|
|
a
|
|
1
|
|
2
|
|
INSERT INTO t5 VALUES(0), (0);
|
|
SELECT * FROM t5;
|
|
a
|
|
1
|
|
2
|
|
INSERT INTO t7 VALUES(0), (0);
|
|
SELECT * FROM t7;
|
|
a
|
|
1
|
|
2
|
|
INSERT INTO t19 VALUES(0), (0);
|
|
SELECT * FROM t19;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
DELETE FROM t19 WHERE a = 4;
|
|
RENAME TABLE t19 to t9;
|
|
INSERT INTO t9 VALUES(0), (0);
|
|
SELECT * FROM t9;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
5
|
|
6
|
|
TRUNCATE TABLE t9;
|
|
INSERT INTO t9 VALUES(0), (0);
|
|
SELECT * FROM t9;
|
|
a
|
|
1
|
|
2
|
|
# Scenario 8: Test ALTER TABLE operations
|
|
INSERT INTO t3 VALUES(0), (0), (100), (200), (1000);
|
|
SELECT * FROM t3;
|
|
a
|
|
1
|
|
2
|
|
3
|
|
4
|
|
100
|
|
200
|
|
1000
|
|
DELETE FROM t3 WHERE a > 300;
|
|
SELECT MAX(a) AS `Expect 200` FROM t3;
|
|
Expect 200
|
|
200
|
|
# This will not change the counter to 150, but to 201, which is the next
|
|
# of current max counter in the table
|
|
ALTER TABLE t3 AUTO_INCREMENT = 150;
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t3 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 201` FROM t3;
|
|
Expect 201
|
|
201
|
|
# This will change the counter to 500, which is bigger than any counter
|
|
# in the table
|
|
ALTER TABLE t3 AUTO_INCREMENT = 500;
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=500 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t3 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 500` FROM t3;
|
|
Expect 500
|
|
500
|
|
TRUNCATE TABLE t3;
|
|
ALTER TABLE t3 AUTO_INCREMENT = 100;
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t3 VALUES(0), (0);
|
|
SELECT * FROM t3;
|
|
a
|
|
100
|
|
101
|
|
INSERT INTO t3 VALUES(150), (180);
|
|
UPDATE t3 SET a = 200 WHERE a = 150;
|
|
INSERT INTO t3 VALUES(220);
|
|
# This still fails to set to 120, but just 221
|
|
ALTER TABLE t3 AUTO_INCREMENT = 120;
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=221 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t3 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 221` FROM t3;
|
|
Expect 221
|
|
221
|
|
DELETE FROM t3 WHERE a > 120;
|
|
ALTER TABLE t3 AUTO_INCREMENT = 120;
|
|
SHOW CREATE TABLE t3;
|
|
Table Create Table
|
|
t3 CREATE TABLE `t3` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
# MDEV-6076: Test adding an AUTO_INCREMENT COLUMN
|
|
CREATE TABLE mdev6076a (b INT) ENGINE=InnoDB;
|
|
INSERT INTO mdev6076a VALUES(2),(1);
|
|
CREATE TABLE mdev6076b (b INT) ENGINE=InnoDB;
|
|
INSERT INTO mdev6076b VALUES(2),(1);
|
|
ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE, LOCK=NONE;
|
|
ERROR 0A000: LOCK=NONE is not supported. Reason: Adding an auto-increment column requires a lock. Try LOCK=SHARED
|
|
ALTER TABLE mdev6076a ADD COLUMN a SERIAL FIRST, ALGORITHM=INPLACE;
|
|
ALTER TABLE mdev6076b ADD COLUMN a SERIAL FIRST, AUTO_INCREMENT=100,
|
|
ALGORITHM=INPLACE;
|
|
# MDEV-6076: Test root page split and page_create_empty()
|
|
CREATE TABLE mdev6076empty (b SERIAL, pad CHAR(255) NOT NULL DEFAULT '')
|
|
ENGINE=InnoDB;
|
|
BEGIN;
|
|
# Insert records in descending order of AUTO_INCREMENT,
|
|
# causing a page split on the very last insert.
|
|
# Without the fix in btr_page_empty() this would lose the counter value.
|
|
# Without the fix in page_create_empty() the counter value would be lost
|
|
# when ROLLBACK deletes the last row.
|
|
ROLLBACK;
|
|
# restart
|
|
INSERT INTO t3 VALUES(0);
|
|
SELECT MAX(a) AS `Expect 120` FROM t3;
|
|
Expect 120
|
|
120
|
|
INSERT INTO mdev6076a SET b=NULL;
|
|
SELECT * FROM mdev6076a;
|
|
a b
|
|
1 2
|
|
2 1
|
|
3 NULL
|
|
INSERT INTO mdev6076b SET b=NULL;
|
|
SELECT * FROM mdev6076b;
|
|
a b
|
|
100 2
|
|
101 1
|
|
102 NULL
|
|
INSERT INTO mdev6076empty SET b=NULL;
|
|
SELECT * FROM mdev6076empty;
|
|
b pad
|
|
56
|
|
DROP TABLE mdev6076a, mdev6076b, mdev6076empty;
|
|
INSERT INTO t3 VALUES(0), (0), (200), (210);
|
|
# Test the different algorithms in ALTER TABLE
|
|
CREATE TABLE t_inplace LIKE t3;
|
|
INSERT INTO t_inplace SELECT * FROM t3;
|
|
SELECT * FROM t_inplace;
|
|
a
|
|
100
|
|
101
|
|
120
|
|
121
|
|
122
|
|
200
|
|
210
|
|
SHOW CREATE TABLE t_inplace;
|
|
Table Create Table
|
|
t_inplace CREATE TABLE `t_inplace` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
# This will keep the autoinc counter
|
|
ALTER TABLE t_inplace AUTO_INCREMENT = 250, ALGORITHM = INPLACE;
|
|
# We expect the counter to be 250
|
|
SHOW CREATE TABLE t_inplace;
|
|
Table Create Table
|
|
t_inplace CREATE TABLE `t_inplace` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
# This should keep the autoinc counter as well
|
|
ALTER TABLE t_inplace ADD COLUMN b INT, ALGORITHM = INPLACE;
|
|
# We expect the counter to be 250
|
|
SHOW CREATE TABLE t_inplace;
|
|
Table Create Table
|
|
t_inplace CREATE TABLE `t_inplace` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
DELETE FROM t_inplace WHERE a > 150;
|
|
SELECT * FROM t_inplace;
|
|
a b
|
|
100 NULL
|
|
101 NULL
|
|
120 NULL
|
|
121 NULL
|
|
122 NULL
|
|
# This should reset the autoinc counter to the one specified
|
|
# Since it's smaller than current one but bigger than existing
|
|
# biggest counter in the table
|
|
ALTER TABLE t_inplace AUTO_INCREMENT = 180, ALGORITHM = INPLACE;
|
|
# We expect the counter to be 180
|
|
SHOW CREATE TABLE t_inplace;
|
|
Table Create Table
|
|
t_inplace CREATE TABLE `t_inplace` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
# This should reset the autoinc counter to the next value of
|
|
# current max counter in the table, since the specified value
|
|
# is smaller than the existing biggest value(50 < 123)
|
|
ALTER TABLE t_inplace DROP COLUMN b, AUTO_INCREMENT = 50, ALGORITHM = INPLACE;
|
|
# We expect the counter to be 123
|
|
SHOW CREATE TABLE t_inplace;
|
|
Table Create Table
|
|
t_inplace CREATE TABLE `t_inplace` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t_inplace VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 124` FROM t_inplace;
|
|
Expect 124
|
|
124
|
|
OPTIMIZE TABLE t_inplace;
|
|
Table Op Msg_type Msg_text
|
|
test.t_inplace optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t_inplace optimize status OK
|
|
DELETE FROM t_inplace WHERE a >= 123;
|
|
CREATE TABLE it_inplace(a INT AUTO_INCREMENT, INDEX(a)) AUTO_INCREMENT=125 ENGINE=InnoDB;
|
|
CREATE UNIQUE INDEX idx_aa ON it_inplace(a);
|
|
# restart
|
|
INSERT INTO t_inplace VALUES(0), (0);
|
|
INSERT INTO it_inplace VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 126` FROM t_inplace;
|
|
Expect 126
|
|
126
|
|
SELECT MAX(a) AS `Expect 126` FROM it_inplace;
|
|
Expect 126
|
|
126
|
|
DROP TABLE t_inplace, it_inplace;
|
|
CREATE TABLE t_copy LIKE t3;
|
|
INSERT INTO t_copy SELECT * FROM t3;
|
|
SELECT * FROM t_copy;
|
|
a
|
|
100
|
|
101
|
|
120
|
|
121
|
|
122
|
|
200
|
|
210
|
|
SHOW CREATE TABLE t_copy;
|
|
Table Create Table
|
|
t_copy CREATE TABLE `t_copy` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
# This will keep the autoinc counter
|
|
ALTER TABLE t_copy AUTO_INCREMENT = 250, ALGORITHM = COPY;
|
|
# We expect the counter to be 250
|
|
SHOW CREATE TABLE t_copy;
|
|
Table Create Table
|
|
t_copy CREATE TABLE `t_copy` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
# This should keep the autoinc counter as well
|
|
ALTER TABLE t_copy ADD COLUMN b INT, ALGORITHM = COPY;
|
|
# We expect the counter to be 250
|
|
SHOW CREATE TABLE t_copy;
|
|
Table Create Table
|
|
t_copy CREATE TABLE `t_copy` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=250 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
DELETE FROM t_copy WHERE a > 150;
|
|
SELECT * FROM t_copy;
|
|
a b
|
|
100 NULL
|
|
101 NULL
|
|
120 NULL
|
|
121 NULL
|
|
122 NULL
|
|
# This should reset the autoinc counter to the one specified
|
|
# Since it's smaller than current one but bigger than existing
|
|
# biggest counter in the table
|
|
ALTER TABLE t_copy AUTO_INCREMENT = 180, ALGORITHM = COPY;
|
|
# We expect the counter to be 180
|
|
SHOW CREATE TABLE t_copy;
|
|
Table Create Table
|
|
t_copy CREATE TABLE `t_copy` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=180 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
# This should reset the autoinc counter to the next value of
|
|
# current max counter in the table, since the specified value
|
|
# is smaller than the existing biggest value(50 < 123)
|
|
ALTER TABLE t_copy DROP COLUMN b, AUTO_INCREMENT = 50, ALGORITHM = COPY;
|
|
# We expect the counter to be 123
|
|
SHOW CREATE TABLE t_copy;
|
|
Table Create Table
|
|
t_copy CREATE TABLE `t_copy` (
|
|
`a` smallint(6) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=123 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
|
INSERT INTO t_copy VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 124` FROM t_copy;
|
|
Expect 124
|
|
124
|
|
OPTIMIZE TABLE t_copy;
|
|
Table Op Msg_type Msg_text
|
|
test.t_copy optimize note Table does not support optimize, doing recreate + analyze instead
|
|
test.t_copy optimize status OK
|
|
DELETE FROM t_copy WHERE a >= 123;
|
|
CREATE TABLE it_copy(a INT AUTO_INCREMENT, INDEX(a)) AUTO_INCREMENT=125 ENGINE=InnoDB;
|
|
CREATE UNIQUE INDEX idx_aa ON it_copy(a);
|
|
# restart
|
|
INSERT INTO t_copy VALUES(0), (0);
|
|
INSERT INTO it_copy VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 126` FROM t_copy;
|
|
Expect 126
|
|
126
|
|
SELECT MAX(a) AS `Expect 126` FROM it_copy;
|
|
Expect 126
|
|
126
|
|
DROP TABLE t_copy, it_copy;
|
|
# Scenario 9: Test the sql_mode = NO_AUTO_VALUE_ON_ZERO
|
|
CREATE TABLE t30 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, key(b)) ENGINE = InnoDB;
|
|
set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
|
|
INSERT INTO t30 VALUES(NULL, 1), (200, 2), (0, 3);
|
|
INSERT INTO t30(b) VALUES(4), (5), (6), (7);
|
|
SELECT * FROM t30 ORDER BY b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
0 3
|
|
201 4
|
|
202 5
|
|
203 6
|
|
204 7
|
|
ALTER TABLE t30 MODIFY b MEDIUMINT;
|
|
SELECT * FROM t30 ORDER BY b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
0 3
|
|
201 4
|
|
202 5
|
|
203 6
|
|
204 7
|
|
# Ensure that all changes before the server is killed are persisted.
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
CREATE TABLE t31 (a INT) ENGINE = InnoDB;
|
|
INSERT INTO t31 VALUES(1), (2);
|
|
ALTER TABLE t31 ADD b INT AUTO_INCREMENT PRIMARY KEY;
|
|
INSERT INTO t31 VALUES(3, 0), (4, NULL), (5, NULL);
|
|
INSERT INTO t31 VALUES(6, 0);
|
|
ERROR 23000: Duplicate entry '0' for key 'PRIMARY'
|
|
SELECT * FROM t31;
|
|
a b
|
|
3 0
|
|
1 1
|
|
2 2
|
|
4 3
|
|
5 4
|
|
SET SQL_MODE = 0;
|
|
# Scenario 10: Rollback would not rollback the counter
|
|
CREATE TABLE t32 (a BIGINT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
|
|
INSERT INTO t32 VALUES(0), (0);
|
|
# Ensure that all changes before the server is killed are persisted.
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
START TRANSACTION;
|
|
INSERT INTO t32 VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 4` FROM t32;
|
|
Expect 4
|
|
4
|
|
DELETE FROM t32 WHERE a >= 2;
|
|
ROLLBACK;
|
|
# Scenario 11: Test duplicate primary key/secondary key will not stop
|
|
# increasing the counter
|
|
CREATE TABLE t33 (
|
|
a BIGINT NOT NULL PRIMARY KEY,
|
|
b BIGINT NOT NULL AUTO_INCREMENT, KEY(b)) ENGINE = InnoDB;
|
|
INSERT INTO t33 VALUES(1, NULL);
|
|
INSERT INTO t33 VALUES(2, NULL);
|
|
INSERT INTO t33 VALUES(2, NULL);
|
|
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
|
|
INSERT INTO t33 VALUES(3, NULL);
|
|
SELECT MAX(b) AS `Expect 4` FROM t33;
|
|
Expect 4
|
|
4
|
|
TRUNCATE TABLE t33;
|
|
INSERT INTO t33 VALUES(1, NULL);
|
|
INSERT INTO t33 VALUES(2, NULL);
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
START TRANSACTION;
|
|
UPDATE t33 SET a = 10 WHERE a = 1;
|
|
INSERT INTO t33 VALUES(2, NULL);
|
|
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
|
|
COMMIT;
|
|
# restart
|
|
# This will not insert 0
|
|
INSERT INTO t31(a) VALUES(6), (0);
|
|
SELECT * FROM t31;
|
|
a b
|
|
3 0
|
|
1 1
|
|
2 2
|
|
4 3
|
|
5 4
|
|
6 5
|
|
0 6
|
|
DROP TABLE t31;
|
|
set SQL_MODE = NO_AUTO_VALUE_ON_ZERO;
|
|
DELETE FROM t30 WHERE a = 0;
|
|
UPDATE t30 set a = 0 where b = 5;
|
|
SELECT * FROM t30 ORDER BY b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
201 4
|
|
0 5
|
|
203 6
|
|
204 7
|
|
DELETE FROM t30 WHERE a = 0;
|
|
UPDATE t30 SET a = NULL WHERE b = 6;
|
|
Warnings:
|
|
Warning 1048 Column 'a' cannot be null
|
|
UPDATE t30 SET a = 300 WHERE b = 7;
|
|
SELECT * FROM t30 ORDER BY b;
|
|
a b
|
|
1 1
|
|
200 2
|
|
201 4
|
|
0 6
|
|
300 7
|
|
SET SQL_MODE = 0;
|
|
SELECT MAX(a) AS `Expect 2` FROM t32;
|
|
Expect 2
|
|
2
|
|
INSERT INTO t32 VALUES(0), (0);
|
|
SELECT MAX(a) AS `Expect 6` FROM t32;
|
|
Expect 6
|
|
6
|
|
FLUSH TABLES t33 FOR EXPORT;
|
|
backup: t33
|
|
UNLOCK TABLES;
|
|
DROP TABLE t33;
|
|
CREATE TABLE t33 (
|
|
a BIGINT NOT NULL PRIMARY KEY,
|
|
b BIGINT NOT NULL AUTO_INCREMENT, KEY(b)) ENGINE = InnoDB;
|
|
ALTER TABLE t33 DISCARD TABLESPACE;
|
|
restore: t33 .ibd and .cfg files
|
|
ALTER TABLE t33 IMPORT TABLESPACE;
|
|
INSERT INTO t33 VALUES(3, NULL);
|
|
SELECT MAX(b) AS `Expect 4` FROM t33;
|
|
Expect 4
|
|
4
|
|
SELECT * FROM t33;
|
|
a b
|
|
10 1
|
|
2 2
|
|
3 4
|
|
DROP TABLE t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t11u, t12u,
|
|
t30, t32, t33;
|
|
ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;
|