Debian Codegraph @

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

raw
1 --source include/innodb_page_size_small.inc
2 --source include/innodb_encrypt_log.inc
3 --source include/have_debug.inc
4 --source include/have_debug_sync.inc
5 --source include/have_sequence.inc
6 --source include/test_db_charset_latin1.inc
7
8 --disable_query_log
9 call mtr.add_suppression("InnoDB: Cannot reset all values for monitor counter");
10 --enable_query_log
11
12 SET GLOBAL innodb_monitor_reset_all=all;
13 --disable_warnings
14 SET GLOBAL innodb_monitor_reset_all=default;
15 --enable_warnings
16 let $innodb_metrics_select=
17 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
18
19 call mtr.add_suppression("InnoDB: Warning: Small buffer pool size");
20 # these will be triggered by DISCARD TABLESPACE
21 call mtr.add_suppression("InnoDB: Error: table 'test/t1'");
22 call mtr.add_suppression("MariaDB is trying to open a table handle but the .ibd file for");
23
24 # Save the initial number of concurrent sessions.
25 --source include/count_sessions.inc
26
27 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT NOT NULL, c3 CHAR(255) NOT NULL)
28 ENGINE = InnoDB;
29 INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,'');
30
31 SET GLOBAL innodb_monitor_enable = module_ddl;
32 eval $innodb_metrics_select;
33
34 SET DEBUG_SYNC = 'RESET';
35 SET DEBUG_SYNC = 'write_row_noreplace SIGNAL have_handle WAIT_FOR go_ahead';
36 --send
37 INSERT INTO t1 VALUES(1,2,3);
38
39 connect (con1,localhost,root,,);
40
41 # This should block at the end because of the INSERT in connection default
42 # is holding a metadata lock.
43 SET DEBUG_SYNC = 'now WAIT_FOR have_handle';
44 SET lock_wait_timeout = 1;
45 --error ER_LOCK_WAIT_TIMEOUT
46 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
47 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
48
49 connection default;
50 --error ER_DUP_ENTRY
51 reap;
52 eval $innodb_metrics_select;
53
54 connection con1;
55 SET @saved_debug_dbug = @@SESSION.debug_dbug;
56 SET DEBUG_DBUG = '+d,innodb_OOM_prepare_inplace_alter';
57 --error ER_OUT_OF_RESOURCES
58 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
59 SET SESSION DEBUG_DBUG = @saved_debug_dbug;
60 SET SESSION DEBUG_DBUG = '+d,innodb_OOM_inplace_alter';
61 --error ER_OUT_OF_RESOURCES
62 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
63 SET SESSION DEBUG_DBUG = @saved_debug_dbug;
64 ALTER TABLE t1 ROW_FORMAT=REDUNDANT, ALGORITHM=INPLACE, LOCK=NONE;
65
66 connection default;
67 SHOW CREATE TABLE t1;
68 # Insert a duplicate entry (4) for the upcoming UNIQUE INDEX(c2).
69 BEGIN;
70 INSERT INTO t1 VALUES(7,4,2);
71
72 connection con1;
73 # This DEBUG_SYNC should not kick in yet, because the duplicate key will be
74 # detected before we get a chance to apply the online log.
75 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR insert_done';
76 # This will be a lock wait timeout on the meta-data lock,
77 # because the transaction inserting (7,4,2) is still active.
78 --error ER_LOCK_WAIT_TIMEOUT
79 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
80
81 connection default;
82 COMMIT;
83
84 connection con1;
85 --error ER_DUP_ENTRY
86 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2);
87
88 connection default;
89 DELETE FROM t1 WHERE c1 = 7;
90
91 connection con1;
92 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ROW_FORMAT=COMPACT,
93 LOCK = SHARED, ALGORITHM = INPLACE;
94
95 # The previous DEBUG_SYNC should be ignored, because an exclusive lock
96 # has been requested and the online log is not being allocated.
97 ALTER TABLE t1 ADD UNIQUE INDEX(c2),
98 LOCK = EXCLUSIVE, ALGORITHM = INPLACE;
99
100 SHOW CREATE TABLE t1;
101 # Now the previous DEBUG_SYNC should kick in.
102 --send
103 ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1);
104
105 connection default;
106 SET DEBUG_SYNC = 'now WAIT_FOR scanned';
107 eval $innodb_metrics_select;
108
109 # Insert a duplicate entry (4) for the already started UNIQUE INDEX(c1).
110 INSERT INTO t1 VALUES(4,7,2);
111 SET DEBUG_SYNC = 'now SIGNAL insert_done';
112
113 connection con1;
114 # Because the modification log will be applied in order and we did
115 # not roll back before the log apply, there will be a duplicate key
116 # error on the (4,7,2).
117 --error ER_DUP_ENTRY
118 reap;
119 DELETE FROM t1 WHERE c1=4 and c2=7;
120 connection default;
121 ROLLBACK;
122
123 connection con1;
124 SHOW CREATE TABLE t1;
125 # Now, rebuild the table without any concurrent DML, while no duplicate exists.
126 --error ER_CANT_DROP_FIELD_OR_KEY
127 ALTER TABLE t1 DROP PRIMARY KEY, ADD UNIQUE INDEX(c2), ALGORITHM = INPLACE;
128 ALTER TABLE t1 DROP INDEX c2, ADD PRIMARY KEY(c1), ALGORITHM = INPLACE;
129 eval $innodb_metrics_select;
130
131 connection default;
132 --error ER_DUP_ENTRY
133 INSERT INTO t1 VALUES(6,3,1);
134 --error ER_DUP_ENTRY
135 INSERT INTO t1 VALUES(7,4,2);
136 DROP INDEX c2_2 ON t1;
137 BEGIN;
138 INSERT INTO t1 VALUES(7,4,2);
139 ROLLBACK;
140
141 connection con1;
142 let $ID= `SELECT @id := CONNECTION_ID()`;
143 --error ER_QUERY_INTERRUPTED
144 KILL QUERY @id;
145
146 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR dml_done';
147 SET DEBUG_SYNC = 'row_log_table_apply2_before SIGNAL applied WAIT_FOR kill_done';
148 --send
149 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
150
151 connection default;
152 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt';
153 eval $innodb_metrics_select;
154 BEGIN;
155 INSERT INTO t1 VALUES(7,4,2);
156 ROLLBACK;
157 SET DEBUG_SYNC = 'now SIGNAL dml_done WAIT_FOR applied';
158 let $ignore= `SELECT @id := $ID`;
159 KILL QUERY @id;
160 SET DEBUG_SYNC = 'now SIGNAL kill_done';
161
162 connection con1;
163 --error ER_QUERY_INTERRUPTED
164 reap;
165 eval $innodb_metrics_select;
166
167 connection default;
168 CHECK TABLE t1;
169 INSERT INTO t1 SELECT 5 + c1, c2, c3 FROM t1;
170 INSERT INTO t1 SELECT 10 + c1, c2, c3 FROM t1;
171 INSERT INTO t1 SELECT 20 + c1, c2, c3 FROM t1;
172 INSERT INTO t1 SELECT 40 + c1, c2, c3 FROM t1;
173 # Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
174 --replace_column 9 ROWS
175 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
176 ANALYZE TABLE t1;
177
178 SET @merge_encrypt_0=
179 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
180 WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
181 SET @merge_decrypt_0=
182 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
183 WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
184 SET @rowlog_encrypt_0=
185 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
186 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
187
188 connection con1;
189 SHOW CREATE TABLE t1;
190 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
191
192 # Exceed the configured innodb_online_alter_log_max_size.
193 # The actual limit is a multiple of innodb_sort_buf_size,
194 # because that is the size of the in-memory log buffers.
195 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt2 WAIT_FOR dml2_done';
196 # Ensure that the ALTER TABLE will be executed even with some concurrent DML.
197 SET lock_wait_timeout = 10;
198 --send
199 ALTER TABLE t1 ROW_FORMAT=COMPACT
200 PAGE_COMPRESSED = YES PAGE_COMPRESSION_LEVEL = 1, ALGORITHM = INPLACE;
201
202 # Generate some log (delete-mark, delete-unmark, insert etc.)
203 # while the index creation is blocked.
204 connection default;
205 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt2';
206 INSERT INTO t1 SELECT 80 + c1, c2, c3 FROM t1;
207 INSERT INTO t1 SELECT 160 + c1, c2, c3 FROM t1;
208 UPDATE t1 SET c2 = c2 + 1;
209 # At this point, the clustered index scan must have completed,
210 # but the modification log keeps accumulating due to the DEBUG_SYNC.
211 eval $innodb_metrics_select;
212 let $c= 8;
213 while ($c)
214 {
215 UPDATE t1 SET c2 = c2 + 1;
216 UPDATE t1 SET c2 = c2 + 2;
217 dec $c;
218 }
219 # Temporary table should exist until the DDL thread notices the overflow.
220 eval $innodb_metrics_select;
221
222 SET @merge_encrypt_1=
223 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
224 WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
225 SET @merge_decrypt_1=
226 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
227 WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
228 SET @rowlog_encrypt_1=
229 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
230 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
231
232 SELECT
233 (@merge_encrypt_1-@merge_encrypt_0)-
234 (@merge_decrypt_1-@merge_decrypt_0) as sort_balance,
235 @merge_encrypt_1>@merge_encrypt_0, @merge_decrypt_1>@merge_decrypt_0,
236 @rowlog_encrypt_1>@rowlog_encrypt_0;
237
238 # Release con1.
239 SET DEBUG_SYNC = 'now SIGNAL dml2_done';
240
241 connection con1;
242 # If the following fails with the wrong error, it probably means that
243 # you should rerun with a larger mtr --debug-sync-timeout.
244 --error ER_INNODB_ONLINE_LOG_TOO_BIG
245 reap;
246 # The table should have been dropped from the data dictionary
247 # when the above error was noticed.
248 eval $innodb_metrics_select;
249
250 SET @merge_encrypt_1=
251 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
252 WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
253 SET @merge_decrypt_1=
254 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
255 WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
256 SET @rowlog_encrypt_1=
257 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
258 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
259 SET @rowlog_decrypt_1=
260 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
261 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_decrypted');
262
263 SET GLOBAL innodb_max_purge_lag_wait=0;
264 # Accumulate and apply some modification log.
265 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL rebuilt3 WAIT_FOR dml3_done';
266 --error ER_MULTIPLE_PRI_KEY
267 ALTER TABLE t1 ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
268 --error ER_DUP_ENTRY
269 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c22f), CHANGE c2 c22f INT;
270 --send
271 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(c22f,c1,c4(5)),
272 CHANGE c2 c22f INT, CHANGE c3 c3 CHAR(255) NULL, CHANGE c1 c1 INT AFTER c22f,
273 ADD COLUMN c4 VARCHAR(6) DEFAULT 'Online', LOCK=NONE;
274
275 connection default;
276 SET DEBUG_SYNC = 'now WAIT_FOR rebuilt3';
277 # Generate some log (delete-mark, delete-unmark, insert etc.)
278 eval $innodb_metrics_select;
279 BEGIN;
280 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 240;
281 DELETE FROM t1 WHERE c1 > 320;
282 UPDATE t1 SET c2 = c2 + 1;
283 COMMIT;
284 eval $innodb_metrics_select;
285 # Release con1.
286 SET DEBUG_SYNC = 'now SIGNAL dml3_done';
287
288 connection con1;
289 reap;
290 eval $innodb_metrics_select;
291 SELECT COUNT(c22f) FROM t1;
292 CHECK TABLE t1;
293
294 SET @merge_encrypt_2=
295 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
296 WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
297 SET @merge_decrypt_2=
298 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
299 WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
300 SET @rowlog_encrypt_2=
301 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
302 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
303 SET @rowlog_decrypt_2=
304 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
305 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_decrypted');
306
307 SELECT
308 (@merge_encrypt_2-@merge_encrypt_1)-
309 (@merge_decrypt_2-@merge_decrypt_1) as sort_balance,
310 (@rowlog_encrypt_2-@rowlog_encrypt_1)-
311 (@rowlog_decrypt_2-@rowlog_decrypt_1) as log_balance;
312 SELECT
313 @merge_encrypt_2-@merge_encrypt_1>0 as sort_encrypted,
314 @merge_decrypt_2-@merge_decrypt_1>0 as sort_decrypted,
315 @rowlog_encrypt_2-@rowlog_encrypt_1>0 as log_encrypted,
316 @rowlog_decrypt_2-@rowlog_decrypt_1>0 as log_decrypted;
317
318 # Create a column prefix index.
319 --error ER_DUP_ENTRY
320 ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5));
321 UPDATE t1 SET c3 = NULL WHERE c3 = '';
322 SET lock_wait_timeout = 1;
323 --error ER_KEY_COLUMN_DOES_NOT_EXIST
324 ALTER TABLE t1 DROP COLUMN c22f, ADD PRIMARY KEY c3p5(c3(5));
325 # NULL -> NOT NULL is only allowed INPLACE without IGNORE.
326 # Adding a PRIMARY KEY will add NOT NULL implicitly!
327 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
328 ALTER IGNORE TABLE t1 DROP COLUMN c22f, DROP PRIMARY KEY, ADD PRIMARY KEY c3p5(c3(5)),
329 ALGORITHM = INPLACE;
330
331 UPDATE t1 SET c3=LEFT(CONCAT(c1,REPEAT('foo',c1)),255) WHERE c3 IS NULL;
332
333 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created0 WAIT_FOR ins_done0';
334 # NULL -> NOT NULL is allowed INPLACE.
335 --send
336 ALTER TABLE t1 MODIFY c3 CHAR(255) NOT NULL, DROP COLUMN c22f,
337 DROP PRIMARY KEY, ADD PRIMARY KEY(c1,c4(5)),
338 ADD COLUMN c5 CHAR(5) DEFAULT 'tired' FIRST;
339
340 connection default;
341
342 SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created0';
343 BEGIN;
344 INSERT INTO t1 VALUES(347,33101,'Pikku kakkosen posti','YLETV2');
345 INSERT INTO t1 VALUES(33101,347,NULL,'');
346 COMMIT;
347 SET DEBUG_SYNC = 'now SIGNAL ins_done0';
348
349 connection con1;
350 --error ER_INVALID_USE_OF_NULL
351 reap;
352 DELETE FROM t1 WHERE c1= 347 and c22f = 33101;
353 ALTER TABLE t1 MODIFY c3 CHAR(255) NOT NULL;
354
355 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL c3p5_created WAIT_FOR ins_done';
356 --send
357 ALTER TABLE t1 DROP PRIMARY KEY, DROP COLUMN c22f,
358 ADD COLUMN c6 VARCHAR(1000) DEFAULT
359 'I love tracking down hard-to-reproduce bugs.',
360 ADD PRIMARY KEY c3p5(c3(5), c6(2));
361
362 connection default;
363 SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created';
364 SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL ins_done WAIT_FOR ddl_timed_out';
365 --error ER_BAD_NULL_ERROR
366 INSERT INTO t1 VALUES(347,33101,NULL,'');
367 --send
368 INSERT INTO t1 VALUES(347,33101,'Pikku kakkosen posti','');
369
370 connection con1;
371 --error ER_LOCK_WAIT_TIMEOUT
372 reap;
373 SET DEBUG_SYNC = 'now SIGNAL ddl_timed_out';
374 eval $innodb_metrics_select;
375
376 connection default;
377 reap;
378 SELECT COUNT(*) FROM t1;
379 ALTER TABLE t1 ROW_FORMAT=REDUNDANT;
380 SELECT * FROM t1 LIMIT 10;
381
382 connection con1;
383 ALTER TABLE t1 DISCARD TABLESPACE;
384
385 connection default;
386 SHOW CREATE TABLE t1;
387 SET GLOBAL innodb_monitor_disable = module_ddl;
388 DROP TABLE t1;
389
390 CREATE TABLE t1 (a INT PRIMARY KEY, b blob) ENGINE=InnoDB;
391 INSERT INTO t1 VALUES(0,NULL);
392
393 connection con1;
394 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL created WAIT_FOR ins';
395 send ALTER TABLE t1 FORCE;
396
397 connection default;
398 SET DEBUG_SYNC = 'now WAIT_FOR created';
399 BEGIN;
400 INSERT INTO t1 VALUES(1, repeat('a', 10000));
401 ROLLBACK;
402 SET DEBUG_SYNC = 'now SIGNAL ins';
403
404 connection con1;
405 reap;
406
407 connection default;
408 SELECT * FROM t1;
409 DROP TABLE t1;
410 SET DEBUG_SYNC = 'RESET';
411
412 --echo #
413 --echo # MDEV-29600 Memory leak in row_log_table_apply_update()
414 --echo #
415
416 CREATE TABLE t1 (pk INT PRIMARY KEY, f TEXT) ENGINE=InnoDB;
417 INSERT INTO t1 SET pk=1;
418
419 connection con1;
420 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL created WAIT_FOR updated';
421 send ALTER TABLE t1 FORCE;
422
423 connection default;
424 SET DEBUG_SYNC = 'now WAIT_FOR created';
425 UPDATE t1 SET f = REPEAT('a', 20000);
426 SET DEBUG_SYNC = 'now SIGNAL updated';
427
428 connection con1;
429 reap;
430
431 connection default;
432 DROP TABLE t1;
433 --echo #
434 --echo # MDEV-29977 Memory leak in row_log_table_apply_update
435 --echo #
436 CREATE TABLE t1(f1 longtext, f2 int, KEY(f1(1024)), KEY(f2, f1(20))) ENGINE=InnoDB;
437 INSERT INTO t1 VALUES('a', 1);
438 connection con1;
439 set DEBUG_SYNC="innodb_inplace_alter_table_enter SIGNAL con_default WAIT_FOR con1_signal";
440 send ALTER TABLE t1 FORCE;
441 connection default;
442 SET DEBUG_SYNC="now WAIT_FOR con_default";
443 UPDATE t1 SET f1 = NULL;
444 UPDATE t1 SET f1 = REPEAT('b', 9000);
445 SET DEBUG_SYNC="now SIGNAL con1_signal";
446 connection con1;
447 reap;
448 DROP TABLE t1;
449 connection default;
450
451 --echo #
452 --echo # MDEV-28122 Optimize table crash while applying online log
453 --echo #
454 CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL,
455 f3 CHAR(200), f4 CHAR(200),
456 f5 VARCHAR(87), PRIMARY KEY(f1))ENGINE=InnoDB;
457 INSERT INTO t1 VALUES(6000, 6000, "InnoDB",
458 "MariaDB", repeat('a', 87));
459
460 SET DEBUG_SYNC="inplace_after_index_build SIGNAL dml_start WAIT_FOR dml_commit";
461 SEND ALTER TABLE t1 FORCE;
462
463 connection con1;
464 SET DEBUG_SYNC="now WAIT_FOR dml_start";
465 BEGIN;
466 INSERT INTO t1 SELECT seq, seq, "IDB", "MDB", repeat('a', 87) FROM seq_1_to_127;
467 INSERT INTO t1 VALUES(128, 128, "IDB", "MDB", repeat('a', 86));
468 INSERT INTO t1 VALUES(129, 129, "idb", "mdb", repeat('a', 2));
469 COMMIT;
470 SET DEBUG_SYNC="now SIGNAL dml_commit";
471
472 connection default;
473 reap;
474 CHECK TABLE t1;
475 DROP TABLE t1;
476 SET DEBUG_SYNC=RESET;
477 disconnect con1;
478
479 # Check that all connections opened by test cases in this file are really
480 # gone so execution of other tests won't be affected by their presence.
481 --source include/wait_until_count_sessions.inc
482
483 --disable_warnings
484 SET GLOBAL innodb_monitor_enable = default;
485 SET GLOBAL innodb_monitor_disable = default;
486 --enable_warnings
487
488 --source include/test_db_charset_restore.inc