Debian Codegraph @

mysql-test/suite/innodb/t/innodb-index-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 count > 16;
6
7 # DISCARD TABLESPACE needs file-per-table
8 SET @global_innodb_file_per_table_orig = @@global.innodb_file_per_table;
9 SET GLOBAL innodb_file_per_table = on;
10
11 # Save the initial number of concurrent sessions.
12 --source include/count_sessions.inc
13
14 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 TEXT)
15 ENGINE=InnoDB STATS_PERSISTENT=0;
16 INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,'');
17
18 SET GLOBAL innodb_monitor_enable = module_ddl;
19 eval $innodb_metrics_select;
20
21 SET DEBUG_SYNC = 'RESET';
22 SET DEBUG_SYNC = 'write_row_noreplace SIGNAL have_handle WAIT_FOR go_ahead';
23 --send
24 INSERT INTO t1 VALUES(1,2,3);
25
26 connect (con1,localhost,root,,);
27 connection con1;
28
29 # This should block at the end because of the INSERT in connection default
30 # is holding a metadata lock.
31 SET DEBUG_SYNC = 'now WAIT_FOR have_handle';
32 SET lock_wait_timeout = 1;
33 --error ER_LOCK_WAIT_TIMEOUT
34 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
35 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
36
37 connection default;
38 --error ER_DUP_ENTRY
39 reap;
40 eval $innodb_metrics_select;
41 SET GLOBAL innodb_monitor_reset = module_ddl;
42
43 connection con1;
44 SET SESSION DEBUG = '+d,innodb_OOM_prepare_inplace_alter';
45 --error ER_OUT_OF_RESOURCES
46 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
47 SET SESSION DEBUG = '-d,innodb_OOM_prepare_inplace_alter';
48 SET SESSION DEBUG = '+d,innodb_OOM_inplace_alter';
49 --error ER_OUT_OF_RESOURCES
50 CREATE UNIQUE INDEX c2 ON t1(c2);
51 SET SESSION DEBUG = '-d,innodb_OOM_inplace_alter';
52 CREATE UNIQUE INDEX c2 ON t1(c2);
53 DROP INDEX c2 ON t1;
54
55 connection default;
56 SHOW CREATE TABLE t1;
57 # Insert a duplicate entry (4) for the upcoming UNIQUE INDEX(c2).
58 BEGIN;
59 INSERT INTO t1 VALUES(7,4,2);
60
61 connection con1;
62 # This DEBUG_SYNC should not kick in yet, because the duplicate key will be
63 # detected before we get a chance to apply the online log.
64 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL scanned WAIT_FOR rollback_done';
65 # This will be a lock wait timeout on the meta-data lock,
66 # because the transaction inserting (7,4,2) is still active.
67 --error ER_LOCK_WAIT_TIMEOUT
68 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
69 connection default;
70 COMMIT;
71 connection con1;
72 --error ER_DUP_ENTRY
73 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
74 connection default;
75 DELETE FROM t1 WHERE c1 = 7;
76 connection con1;
77 # The new FOREIGN KEY will make a new supporting index be created, but there
78 # is no additional index that can be used as parent index.
79 --error ER_FK_NO_INDEX_PARENT
80 ALTER TABLE t1 ADD FOREIGN KEY(c2) REFERENCES t1(c2), ALGORITHM = INPLACE;
81 # The previous DEBUG_SYNC should be ignored, because an exclusive lock
82 # has been requested and the online log is not being allocated.
83 ALTER TABLE t1 ADD UNIQUE INDEX(c2), LOCK = EXCLUSIVE, ALGORITHM = INPLACE;
84 DROP INDEX c2 ON t1;
85 # Now the previous DEBUG_SYNC should kick in.
86 --send
87 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
88 connection default;
89 SET DEBUG_SYNC = 'now WAIT_FOR scanned';
90 eval $innodb_metrics_select;
91 SET GLOBAL innodb_monitor_reset = module_ddl;
92
93 # Insert a duplicate entry (4) for the already started UNIQUE INDEX(c2).
94 BEGIN;
95 INSERT INTO t1 VALUES(7,4,2);
96 ROLLBACK;
97 SET DEBUG_SYNC = 'now SIGNAL rollback_done';
98
99 connection con1;
100 # Because the modification log will be applied in order, there will be
101 # a duplicate key error on the (7,4,2) even though we roll it back.
102 --error ER_DUP_ENTRY
103 reap;
104 # Now, create the index without any concurrent DML, while no duplicate exists.
105 SET DEBUG_SYNC = 'row_log_apply_after SIGNAL created WAIT_FOR dml_done';
106 --send
107 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
108 connection default;
109 SET DEBUG_SYNC = 'now WAIT_FOR created';
110 # At this point, the index has been created inside InnoDB but not yet
111 # in the MySQL data dictionary.
112 eval $innodb_metrics_select;
113 SET GLOBAL innodb_monitor_reset = module_ddl;
114
115 # A duplicate key error should now be triggered by InnoDB, but reported
116 # by the ALTER TABLE because the index does not 'officially' exist yet.
117 INSERT INTO t1 VALUES(6,3,1);
118 SET DEBUG_SYNC = 'now SIGNAL dml_done';
119 connection con1;
120 # This is due to the duplicate entry (6,3,1).
121 --error ER_DUP_UNKNOWN_IN_INDEX
122 reap;
123 DELETE FROM t1 WHERE c1=6;
124 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
125 eval $innodb_metrics_select;
126 SET GLOBAL innodb_monitor_reset = module_ddl;
127
128 connection default;
129 --error ER_DUP_ENTRY
130 INSERT INTO t1 VALUES(6,3,1);
131 --error ER_DUP_ENTRY
132 INSERT INTO t1 VALUES(7,4,2);
133 ALTER TABLE t1 STATS_PERSISTENT=1;
134 ANALYZE TABLE t1;
135 # Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
136 UPDATE mysql.innodb_index_stats SET stat_value = 5
137 WHERE database_name = 'test' AND table_name= 't1' AND index_name = 'PRIMARY'
138 AND stat_value = 6;
139 --replace_column 4 LAST_UPDATE
140 SELECT * FROM mysql.innodb_index_stats WHERE table_name IN ('t1');
141 CREATE TABLE t1_c2_stats SELECT * FROM mysql.innodb_index_stats
142 WHERE database_name = 'test' AND table_name = 't1' and index_name = 'c2';
143 DROP INDEX c2 ON t1;
144 ANALYZE TABLE t1_c2_stats;
145 --replace_column 4 LAST_UPDATE
146 SELECT * FROM mysql.innodb_index_stats WHERE table_name IN ('t1', 't1_c2_stats');
147
148 connection con1;
149 let $ID= `SELECT @id := CONNECTION_ID()`;
150 --error ER_QUERY_INTERRUPTED
151 KILL QUERY @id;
152
153 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2d_created WAIT_FOR kill_done';
154 --send
155 CREATE INDEX c2d ON t1(c2);
156
157 connection default;
158 SET DEBUG_SYNC = 'now WAIT_FOR c2d_created';
159 eval $innodb_metrics_select;
160 SET GLOBAL innodb_monitor_reset = module_ddl;
161 let $ignore= `SELECT @id := $ID`;
162 KILL QUERY @id;
163 SET DEBUG_SYNC = 'now SIGNAL kill_done';
164
165 connection con1;
166 --error ER_QUERY_INTERRUPTED
167 reap;
168 eval $innodb_metrics_select;
169 SET GLOBAL innodb_monitor_reset = module_ddl;
170
171 connection default;
172 CHECK TABLE t1;
173 INSERT INTO t1 SELECT 5 + c1, c2, c3 FROM t1;
174 INSERT INTO t1 SELECT 10 + c1, c2, c3 FROM t1;
175 INSERT INTO t1 SELECT 20 + c1, c2, c3 FROM t1;
176 INSERT INTO t1 SELECT 40 + c1, c2, c3 FROM t1;
177 # Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
178 --replace_result 81 80
179 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
180 ANALYZE TABLE t1;
181
182 connection con1;
183 # Forge some statistics for c2d, and see that they will be used
184 UPDATE t1_c2_stats SET index_name = 'c2d';
185 # Fake the statistics. The cardinality should be 5,80.
186 UPDATE t1_c2_stats SET stat_value = 2 WHERE stat_name = 'n_diff_pfx01';
187 INSERT INTO t1_c2_stats
188 SELECT database_name, table_name, index_name, last_update, 'n_diff_pfx02', 80,
189 sample_size, 'c2,c1' FROM t1_c2_stats
190 WHERE stat_name = 'n_diff_pfx01' AND stat_description = 'c2';
191 INSERT INTO mysql.innodb_index_stats SELECT * FROM t1_c2_stats;
192 DROP TABLE t1_c2_stats;
193
194 CREATE INDEX c2d ON t1(c2);
195 # This should show the newly calculated stats by CREATE INDEX above,
196 # not the faked cardinality=4 for c2d(c2).
197 # Purge may or may not have cleaned up the DELETE FROM t1 WHERE c1 = 7;
198 ANALYZE TABLE t1;
199 --replace_result 81 80
200 SHOW INDEX FROM t1;
201 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
202
203 SHOW CREATE TABLE t1;
204
205 # Exceed the configured innodb_online_alter_log_max_size.
206 # The actual limit is a multiple of innodb_sort_buf_size,
207 # because that is the size of the in-memory log buffers.
208 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2e_created WAIT_FOR dml2_done';
209 # Ensure that the ALTER TABLE will be executed even with some concurrent DML.
210 SET lock_wait_timeout = 10;
211 --send
212 ALTER TABLE t1 CHANGE c2 c22 INT, DROP INDEX c2d, ADD INDEX c2e(c22),
213 ALGORITHM = INPLACE;
214
215 # Generate some log (delete-mark, delete-unmark, insert etc.)
216 # while the index creation is blocked. Some of this may run
217 # in parallel with the clustered index scan.
218 connection default;
219 INSERT INTO t1 SELECT 80 + c1, c2, c3 FROM t1;
220 INSERT INTO t1 SELECT 160 + c1, c2, c3 FROM t1;
221 UPDATE t1 SET c2 = c2 + 1;
222 SET DEBUG_SYNC = 'now WAIT_FOR c2e_created';
223 # At this point, the clustered index scan must have completed,
224 # but the modification log keeps accumulating due to the DEBUG_SYNC.
225 eval $innodb_metrics_select;
226 SET GLOBAL innodb_monitor_reset = module_ddl;
227 let $c= 2;
228 while ($c)
229 {
230 BEGIN;
231 DELETE FROM t1;
232 ROLLBACK;
233 UPDATE t1 SET c2 = c2 + 1;
234 BEGIN;
235 UPDATE t1 SET c2 = c2 + 1;
236 DELETE FROM t1;
237 ROLLBACK;
238 dec $c;
239 }
240 # Incomplete index c2e should exist until the DDL thread notices the overflow.
241 # (The output below strips TEMP_INDEX_PREFIX from the name.)
242 eval $innodb_metrics_select;
243 SET GLOBAL innodb_monitor_reset = module_ddl;
244 SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_INDEXES si
245 INNER JOIN INFORMATION_SCHEMA.INNODB_FIELDS sf
246 ON si.index_id = sf.index_id WHERE si.name = '?c2e';
247
248 # Release con1.
249 SET DEBUG_SYNC = 'now SIGNAL dml2_done';
250
251 connection con1;
252 # If the following fails with the wrong error, it probably means that
253 # you should rerun with a larger mtr --debug-sync-timeout.
254 --error ER_INNODB_ONLINE_LOG_TOO_BIG
255 reap;
256 # The index c2e should have been dropped from the data dictionary
257 # when the above error was noticed. It should still exist in the
258 # cache with index->online_status = ONLINE_INDEX_ABORTED_DROPPED.
259 eval $innodb_metrics_select;
260 SET GLOBAL innodb_monitor_reset = module_ddl;
261 SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_INDEXES si
262 INNER JOIN INFORMATION_SCHEMA.INNODB_FIELDS sf
263 ON si.index_id = sf.index_id WHERE si.name = 'c2e';
264
265 # ddl_background_drop_indexes = 1 here, because the incomplete index c2e still
266 # exists in the InnoDB data dictionary cache.
267 eval $innodb_metrics_select;
268 SET GLOBAL innodb_monitor_reset = module_ddl;
269
270 connection default;
271
272 ALTER TABLE t1 COMMENT 'testing if c2e will be dropped';
273
274 # Check that the 'zombie' index c2e was dropped.
275 eval $innodb_metrics_select;
276 SET GLOBAL innodb_monitor_reset = module_ddl;
277
278 connection con1;
279 # Accumulate and apply some modification log.
280 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2f_created WAIT_FOR dml3_done';
281 --send
282 ALTER TABLE t1 ADD INDEX c2f(c22f), CHANGE c2 c22f INT;
283
284 connection default;
285 SET DEBUG_SYNC = 'now WAIT_FOR c2f_created';
286 # Generate some log (delete-mark, delete-unmark, insert etc.)
287 eval $innodb_metrics_select;
288 SET GLOBAL innodb_monitor_reset = module_ddl;
289 BEGIN;
290 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
291 DELETE FROM t1 WHERE c1 > 320;
292 ROLLBACK;
293 BEGIN;
294 UPDATE t1 SET c2 = c2 + 1;
295 DELETE FROM t1;
296 ROLLBACK;
297 eval $innodb_metrics_select;
298 SET GLOBAL innodb_monitor_reset = module_ddl;
299 # Release con1.
300 SET DEBUG_SYNC = 'now SIGNAL dml3_done';
301
302 connection con1;
303 reap;
304 eval $innodb_metrics_select;
305 SET GLOBAL innodb_monitor_reset = module_ddl;
306 SELECT COUNT(c22f) FROM t1;
307 CHECK TABLE t1;
308
309 # Create a column prefix index.
310 --error ER_DUP_ENTRY
311 ALTER TABLE t1 ADD UNIQUE INDEX c3p5(c3(5));
312 UPDATE t1 SET c3 = NULL WHERE c3 = '';
313 SET lock_wait_timeout = 1;
314 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c3p5_created WAIT_FOR ins_done';
315 --send
316 ALTER TABLE t1 ADD UNIQUE INDEX c3p5(c3(5));
317
318 connection default;
319 SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created';
320
321 # Check that the index was created.
322 SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_INDEXES si
323 INNER JOIN INFORMATION_SCHEMA.INNODB_FIELDS sf
324 ON si.index_id = sf.index_id WHERE si.name = '?c3p5';
325
326 SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL ins_done WAIT_FOR ddl_timed_out';
327 --send
328 INSERT INTO t1 VALUES(347,33101,NULL);
329
330 connection con1;
331 --error ER_LOCK_WAIT_TIMEOUT
332 reap;
333 SET DEBUG_SYNC = 'now SIGNAL ddl_timed_out';
334
335 # InnoDB should have cleaned up the index c3p5 from the data dictionary,
336 # but not yet from the dictionary cache.
337 SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_INDEXES si
338 INNER JOIN INFORMATION_SCHEMA.INNODB_FIELDS sf
339 ON si.index_id = sf.index_id WHERE si.name = 'c3p5';
340 eval $innodb_metrics_select;
341 SET GLOBAL innodb_monitor_reset = module_ddl;
342
343 connection default;
344 reap;
345 # Index c3p5 should still exist in the data dictionary cache.
346 eval $innodb_metrics_select;
347 SET GLOBAL innodb_monitor_reset = module_ddl;
348
349 --disable_testcase BUG#0000
350 # Temporarily disabled by fix for bug#14213236. Should be either
351 # removed or updated to take into account that locking for IMPORT/
352 # DISCARD TABLESPACE happens on MDL layer. New test case is added
353 # to validate this at MDL layer(i_main.alter_table.test)
354
355 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2g_created WAIT_FOR dml4_done';
356 # The lock upgrade at the end of the ALTER will conflict with the DISCARD.
357 SET lock_wait_timeout = 1;
358 --send
359 ALTER TABLE t1 DROP INDEX c2f, ADD INDEX c2g(c22f);
360
361 connection con1;
362 SET DEBUG_SYNC = 'now WAIT_FOR c2g_created';
363
364 connect (con2,localhost,root,,);
365 connection con2;
366
367 # This will conflict with the ALTER in connection default, above.
368 SET lock_wait_timeout = 10;
369 --send
370 ALTER TABLE t1 DISCARD TABLESPACE;
371
372 connection con1;
373 let $wait_condition=
374 SELECT COUNT(*) = 1 FROM information_schema.processlist
375 WHERE state = 'Waiting for table level lock' and
376 info = 'ALTER TABLE t1 DISCARD TABLESPACE';
377 --source include/wait_condition.inc
378
379 SET DEBUG_SYNC = 'now SIGNAL dml4_done';
380 disconnect con1;
381 connection con2;
382 reap;
383 disconnect con2;
384 connection default;
385 --error ER_LOCK_WAIT_TIMEOUT
386 reap;
387 --enable_testcase
388 #remove below con1 disconnect if above test case is enabled
389 connection con1;
390 disconnect con1;
391 connection default;
392
393 SHOW CREATE TABLE t1;
394 ALTER TABLE t1 DROP INDEX c2d, DROP INDEX c2f;
395 # The ALTER TABLE should have cleaned up c3p5 from the cache.
396 eval $innodb_metrics_select;
397 SET GLOBAL innodb_monitor_reset = module_ddl;
398 ALTER TABLE t1 ADD INDEX c2h(c22f), ALGORITHM = INPLACE;
399 --error ER_DUP_KEYNAME
400 ALTER TABLE t1 ADD INDEX c2h(c22f), ALGORITHM = COPY;
401
402 SET DEBUG_SYNC = 'RESET';
403 SET DEBUG = '';
404 SET GLOBAL innodb_monitor_disable = module_ddl;
405
406 DROP TABLE t1;
407
408 # Check that all connections opened by test cases in this file are really
409 # gone so execution of other tests won't be affected by their presence.
410 --source include/wait_until_count_sessions.inc
411
412 SET GLOBAL DEBUG = '';
413 SET GLOBAL innodb_file_per_table = @global_innodb_file_per_table_orig;
414 --disable_warnings
415 SET GLOBAL innodb_monitor_reset = default;
416 SET GLOBAL innodb_monitor_enable = default;
417 SET GLOBAL innodb_monitor_disable = default;
418 --enable_warnings