Debian Codegraph @

mysql-test/suite/innodb/t/autoinc_persist_debug.test

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