Debian Codegraph @

mysql-test/suite/innodb/r/autoinc_persist_debug.result

raw
1 # Pre-create some tables, so that DDTableBuffer is not empty
2 set global innodb_flush_log_at_trx_commit=1;
3 CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
4 INSERT INTO t1 VALUES(0), (0), (0), (0), (-1), (-10), (0),
5 (20), (30), (31);
6 SELECT * FROM t1;
7 a
8 -10
9 -1
10 1
11 2
12 3
13 4
14 5
15 20
16 30
17 31
18 CREATE TABLE t2(a TINYINT UNSIGNED AUTO_INCREMENT KEY) ENGINE = InnoDB;
19 INSERT INTO t2 VALUES(-5);
20 ERROR 22003: Out of range value for column 'a' at row 1
21 INSERT INTO t2 VALUES(0), (0), (0), (0), (8), (10), (0),
22 (20), (30), (31);
23 SELECT * FROM t2;
24 a
25 1
26 2
27 3
28 4
29 8
30 10
31 11
32 20
33 30
34 31
35 CREATE TABLE t3(a SMALLINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
36 INSERT INTO t3 VALUES(0), (0), (0), (0), (-1), (-10), (0),
37 (20), (30), (31), (1024), (4096);
38 SELECT * FROM t3;
39 a
40 -10
41 -1
42 1
43 2
44 3
45 4
46 5
47 20
48 30
49 31
50 1024
51 4096
52 # restart
53 set global innodb_flush_log_at_trx_commit=1;
54 # Scenario 1: Create two new tables, with simple DMLs on them,
55 # and force a checkpoint, then some other DMLs on them
56 CREATE TABLE t4(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, KEY b(b)) ENGINE = InnoDB;
57 CREATE TABLE t5(a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b INT, KEY b(b)) ENGINE = InnoDB;
58 SET @start_global_value = @@global.innodb_log_checkpoint_now;
59 SELECT @start_global_value;
60 @start_global_value
61 0
62 # Invoke a checkpoint, which would persist the autoinc counters
63 # of t4 and t5 into DD Table Buffer
64 set global innodb_log_checkpoint_now=ON;
65 INSERT INTO t4 VALUES(0, 1), (0, 2), (10, 3), (12, 4), (0, 5);
66 SELECT * FROM t4;
67 a b
68 1 1
69 2 2
70 10 3
71 12 4
72 13 5
73 SHOW CREATE TABLE t4;
74 Table Create Table
75 t4 CREATE TABLE `t4` (
76 `a` int NOT NULL AUTO_INCREMENT,
77 `b` int DEFAULT NULL,
78 PRIMARY KEY (`a`),
79 KEY `b` (`b`)
80 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
81 INSERT INTO t5 VALUES(0, 1), (100, 2), (50, 3);
82 INSERT INTO t5 VALUES(0, 4), (109, 5), (0, 6);
83 SELECT * FROM t5;
84 a b
85 1 1
86 100 2
87 50 3
88 101 4
89 109 5
90 110 6
91 SHOW CREATE TABLE t5;
92 Table Create Table
93 t5 CREATE TABLE `t5` (
94 `a` int NOT NULL AUTO_INCREMENT,
95 `b` int DEFAULT NULL,
96 PRIMARY KEY (`a`),
97 KEY `b` (`b`)
98 ) ENGINE=InnoDB AUTO_INCREMENT=111 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
99 set global innodb_log_checkpoint_now=ON;
100 SELECT @start_global_value;
101 @start_global_value
102 0
103 SET @@global.innodb_log_checkpoint_now = @start_global_value;
104 SELECT @@global.innodb_log_checkpoint_now;
105 @@global.innodb_log_checkpoint_now
106 0
107 START TRANSACTION;
108 # This should insert 14, 15
109 INSERT INTO t4 VALUES(0, 10), (0, 20);
110 # This should insert 111, 112
111 INSERT INTO t5 VALUES(0, 10), (0, 20);
112 COMMIT;
113 START TRANSACTION;
114 # This should insert 16, 17
115 INSERT INTO t4 VALUES(0, 10), (0, 20);
116 # This should insert 113, 114
117 INSERT INTO t5 VALUES(0, 10), (0, 20);
118 # This will not rollback the counter
119 ROLLBACK;
120 # Kill and restart
121 SET GLOBAL innodb_flush_log_at_trx_commit=1;
122 SELECT MAX(a) AS `Expect 15` FROM t4;
123 Expect 15
124 15
125 SELECT MAX(a) AS `Expect 112` FROM t5;
126 Expect 112
127 112
128 SHOW CREATE TABLE t4;
129 Table Create Table
130 t4 CREATE TABLE `t4` (
131 `a` int NOT NULL AUTO_INCREMENT,
132 `b` int DEFAULT NULL,
133 PRIMARY KEY (`a`),
134 KEY `b` (`b`)
135 ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
136 SHOW CREATE TABLE t5;
137 Table Create Table
138 t5 CREATE TABLE `t5` (
139 `a` int NOT NULL AUTO_INCREMENT,
140 `b` int DEFAULT NULL,
141 PRIMARY KEY (`a`),
142 KEY `b` (`b`)
143 ) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
144 # Scenario 2: Mix the persisted autoinc counter and corrupted bits
145 TRUNCATE TABLE t1;
146 TRUNCATE TABLE t2;
147 TRUNCATE TABLE t3;
148 set debug = "+d, dict_set_index_corrupted";
149 CHECK TABLE t4;
150 Table Op Msg_type Msg_text
151 test.t4 check Warning InnoDB: The B-tree of index b is corrupted.
152 test.t4 check error Corrupt
153 set debug = "-d, dict_set_index_corrupted";
154 SELECT b FROM t4 WHERE b > 3;
155 ERROR HY000: Index b is corrupted
156 # This should insert 18, 19
157 INSERT INTO t4 VALUES(0, 6), (0, 7);
158 SELECT MAX(a) AS `Expect 19` FROM t4;
159 Expect 19
160 19
161 INSERT INTO t1 VALUES(0), (0);
162 INSERT INTO t2 VALUES(0), (0);
163 INSERT INTO t3 VALUES(0), (0);
164 # This should insert 115, 116
165 INSERT INTO t5 VALUES(0, 10), (0, 11);
166 # Kill and restart
167 set global innodb_flush_log_at_trx_commit=1;
168 CHECK TABLE t4;
169 Table Op Msg_type Msg_text
170 test.t4 check Warning InnoDB: Index b is marked as corrupted
171 test.t4 check error Corrupt
172 SELECT b FROM t4 WHERE b > 3;
173 ERROR HY000: Index b is corrupted
174 # This should fix the corrupted index
175 DROP INDEX b ON t4;
176 CREATE INDEX b ON t4(b);
177 CHECK TABLE t4;
178 Table Op Msg_type Msg_text
179 test.t4 check status OK
180 INSERT INTO t4 VALUES(0, 8), (0, 9), (30, 10);
181 SET @start_global_value = @@global.innodb_log_checkpoint_now;
182 SELECT @start_global_value;
183 @start_global_value
184 0
185 set global innodb_log_checkpoint_now=ON;
186 SELECT MAX(a) AS `Expect 116` FROM t5;
187 Expect 116
188 116
189 DELETE FROM t5;
190 set debug = "+d, dict_set_index_corrupted";
191 CHECK TABLE t5;
192 Table Op Msg_type Msg_text
193 test.t5 check Warning InnoDB: The B-tree of index b is corrupted.
194 test.t5 check error Corrupt
195 set debug = "-d, dict_set_index_corrupted";
196 SELECT @start_global_value;
197 @start_global_value
198 0
199 SET @@global.innodb_log_checkpoint_now = @start_global_value;
200 SELECT @@global.innodb_log_checkpoint_now;
201 @@global.innodb_log_checkpoint_now
202 0
203 # This should insert 117, 118
204 INSERT INTO t5 VALUES(0, 10), (0, 20);
205 # Kill and restart
206 set global innodb_flush_log_at_trx_commit=1;
207 SELECT b FROM t5 WHERE b > 3;
208 ERROR HY000: Index b is corrupted
209 # This should fix the corrupted index
210 DROP INDEX b ON t5;
211 CREATE INDEX b ON t5(b);
212 CHECK TABLE t5;
213 Table Op Msg_type Msg_text
214 test.t5 check status OK
215 # This should insert 31, 32
216 INSERT INTO t4 VALUES(0, 11), (0, 12);
217 SELECT MAX(a) AS `Expect 32` FROM t4;
218 Expect 32
219 32
220 # restart
221 set global innodb_flush_log_at_trx_commit=1;
222 # This should insert 119, 120
223 INSERT INTO t5 VALUES(0, 12), (0, 13);
224 SELECT MAX(a) AS `Expect 120` FROM t5;
225 Expect 120
226 120
227 CREATE TABLE t6 (
228 id INT AUTO_INCREMENT PRIMARY KEY,
229 name VARCHAR(100)
230 ) ENGINE=InnoDB AUTO_INCREMENT=99;
231 SET GLOBAL debug="+d,innodb_evict_autoinc_table";
232 # Evict table from dictionary cache
233 SET GLOBAL innodb_ft_aux_table="test/t6";
234 ERROR 42000: Variable 'innodb_ft_aux_table' can't be set to the value of 'test/t6'
235 SET GLOBAL debug="-d,innodb_evict_autoinc_table";
236 INSERT INTO t6(name) VALUES('mysql');
237 SELECT * FROM t6;
238 id name
239 99 mysql
240 DROP TABLE t1, t2, t3, t4, t5, t6;
241 #
242 # Scenario 4: Test ALTER TABLE .. AUTO_INCREMENT to a smaller value
243 # and maybe crash
244 #
245 CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
246 INSERT INTO t1 VALUES(0), (0), (10), (20), (30), (31), (32);
247 INSERT INTO t1 VALUES(0), (0), (0);
248 SELECT MAX(a) `Expect 35` FROM t1;
249 Expect 35
250 35
251 DELETE FROM t1 WHERE a >= 30;
252 ALTER TABLE t1 AUTO_INCREMENT = 21;
253 # restart
254 SHOW CREATE TABLE t1;
255 Table Create Table
256 t1 CREATE TABLE `t1` (
257 `a` tinyint NOT NULL AUTO_INCREMENT,
258 PRIMARY KEY (`a`)
259 ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
260 INSERT INTO t1 VALUES(0), (0), (0);
261 SELECT MAX(a) `Expect 23` FROM t1;
262 Expect 23
263 23
264 DROP TABLE t1;
265 CREATE TABLE t1(a TINYINT AUTO_INCREMENT KEY) ENGINE = InnoDB;
266 INSERT INTO t1 VALUES(0), (0), (10), (20), (30), (31), (32);
267 INSERT INTO t1 VALUES(0), (0), (0);
268 SELECT MAX(a) `Expect 35` FROM t1;
269 Expect 35
270 35
271 DELETE FROM t1 WHERE a >= 30;
272 SET SESSION debug="+d, ib_ddl_crash_before_update_stats";
273 # Write file to make mysql-test-run.pl expect crash
274 ALTER TABLE t1 AUTO_INCREMENT = 21;
275 ERROR HY000: Lost connection to MySQL server during query
276 # Restart mysqld after the crash and reconnect.
277 SHOW CREATE TABLE t1;
278 Table Create Table
279 t1 CREATE TABLE `t1` (
280 `a` tinyint NOT NULL AUTO_INCREMENT,
281 PRIMARY KEY (`a`)
282 ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
283 INSERT INTO t1 VALUES(0), (0), (0);
284 SELECT MAX(a) `Expect 38` FROM t1;
285 Expect 38
286 38
287 DROP TABLE t1;
288 CREATE TABLE t1 (a INT AUTO_INCREMENT NOT NULL, KEY(a))
289 PARTITION BY RANGE (a)
290 ( PARTITION p0 VALUES LESS THAN (10),
291 PARTITION p1 VALUES LESS THAN (20),
292 PARTITION p2 VALUES LESS THAN (50));
293 INSERT INTO t1 VALUES(0), (0), (10), (20), (30), (31), (32);
294 INSERT INTO t1 VALUES(0), (0), (0);
295 SELECT MAX(a) `Expect 35` FROM t1;
296 Expect 35
297 35
298 DELETE FROM t1 WHERE a >= 30;
299 ALTER TABLE t1 AUTO_INCREMENT = 21;
300 # restart
301 SHOW CREATE TABLE t1;
302 Table Create Table
303 t1 CREATE TABLE `t1` (
304 `a` int NOT NULL AUTO_INCREMENT,
305 KEY `a` (`a`)
306 ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
307 /*!50100 PARTITION BY RANGE (`a`)
308 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
309 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
310 PARTITION p2 VALUES LESS THAN (50) ENGINE = InnoDB) */
311 INSERT INTO t1 VALUES(0), (0), (0);
312 SELECT MAX(a) `Expect 23` FROM t1;
313 Expect 23
314 23
315 DROP TABLE t1;
316 CREATE TABLE t1 (a INT AUTO_INCREMENT NOT NULL, KEY(a))
317 PARTITION BY RANGE (a)
318 ( PARTITION p0 VALUES LESS THAN (10),
319 PARTITION p1 VALUES LESS THAN (20),
320 PARTITION p2 VALUES LESS THAN (50));
321 INSERT INTO t1 VALUES(0), (0), (10), (20), (30), (31), (32);
322 INSERT INTO t1 VALUES(0), (0), (0);
323 SELECT MAX(a) `Expect 35` FROM t1;
324 Expect 35
325 35
326 DELETE FROM t1 WHERE a >= 30;
327 SET SESSION debug="+d, ib_ddl_crash_before_update_stats";
328 # Write file to make mysql-test-run.pl expect crash
329 ALTER TABLE t1 AUTO_INCREMENT = 21;
330 ERROR HY000: Lost connection to MySQL server during query
331 # Restart mysqld after the crash and reconnect.
332 SHOW CREATE TABLE t1;
333 Table Create Table
334 t1 CREATE TABLE `t1` (
335 `a` int NOT NULL AUTO_INCREMENT,
336 KEY `a` (`a`)
337 ) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
338 /*!50100 PARTITION BY RANGE (`a`)
339 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
340 PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB,
341 PARTITION p2 VALUES LESS THAN (50) ENGINE = InnoDB) */
342 INSERT INTO t1 VALUES(0), (0), (0);
343 SELECT MAX(a) `Expect 38` FROM t1;
344 Expect 38
345 38
346 DROP TABLE t1;
347 #
348 # BUG#31763837: DEBUG ASSERT IS HIT IN DD DURING TABLE TRUNCATE
349 #
350 CREATE TABLE t1(col1 INT AUTO_INCREMENT PRIMARY KEY);
351 INSERT INTO t1 VALUES (DEFAULT), (DEFAULT), (DEFAULT);
352 SELECT * FROM t1 ORDER BY col1;
353 col1
354 1
355 2
356 3
357 # Stop mysqld and trigger failure during boot.
358 # Kill the server
359 # Start server normally.
360 # restart:
361 SELECT * FROM t1 ORDER BY col1;
362 col1
363 1
364 2
365 3
366 INSERT INTO t1 VALUES (DEFAULT);
367 SELECT * FROM t1 ORDER BY col1;
368 col1
369 1
370 2
371 3
372 4
373 ALTER TABLE t1 AUTO_INCREMENT = 4;
374 INSERT INTO t1 VALUES (DEFAULT);
375 SELECT * FROM t1 ORDER BY col1;
376 col1
377 1
378 2
379 3
380 4
381 5
382 TRUNCATE TABLE t1;
383 DROP TABLE t1;