Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_debug.inc
3 --source include/have_debug_sync.inc
4 --source include/have_sequence.inc
5
6 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
7 SET GLOBAL innodb_stats_persistent = 0;
8
9 SET @old_instant=
10 (SELECT variable_value FROM information_schema.global_status
11 WHERE variable_name = 'innodb_instant_alter_column');
12
13 CREATE TABLE t1 (
14 pk INT AUTO_INCREMENT PRIMARY KEY,
15 c1 INT,
16 c2 VARCHAR(255),
17 c3 VARCHAR(255),
18 c4 INT,
19 c5 INT,
20 c6 INT,
21 c7 VARCHAR(255),
22 c8 TIMESTAMP NULL
23 ) ENGINE=InnoDB;
24 INSERT INTO t1 VALUES (NULL,1,NULL,'foo',NULL,1,NULL,NULL,'2011-11-11 00:00:00');
25 ALTER TABLE t1 ADD COLUMN f INT;
26 REPLACE INTO t1 (c7) VALUES ('bar');
27
28 CREATE TABLE t2 (i INT PRIMARY KEY) ENGINE=InnoDB;
29 INSERT INTO t2 VALUES (-1),(1);
30 ALTER TABLE t2 ADD COLUMN j INT;
31 BEGIN;
32 DELETE FROM t2;
33 ROLLBACK;
34 TRUNCATE TABLE t2;
35 INSERT INTO t2 VALUES (1,2);
36
37 CREATE TABLE t3 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=InnoDB;
38 INSERT INTO t3 () VALUES ();
39 ALTER TABLE t3 ADD COLUMN f INT;
40 UPDATE t3 SET pk = DEFAULT;
41 SELECT * FROM t3;
42
43 CREATE TABLE t4 (pk INT PRIMARY KEY) ENGINE=InnoDB;
44 INSERT INTO t4 VALUES (0);
45 ALTER TABLE t4 ADD COLUMN b INT;
46 SELECT COUNT(*)>0 FROM INFORMATION_SCHEMA.COLUMNS
47 LEFT JOIN t4 ON (NUMERIC_SCALE = pk);
48 ALTER TABLE t4 ADD COLUMN c INT;
49
50 CREATE TABLE t5 (i INT, KEY(i)) ENGINE=InnoDB;
51 INSERT INTO t5 VALUES (-42);
52 ALTER TABLE t5 ADD UNIQUE ui(i);
53 ALTER TABLE t5 ADD COLUMN i2 INT, DROP INDEX i;
54
55 CREATE TABLE t6 (i INT NOT NULL) ENGINE=InnoDB;
56 INSERT INTO t6 VALUES (0);
57 ALTER TABLE t6 ADD COLUMN j INT;
58 TRUNCATE TABLE t6;
59 INSERT INTO t6 VALUES (1,2);
60
61 CREATE TABLE t7 (i INT) ENGINE=InnoDB;
62 INSERT INTO t7 VALUES (1),(2),(3),(4),(5);
63 ALTER TABLE t7 ADD t TEXT DEFAULT '';
64
65 CREATE TABLE t8 (i INT) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
66 INSERT INTO t8 VALUES (NULL);
67 ALTER TABLE t8 ADD c CHAR(3);
68 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR dml';
69 --send
70 ALTER TABLE t8 FORCE;
71 connect (dml,localhost,root,,);
72 SET DEBUG_SYNC='now WAIT_FOR rebuilt';
73 BEGIN;
74 INSERT INTO t8 SET i=1;
75 UPDATE t8 SET i=ISNULL(i);
76 ROLLBACK;
77 SET DEBUG_SYNC='now SIGNAL dml';
78 connection default;
79 reap;
80 SET DEBUG_SYNC='RESET';
81
82 CREATE TABLE t9 (
83 pk INT AUTO_INCREMENT PRIMARY KEY,
84 c1 BIGINT UNSIGNED,
85 c2 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
86 c3 BIGINT,
87 c4 VARCHAR(257) CHARACTER SET utf8,
88 c5 TINYINT UNSIGNED,
89 c6 TINYINT,
90 c7 VARCHAR(257) CHARACTER SET latin1,
91 c8 VARCHAR(257) CHARACTER SET binary
92 ) ENGINE=InnoDB;
93 INSERT INTO t9 () VALUES ();
94 ALTER TABLE t9 ADD COLUMN IF NOT EXISTS t TIMESTAMP NULL;
95 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL rebuilt WAIT_FOR dml';
96 --send
97 OPTIMIZE TABLE t9;
98 connection dml;
99 SET DEBUG_SYNC='now WAIT_FOR rebuilt';
100 BEGIN;
101 INSERT INTO t9 () VALUES (),();
102 UPDATE t9 SET t=current_timestamp();
103 ROLLBACK;
104 SET DEBUG_SYNC='now SIGNAL dml';
105 disconnect dml;
106 connection default;
107 reap;
108 SET DEBUG_SYNC='RESET';
109
110 CREATE TABLE t10 (pk INT DEFAULT 0 KEY) ENGINE=InnoDB;
111 INSERT INTO t10 (pk) VALUES (1);
112 ALTER TABLE t10 ADD c INT;
113 TRUNCATE TABLE t10;
114 INSERT INTO t10 VALUES (1,1),(2,2);
115 ALTER TABLE t10 FORCE;
116
117 CREATE TABLE t11 (
118 c01 enum('a','b'),
119 c02 bit,
120 c03 blob,
121 c04 enum('c','d'),
122 c05 blob,
123 c06 decimal,
124 c07 char(1),
125 c08 int,
126 c09 char(1),
127 c10 set('e','f'),
128 c11 char(1),
129 c12 float,
130 c13 bit,
131 c14 char(1),
132 c15 int,
133 c16 float,
134 c17 decimal,
135 c18 char(1) CHARACTER SET utf8 not null default '',
136 c19 float,
137 c20 set('g','h'),
138 c21 char(1),
139 c22 int,
140 c23 int,
141 c24 int,
142 c25 set('i','j'),
143 c26 decimal,
144 c27 float,
145 c28 char(1),
146 c29 int,
147 c30 enum('k','l'),
148 c31 decimal,
149 c32 char(1),
150 c33 decimal,
151 c34 bit,
152 c35 enum('m','n'),
153 c36 set('o','p'),
154 c37 enum('q','r'),
155 c38 blob,
156 c39 decimal,
157 c40 blob not null default '',
158 c41 char(1),
159 c42 int,
160 c43 float,
161 c44 float,
162 c45 enum('s','t'),
163 c46 decimal,
164 c47 set('u','v'),
165 c48 enum('w','x'),
166 c49 set('y','z'),
167 c50 float
168 ) ENGINE=InnoDB;
169 INSERT INTO t11 () VALUES ();
170 ALTER TABLE t11 ADD COLUMN f INT;
171 INSERT INTO t11 () VALUES ();
172 UPDATE t11 SET c22 = 1;
173
174 --source include/wait_all_purged.inc
175 DROP TABLE t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11;
176
177 --echo #
178 --echo # MDEV-15060 Assertion in row_log_table_apply_op after instant ADD
179 --echo # when the table is emptied during subsequent ALTER TABLE
180 --echo #
181
182 CREATE TABLE t1 (a INT) ENGINE=InnoDB;
183 INSERT INTO t1 VALUES (NULL);
184 ALTER TABLE t1 ADD COLUMN b INT NOT NULL;
185 connect stop_purge,localhost,root;
186 START TRANSACTION WITH CONSISTENT SNAPSHOT;
187 connect ddl,localhost,root,,test;
188 DELETE FROM t1;
189 INSERT INTO t1 VALUES(1, 2);
190 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
191 send ALTER TABLE t1 FORCE;
192 connection default;
193 SET DEBUG_SYNC='now WAIT_FOR copied';
194
195 BEGIN;
196 INSERT INTO t1 SET b=1;
197 ROLLBACK;
198 connection stop_purge;
199 COMMIT;
200 connection default;
201
202 # Wait for purge to empty the table.
203 let $wait_all_purged=1;
204 --source include/wait_all_purged.inc
205 let $wait_all_purged=0;
206
207 SET DEBUG_SYNC='now SIGNAL logged';
208 connection ddl;
209 reap;
210 connection default;
211 DROP TABLE t1;
212 SET DEBUG_SYNC='RESET';
213
214 --echo #
215 --echo # MDEV-16131 Assertion failed in dict_index_t::instant_field_value()
216 --echo #
217 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
218 INSERT INTO t1 SET a=0;
219 ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 2, ADD COLUMN c INT;
220
221 DELIMITER $$;
222 BEGIN NOT ATOMIC
223 DECLARE c TEXT DEFAULT(SELECT CONCAT('ALTER TABLE t1 ADD (c',
224 GROUP_CONCAT(seq SEPARATOR ' INT, c'), ' INT), ALGORITHM=INSTANT;') FROM seq_1_to_130);
225 EXECUTE IMMEDIATE c;
226 END;
227 $$
228 DELIMITER ;$$
229
230 connection stop_purge;
231 START TRANSACTION WITH CONSISTENT SNAPSHOT;
232
233 connection default;
234 DELETE FROM t1;
235
236 connection ddl;
237 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
238 send ALTER TABLE t1 FORCE;
239
240 connection default;
241 SET DEBUG_SYNC = 'now WAIT_FOR copied';
242 connection stop_purge;
243 COMMIT;
244 connection default;
245 let $wait_all_purged = 1;
246 --source include/wait_all_purged.inc
247 let $wait_all_purged = 0;
248 INSERT INTO t1 SET a=1;
249 INSERT INTO t1 SET a=2,b=3,c=4;
250 SET DEBUG_SYNC = 'now SIGNAL logged';
251
252 connection ddl;
253 reap;
254
255 connection default;
256 SET DEBUG_SYNC = RESET;
257 SELECT a, b, c FROM t1;
258 ALTER TABLE t1 DROP b, ALGORITHM=INSTANT;
259 connection stop_purge;
260 START TRANSACTION WITH CONSISTENT SNAPSHOT;
261
262 connection default;
263 DELETE FROM t1;
264
265 connection ddl;
266 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
267 send ALTER TABLE t1 ADD COLUMN b INT NOT NULL DEFAULT 2 AFTER a, FORCE;
268
269 connection default;
270 SET DEBUG_SYNC = 'now WAIT_FOR copied';
271 disconnect stop_purge;
272 let $wait_all_purged = 1;
273 --source include/wait_all_purged.inc
274 let $wait_all_purged = 0;
275 INSERT INTO t1 SET a=1;
276 INSERT INTO t1 SET a=2,c=4;
277 SET DEBUG_SYNC = 'now SIGNAL logged';
278
279 connection ddl;
280 reap;
281 UPDATE t1 SET b = b + 1 WHERE a = 2;
282
283 connection default;
284 SET DEBUG_SYNC = RESET;
285 SELECT a, b, c FROM t1;
286
287 --echo #
288 --echo # MDEV-15872 Crash in online ALTER TABLE...ADD PRIMARY KEY
289 --echo # after instant ADD COLUMN ... NULL
290 --echo #
291 ALTER TABLE t1 ADD COLUMN d INT, ALGORITHM=INSTANT;
292 UPDATE t1 SET d=1;
293
294 connection ddl;
295 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL copied WAIT_FOR logged';
296 send ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (a,d);
297
298 connection default;
299 SET DEBUG_SYNC = 'now WAIT_FOR copied';
300 BEGIN;
301 INSERT INTO t1 SET a=3;
302 ROLLBACK;
303 SET DEBUG_SYNC = 'now SIGNAL logged';
304
305 connection ddl;
306 reap;
307 disconnect ddl;
308
309 connection default;
310 SET DEBUG_SYNC = RESET;
311 SELECT a, b, c, d FROM t1;
312 DROP TABLE t1;
313
314 --echo #
315 --echo # MDEV-19916 Corruption after instant ADD/DROP and shrinking the tree
316 --echo #
317 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
318
319 # Create an index tree with 2 levels of node pointer pages.
320
321 SET @old_limit = @@innodb_limit_optimistic_insert_debug;
322 SET GLOBAL innodb_limit_optimistic_insert_debug = 2;
323 INSERT INTO t1 VALUES (1),(5),(4),(3),(2);
324 SET GLOBAL innodb_limit_optimistic_insert_debug = @old_limit;
325
326 ALTER TABLE t1 ADD COLUMN b INT, ALGORITHM=INSTANT;
327
328 # Exploit MDEV-17468 to force the table definition to be reloaded
329 ALTER TABLE t1 ADD vb INT AS (b) VIRTUAL;
330 CHECK TABLE t1;
331 SELECT * FROM t1;
332 DROP TABLE t1;
333
334 --echo #
335 --echo # MDEV-21045 AddressSanitizer: use-after-poison in mem_heap_dup / row_log_table_get_pk_col
336 --echo #
337 CREATE TABLE t1 (a TEXT) ENGINE = InnoDB ROW_FORMAT=REDUNDANT;
338 INSERT INTO t1 (a) VALUES ('foo');
339
340 ALTER TABLE t1 ADD COLUMN b INT DEFAULT 0,algorithm=instant;
341
342 --connect (con2,localhost,root,,test)
343 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL onlinealter WAIT_FOR update';
344 --send
345 ALTER TABLE t1 ADD PRIMARY KEY (b);
346
347 --connection default
348 SET DEBUG_SYNC='now WAIT_FOR onlinealter';
349 UPDATE t1 SET b = 1;
350 SET DEBUG_SYNC='now SIGNAL update';
351
352 --connection con2
353 --reap
354
355 --connection default
356 DROP TABLE t1;
357
358 --echo #
359 --echo # MDEV-21658 Error on online ADD PRIMARY KEY after instant DROP/reorder
360 --echo #
361
362 CREATE TABLE t1 (a INT, b INT, c INT, col INT) ENGINE=InnoDB;
363 INSERT INTO t1 () VALUES ();
364 ALTER TABLE t1 DROP b, DROP c, DROP col;
365 ALTER TABLE t1 ADD COLUMN col INT;
366 ALTER TABLE t1 DROP a, DROP col, ADD COLUMN b INT;
367
368 --connection con2
369 SET SQL_MODE= '';
370 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL scanned WAIT_FOR dml';
371 send ALTER TABLE t1 ADD PRIMARY KEY(b);
372
373 --connection default
374 SET DEBUG_SYNC = 'now WAIT_FOR scanned';
375 UPDATE t1 SET b = 1;
376 SET DEBUG_SYNC = 'now SIGNAL dml';
377 --connection con2
378 reap;
379 --connection default
380 SELECT * FROM t1;
381
382 SET DEBUG_SYNC='RESET';
383 --disconnect con2
384 DROP TABLE t1;
385
386 --echo #
387 --echo # MDEV-24653 Assertion block->page.id.page_no() == index->page failed
388 --echo # in innobase_add_instant_try()
389 --echo #
390
391 SET @saved_limit = @@GLOBAL.innodb_limit_optimistic_insert_debug;
392 SET GLOBAL innodb_limit_optimistic_insert_debug = 2;
393
394 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
395 INSERT INTO t1 VALUES (1),(2),(3),(4);
396 ALTER TABLE t1 ADD COLUMN b INT;
397 DELETE FROM t1;
398 --source include/wait_all_purged.inc
399 ALTER TABLE t1 ADD COLUMN c INT;
400
401 SELECT * FROM t1;
402 DROP TABLE t1;
403 SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit;
404
405 --echo #
406 --echo # MDEV-24796 Assertion page_has_next... failed
407 --echo # in btr_pcur_store_position()
408 --echo #
409
410 CREATE TABLE t1 (c INT KEY) ENGINE=InnoDB;
411 INSERT INTO t1 VALUES(1),(2);
412 SET GLOBAL innodb_limit_optimistic_insert_debug=2;
413 ALTER TABLE t1 ADD COLUMN d INT;
414 DELETE FROM t1;
415 --source include/wait_all_purged.inc
416 SELECT * FROM t1 WHERE c<>1 ORDER BY c DESC;
417 DROP TABLE t1;
418
419 SET GLOBAL innodb_limit_optimistic_insert_debug = @saved_limit;
420
421 --echo #
422 --echo # MDEV-24620 ASAN heap-buffer-overflow in btr_pcur_restore_position()
423 --echo #
424
425 CREATE TABLE t1 (a VARCHAR(1) PRIMARY KEY) ENGINE=InnoDB;
426 INSERT INTO t1 VALUES (1);
427 connect (stop_purge,localhost,root,,);
428 START TRANSACTION WITH CONSISTENT SNAPSHOT;
429
430 connection default;
431 ALTER TABLE t1 ADD c INT;
432 BEGIN;
433 DELETE FROM t1;
434
435 connect (dml,localhost,root,,test);
436 SET DEBUG_SYNC='row_mysql_handle_errors SIGNAL s1 WAIT_FOR s2';
437 send UPDATE t1 SET c=1;
438
439 connection default;
440 SET DEBUG_SYNC='now WAIT_FOR s1';
441 COMMIT;
442
443 connection stop_purge;
444 COMMIT;
445 disconnect stop_purge;
446
447 connection default;
448 let $wait_all_purged = 1;
449 --source include/wait_all_purged.inc
450 let $wait_all_purged = 0;
451 SET DEBUG_SYNC='now SIGNAL s2';
452
453 connection dml;
454 reap;
455 disconnect dml;
456
457 connection default;
458 --source include/wait_all_purged.inc
459 SET DEBUG_SYNC=RESET;
460 DROP TABLE t1;
461
462 --echo # End of 10.3 tests
463
464 --echo #
465 --echo # MDEV-17899 Assertion failures on rollback of instant ADD/DROP
466 --echo # MDEV-18098 Crash after rollback of instant DROP COLUMN
467 --echo #
468
469 SET @save_dbug = @@SESSION.debug_dbug;
470 SET debug_dbug='+d,ib_commit_inplace_fail_1';
471 CREATE TABLE t1 (a int, b int) ENGINE=InnoDB;
472 INSERT INTO t1 VALUES (1,2);
473 --error ER_INTERNAL_ERROR
474 ALTER TABLE t1 DROP COLUMN b;
475 --error ER_INTERNAL_ERROR
476 ALTER TABLE t1 DROP COLUMN b;
477 --error ER_INTERNAL_ERROR
478 ALTER TABLE t1 ADD COLUMN c INT;
479 SELECT * FROM t1;
480 DROP TABLE t1;
481
482 CREATE TABLE t1 (a int, b int) ENGINE=InnoDB;
483 --error ER_INTERNAL_ERROR
484 ALTER TABLE t1 ADD COLUMN c INT;
485 BEGIN;
486 INSERT INTO t1 VALUES(1, 1);
487 ROLLBACK;
488 --error ER_INTERNAL_ERROR
489 ALTER TABLE t1 DROP COLUMN b;
490 INSERT INTO t1 values (1,1);
491 SELECT * FROM t1;
492 DROP TABLE t1;
493
494 SET debug_dbug = @save_dbug;
495
496 --echo #
497 --echo # MDEV-24512 Assertion failed in rec_is_metadata()
498 --echo # in btr_discard_only_page_on_level()
499 --echo #
500
501 SET @save_limit= @@GLOBAL.innodb_limit_optimistic_insert_debug;
502 SET GLOBAL innodb_limit_optimistic_insert_debug=2;
503 CREATE TABLE t1 (c CHAR(1) UNIQUE) ENGINE=InnoDB;
504
505 ALTER TABLE t1 ADD c2 INT NOT NULL DEFAULT 0 FIRST;
506 --error ER_DUP_ENTRY
507 INSERT INTO t1 (c) VALUES ('x'),('d'),('r'),('f'),('y'),('u'),('m'),('d');
508 SET GLOBAL innodb_limit_optimistic_insert_debug=@save_limit;
509 SELECT * FROM t1;
510 DROP TABLE t1;
511
512 --echo #
513 --echo # MDEV-25236 Online log apply fails for ROW_FORMAT=REDUNDANT tables
514 --echo #
515
516 CREATE TABLE t1
517 (a INT NOT NULL, b INT, c INT, d INT, e INT, f INT, g INT, h INT, i TEXT)
518 ENGINE=InnoDB;
519 INSERT INTO t1 VALUES(1, 2, 3, 4, 5, 6, 7, 8, "test");
520 ALTER TABLE t1 MODIFY a INT NULL;
521
522 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL alter WAIT_FOR go';
523 send ALTER TABLE t1 ADD PRIMARY KEY (a);
524 connect(con1,localhost,root,,);
525 set DEBUG_SYNC='now WAIT_FOR alter';
526 BEGIN;
527 INSERT INTO t1 SET a=0, i=REPEAT('1', 10000);
528 ROLLBACK;
529 set DEBUG_SYNC='now SIGNAL go';
530 connection default;
531 reap;
532
533 SELECT * FROM t1;
534 DROP TABLE t1;
535 SET DEBUG_SYNC=RESET;
536
537 --echo #
538 --echo # MDEV-27962 Instant DDL downgrades the MDL when table is empty
539 --echo #
540 CREATE TABLE t1(f1 INT NOT NULL, f2 INT NOT NULL)ENGINE=InnoDB;
541 SET DEBUG_SYNC="alter_table_inplace_after_lock_downgrade SIGNAL try_insert WAIT_FOR alter_progress";
542 send ALTER TABLE t1 ADD INDEX(f1), ADD INDEX(f2);
543 connection con1;
544 SET SESSION lock_wait_timeout=1;
545 SET DEBUG_SYNC="now WAIT_FOR try_insert";
546 --error ER_LOCK_WAIT_TIMEOUT
547 INSERT INTO t1 VALUES(1, 2);
548 SET DEBUG_SYNC="now SIGNAL alter_progress";
549 connection default;
550 reap;
551 DROP TABLE t1;
552
553 --echo #
554 --echo # MDEV-26198 Assertion `0' failed in row_log_table_apply_op during
555 --echo # ADD PRIMARY KEY or OPTIMIZE TABLE
556 --echo #
557 CREATE TABLE t1(f1 year default null, f2 year default null,
558 f3 text, f4 year default null, f5 year default null,
559 f6 year default null, f7 year default null,
560 f8 year default null)ENGINE=InnoDB;
561 INSERT INTO t1 VALUES(1, 1, 1, 1, 1, 1, 1, 1);
562 ALTER TABLE t1 ADD COLUMN f9 year default null, ALGORITHM=INPLACE;
563 set DEBUG_SYNC="row_log_table_apply1_before SIGNAL con1_insert WAIT_FOR con1_finish";
564 send ALTER TABLE t1 ADD COLUMN f10 YEAR DEFAULT NULL, FORCE, ALGORITHM=INPLACE;
565
566 connection con1;
567 SET DEBUG_SYNC="now WAIT_FOR con1_insert";
568 INSERT IGNORE INTO t1 (f3) VALUES ( 'b' );
569 INSERT IGNORE INTO t1 (f3) VALUES ( 'l' );
570 SET DEBUG_SYNC="now SIGNAL con1_finish";
571
572 connection default;
573 reap;
574 CHECK TABLE t1;
575 DROP TABLE t1;
576
577 --echo #
578 --echo # MDEV-19044 Alter table corrupts while applying the
579 --echo # modification log
580 --echo #
581 CREATE TABLE t1 (
582 f1 INT,
583 f2 INT,
584 f3 char(19) CHARACTER SET utf8mb3,
585 f4 VARCHAR(500),
586 f5 TEXT)ENGINE=InnoDB;
587 INSERT INTO t1 VALUES(3, 1, REPEAT('a', 2), REPEAT("b", 20),'a');
588 ALTER TABLE t1 ADD COLUMN f6 INT NOT NULL, ALGORITHM=INSTANT;
589 INSERT INTO t1 VALUES(1, 2, REPEAT('InnoDB', 2),
590 REPEAT("MariaDB", 20), REPEAT('a', 8000), 12);
591 INSERT INTO t1 VALUES(1, 2, REPEAT('MYSQL', 2),
592 REPEAT("MariaDB", 20), REPEAT('a', 8000), 12);
593 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL con1_begin WAIT_FOR con1_update';
594 send ALTER TABLE t1 MODIFY COLUMN f2 INT NOT NULL, FORCE, ALGORITHM=INPLACE;
595 connection con1;
596 SET DEBUG_SYNC='now WAIT_FOR con1_begin';
597 UPDATE t1 SET f2=204 order by f1 limit 2;
598 SET DEBUG_SYNC='now SIGNAL con1_update';
599 connection default;
600 reap;
601 disconnect con1;
602 SET DEBUG_SYNC=reset;
603 CHECK TABLE t1;
604 DROP TABLE t1;
605
606 --echo # End of 10.4 tests
607
608 --echo #
609 --echo # MDEV-22867 Assertion instant.n_core_fields == n_core_fields
610 --echo # in dict_index_t::instant_add_field
611 --echo #
612
613 CREATE TABLE t1 (a INT PRIMARY KEY, b INT UNIQUE) ENGINE=InnoDB;
614 INSERT INTO t1 SET a=1;
615
616 connect (prevent_purge,localhost,root,,);
617 START TRANSACTION WITH CONSISTENT SNAPSHOT;
618 connection default;
619 ALTER TABLE t1 ADD COLUMN c INT;
620 DELETE FROM t1;
621
622 connect (con2,localhost,root,,test);
623 # FIXME: If this is implemented then also i->online_log must be checked in
624 # dict_index_t::must_avoid_clear_instant_add(). See the comment there.
625 --error ER_ALTER_OPERATION_NOT_SUPPORTED
626 ALTER TABLE t1 DROP b, ADD INDEX(c), ALGORITHM=NOCOPY;
627 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL ddl WAIT_FOR emptied';
628 send ALTER TABLE t1 DROP b;
629
630 connection default;
631 SET DEBUG_SYNC='now WAIT_FOR ddl';
632 BEGIN;
633 INSERT INTO t1 SET a=1;
634
635 connection prevent_purge;
636 COMMIT;
637 disconnect prevent_purge;
638
639 connection default;
640 ROLLBACK;
641 SELECT * FROM t1;
642 SET DEBUG_SYNC='now SIGNAL emptied';
643
644 connection con2;
645 reap;
646 disconnect con2;
647 connection default;
648 ALTER TABLE t1 DROP c;
649 INSERT INTO t1 VALUES (2),(3),(4);
650 CHECK TABLE t1;
651 DROP TABLE t1;
652 SET DEBUG_SYNC=RESET;
653
654 --echo # End of 10.5 tests
655 SELECT variable_value-@old_instant instants
656 FROM information_schema.global_status
657 WHERE variable_name = 'innodb_instant_alter_column';
658
659 CREATE TABLE t1(f1 INT, f2 TEXT)ENGINE=InnoDB;
660 INSERT INTO t1 VALUES(1, 'a');
661 ALTER TABLE t1 ADD COLUMN f3 TEXT FIRST;
662 --error ER_INTERNAL_ERROR
663 SET STATEMENT DEBUG_DBUG="+d,instant_insert_fail" FOR
664 ALTER TABLE t1 DROP COLUMN f1;
665 ALTER TABLE t1 DROP COLUMN f1;
666 CHECK TABLE t1;
667 DROP TABLE t1;
668
669 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
670 --echo # End of 10.6 tests