Debian Codegraph @

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

raw
1 --source include/have_debug_sync.inc
2 --source include/have_debug.inc
3 --source include/count_sessions.inc
4 --source include/have_innodb_max_16k.inc
5
6 CREATE TABLE `t` (
7 `a` VARCHAR(100),
8 `b` VARCHAR(100),
9 `c` VARCHAR(200) GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
10 `h` VARCHAR(10) DEFAULT NULL,
11 `i` int
12 ) ENGINE=InnoDB;
13
14 INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 10), DEFAULT, "kk", 1);
15 INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2);
16
17 CREATE INDEX idx ON t(c(100));
18
19 SET session debug="+d,ib_alter_add_virtual_fail";
20 --error ER_WRONG_KEY_COLUMN
21 ALTER TABLE t ADD COLUMN x VARCHAR(200) GENERATED ALWAYS AS (a) VIRTUAL, ADD INDEX(h),
22 ALGORITHM = INPLACE;
23 --error ER_WRONG_KEY_COLUMN
24 ALTER TABLE t DROP COLUMN c, ADD INDEX(h), ALGORITHM = INPLACE;
25 SET session debug="-d,ib_alter_add_virtual_fail";
26 DROP TABLE t;
27
28 #online test
29 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
30
31 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
32 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
33 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
34 INSERT INTO t VALUES (null, null, DEFAULT, "mx");
35
36 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
37 --send CREATE INDEX idx ON t(c);
38
39 connect (con1,localhost,root,,);
40 connection con1;
41
42 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
43 update t set a=0 where a = 11;
44 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
45
46 connection default;
47 reap;
48
49 --sorted_result
50 SELECT c FROM t;
51 SHOW CREATE TABLE t;
52 SELECT * FROM t;
53
54 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
55 --send ALTER TABLE t ADD COLUMN x INT;
56
57 connection con1;
58
59 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
60 start transaction;
61 update t set a=1 where a = 0;
62 ROLLBACK;
63 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
64
65 connection default;
66 reap;
67
68 --sorted_result
69 SELECT c FROM t;
70
71 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
72 --send ALTER TABLE t ADD COLUMN x2 INT;
73
74 connection con1;
75
76 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
77 start transaction;
78 DELETE FROM t WHERE a = 0;
79 ROLLBACK;
80 DELETE FROM t WHERE a = 0;
81 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
82
83 connection default;
84 reap;
85
86 --sorted_result
87 SELECT c FROM t;
88
89 disconnect con1;
90 DROP TABLE t;
91
92 SET DEBUG_SYNC = 'RESET';
93
94
95 # Test add virtual column and add index at the same time
96 # introduce some error
97
98 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
99
100 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
101
102 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
103
104 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
105
106 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
107
108 CREATE INDEX idx_1 on t(c);
109
110 SET SESSION debug="+d,create_index_fail";
111
112 --enable_info
113 --error ER_DUP_ENTRY
114 ALTER TABLE t ADD COLUMN x INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (x);
115 SET SESSION debug="-d,create_index_fail";
116 --disable_info
117
118 SHOW CREATE TABLE t;
119
120 --sorted_result
121 SELECT c FROM t;
122
123 DROP TABLE t;
124
125
126 --echo #
127 --echo # Bug#22018532 ASSERTION WHEN ONLINE REAPPLY REBUILD LOG ON
128 --echo # MULTIPLE INDEXED VIRTUAL COLUMNS
129 --echo #
130
131 create table t (
132 a int as (1) virtual,
133 b int,
134 c int as (1) virtual,
135 unique(b),
136 unique(c),
137 key(a)
138 ) engine=innodb;
139
140 insert ignore into t values();
141
142 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
143 --send optimize table t
144
145 connect (con1,localhost,root,,);
146
147 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
148 insert ignore into t values();
149 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
150
151 connection default;
152 --echo /* connection default */ optimize table t;
153 reap;
154 --sorted_result
155 SELECT c FROM t;
156 SHOW CREATE TABLE t;
157 SELECT * FROM t;
158 DROP TABLE t;
159
160 # Do another test without duplicate error
161
162 CREATE TABLE t (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10));
163
164 INSERT INTO t VALUES (11, 3, DEFAULT, 'mm');
165 INSERT INTO t VALUES (18, 1, DEFAULT, 'mm');
166 INSERT INTO t VALUES (28, 1, DEFAULT, 'mm');
167 INSERT INTO t VALUES (null, null, DEFAULT, 'mm');
168
169 CREATE INDEX idx ON t(c);
170
171 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_rebuild WAIT_FOR go_ahead';
172 --send optimize table t
173
174 connection con1;
175 SET DEBUG_SYNC = 'now WAIT_FOR start_rebuild';
176 INSERT INTO t VALUES (48, 2, DEFAULT, 'xx');
177 INSERT INTO t VALUES (68, 3, DEFAULT, 'sx');
178 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
179
180 connection default;
181 --echo /* connection default */ optimize table t;
182 reap;
183
184 --sorted_result
185 SELECT c FROM t;
186
187 disconnect con1;
188
189 DROP TABLE t;
190 --echo #
191 --echo # Bug#22140944 WL#8149: ADD TEST FOR THE ROW0LOG.CC FIX THAT
192 --echo # WAS UNRELATED TO Bug#21894654
193 --echo #
194
195 CREATE TABLE t1 ( id INT ,a VARCHAR(20), b VARCHAR(20) ,c VARCHAR(40)
196 GENERATED ALWAYS AS (CONCAT(a,b)),key(c));
197 INSERT INTO t1 (id,a,b) VALUES (10,'aditya','hello');
198
199 connect (con1,localhost,root);
200 connection con1;
201
202 SET DEBUG_SYNC= 'ddl_after_scan SIGNAL opened WAIT_FOR go_ddl';
203 send ALTER TABLE t1 ADD CONSTRAINT pk1 PRIMARY KEY (id);
204
205 connection default;
206 SET DEBUG_SYNC= 'now WAIT_FOR opened';
207 UPDATE t1 SET a = 'fg', id = 20 WHERE id = 10;
208 SET DEBUG_SYNC= 'now SIGNAL go_ddl';
209
210 connection con1;
211 reap;
212 disconnect con1;
213
214 connection default;
215 SELECT * from t1;
216 DROP TABLE t1;
217
218 --echo #
219 --echo # Bug#22951879 - ASSERTS RELATED TO ONLINE DDL AND GCOL
220 --echo #
221
222 # Create a table with 2 virtual column, one (vbidxcol) is indexed and
223 # the other one (vbcol) is not
224 create table ibstd_14 (a int not null, d int not null, b varchar(198) not null, c char(181), vadcol int as (a+length(d)) stored, vbcol char(2) as (substr(b,2,2)) virtual, vbidxcol char(3) as (substr(b,1,3)) virtual , index(d), index(a), index(vbidxcol), index(a,vbidxcol), index(vbidxcol,d), unique key (b(10), a, d), index(c(99), b(31)), index(b(5), c(10), a) , index(a,d)) engine=InnoDB stats_persistent=1 row_format=dynamic charset latin1;
225
226 # Do an alter table rebuild table and also create a new index on this
227 # non-indexed virtual column
228 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
229 --send alter table ibstd_14 row_format=compressed key_block_size=4,add key kn3 (d,c,vbcol,b);
230
231 # Do a concurrent insert, and make sure this newly indexed virtual column
232 # is also logged
233 connect (con1,localhost,root);
234 connection con1;
235 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
236 insert into ibstd_14 (a,d,b,c, vbidxcol, vbcol) values ('118','6',repeat('oacolaarlruoacuroauurloraarucoooarcooauoolacalllaulrruarrrucruuooclacuoouccarrcoocloccorrrrarourcooalloocooccouruolaorlcaocualolc','1'),repeat('lolrrlalcocroraaulauclaaucolcorcuooaolruaooooluooooouaoorlarucorullalcrrloccououaooaorluorraclrcooouuolocoaolcocaaculruoocucoocoooauuolarcoraraocaoolulolarru','1'),default,default);
237
238 insert into ibstd_14 (a,d,b,c, vbidxcol, vbcol) values ('118','6', 'aaaa', 'lll', default, default);
239
240 # Also do an concurrent update, make sure this is performed
241 update ibstd_14 set b='11111' where b='aaaa';
242
243 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
244
245 connection default;
246 reap;
247
248 select * from ibstd_14;
249
250 # This will use the newly added "kn3" index, to check materialized vbcol
251 # after log reapply
252 --sorted_result
253 select d,c,vbcol,b from ibstd_14;
254
255 # check the value is inserted into the index
256 --sorted_result
257 select vbcol from ibstd_14;
258
259 drop table ibstd_14;
260
261 --echo #
262 --echo # Bug#26375771 INNODB: ASSERTION FAILURE: MACH0DATA.IC:308:VAL > 0X7F
263 --echo #
264 create table ibstd_07 (a int not null, d int not null, b blob not null, c text, vadcol int as (a+length(d)) stored, vbcol char(2) as (substr(b,2,2)) virtual, vbidxcol char(3) as (substr(b,1,3)) virtual , index(d desc), index(a desc), index(vbidxcol desc), index(vbidxcol desc,d asc), unique key (b(101) desc, a asc, d ), index(c(255) desc, b(255) asc), index(b(5) desc, c(10) asc, a ) ) engine=InnoDB default charset=latin1 row_format=redundant;
265
266 insert into ibstd_07 values (2, 2, repeat('rocalrulcrcaurcuccoolrouuocacaooaucauualcucuoucucclolcllloocuarcoorlaccarocouuaoorcolloucraoaaooc','281'),repeat('ouolrculuouocououooalcoraooaulouuacrolrocooraoaooooolaccralacalooolalocoaacoorarorcurccarocucla','317'), default, default, default);
267
268
269 SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead';
270 --send alter table ibstd_07 add primary key (b(11) desc,a asc, d );
271
272 # Do a concurrent update
273 connection con1;
274 SET DEBUG_SYNC = 'now WAIT_FOR start_create';
275 update ibstd_07 set c=repeat('0.366710324443464crllooolarlollorlulololucrluacoorrrluroluroocouauacacruloulcurlraorcrclrolcocrrcoccolourrooclrcocruruauallo','70');
276
277
278 SET DEBUG_SYNC = 'now SIGNAL go_ahead';
279
280 connection default;
281 reap;
282
283 SELECT * FROM ibstd_07;
284
285 DROP TABLE ibstd_07;
286
287 --echo #
288 --echo # Bug#22018745 CORRUPTION IN ONLINE TABLE REBUILD
289 --echo # (ROW_FORMAT=REDUNDANT, INDEXED VIRTUAL COLUMN)
290 --echo #
291
292 CREATE TABLE t (
293 b char(5) PRIMARY KEY,
294 v char(3) GENERATED ALWAYS AS (substr(b,1,3)) VIRTUAL, KEY(v)
295 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;
296
297 connection con1;
298 SET DEBUG_SYNC='row_log_table_apply1_before SIGNAL prepared WAIT_FOR apply';
299 --send OPTIMIZE TABLE t
300 connection default;
301
302 SET DEBUG_SYNC='now WAIT_FOR prepared';
303 INSERT INTO t SET b='fubar';
304 BEGIN;
305 DELETE FROM t;
306 ROLLBACK;
307 SET DEBUG_SYNC='now SIGNAL apply';
308
309 connection con1;
310 reap;
311
312 connection default;
313 CHECK TABLE t;
314 SELECT * FROM t;
315 DROP TABLE t;
316
317 disconnect con1;
318
319 SET DEBUG_SYNC = 'RESET';
320
321 --source include/wait_until_count_sessions.inc
322
323 --echo #
324 --echo # Bug#24658707 ASSERT: BUF0BUF.CC:2469:BUF_BLOCK_GET_STATE(BLOCK)
325 --echo # == BUF_BLOCK_FILE_PAGE#
326 --echo #
327
328 --connect(con1,localhost,root)
329 --connect(con2,localhost,root)
330
331 CREATE TABLE t1 (
332 col1 int(11) NOT NULL,
333 col2 int(11) DEFAULT NULL
334 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
335
336 --connection con1
337 SET DEBUG_SYNC='purge_wait_for_btr_search_latch WAIT_FOR go';
338 --send ALTER TABLE t1 ADD COLUMN col3 INT GENERATED ALWAYS AS (col1 % col2) VIRTUAL, algorithm=inplace;
339
340 --connection con2
341 # Allow purge to wait for btr_search_latch
342 --sleep 10
343 SET DEBUG_SYNC = 'now signal go';
344
345 --connection con1
346 --reap
347
348 SET DEBUG_SYNC = 'RESET';
349
350 DROP TABLE t1;
351
352 --connection default
353 --disconnect con1
354 --disconnect con2
355
356 --echo #
357 --echo # Bug#26330279 - ASSERT ON ROW_PURGE_REMOVE_SEC_IF_POSS_LEAF| INNOBASE/ROW/ROW0PURGE.CC
358 --echo #
359
360 SET GLOBAL innodb_purge_stop_now=ON;
361 CREATE TABLE t1 (col1 INT,
362 col2 VARCHAR(100),
363 col3 VARCHAR(80) GENERATED ALWAYS AS (SUBSTR(col2,1,70)),
364 PRIMARY KEY (col1), UNIQUE KEY uidx(col3(10))) ENGINE = InnoDB;
365
366 INSERT INTO t1(col1, col2) VALUES(1, CONCAT(1, REPEAT('z',90)));
367
368 REPLACE INTO t1(col1, col2) SELECT col1, col2 FROM t1;
369
370 SET GLOBAL innodb_purge_run_now=ON;
371 --source include/wait_innodb_all_purged.inc
372
373 DROP TABLE t1;
374
375
376 --echo #
377 --echo # BUG#27319084 - INDEX CORRUPT: INNODB: FIELD 0 LEN IS NNN, SHOULD BE NNN; RECORD
378 --echo #
379
380 CREATE TABLE t1(a INT NOT NULL, d INT NOT NULL, b VARCHAR(198) NOT NULL, c CHAR(84), vbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL, vbidxcol CHAR(3) AS (substr(b, 1, 3)) VIRTUAL, INDEX(vbidxcol ASC)) ROW_FORMAT = DYNAMIC;
381
382 INSERT INTO t1(a, d, b, c) VALUES(200, 300, '1.3.1415926535', 'hello world');
383
384 connect (conn1, localhost, root,,);
385
386 connection default;
387
388 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done';
389
390 --send ALTER TABLE t1 ROW_FORMAT = REDUNDANT
391
392 connection conn1;
393
394 SET DEBUG_SYNC = 'now WAIT_FOR altered';
395
396 INSERT INTO t1(a, d, b, c) VALUES(201, 300, '3.1415926535', 'hello world');
397
398 UPDATE t1 SET b = '6.1516179' WHERE a = 200;
399
400 SET DEBUG_SYNC = 'now SIGNAL dmls_done';
401
402 disconnect conn1;
403
404 connection default;
405
406 reap;
407
408 SET DEBUG_SYNC = 'reset';
409
410 CHECK TABLE t1;
411
412 SELECT * FROM t1;
413
414 DROP TABLE t1;
415
416
417 CREATE TABLE t1(a INT NOT NULL, d INT NOT NULL, b VARCHAR(198) NOT NULL, c CHAR(84), vbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL, vbidxcol CHAR(3) AS (substr(b, 1, 3)) VIRTUAL, INDEX(vbidxcol ASC)) ROW_FORMAT = REDUNDANT;
418
419 INSERT INTO t1(a, d, b, c) VALUES(200, 300, '1.3.1415926535', 'hello world');
420
421 connect (conn1, localhost, root,,);
422
423 connection default;
424
425 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL altered WAIT_FOR dmls_done';
426
427 --send ALTER TABLE t1 ROW_FORMAT = DYNAMIC
428
429 connection conn1;
430
431 SET DEBUG_SYNC = 'now WAIT_FOR altered';
432
433 INSERT INTO t1(a, d, b, c) VALUES(201, 300, '3.1415926535', 'hello world');
434
435 UPDATE t1 SET b = '6.1516179' WHERE a = 200;
436
437 SET DEBUG_SYNC = 'now SIGNAL dmls_done';
438
439 disconnect conn1;
440
441 connection default;
442
443 reap;
444
445 SET DEBUG_SYNC = 'reset';
446
447 CHECK TABLE t1;
448
449 SELECT * FROM t1;
450
451 DROP TABLE t1;
452
453
454 --echo #
455 --echo # BUG#28448853 - ASSERTION FAILURE: ROW0LOG.CC:2153 ROW_LOG_TABLE_APPLY (THR=0X7F7C7C050030
456 --echo #
457
458 CREATE TABLE t1(a INT NOT NULL, d INT NOT NULL, b VARCHAR(198) NOT NULL, c CHAR(84), vbcol CHAR(2) AS (substr(b, 2, 2)) VIRTUAL);
459
460 INSERT INTO t1(a, d, b, c) VALUES(200, 300, '1.3.1415926535', 'hello world');
461
462 SELECT * FROM t1;
463
464 connect (conn1, localhost, root,,);
465
466 connection default;
467
468 SET DEBUG_SYNC = 'inplace_after_index_build SIGNAL altered WAIT_FOR dmls_done';
469
470 --send ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
471
472 connection conn1;
473
474 SET DEBUG_SYNC = 'now WAIT_FOR altered';
475
476 START TRANSACTION;
477 UPDATE t1 SET b = '6.1516179' WHERE a = 200;
478 ROLLBACK;
479
480 SET DEBUG_SYNC = 'now SIGNAL dmls_done';
481
482 disconnect conn1;
483
484 connection default;
485
486 reap;
487
488 SET DEBUG_SYNC = 'reset';
489
490 CHECK TABLE t1;
491
492 SHOW CREATE TABLE t1;
493
494 SELECT * FROM t1;
495
496 DROP TABLE t1;
497
498
499
500 --echo #
501 --echo # Bug#28825718 - ASSERTION FAILURE: TRX0REC.CC:NNN:N_IDX > 0 WHILE DOING REPLACE/INSERT
502 --echo #
503
504 CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT GENERATED ALWAYS AS(b+1) VIRTUAL) ENGINE=InnoDB;
505
506 INSERT INTO t1(a, b) VALUES(1, 1);
507
508 connect (con1,localhost,root,,);
509 connection con1;
510 SET DEBUG_SYNC = 'row_log_apply_after SIGNAL s1 WAIT_FOR s2';
511 SET lock_wait_timeout = 1;
512 --send ALTER TABLE t1 ADD UNIQUE INDEX(c, b)
513
514 connection default;
515
516 SET DEBUG_SYNC = 'now WAIT_FOR s1';
517 SET DEBUG_SYNC = 'row_ins_sec_index_enter SIGNAL s2 WAIT_FOR s3';
518 --send INSERT INTO t1(a, b) VALUES(2, 2)
519
520 connection con1;
521 --echo /* con1 reap */ ALTER TABLE t1 ADD UNIQUE INDEX(c, b);
522 --error ER_LOCK_WAIT_TIMEOUT
523 reap;
524
525 SET DEBUG_SYNC ='now SIGNAL s3';
526
527 disconnect con1;
528
529 connection default;
530 reap;
531
532 ALTER TABLE t1 ADD KEY(b);
533
534 INSERT INTO t1(a, b) VALUES(3, 3);
535
536 SELECT * FROM t1;
537
538 CHECK TABLE t1;
539
540 DROP TABLE t1;
541
542 --echo #
543 --echo # Bug#28834208 ROW_LOG_TABLE_APPLY_UPDATE*INPLACE_ALTER_TABLE
544 --echo #
545
546 CREATE TABLE t1 (
547 pkey INT(11) PRIMARY KEY,
548 a VARCHAR(100),
549 v VARCHAR(100) GENERATED ALWAYS AS (SUBSTRING(a,2,2)) VIRTUAL,
550 c varchar(100),
551 UNIQUE KEY k (v)
552 );
553 INSERT INTO t1 ( pkey,a,c) VALUES (149,'bata','test');
554 SELECT pkey,a,v FROM t1;
555 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL index_built WAIT_FOR S2';
556 --send OPTIMIZE TABLE t1
557 connect (con1,localhost,root,,);
558 --echo # Connect to con1
559 --connection con1
560 SET DEBUG_SYNC = 'now WAIT_FOR index_built';
561 UPDATE t1 SET a='tata' WHERE pkey=149;
562 SET DEBUG_SYNC ='now signal S2';
563 SELECT pkey,a,v FROM t1;
564 disconnect con1;
565 --connection default
566 --echo # Connect to Default
567 reap;
568 SELECT * FROM t1;
569 DROP TABLE t1;
570
571 CREATE TABLE t1 (
572 pkey INT(11) PRIMARY KEY,
573 a VARCHAR(100),
574 v VARCHAR(100) GENERATED ALWAYS AS (SUBSTRING(a,2,2)) VIRTUAL,
575 c varchar(100),
576 v1 VARCHAR(100) GENERATED ALWAYS AS (SUBSTRING(c,2,2)) VIRTUAL,
577 KEY k (v),
578 KEY v (v1)
579 );
580 INSERT INTO t1 (pkey, a, c) VALUES (149, 'bata', 'test');
581 INSERT INTO t1 (pkey, a, c) VALUES (150, 'bata', 'test');
582 INSERT INTO t1 (pkey, a, c) VALUES (160, 'bata', 'test');
583 SELECT pkey, a, v, c, v1 FROM t1;
584 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL index_built WAIT_FOR S2';
585 --send OPTIMIZE TABLE t1
586 connect (con1,localhost,root,,);
587 --echo # Connect to con1
588 --connection con1
589 SET DEBUG_SYNC = 'now WAIT_FOR index_built';
590 UPDATE t1 SET c = 'best' WHERE pkey = 149;
591 UPDATE t1 SET a = 'tata' WHERE pkey = 150;
592 UPDATE t1 SET a = 'tata', c = 'best' WHERE pkey = 160;
593 SET DEBUG_SYNC ='now signal S2';
594 SELECT pkey, a, v, c, v1 FROM t1;
595 disconnect con1;
596 --connection default
597 --echo # Connect to Default
598 reap;
599 SELECT * FROM t1;
600 DROP TABLE t1;
601
602 CREATE TABLE t1(
603 j SERIAL PRIMARY KEY,
604 a VARCHAR(36),
605 isa BOOLEAN,
606 ab VARCHAR(36) AS (IF(`isa`,`a`,NULL )),
607 KEY k(ab)
608 );
609 INSERT INTO t1 (a, isa) VALUES ('test', 1);
610 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL index_built WAIT_FOR S2';
611 --send OPTIMIZE TABLE t1
612 connect (con1,localhost,root,,);
613 --echo # Connect to con1
614 --connection con1
615 SET DEBUG_SYNC = 'now WAIT_FOR index_built';
616 UPDATE t1 SET isa=0 ;
617 SET DEBUG_SYNC ='now signal S2';
618 SELECT j,a,isa,ab FROM t1;
619 disconnect con1;
620 --echo # Connect to Default
621 --connection default
622 reap;
623 SELECT j,a,isa,ab FROM t1;
624 UPDATE t1 SET isa = 1 WHERE a = 'test' AND isa = 0;
625 SELECT j,a,isa,ab FROM t1;
626 DROP TABLE t1;
627
628
629
630 # Test for the rollback scenario when both a virtual column and a virtual
631 # Index are added. The in-memory dict_index_t object should be handled
632 # properly, in case of any unexpected crash
633
634 CREATE TABLE t1(a INT);
635
636 INSERT INTO t1 VALUES(1), (1);
637
638 set session debug = '+d, force_virtual_col_build_fail';
639
640 # Prepare an index which is marked as aborted
641 connect (conn1,localhost,root,,);
642 connect (conn2,localhost,root,,);
643
644 connection default;
645
646 SET DEBUG_SYNC='innodb_commit_inplace_alter_table_enter SIGNAL rolling_back WAIT_FOR drop_created';
647 --send ALTER TABLE t1 ADD COLUMN (h INT GENERATED ALWAYS AS(a+1) VIRTUAL), ADD INDEX idx(h), ALGORITHM=INPLACE;
648
649 connection conn1;
650 SET DEBUG_SYNC='now WAIT_FOR rolling_back';
651 SET DEBUG_SYNC='row_search_for_mysql_before_return SIGNAL scanning WAIT_FOR continue_scan';
652 --send SELECT * FROM t1;
653
654 connection conn2;
655 SET DEBUG_SYNC='now WAIT_FOR scanning';
656 SET DEBUG_SYNC='now SIGNAL drop_created';
657
658 connection default;
659 --error ER_GET_ERRNO
660 --reap
661
662 connection conn2;
663 SET DEBUG_SYNC='now SIGNAL continue_scan';
664
665 connection conn1;
666 --reap;
667
668 disconnect conn1;
669 disconnect conn2;
670
671 connection default;
672
673 set session debug = '-d, force_virtual_col_build_fail';
674
675 INSERT INTO t1 VALUES(1);
676
677 CHECK TABLE t1;
678
679 DROP TABLE t1;
680
681 --source include/wait_until_count_sessions.inc
682
683 --echo #
684 --echo # Bug 31279528 - CRASH IN MYSQL SERVER, ASSERTION FAILURE: DICT0DICT.CC
685 --echo #
686 CREATE TABLE t1 (c1 INT);
687
688 # Create a secondary index which will cause virtual columns to be added into table
689 CREATE INDEX idx ON t1 ( ( c1 = 127 ) , ( c1 + c1 ) , c1 , ( c1 + - -1 ) , (c1 > 26 ) ) ;
690
691 SHOW WARNINGS;
692
693 SHOW CREATE TABLE t1;
694
695 SET SESSION debug= '+d,skip_dd_table_access_check';
696 SELECT ID INTO @tid FROM mysql.tables WHERE NAME='t1';
697 SELECT NAME, ORDINAL_POSITION, TYPE, HIDDEN, GENERATION_EXPRESSION_UTF8 FROM mysql.columns WHERE TABLE_ID=@tid ORDER BY ORDINAL_POSITION;
698
699 DROP TABLE t1;
700
701
702 --echo #
703 --echo # Bug #30556595 FAILING ASSERTION: !CURSOR->INDEX->IS_COMMITTED()
704 --echo # ON TABLE WITH GCOLS
705 --echo #
706
707 --echo # Set binlog_row_image = MINIMAL.
708 SET @old_binlog_row_image = @@binlog_row_image;
709 SET @@session.binlog_row_image = 'MINIMAL';
710
711 --echo # Testcase-1
712 CREATE TABLE t (pid int PRIMARY KEY,
713 uid int,
714 vid int AS (uid) VIRTUAL);
715 connect(con1,localhost,root,,);
716 --echo # Create index on virtual column and stop before applying row logs.
717 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL start_dml WAIT_FOR resume_ddl';
718 --send ALTER TABLE t ADD INDEX idx_vid(vid), algorithm=inplace
719 connection default;
720 SET DEBUG_SYNC= 'now WAIT_FOR start_dml';
721 --echo # Insert a record to generate an insert row log for the new index.
722 --echo # LOG-1 idx_vid : INSERT [10, 1]
723 INSERT INTO t(pid, uid) VALUES (1,10);
724 --echo # Update the record changing PK. It should generate 2 row logs for the new index.
725 --echo # LOG-2 idx_vid : DELETE [10, 1]
726 --echo # LOG-3 idx_vid : INSERT [10, 2]
727 UPDATE t SET pid = 2 WHERE pid = 1;
728 SELECT * FROM t;
729 SET DEBUG_SYNC= 'now SIGNAL resume_ddl';
730 connection con1;
731 --reap
732 connection default;
733 --echo # Insert a record to re-use the delete marked record in idx_vid [10, 1]
734 INSERT INTO t(pid, uid) VALUES (1,10);
735 SELECT * FROM t;
736 disconnect con1;
737 DROP TABLE t;
738
739 --echo # Testcase-2
740 CREATE TABLE t (pid int PRIMARY KEY,
741 uid int,
742 vid int AS (uid) VIRTUAL);
743 connect(con1,localhost,root,,);
744 --echo # Create index on virtual column and stop before applying row logs.
745 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL start_dml WAIT_FOR resume_ddl';
746 --send ALTER TABLE t ADD INDEX idx_vid(vid), algorithm=inplace
747 connection default;
748 SET DEBUG_SYNC= 'now WAIT_FOR start_dml';
749 --echo # Start transaction
750 BEGIN;
751 --echo # Insert a record to generate an insert row log for the new index.
752 --echo # LOG-1 idx_vid : INSERT [10, 1]
753 INSERT INTO t(pid, uid) VALUES (1,10);
754 --echo # Update the record changing PK. It should generate 2 row logs for the new index.
755 --echo # LOG-2 idx_vid : DELETE [10, 1]
756 --echo # LOG-3 idx_vid : INSERT [10, 2]
757 UPDATE t SET pid = 2 WHERE pid = 1;
758 SELECT * FROM t;
759 --echo # Rollback transaction
760 --echo # LOG-4 idx_vid : DELETE [10, 2]
761 --echo # LOG-5 idx_vid : INSERT [10, 1]
762 --echo # LOG-6 idx_vid : DELETE [10, 1]
763 ROLLBACK;
764 SET DEBUG_SYNC= 'now SIGNAL resume_ddl';
765 connection con1;
766 --reap
767 connection default;
768 --echo # Insert a record to check valid entry in idx_vid [NULL, 1]
769 INSERT INTO t(pid) VALUES (1);
770 SELECT * FROM t;
771 disconnect con1;
772 DROP TABLE t;
773
774 --echo # Testcase-3
775 CREATE TABLE t (pid int PRIMARY KEY,
776 uid int,
777 vid int AS (uid) VIRTUAL);
778 INSERT INTO t(pid, uid) VALUES (1,10);
779 connect(con1,localhost,root,,);
780 --echo # Create index on virtual column and stop before applying row logs.
781 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL start_dml WAIT_FOR resume_ddl';
782 --send ALTER TABLE t ADD INDEX idx_vid(vid), algorithm=inplace
783 connection default;
784 SET DEBUG_SYNC= 'now WAIT_FOR start_dml';
785 --echo # Update the record changing PK. It should generate 2 row logs for the new index.
786 --echo # LOG-2 idx_vid : DELETE [10, 1]
787 --echo # LOG-3 idx_vid : INSERT [10, 2]
788 UPDATE t SET pid = 2 WHERE pid = 1;
789 SET DEBUG_SYNC= 'now SIGNAL resume_ddl';
790 connection con1;
791 --reap
792 connection default;
793 --echo # Read [2, 10, 10] row using idx_vid index key.
794 --sleep 1
795 --skip_if_hypergraph # Hits assertion. Bug#33888263.
796 select * from t where VID=10;
797 disconnect con1;
798 DROP TABLE t;
799
800 --echo # Testcase-4
801 CREATE TABLE t (pid int PRIMARY KEY,
802 uid int,
803 vid int AS (uid) VIRTUAL);
804 INSERT INTO t(pid, uid) VALUES (1,10);
805 connect(con1,localhost,root,,);
806 --echo # Create index on virtual column and stop before applying row logs.
807 SET DEBUG_SYNC = 'row_log_apply_before SIGNAL start_dml WAIT_FOR resume_ddl';
808 --send ALTER TABLE t ADD INDEX idx_vid(vid), algorithm=inplace
809 connection default;
810 SET DEBUG_SYNC= 'now WAIT_FOR start_dml';
811 --echo # Update the record changing PK. It should generate 2 row logs for the new index.
812 --echo # LOG-2 idx_vid : DELETE [10, 1]
813 --echo # LOG-3 idx_vid : INSERT [10, 2]
814 UPDATE t SET pid = 2 WHERE pid = 1;
815 SET DEBUG_SYNC= 'now SIGNAL resume_ddl';
816 connection con1;
817 --reap
818 connection default;
819 --echo # Delete a record.
820 --skip_if_hypergraph # Hits assertion. Bug#33888263.
821 delete from t limit 1;
822 disconnect con1;
823 DROP TABLE t;
824 SET @@session.binlog_row_image = @old_binlog_row_image;