Debian Codegraph @

mysql-test/suite/innodb/r/innodb-index-online.result

raw
1 SET GLOBAL innodb_monitor_reset_all=all;
2 SET GLOBAL innodb_monitor_reset_all=default;
3 call mtr.add_suppression("InnoDB: Warning: Small buffer pool size");
4 CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 INT, c3 TEXT)
5 ENGINE=InnoDB STATS_PERSISTENT=0;
6 INSERT INTO t1 VALUES (1,1,''), (2,2,''), (3,3,''), (4,4,''), (5,5,'');
7 SET GLOBAL innodb_monitor_enable = module_ddl;
8 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
9 name count
10 ddl_background_drop_indexes 0
11 ddl_online_create_index 0
12 ddl_pending_alter_table 0
13 ddl_sort_file_alter_table 0
14 ddl_log_file_alter_table 0
15 SET DEBUG_SYNC = 'RESET';
16 SET DEBUG_SYNC = 'write_row_noreplace SIGNAL have_handle WAIT_FOR go_ahead';
17 INSERT INTO t1 VALUES(1,2,3);
18 connect con1,localhost,root,,;
19 SET DEBUG_SYNC = 'now WAIT_FOR have_handle';
20 SET lock_wait_timeout = 1;
21 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
22 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
23 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
24 connection default;
25 ERROR 23000: Duplicate entry '1' for key 'PRIMARY'
26 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
27 name count
28 ddl_background_drop_indexes 0
29 ddl_online_create_index 0
30 ddl_pending_alter_table 0
31 ddl_sort_file_alter_table 0
32 ddl_log_file_alter_table 0
33 connection con1;
34 SET @saved_debug_dbug = @@SESSION.debug_dbug;
35 SET DEBUG_DBUG = '+d,innodb_OOM_prepare_inplace_alter';
36 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
37 ERROR HY000: Out of memory.
38 SET DEBUG_DBUG = @saved_debug_dbug;
39 SET DEBUG_DBUG = '+d,innodb_OOM_inplace_alter';
40 CREATE UNIQUE INDEX c2 ON t1(c2);
41 ERROR HY000: Out of memory.
42 SET DEBUG_DBUG = @saved_debug_dbug;
43 SET DEBUG_DBUG = '+d,innodb_OOM_prepare_add_index';
44 ALTER TABLE t1 ADD KEY(c2), ADD KEY c3_10(c3(10)), ADD KEY c3_c2(c3(4),c2);
45 ERROR HY000: Out of memory.
46 SET DEBUG_DBUG = @saved_debug_dbug;
47 CREATE UNIQUE INDEX c2 ON t1(c2);
48 DROP INDEX c2 ON t1;
49 connection default;
50 SHOW CREATE TABLE t1;
51 Table Create Table
52 t1 CREATE TABLE `t1` (
53 `c1` int(11) NOT NULL,
54 `c2` int(11) DEFAULT NULL,
55 `c3` text DEFAULT NULL,
56 PRIMARY KEY (`c1`)
57 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=0
58 BEGIN;
59 INSERT INTO t1 VALUES(7,4,2);
60 connection con1;
61 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL scanned WAIT_FOR rollback_done';
62 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
63 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
64 connection default;
65 COMMIT;
66 connection con1;
67 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
68 ERROR 23000: Duplicate entry '4' for key 'c2'
69 connection default;
70 DELETE FROM t1 WHERE c1 = 7;
71 connection con1;
72 ALTER TABLE t1 ADD FOREIGN KEY(c2) REFERENCES t1(c2), ALGORITHM = INPLACE;
73 ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Adding foreign keys needs foreign_key_checks=OFF. Try ALGORITHM=COPY
74 ALTER TABLE t1 ADD UNIQUE INDEX(c2), LOCK = EXCLUSIVE, ALGORITHM = INPLACE;
75 DROP INDEX c2 ON t1;
76 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
77 connection default;
78 SET DEBUG_SYNC = 'now WAIT_FOR scanned';
79 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
80 name count
81 ddl_background_drop_indexes 0
82 ddl_online_create_index 1
83 ddl_pending_alter_table 1
84 ddl_sort_file_alter_table 0
85 ddl_log_file_alter_table 0
86 BEGIN;
87 INSERT INTO t1 VALUES(7,4,2);
88 COMMIT;
89 DELETE FROM t1 where c1 = 7;
90 SET DEBUG_SYNC = 'now SIGNAL rollback_done';
91 connection con1;
92 ERROR 23000: Duplicate entry '4' for key 'c2'
93 SET DEBUG_SYNC = 'row_log_apply_after SIGNAL created WAIT_FOR dml_done';
94 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
95 connection default;
96 SET DEBUG_SYNC = 'now WAIT_FOR created';
97 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
98 name count
99 ddl_background_drop_indexes 0
100 ddl_online_create_index 1
101 ddl_pending_alter_table 1
102 ddl_sort_file_alter_table 0
103 ddl_log_file_alter_table 0
104 INSERT INTO t1 VALUES(6,3,1);
105 SET DEBUG_SYNC = 'now SIGNAL dml_done';
106 connection con1;
107 ERROR 23000: Duplicate entry '' for key '*UNKNOWN*'
108 DELETE FROM t1 WHERE c1=6;
109 ALTER TABLE t1 ADD UNIQUE INDEX(c2);
110 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
111 name count
112 ddl_background_drop_indexes 0
113 ddl_online_create_index 0
114 ddl_pending_alter_table 0
115 ddl_sort_file_alter_table 0
116 ddl_log_file_alter_table 0
117 connection default;
118 INSERT INTO t1 VALUES(6,3,1);
119 ERROR 23000: Duplicate entry '3' for key 'c2'
120 INSERT INTO t1 VALUES(7,4,2);
121 ERROR 23000: Duplicate entry '4' for key 'c2'
122 ALTER TABLE t1 STATS_PERSISTENT=1;
123 ANALYZE TABLE t1;
124 Table Op Msg_type Msg_text
125 test.t1 analyze status Engine-independent statistics collected
126 test.t1 analyze Warning Engine-independent statistics are not collected for column 'c3'
127 test.t1 analyze status OK
128 UPDATE mysql.innodb_index_stats SET stat_value = 5
129 WHERE database_name = 'test' AND table_name= 't1' AND index_name = 'PRIMARY'
130 AND stat_value = 6;
131 SELECT * FROM mysql.innodb_index_stats WHERE table_name IN ('t1');
132 database_name table_name index_name last_update stat_name stat_value sample_size stat_description
133 test t1 PRIMARY LAST_UPDATE n_diff_pfx01 5 1 c1
134 test t1 PRIMARY LAST_UPDATE n_leaf_pages 1 NULL Number of leaf pages in the index
135 test t1 PRIMARY LAST_UPDATE size 1 NULL Number of pages in the index
136 test t1 c2 LAST_UPDATE n_diff_pfx01 5 1 c2
137 test t1 c2 LAST_UPDATE n_leaf_pages 1 NULL Number of leaf pages in the index
138 test t1 c2 LAST_UPDATE size 1 NULL Number of pages in the index
139 CREATE TABLE t1_c2_stats SELECT * FROM mysql.innodb_index_stats
140 WHERE database_name = 'test' AND table_name = 't1' and index_name = 'c2';
141 ALTER TABLE t1_c2_stats ENGINE=INNODB;
142 DROP INDEX c2 ON t1;
143 ANALYZE TABLE t1_c2_stats;
144 Table Op Msg_type Msg_text
145 test.t1_c2_stats analyze status Engine-independent statistics collected
146 test.t1_c2_stats analyze status OK
147 SELECT * FROM mysql.innodb_index_stats WHERE table_name IN ('t1', 't1_c2_stats');
148 database_name table_name index_name last_update stat_name stat_value sample_size stat_description
149 test t1 PRIMARY LAST_UPDATE n_diff_pfx01 5 1 c1
150 test t1 PRIMARY LAST_UPDATE n_leaf_pages 1 NULL Number of leaf pages in the index
151 test t1 PRIMARY LAST_UPDATE size 1 NULL Number of pages in the index
152 test t1_c2_stats GEN_CLUST_INDEX LAST_UPDATE n_diff_pfx01 3 1 DB_ROW_ID
153 test t1_c2_stats GEN_CLUST_INDEX LAST_UPDATE n_leaf_pages 1 NULL Number of leaf pages in the index
154 test t1_c2_stats GEN_CLUST_INDEX LAST_UPDATE size 1 NULL Number of pages in the index
155 connection con1;
156 KILL QUERY @id;
157 ERROR 70100: Query execution was interrupted
158 SET GLOBAL innodb_max_purge_lag_wait=0;
159 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2d_created WAIT_FOR kill_done';
160 CREATE INDEX c2d ON t1(c2);
161 connection default;
162 SET DEBUG_SYNC = 'now WAIT_FOR c2d_created';
163 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
164 name count
165 ddl_background_drop_indexes 0
166 ddl_online_create_index 1
167 ddl_pending_alter_table 1
168 ddl_sort_file_alter_table 0
169 ddl_log_file_alter_table 0
170 KILL QUERY @id;
171 SET DEBUG_SYNC = 'now SIGNAL kill_done';
172 connection con1;
173 ERROR 70100: Query execution was interrupted
174 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
175 name count
176 ddl_background_drop_indexes 0
177 ddl_online_create_index 0
178 ddl_pending_alter_table 0
179 ddl_sort_file_alter_table 0
180 ddl_log_file_alter_table 0
181 connection default;
182 CHECK TABLE t1;
183 Table Op Msg_type Msg_text
184 test.t1 check status OK
185 INSERT INTO t1 SELECT 5 + c1, c2, c3 FROM t1;
186 INSERT INTO t1 SELECT 10 + c1, c2, c3 FROM t1;
187 INSERT INTO t1 SELECT 20 + c1, c2, c3 FROM t1;
188 INSERT INTO t1 SELECT 40 + c1, c2, c3 FROM t1;
189 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
190 id select_type table type possible_keys key key_len ref rows Extra
191 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where
192 ANALYZE TABLE t1;
193 Table Op Msg_type Msg_text
194 test.t1 analyze status Engine-independent statistics collected
195 test.t1 analyze Warning Engine-independent statistics are not collected for column 'c3'
196 test.t1 analyze status OK
197 connection con1;
198 UPDATE t1_c2_stats SET index_name = 'c2d';
199 UPDATE t1_c2_stats SET stat_value = 2 WHERE stat_name = 'n_diff_pfx01';
200 INSERT INTO t1_c2_stats
201 SELECT database_name, table_name, index_name, last_update, 'n_diff_pfx02', 80,
202 sample_size, 'c2,c1' FROM t1_c2_stats
203 WHERE stat_name = 'n_diff_pfx01' AND stat_description = 'c2';
204 INSERT INTO mysql.innodb_index_stats SELECT * FROM t1_c2_stats;
205 DROP TABLE t1_c2_stats;
206 CREATE INDEX c2d ON t1(c2);
207 SHOW INDEX FROM t1;
208 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
209 t1 0 PRIMARY 1 c1 A 80 NULL NULL BTREE NO
210 t1 1 c2d 1 c2 A 5 NULL NULL YES BTREE NO
211 EXPLAIN SELECT COUNT(*) FROM t1 WHERE c2 > 3;
212 id select_type table type possible_keys key key_len ref rows Extra
213 1 SIMPLE t1 range c2d c2d 5 NULL 32 Using where; Using index
214 SHOW CREATE TABLE t1;
215 Table Create Table
216 t1 CREATE TABLE `t1` (
217 `c1` int(11) NOT NULL,
218 `c2` int(11) DEFAULT NULL,
219 `c3` text DEFAULT NULL,
220 PRIMARY KEY (`c1`),
221 KEY `c2d` (`c2`)
222 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=1
223 connection default;
224 SET @merge_encrypt_0=
225 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
226 WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
227 SET @merge_decrypt_0=
228 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
229 WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
230 SET @rowlog_encrypt_0=
231 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
232 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
233 connection con1;
234 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2e_created WAIT_FOR dml2_done';
235 SET lock_wait_timeout = 10;
236 ALTER TABLE t1 CHANGE c2 c22 INT, DROP INDEX c2d, ADD INDEX c2e(c22, c3(10)), ALGORITHM = NOCOPY;
237 connection default;
238 INSERT INTO t1 SELECT 80 + c1, c2, c3 FROM t1;
239 INSERT INTO t1 SELECT 160 + c1, c2, c3 FROM t1;
240 SET DEBUG_SYNC = 'now WAIT_FOR c2e_created';
241 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
242 name count
243 ddl_background_drop_indexes 0
244 ddl_online_create_index 1
245 ddl_pending_alter_table 1
246 ddl_sort_file_alter_table 0
247 ddl_log_file_alter_table 0
248 UPDATE t1 SET c2 = c2 + 1;
249 UPDATE t1 SET c2 = c2 + 2;
250 UPDATE t1 SET c2 = c2 + 1;
251 UPDATE t1 SET c2 = c2 + 2;
252 UPDATE t1 SET c2 = c2 + 1;
253 UPDATE t1 SET c2 = c2 + 2;
254 UPDATE t1 SET c2 = c2 + 1;
255 UPDATE t1 SET c2 = c2 + 2;
256 UPDATE t1 SET c2 = c2 + 1;
257 UPDATE t1 SET c2 = c2 + 2;
258 UPDATE t1 SET c2 = c2 + 1;
259 UPDATE t1 SET c2 = c2 + 2;
260 UPDATE t1 SET c2 = c2 + 1;
261 UPDATE t1 SET c2 = c2 + 2;
262 UPDATE t1 SET c2 = c2 + 1;
263 UPDATE t1 SET c2 = c2 + 2;
264 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
265 name count
266 ddl_background_drop_indexes 0
267 ddl_online_create_index 1
268 ddl_pending_alter_table 1
269 ddl_sort_file_alter_table 0
270 ddl_log_file_alter_table 1
271 SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES si
272 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS sf
273 ON si.index_id = sf.index_id WHERE si.name = '?c2e';
274 name pos
275 c2 0
276 c3 1
277 SET @merge_encrypt_1=
278 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
279 WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
280 SET @merge_decrypt_1=
281 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
282 WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
283 SET @rowlog_encrypt_1=
284 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
285 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
286 SELECT
287 (@merge_encrypt_1-@merge_encrypt_0)-
288 (@merge_decrypt_1-@merge_decrypt_0) as sort_balance,
289 @merge_encrypt_1>@merge_encrypt_0, @merge_decrypt_1>@merge_decrypt_0,
290 @rowlog_encrypt_1>@rowlog_encrypt_0;
291 sort_balance @merge_encrypt_1>@merge_encrypt_0 @merge_decrypt_1>@merge_decrypt_0 @rowlog_encrypt_1>@rowlog_encrypt_0
292 0 0 0 0
293 SET DEBUG_SYNC = 'now SIGNAL dml2_done';
294 connection con1;
295 ERROR HY000: Creating index 'c2e' required more than 'innodb_online_alter_log_max_size' bytes of modification log. Please try again
296 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
297 name count
298 ddl_background_drop_indexes 1
299 ddl_online_create_index 1
300 ddl_pending_alter_table 0
301 ddl_sort_file_alter_table 0
302 ddl_log_file_alter_table 1
303 SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES si
304 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS sf
305 ON si.index_id = sf.index_id WHERE si.name = 'c2e';
306 name pos
307 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
308 name count
309 ddl_background_drop_indexes 1
310 ddl_online_create_index 1
311 ddl_pending_alter_table 0
312 ddl_sort_file_alter_table 0
313 ddl_log_file_alter_table 1
314 connection default;
315 ALTER TABLE t1 COMMENT 'testing if c2e will be dropped';
316 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
317 name count
318 ddl_background_drop_indexes 0
319 ddl_online_create_index 0
320 ddl_pending_alter_table 0
321 ddl_sort_file_alter_table 0
322 ddl_log_file_alter_table 1
323 SET @merge_encrypt_1=
324 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
325 WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
326 SET @merge_decrypt_1=
327 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
328 WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
329 SET @rowlog_encrypt_1=
330 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
331 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
332 SET @rowlog_decrypt_1=
333 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
334 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_decrypted');
335 connection con1;
336 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c2f_created WAIT_FOR dml3_done';
337 ALTER TABLE t1 ADD INDEX c2f(c22f), CHANGE c2 c22f INT;
338 connection default;
339 SET DEBUG_SYNC = 'now WAIT_FOR c2f_created';
340 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
341 name count
342 ddl_background_drop_indexes 0
343 ddl_online_create_index 1
344 ddl_pending_alter_table 1
345 ddl_sort_file_alter_table 0
346 ddl_log_file_alter_table 1
347 BEGIN;
348 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
349 DELETE FROM t1 WHERE c1 > 320;
350 COMMIT;
351 BEGIN;
352 UPDATE t1 SET c2 = c2 + 1;
353 COMMIT;
354 BEGIN;
355 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
356 DELETE FROM t1 WHERE c1 > 320;
357 COMMIT;
358 BEGIN;
359 UPDATE t1 SET c2 = c2 + 1;
360 COMMIT;
361 BEGIN;
362 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
363 DELETE FROM t1 WHERE c1 > 320;
364 COMMIT;
365 BEGIN;
366 UPDATE t1 SET c2 = c2 + 1;
367 COMMIT;
368 BEGIN;
369 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
370 DELETE FROM t1 WHERE c1 > 320;
371 COMMIT;
372 BEGIN;
373 UPDATE t1 SET c2 = c2 + 1;
374 COMMIT;
375 BEGIN;
376 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
377 DELETE FROM t1 WHERE c1 > 320;
378 COMMIT;
379 BEGIN;
380 UPDATE t1 SET c2 = c2 + 1;
381 COMMIT;
382 BEGIN;
383 INSERT INTO t1 SELECT 320 + c1, c2, c3 FROM t1 WHERE c1 > 160;
384 DELETE FROM t1 WHERE c1 > 320;
385 COMMIT;
386 BEGIN;
387 UPDATE t1 SET c2 = c2 + 1;
388 COMMIT;
389 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
390 name count
391 ddl_background_drop_indexes 0
392 ddl_online_create_index 1
393 ddl_pending_alter_table 1
394 ddl_sort_file_alter_table 0
395 ddl_log_file_alter_table 2
396 SET DEBUG_SYNC = 'now SIGNAL dml3_done';
397 connection con1;
398 Warnings:
399 Note 1831 Duplicate index `c2f`. This is deprecated and will be disallowed in a future release
400 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
401 name count
402 ddl_background_drop_indexes 0
403 ddl_online_create_index 0
404 ddl_pending_alter_table 0
405 ddl_sort_file_alter_table 0
406 ddl_log_file_alter_table 2
407 connection default;
408 SET @merge_encrypt_2=
409 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
410 WHERE variable_name = 'innodb_encryption_n_merge_blocks_encrypted');
411 SET @merge_decrypt_2=
412 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
413 WHERE variable_name = 'innodb_encryption_n_merge_blocks_decrypted');
414 SET @rowlog_encrypt_2=
415 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
416 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_encrypted');
417 SET @rowlog_decrypt_2=
418 (SELECT CAST(variable_value AS INTEGER) FROM information_schema.global_status
419 WHERE variable_name = 'innodb_encryption_n_rowlog_blocks_decrypted');
420 SELECT
421 (@merge_encrypt_2-@merge_encrypt_1)-
422 (@merge_decrypt_2-@merge_decrypt_1) as sort_balance,
423 (@rowlog_encrypt_2-@rowlog_encrypt_1)-
424 (@rowlog_decrypt_2-@rowlog_decrypt_1) as log_balance;
425 sort_balance log_balance
426 0 0
427 SELECT
428 @merge_encrypt_2-@merge_encrypt_1>0 as sort_encrypted,
429 @merge_decrypt_2-@merge_decrypt_1>0 as sort_decrypted,
430 @rowlog_encrypt_2-@rowlog_encrypt_1>0 as log_encrypted,
431 @rowlog_decrypt_2-@rowlog_decrypt_1>0 as log_decrypted;
432 sort_encrypted sort_decrypted log_encrypted log_decrypted
433 0 0 0 0
434 connection con1;
435 SELECT COUNT(c22f) FROM t1;
436 COUNT(c22f)
437 320
438 CHECK TABLE t1;
439 Table Op Msg_type Msg_text
440 test.t1 check status OK
441 SET GLOBAL innodb_max_purge_lag_wait=0;
442 ALTER TABLE t1 ADD UNIQUE INDEX c3p5(c3(5));
443 ERROR 23000: Duplicate entry 'NULL' for key 'c3p5'
444 UPDATE t1 SET c3 = NULL WHERE c3 = '';
445 SET lock_wait_timeout = 1;
446 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL c3p5_created WAIT_FOR ins_done';
447 ALTER TABLE t1 ADD UNIQUE INDEX c3p5(c3(5));
448 connection default;
449 SET DEBUG_SYNC = 'now WAIT_FOR c3p5_created';
450 SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES si
451 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS sf
452 ON si.index_id = sf.index_id WHERE si.name = '?c3p5';
453 name pos
454 c3 0
455 SET DEBUG_SYNC = 'ib_after_row_insert SIGNAL ins_done WAIT_FOR ddl_timed_out';
456 INSERT INTO t1 VALUES(347,33101,NULL);
457 connection con1;
458 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
459 SET DEBUG_SYNC = 'now SIGNAL ddl_timed_out';
460 SELECT sf.name, sf.pos FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES si
461 INNER JOIN INFORMATION_SCHEMA.INNODB_SYS_FIELDS sf
462 ON si.index_id = sf.index_id WHERE si.name = 'c3p5';
463 name pos
464 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
465 name count
466 ddl_background_drop_indexes 1
467 ddl_online_create_index 1
468 ddl_pending_alter_table 0
469 ddl_sort_file_alter_table 0
470 ddl_log_file_alter_table 2
471 connection default;
472 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
473 name count
474 ddl_background_drop_indexes 1
475 ddl_online_create_index 1
476 ddl_pending_alter_table 0
477 ddl_sort_file_alter_table 0
478 ddl_log_file_alter_table 2
479 connection default;
480 SHOW CREATE TABLE t1;
481 Table Create Table
482 t1 CREATE TABLE `t1` (
483 `c1` int(11) NOT NULL,
484 `c22f` int(11) DEFAULT NULL,
485 `c3` text DEFAULT NULL,
486 PRIMARY KEY (`c1`),
487 KEY `c2d` (`c22f`),
488 KEY `c2f` (`c22f`)
489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci STATS_PERSISTENT=1 COMMENT='testing if c2e will be dropped'
490 ALTER TABLE t1 DROP INDEX c2d, DROP INDEX c2f;
491 SELECT name, count FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE subsystem = 'ddl';
492 name count
493 ddl_background_drop_indexes 0
494 ddl_online_create_index 0
495 ddl_pending_alter_table 0
496 ddl_sort_file_alter_table 0
497 ddl_log_file_alter_table 2
498 ALTER TABLE t1 ADD INDEX c2h(c22f), ALGORITHM = INPLACE;
499 ALTER TABLE t1 ADD INDEX c2h(c22f), ALGORITHM = COPY;
500 ERROR 42000: Duplicate key name 'c2h'
501 SET DEBUG_SYNC = 'RESET';
502 SET GLOBAL innodb_monitor_disable = module_ddl;
503 DROP TABLE t1;
504 #
505 # MDEV-13205 assertion !dict_index_is_online_ddl(index) upon ALTER TABLE
506 #
507 CREATE TABLE t1 (c VARCHAR(64)) ENGINE=InnoDB;
508 INSERT INTO t1 VALUES('foo');
509 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL t1u_created WAIT_FOR dup_done';
510 ALTER TABLE t1 ADD UNIQUE(c);
511 connection con1;
512 SET DEBUG_SYNC = 'now WAIT_FOR t1u_created';
513 INSERT INTO t1 VALUES('bar'),('bar');
514 SET DEBUG_SYNC = 'now SIGNAL dup_done';
515 connection default;
516 ERROR 23000: Duplicate entry 'bar' for key 'c'
517 SET DEBUG_SYNC = 'RESET';
518 disconnect con1;
519 CREATE TABLE t2 (c VARCHAR(64)) ENGINE=InnoDB;
520 ALTER TABLE t2 ADD FOREIGN KEY (c) REFERENCES t1 (c);
521 ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
522 DROP TABLE t2,t1;
523 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB;
524 INSERT INTO t1 VALUES(0,0);
525 connect con1,localhost,root,,;
526 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL created WAIT_FOR inserted';
527 ALTER TABLE t1 ADD INDEX(b);
528 connection default;
529 SET DEBUG_SYNC = 'now WAIT_FOR created';
530 BEGIN;
531 INSERT INTO t1 VALUES(1,1);
532 ROLLBACK;
533 SET DEBUG_SYNC = 'now SIGNAL inserted';
534 connection con1;
535 connection default;
536 SELECT * FROM t1;
537 a b
538 0 0
539 CHECK TABLE t1;
540 Table Op Msg_type Msg_text
541 test.t1 check status OK
542 DROP TABLE t1;
543 #
544 # MDEV-36281 DML aborts during online virtual index
545 #
546 CREATE TABLE t1(f1 INT NOT NULL PRIMARY KEY, f2 INT NOT NULL,
547 f3 INT NOT NULL, f4 INT AS (f3) VIRTUAL,
548 f5 INT AS (f1) VIRTUAL, INDEX(f4))ENGINE=InnoDB;
549 INSERT INTO t1(f1, f2, f3) VALUES(1, 2, 3);
550 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL dml_start WAIT_FOR dml_finish';
551 ALTER TABLE t1 ADD INDEX v1(f5, f2, f4), ADD INDEX v2(f3, f5);
552 connection con1;
553 set DEBUG_SYNC="now WAIT_FOR dml_start";
554 UPDATE t1 SET f3= f3 + 1;
555 set DEBUG_SYNC="now SIGNAL dml_finish";
556 disconnect con1;
557 connection default;
558 CHECK TABLE t1 EXTENDED;
559 Table Op Msg_type Msg_text
560 test.t1 check status OK
561 SELECT f5, f2, f4 FROM t1 USE INDEX(v1);
562 f5 f2 f4
563 1 2 4
564 SELECT f3, f5 FROM t1 USE INDEX(v2);
565 f3 f5
566 4 1
567 DROP TABLE t1;
568 SET DEBUG_SYNC = 'RESET';
569 SET GLOBAL innodb_monitor_enable = default;
570 SET GLOBAL innodb_monitor_disable = default;