Debian Codegraph @

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

raw
1 --source include/count_sessions.inc
2
3 --echo #
4 --echo # Bug#21869656 UNDO LOG DOES NOT CONTAIN ENOUGH INFORMATION
5 --echo # ON INDEXED VIRTUAL COLUMNS
6 --echo #
7
8 CREATE TABLE t1 (a INT, b INT,
9 a1 INT GENERATED ALWAYS AS (a) VIRTUAL, INDEX(a1)
10 ) ENGINE=InnoDB;
11
12 INSERT INTO t1 (a,b) VALUES(1,1);
13
14 connect (con1,localhost,root,,);
15 # disable purge
16 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
17 BEGIN; SELECT * FROM t0;
18
19 connection default;
20 # write the problematic update_undo log record
21 UPDATE t1 SET a=0;
22
23 ALTER TABLE t1 DROP COLUMN a1, ALGORITHM=INPLACE;
24 ALTER TABLE t1 ADD COLUMN b1 INT GENERATED ALWAYS AS (b) VIRTUAL, ADD
25 INDEX(b1),
26 ALGORITHM=INPLACE;
27
28 connection con1;
29 # enable purge
30 COMMIT;
31 UPDATE t1 SET a=1;
32
33 connection default;
34 # wait for purge to process the update_undo record.
35 --source include/wait_innodb_all_purged.inc
36
37 CHECK TABLE t1;
38 SELECT b1 FROM t1;
39
40
41 # Create multi-virtual column, more ADD/DROP to test it
42 ALTER TABLE t1
43 ADD COLUMN a1 INT GENERATED ALWAYS AS (a) VIRTUAL,
44 ADD COLUMN a2 INT GENERATED ALWAYS AS (a + b) VIRTUAL,
45 ADD COLUMN a3 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
46 ADD COLUMN a4 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
47 ADD INDEX(a1), ADD INDEX(a2), ADD INDEX(a3), ALGORITHM=INPLACE;
48
49 CREATE TABLE t2 (
50 a BLOB,
51 b BLOB,
52 c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
53 h VARCHAR(10) DEFAULT NULL
54 ) ENGINE=InnoDB;
55
56 INSERT INTO t2 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, 'kk');
57
58 INSERT INTO t2 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
59
60 CREATE INDEX idx ON t2(c(100));
61
62 INSERT INTO t1 (a, b) VALUES(1,1);
63
64 connection con1;
65 # disable purge
66 BEGIN; SELECT * FROM t0;
67
68 connection default;
69 --enable_info
70
71 # write the problematic update_undo log record
72 UPDATE t1 SET a=0;
73 UPDATE t1 SET b=0;
74
75 ALTER TABLE t1 DROP COLUMN a3, ALGORITHM=INPLACE;
76
77 UPDATE t1 SET a=2;
78 ALTER TABLE t1 DROP COLUMN a2, ALGORITHM=INPLACE;
79 UPDATE t1 SET b=3;
80
81 ALTER TABLE t1 ADD COLUMN b2 INT GENERATED ALWAYS AS (b) VIRTUAL,
82 ADD INDEX(b2), ALGORITHM=INPLACE;
83 UPDATE t1 SET b=9;
84
85 ALTER TABLE t1 ADD COLUMN b3 INT GENERATED ALWAYS AS (a) VIRTUAL,
86 ADD INDEX(b3), ALGORITHM=INPLACE;
87
88 UPDATE t1 SET b=10;
89
90 ALTER TABLE t2 DROP COLUMN c;
91
92 UPDATE t2 SET a = REPEAT('s', 6000) WHERE a like 'aaa%';
93
94 ALTER TABLE t2 ADD COLUMN x1 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
95 ADD COLUMN x2 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
96 ADD INDEX(x1(100), x2(120)), ADD INDEX (x1(20));
97
98 UPDATE t1 SET a=5;
99
100 UPDATE t2 SET a = REPEAT('m', 16000) WHERE a like 'sss%';
101
102 ALTER TABLE t1 DROP COLUMN b2, ALGORITHM=INPLACE;
103
104 UPDATE t1 SET a=6;
105
106 ALTER TABLE t2 DROP COLUMN x1, DROP COLUMN x2, ALGORITHM=INPLACE;
107
108 UPDATE t2 SET a = REPEAT('x', 1000) WHERE a like 'mmm%';
109
110 ALTER TABLE t1 DROP INDEX b3;
111 UPDATE t1 SET a=100;
112 --disable_info
113
114 connection con1;
115 # enable purge
116 COMMIT;
117 disconnect con1;
118
119 connection default;
120 # wait for purge to process the update_undo record.
121 --source include/wait_innodb_all_purged.inc
122
123 CHECK TABLE t1;
124 SELECT b1 FROM t1;
125
126 SELECT * FROM t1;
127 CHECK TABLE t2;
128 DROP TABLE t2, t1, t0;
129
130 CREATE TABLE t1 (a VARCHAR(30), b INT, a2 VARCHAR(30) GENERATED ALWAYS AS (a) VIRTUAL);
131
132 --error ER_DUP_FIELDNAME
133 CREATE INDEX idx ON t1(a2(10), b, a2(20));
134
135 DROP TABLE t1;
136
137 --source include/wait_until_count_sessions.inc