Debian Codegraph @

mysql-test/suite/innodb/t/innodb-table-online.test

raw
1 --source include/have_debug_sync.inc
2
3 let $innodb_metrics_select=
4 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE
5 subsystem = 'ddl' and name not in
6 ('ddl_sort_file_alter_table','ddl_log_file_alter_table') and count > 0;
7
8 # DISCARD TABLESPACE needs file-per-table
9 SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table;
10 SET GLOBAL innodb_file_per_table = on;
11
12 # Save the initial number of concurrent sessions.
13 --source include/count_sessions.inc
14
15 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 TEXT NOT NULL)
16 ENGINE = InnoDB;
17 INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,'');
18
19 SET GLOBAL innodb_monitor_enable = module_ddl;
20 eval $innodb_metrics_select;
21
22 SET DEBUG_SYNC = 'RESET';
23 SET DEBUG_SYNC = 'write_row_noreplace SIGNAL have_handle WAIT_FOR go_ahead';
24 --send
25 INSERT INTO t1 VALUES(1,2,3);
26
27 --echo # Establish session con1 (user=root)
28 connect (con1,localhost,root,,);
29 connection con1;
30
31 # This should block at the end because of the INSERT in connection default
32 # is holding a metadata lock.
33 SET DEBUG_SYNC = 'now WAIT_FOR have_handle';
34 SET lock_wait_timeout = 1;
35 --error ER_LOCK_WAIT_TIMEOUT
36 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
37 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
38
39 --echo # session default
40 connection default;
41 --error ER_DUP_ENTRY
42 reap;
43 eval $innodb_metrics_select;
44
45 --echo # session con1
46 connection con1;
47 SET SESSION DEBUG = '+d,innodb_OOM_prepare_inplace_alter';
48 --error ER_OUT_OF_RESOURCES
49 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
50 SET SESSION DEBUG = '-d,innodb_OOM_prepare_inplace_alter';
51 SET SESSION DEBUG = '+d,innodb_OOM_inplace_alter';
52 --error ER_OUT_OF_RESOURCES
53 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
54 SET SESSION DEBUG = '-d,innodb_OOM_inplace_alter';
55 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
56
57 --echo # session default
58 connection default;
59 SHOW CREATE TABLE t1;
60 # Insert a duplicate entry (4) for the upcoming UNIQUE INDEX(c2).
61 BEGIN;
62 INSERT INTO t1 VALUES(7,4,2);
63
64 --echo # session con1
65 connection con1;
66 # This DEBUG_SYNC should not kick in yet, because the duplicate key will be
67 # detected before we get a chance to apply the online log.
68 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done';
69 # This will be a lock wait timeout on the meta-data lock,
70 # because the transaction inserting (7,4,2) is still active.
71 --error ER_LOCK_WAIT_TIMEOUT
72 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
73
74 --echo # session default
75 connection default;
76 COMMIT;
77
78 --echo # session con1
79 connection con1;
80 --error ER_DUP_ENTRY
81 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
82
83 --echo # session default
84 connection default;
85 DELETE FROM t1 WHERE c1 = 7;
86
87 --echo # session con1
88 connection con1;
89 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ROW_FORMAT=COMPACT,
90 LOCK = SHARED, ALGORITHM = INPLACE;
91
92 # The previous DEBUG_SYNC should be ignored, because an exclusive lock
93 # has been requested and the online log is not being allocated.
94 ALTER TABLE t1 ADD UNIQUE INDEX(c2),
95 LOCK = EXCLUSIVE, ALGORITHM = INPLACE;
96
97 SHOW CREATE TABLE t1;
98 # We do not support plain DROP_PK_INDEX without ADD_PK_INDEX.
99 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
100 ALTER TABLE t1 DROP INDEX c2, ALGORITHM = INPLACE;
101 SHOW CREATE TABLE t1;
102 # Now the previous DEBUG_SYNC should kick in.
103 --send
104 ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1);
105
106 --echo # session default
107 connection default;
108 SET DEBUG_SYNC = 'now WAIT_FOR scanned';
109 eval $innodb_metrics_select;
110
111 # Insert a duplicate entry (4) for the already started UNIQUE INDEX(c1).
112 BEGIN;
113 INSERT INTO t1 VALUES(4,7,2);
114 SET DEBUG_SYNC = 'now SIGNAL insert_done';
115
116 --echo # session con1
117 connection con1;
118 # Because the modification log will be applied in order and we did
119 # not roll back before the log apply, there will be a duplicate key
120 # error on the (4,7,2).
121 --error ER_DUP_ENTRY
122 reap;
123
124 --echo # session default
125 connection default;
126 ROLLBACK;
127
128 --echo # session con1
129 connection con1;
130 SHOW CREATE TABLE t1;
131 # Now, rebuild the table without any concurrent DML, while no duplicate exists.
132 --error ER_CANT_DROP_FIELD_OR_KEY
133 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ALGORITHM = INPLACE;
134 ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1), ALGORITHM = INPLACE;
135 eval $innodb_metrics_select;
136
137 --echo # session default
138 connection default;
139 --error ER_DUP_ENTRY
140 INSERT INTO t1 VALUES(6,3,1);
141 --error ER_DUP_ENTRY
142 INSERT INTO t1 VALUES(7,4,2);
143 DROP INDEX c2_2 ON t1;
144 BEGIN;
145 INSERT INTO t1 VALUES(7,4,2);
146 ROLLBACK;
147
148 --echo # session con1
149 connection con1;
150 let $ID= `SELECT @id := CONNECTION_ID()`;
151 --error ER_QUERY_INTERRUPTED
152 KILL QUERY @id;
153
154 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR kill_done';
155 --send
156 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
157
158 --echo # session default
159 connection default;
160 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt';
161 eval $innodb_metrics_select;
162 let $ignore= `SELECT @id := $ID`;
163 KILL QUERY @id;
164 SET DEBUG_SYNC = 'now SIGNAL kill_done';
165
166 --echo # session con1
167 connection con1;
168 --error ER_QUERY_INTERRUPTED
169 reap;
170 eval $innodb_metrics_select;
171
172 --echo # session default
173 connection default;
174 CHECK TABLE t1;
175 INSERT INTO t1 SELECT 5 + c1, c2, c3 FROM t1;
176 INSERT INTO t1 SELECT 10 + c1, c2, c3 FROM t1;
177 INSERT INTO t1 SELECT 20 + c1, c2, c3 FROM t1;
178 INSERT INTO t1 SELECT 40 + c1, c2, c3 FROM t1;
179 # Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
180 --replace_result 81 80
181 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
182 ANALYZE TABLE t1;
183
184 --echo # session con1
185 connection con1;
186 SHOW CREATE TABLE t1;
187
188 # Exceed the configured innodb_online_alter_log_max_size.
189 # The actual limit is a multiple of innodb_sort_buf_size,
190 # because that is the size of the in-memory log buffers.
191 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt2 WAIT_FOR dml2_done';
192 # Ensure that the ALTER TABLE will be executed even with some concurrent DML.
193 SET lock_wait_timeout = 10;
194 --send
195 ALTER TABLE t1 ROW_FORMAT=COMPACT, ALGORITHM = INPLACE;
196
197 # Generate some log (delete-mark, delete-unmark, insert etc.)
198 # while the index creation is blocked. Some of this may run
199 # in parallel with the clustered index scan.
200 --echo # session default
201 connection default;
202 INSERT INTO t1 SELECT 80 + c1, c2, c3 FROM t1;
203 INSERT INTO t1 SELECT 160 + c1, c2, c3 FROM t1;
204 UPDATE t1 SET c2 = c2 + 1;
205 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt2';
206 # At this point, the clustered index scan must have completed,
207 # but the modification log keeps accumulating due to the DEBUG_SYNC.
208 eval $innodb_metrics_select;
209 let $c= 3;
210 while ($c)
211 {
212 BEGIN;
213 DELETE FROM t1;
214 ROLLBACK;
215 UPDATE t1 SET c2 = c2 + 1;
216 BEGIN;
217 UPDATE t1 SET c2 = c2 + 1;
218 DELETE FROM t1;
219 ROLLBACK;
220 dec $c;
221 }
222 # Temporary table should exist until the DDL thread notices the overflow.
223 eval $innodb_metrics_select;
224
225 # Release con1.
226 SET DEBUG_SYNC = 'now SIGNAL dml2_done';
227
228 --echo # session con1
229 connection con1;
230 # If the following fails with the wrong error, it probably means that
231 # you should rerun with a larger mtr --debug-sync-timeout.
232 --error ER_INNODB_ONLINE_LOG_TOO_BIG
233 reap;
234 # The table should have been dropped from the data dictionary
235 # when the above error was noticed.
236 eval $innodb_metrics_select;
237
238 # Accumulate and apply some modification log.
239 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt3 WAIT_FOR dml3_done';
240 --error ER_MULTIPLE_PRI_KEY
241 ALTER TABLE t1 ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
242 --replace_regex /'\d'/'N'/
243 --error ER_DUP_ENTRY
244 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
245 --send
246 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c1,c22f,c4(5)),
247 CHANGE c2 c22f INT, CHANGE c3 c3 TEXT NULL, CHANGE c1 c1 INT AFTER c22f,
248 ADD COLUMN c4 VARCHAR(6) DEFAULT 'Online';
249
250 --echo # session default
251 connection default;
252 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt3';
253 # Generate some log (delete-mark, delete-unmark, insert etc.)
254 eval $innodb_metrics_select;
255 BEGIN;
256 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 240;
257 DELETE FROM t1 WHERE c1 > 320;
258 ROLLBACK;
259 BEGIN;
260 UPDATE t1 SET c2 = c2 + 1;
261 DELETE FROM t1;
262 ROLLBACK;
263 eval $innodb_metrics_select;
264 # Release con1.
265 SET DEBUG_SYNC = 'now SIGNAL dml3_done';
266
267 --echo # session con1
268 connection con1;
269 reap;
270 eval $innodb_metrics_select;
271 SELECT COUNT(c22f) FROM t1;
272 CHECK TABLE t1;
273
274 # Create a column prefix index.
275 --error ER_DUP_ENTRY
276 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5));
277 UPDATE t1 SET c3 = NULL WHERE c3 = '';
278 SET lock_wait_timeout = 1;
279 --error ER_MULTIPLE_PRI_KEY
280 ALTER TABLE t1 DROP COLUMN c22f, ADD PRIMARY KEY c3p5(c3(5));
281 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
282 # And adding a PRIMARY KEY will also add NOT NULL implicitly!
283 --error ER_INVALID_USE_OF_NULL
284 ALTER TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
285 ALGORITHM = INPLACE;
286
287 --error ER_INVALID_USE_OF_NULL
288 ALTER TABLE t1 MODIFY c3 TEXT NOT NULL;
289 UPDATE t1 SET c3=CONCAT(c1,REPEAT('foo',c1)) WHERE c3 IS NULL;
290
291 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created0 WAIT_FOR ins_done0';
292 # NULL -> NOT NULL only allowed INPLACE if strict sql_mode is on.
293 --send
294 ALTER TABLE t1 MODIFY c3 TEXT NOT NULL, DROP COLUMN c22f,
295 ADD COLUMN c5 CHAR(5) DEFAULT 'tired' FIRST;
296
297 --echo # session default
298 connection default;
299
300 SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created0';
301 BEGIN;
302 INSERT INTO t1 VALUES(347,33101,'Pikku kakkosen posti','YLETV2');
303 INSERT INTO t1 VALUES(33101,347,NULL,'');
304 SET DEBUG_SYNC = 'now SIGNAL ins_done0';
305
306 --echo # session con1
307 connection con1;
308 --error ER_INVALID_USE_OF_NULL
309 reap;
310
311 --echo # session default
312 connection default;
313 ROLLBACK;
314
315 --echo # session con1
316 connection con1;
317 ALTER TABLE t1 MODIFY c3 TEXT NOT NULL;
318 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created WAIT_FOR ins_done';
319 --send
320 ALTER TABLE t1 DROP PRIMARY KEY, DROP COLUMN c22f,
321 ADD COLUMN c6 VARCHAR(1000) DEFAULT
322 'I love tracking down hard-to-reproduce bugs.',
323 ADD PRIMARY KEY c3p5(c3(5), c6(2));
324
325 --echo # session default
326 connection default;
327 SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created';
328 SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL ins_done WAIT_FOR ddl_timed_out';
329 --error ER_BAD_NULL_ERROR
330 INSERT INTO t1 VALUES(347,33101,NULL,'');
331 --send
332 INSERT INTO t1 VALUES(347,33101,'Pikku kakkosen posti','');
333
334 --echo # session con1
335 connection con1;
336 --error ER_LOCK_WAIT_TIMEOUT
337 reap;
338 SET DEBUG_SYNC = 'now SIGNAL ddl_timed_out';
339 eval $innodb_metrics_select;
340
341 --echo # session default
342 connection default;
343 reap;
344 SELECT COUNT(*) FROM t1;
345 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
346 SELECT * FROM t1 LIMIT 10;
347
348 --echo # session con1
349 connection con1;
350 ALTER TABLE t1 DISCARD TABLESPACE;
351
352 --echo # session default
353 connection default;
354 SHOW CREATE TABLE t1;
355 DROP TABLE t1;
356
357 --echo #
358 --echo # Bug#18894337 ONLINE ALTER TABLE LOG APPLY FAILS TO SKIP LOCKING
359 --echo #
360
361 --echo # session con1
362 connection con1;
363 CREATE TABLE t1 (a SERIAL, c64 VARCHAR(64) UNIQUE) ENGINE=InnoDB;
364 INSERT INTO t1 VALUES(0, NULL);
365 SET DEBUG='+d,innodb_trx_duplicates';
366 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL t1_ddl WAIT_FOR t1_dml';
367 --send
368 ALTER TABLE t1 ENGINE=InnoDB;
369
370 --echo # session default
371 connection default;
372
373 SET DEBUG_SYNC='now WAIT_FOR t1_ddl';
374 INSERT INTO t1 VALUES(0, NULL), (0, NULL);
375 SET DEBUG_SYNC='now SIGNAL t1_dml';
376
377 --echo # session con1
378 connection con1;
379 reap;
380 --echo # Disconnect session con1
381 disconnect con1;
382 --echo # session default
383 connection default;
384 DROP TABLE t1;
385
386 SET DEBUG_SYNC = 'RESET';
387 SET DEBUG = '';
388 SET GLOBAL innodb_monitor_disable = module_ddl;
389
390 # Check that all connections opened by test cases in this file are really
391 # gone so execution of other tests won't be affected by their presence.
392 --source include/wait_until_count_sessions.inc
393
394 SET GLOBAL DEBUG = '';
395 SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig;
396 --disable_warnings
397 SET GLOBAL innodb_monitor_enable = default;
398 SET GLOBAL innodb_monitor_disable = default;
399 --enable_warnings