347 lines
9.6 KiB
Plaintext
347 lines
9.6 KiB
Plaintext
# Pre-create some tables, so that DDTableBuffer is not empty
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
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
|
|
# restart
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
# Scenario 1: Create two new tables, with simple DMLs on them,
|
|
# and force a checkpoint, then some other DMLs on them
|
|
CREATE TABLE t4(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, KEY b(b)) ENGINE = InnoDB;
|
|
CREATE TABLE t5(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, KEY b(b)) ENGINE = InnoDB;
|
|
SET @start_global_value = @@global.innodb_log_checkpoint_now;
|
|
SELECT @start_global_value;
|
|
@start_global_value
|
|
0
|
|
# Invoke a checkpoint, which would persist the autoinc counters
|
|
# of t4 and t5 into DD Table Buffer
|
|
set global innodb_log_checkpoint_now=ON;
|
|
INSERT INTO t4 VALUES(0, 1), (0, 2), (10, 3), (12, 4), (0, 5);
|
|
SELECT * FROM t4;
|
|
a b
|
|
1 1
|
|
2 2
|
|
10 3
|
|
12 4
|
|
13 5
|
|
SHOW CREATE TABLE t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
INSERT INTO t5 VALUES(0, 1), (100, 2), (50, 3);
|
|
INSERT INTO t5 VALUES(0, 4), (109, 5), (0, 6);
|
|
SELECT * FROM t5;
|
|
a b
|
|
1 1
|
|
100 2
|
|
50 3
|
|
101 4
|
|
109 5
|
|
110 6
|
|
SHOW CREATE TABLE t5;
|
|
Table Create Table
|
|
t5 CREATE TABLE `t5` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
set global innodb_log_checkpoint_now=ON;
|
|
SELECT @start_global_value;
|
|
@start_global_value
|
|
0
|
|
SET @@global.innodb_log_checkpoint_now = @start_global_value;
|
|
SELECT @@global.innodb_log_checkpoint_now;
|
|
@@global.innodb_log_checkpoint_now
|
|
0
|
|
START TRANSACTION;
|
|
# This should insert 14, 15
|
|
INSERT INTO t4 VALUES(0, 10), (0, 20);
|
|
# This should insert 111, 112
|
|
INSERT INTO t5 VALUES(0, 10), (0, 20);
|
|
COMMIT;
|
|
START TRANSACTION;
|
|
# This should insert 16, 17
|
|
INSERT INTO t4 VALUES(0, 10), (0, 20);
|
|
# This should insert 113, 114
|
|
INSERT INTO t5 VALUES(0, 10), (0, 20);
|
|
# This will not rollback the counter
|
|
ROLLBACK;
|
|
# Kill and restart
|
|
SET GLOBAL innodb_flush_log_at_trx_commit=1;
|
|
SELECT MAX(a) AS `Expect 15` FROM t4;
|
|
Expect 15
|
|
15
|
|
SELECT MAX(a) AS `Expect 112` FROM t5;
|
|
Expect 112
|
|
112
|
|
SHOW CREATE TABLE t4;
|
|
Table Create Table
|
|
t4 CREATE TABLE `t4` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
SHOW CREATE TABLE t5;
|
|
Table Create Table
|
|
t5 CREATE TABLE `t5` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
`b` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`a`),
|
|
KEY `b` (`b`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
# Scenario 2: Mix the persisted autoinc counter and corrupted bits
|
|
TRUNCATE TABLE t1;
|
|
TRUNCATE TABLE t2;
|
|
TRUNCATE TABLE t3;
|
|
set debug = "+d, dict_set_index_corrupted";
|
|
CHECK TABLE t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t4 check Warning InnoDB: The B-tree of index b is corrupted.
|
|
test.t4 check error Corrupt
|
|
set debug = "-d, dict_set_index_corrupted";
|
|
SELECT b FROM t4 WHERE b > 3;
|
|
ERROR HY000: Index b is corrupted
|
|
# This should insert 18, 19
|
|
INSERT INTO t4 VALUES(0, 6), (0, 7);
|
|
SELECT MAX(a) AS `Expect 19` FROM t4;
|
|
Expect 19
|
|
19
|
|
INSERT INTO t1 VALUES(0), (0);
|
|
INSERT INTO t2 VALUES(0), (0);
|
|
INSERT INTO t3 VALUES(0), (0);
|
|
# This should insert 115, 116
|
|
INSERT INTO t5 VALUES(0, 10), (0, 11);
|
|
# Kill and restart
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
CHECK TABLE t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t4 check Warning InnoDB: Index b is marked as corrupted
|
|
test.t4 check error Corrupt
|
|
SELECT b FROM t4 WHERE b > 3;
|
|
ERROR HY000: Index b is corrupted
|
|
# This should fix the corrupted index
|
|
DROP INDEX b ON t4;
|
|
CREATE INDEX b ON t4(b);
|
|
CHECK TABLE t4;
|
|
Table Op Msg_type Msg_text
|
|
test.t4 check status OK
|
|
INSERT INTO t4 VALUES(0, 8), (0, 9), (30, 10);
|
|
SET @start_global_value = @@global.innodb_log_checkpoint_now;
|
|
SELECT @start_global_value;
|
|
@start_global_value
|
|
0
|
|
set global innodb_log_checkpoint_now=ON;
|
|
SELECT MAX(a) AS `Expect 116` FROM t5;
|
|
Expect 116
|
|
116
|
|
DELETE FROM t5;
|
|
set debug = "+d, dict_set_index_corrupted";
|
|
CHECK TABLE t5;
|
|
Table Op Msg_type Msg_text
|
|
test.t5 check Warning InnoDB: The B-tree of index b is corrupted.
|
|
test.t5 check error Corrupt
|
|
set debug = "-d, dict_set_index_corrupted";
|
|
SELECT @start_global_value;
|
|
@start_global_value
|
|
0
|
|
SET @@global.innodb_log_checkpoint_now = @start_global_value;
|
|
SELECT @@global.innodb_log_checkpoint_now;
|
|
@@global.innodb_log_checkpoint_now
|
|
0
|
|
# This should insert 117, 118
|
|
INSERT INTO t5 VALUES(0, 10), (0, 20);
|
|
# Kill and restart
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
SELECT b FROM t5 WHERE b > 3;
|
|
ERROR HY000: Index b is corrupted
|
|
# This should fix the corrupted index
|
|
DROP INDEX b ON t5;
|
|
CREATE INDEX b ON t5(b);
|
|
CHECK TABLE t5;
|
|
Table Op Msg_type Msg_text
|
|
test.t5 check status OK
|
|
# This should insert 31, 32
|
|
INSERT INTO t4 VALUES(0, 11), (0, 12);
|
|
SELECT MAX(a) AS `Expect 32` FROM t4;
|
|
Expect 32
|
|
32
|
|
# restart
|
|
set global innodb_flush_log_at_trx_commit=1;
|
|
# This should insert 119, 120
|
|
INSERT INTO t5 VALUES(0, 12), (0, 13);
|
|
SELECT MAX(a) AS `Expect 120` FROM t5;
|
|
Expect 120
|
|
120
|
|
CREATE TABLE t6 (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=99;
|
|
SET GLOBAL debug="+d,innodb_evict_autoinc_table";
|
|
# Evict table from dictionary cache
|
|
SET GLOBAL innodb_ft_aux_table="test/t6";
|
|
ERROR 42000: Variable 'innodb_ft_aux_table' can't be set to the value of 'test/t6'
|
|
SET GLOBAL debug="-d,innodb_evict_autoinc_table";
|
|
INSERT INTO t6(name) VALUES('mysql');
|
|
SELECT * FROM t6;
|
|
id name
|
|
99 mysql
|
|
DROP TABLE t1, t2, t3, t4, t5, t6;
|
|
#
|
|
# Scenario 4: Test ALTER TABLE .. AUTO_INCREMENT to a smaller value
|
|
# and maybe crash
|
|
#
|
|
CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t1 VALUES(0), (0), (10), (20), (30), (31), (32);
|
|
INSERT INTO t1 VALUES(0), (0), (0);
|
|
SELECT MAX(a) `Expect 35` FROM t1;
|
|
Expect 35
|
|
35
|
|
DELETE FROM t1 WHERE a >= 30;
|
|
ALTER TABLE t1 AUTO_INCREMENT = 21;
|
|
# restart
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` tinyint(4) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
INSERT INTO t1 VALUES(0), (0), (0);
|
|
SELECT MAX(a) `Expect 23` FROM t1;
|
|
Expect 23
|
|
23
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
|
|
INSERT INTO t1 VALUES(0), (0), (10), (20), (30), (31), (32);
|
|
INSERT INTO t1 VALUES(0), (0), (0);
|
|
SELECT MAX(a) `Expect 35` FROM t1;
|
|
Expect 35
|
|
35
|
|
DELETE FROM t1 WHERE a >= 30;
|
|
SET SESSION debug="+d, ib_ddl_crash_before_update_stats";
|
|
# Write file to make mysql-test-run.pl expect crash
|
|
ALTER TABLE t1 AUTO_INCREMENT = 21;
|
|
ERROR HY000: Lost connection to MySQL server during query
|
|
# Restart mysqld after the crash and reconnect.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` tinyint(4) NOT NULL AUTO_INCREMENT,
|
|
PRIMARY KEY (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
INSERT INTO t1 VALUES(0), (0), (0);
|
|
SELECT MAX(a) `Expect 38` FROM t1;
|
|
Expect 38
|
|
38
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT AUTO_INCREMENT NOT NULL, KEY(a))
|
|
PARTITION BY RANGE (a)
|
|
( PARTITION p0 VALUES LESS THAN (10),
|
|
PARTITION p1 VALUES LESS THAN (20),
|
|
PARTITION p2 VALUES LESS THAN (50));
|
|
INSERT INTO t1 VALUES(0), (0), (10), (20), (30), (31), (32);
|
|
INSERT INTO t1 VALUES(0), (0), (0);
|
|
SELECT MAX(a) `Expect 35` FROM t1;
|
|
Expect 35
|
|
35
|
|
DELETE FROM t1 WHERE a >= 30;
|
|
ALTER TABLE t1 AUTO_INCREMENT = 21;
|
|
# restart
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
KEY `a` (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (50) ENGINE = InnoDB) */
|
|
INSERT INTO t1 VALUES(0), (0), (0);
|
|
SELECT MAX(a) `Expect 23` FROM t1;
|
|
Expect 23
|
|
23
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (a INT AUTO_INCREMENT NOT NULL, KEY(a))
|
|
PARTITION BY RANGE (a)
|
|
( PARTITION p0 VALUES LESS THAN (10),
|
|
PARTITION p1 VALUES LESS THAN (20),
|
|
PARTITION p2 VALUES LESS THAN (50));
|
|
INSERT INTO t1 VALUES(0), (0), (10), (20), (30), (31), (32);
|
|
INSERT INTO t1 VALUES(0), (0), (0);
|
|
SELECT MAX(a) `Expect 35` FROM t1;
|
|
Expect 35
|
|
35
|
|
DELETE FROM t1 WHERE a >= 30;
|
|
SET SESSION debug="+d, ib_ddl_crash_before_update_stats";
|
|
# Write file to make mysql-test-run.pl expect crash
|
|
ALTER TABLE t1 AUTO_INCREMENT = 21;
|
|
ERROR HY000: Lost connection to MySQL server during query
|
|
# Restart mysqld after the crash and reconnect.
|
|
SHOW CREATE TABLE t1;
|
|
Table Create Table
|
|
t1 CREATE TABLE `t1` (
|
|
`a` int(11) NOT NULL AUTO_INCREMENT,
|
|
KEY `a` (`a`)
|
|
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
|
|
/*!50100 PARTITION BY RANGE (`a`)
|
|
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
|
|
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
|
|
PARTITION p2 VALUES LESS THAN (50) ENGINE = InnoDB) */
|
|
INSERT INTO t1 VALUES(0), (0), (0);
|
|
SELECT MAX(a) `Expect 38` FROM t1;
|
|
Expect 38
|
|
38
|
|
DROP TABLE t1;
|