Debian Codegraph @

mysql-test/suite/gcol/t/innodb_virtual_purge.test

raw
1 --source include/have_innodb.inc
2 --source include/count_sessions.inc
3
4 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
5 SET GLOBAL innodb_stats_persistent = 0;
6
7 --echo #
8 --echo # Bug#21869656 UNDO LOG DOES NOT CONTAIN ENOUGH INFORMATION
9 --echo # ON INDEXED VIRTUAL COLUMNS
10 --echo #
11
12 CREATE TABLE t1 (a INT, b INT,
13 a1 INT GENERATED ALWAYS AS (a) VIRTUAL, INDEX(a1)
14 ) ENGINE=InnoDB;
15
16 INSERT INTO t1 (a,b) VALUES(1,1);
17
18 connect (con1,localhost,root,,);
19 # disable purge
20 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
21 BEGIN; SELECT * FROM t0;
22
23 connection default;
24 # write the problematic update_undo log record
25 UPDATE t1 SET a=0;
26
27 ALTER TABLE t1 DROP COLUMN a1, ALGORITHM=INPLACE;
28 ALTER TABLE t1 ADD COLUMN b1 INT GENERATED ALWAYS AS (b) VIRTUAL, ADD
29 INDEX(b1),
30 ALGORITHM=INPLACE;
31
32 connection con1;
33 # enable purge
34 COMMIT;
35 UPDATE t1 SET a=1;
36
37 connection default;
38 --source ../../innodb/include/wait_all_purged.inc
39
40 CHECK TABLE t1 EXTENDED;
41 SELECT b1 FROM t1;
42
43
44 # Create multi-virtual column, more ADD/DROP to test it
45 ALTER TABLE t1
46 ADD COLUMN a1 INT GENERATED ALWAYS AS (a) VIRTUAL,
47 ADD COLUMN a2 INT GENERATED ALWAYS AS (a + b) VIRTUAL,
48 ADD COLUMN a3 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
49 ADD COLUMN a4 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
50 ADD INDEX(a1), ADD INDEX(a2), ADD INDEX(a3), ALGORITHM=INPLACE;
51
52 CREATE TABLE t2 (
53 a BLOB,
54 b BLOB,
55 c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
56 h VARCHAR(10) DEFAULT NULL
57 ) ENGINE=InnoDB;
58
59 INSERT INTO t2 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, 'kk');
60
61 INSERT INTO t2 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
62
63 CREATE INDEX idx ON t2(c(100));
64
65 INSERT INTO t1 (a, b) VALUES(1,1);
66
67 connection con1;
68 # disable purge
69 BEGIN; SELECT * FROM t0;
70
71 connection default;
72 --enable_info
73
74 # write the problematic update_undo log record
75 UPDATE t1 SET a=0;
76 UPDATE t1 SET b=0;
77
78 ALTER TABLE t1 DROP COLUMN a3, ALGORITHM=INPLACE;
79
80 UPDATE t1 SET a=2;
81 ALTER TABLE t1 DROP COLUMN a2, ALGORITHM=INPLACE;
82 UPDATE t1 SET b=3;
83
84 ALTER TABLE t1 ADD COLUMN b2 INT GENERATED ALWAYS AS (b) VIRTUAL,
85 ADD INDEX(b2), ALGORITHM=INPLACE;
86 UPDATE t1 SET b=9;
87
88 ALTER TABLE t1 ADD COLUMN b3 INT GENERATED ALWAYS AS (a) VIRTUAL,
89 ADD INDEX(b3), ALGORITHM=INPLACE;
90
91 UPDATE t1 SET b=10;
92
93 ALTER TABLE t2 DROP COLUMN c;
94
95 UPDATE t2 SET a = REPEAT('s', 6000) WHERE a like 'aaa%';
96
97 ALTER TABLE t2 ADD COLUMN x1 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
98 ADD COLUMN x2 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
99 ADD INDEX(x1(100), x2(120)), ADD INDEX (x1(20));
100
101 UPDATE t1 SET a=5;
102
103 UPDATE t2 SET a = REPEAT('m', 16000) WHERE a like 'sss%';
104
105 ALTER TABLE t1 DROP COLUMN b2, ALGORITHM=INPLACE;
106
107 UPDATE t1 SET a=6;
108
109 ALTER TABLE t2 DROP COLUMN x1, DROP COLUMN x2, ALGORITHM=INPLACE;
110
111 UPDATE t2 SET a = REPEAT('x', 1000) WHERE a like 'mmm%';
112
113 ALTER TABLE t1 DROP INDEX b3;
114 UPDATE t1 SET a=100;
115 --disable_info
116
117 connection con1;
118 # enable purge
119 COMMIT;
120 disconnect con1;
121
122 connection default;
123 --source ../../innodb/include/wait_all_purged.inc
124
125 CHECK TABLE t1 EXTENDED;
126 SELECT b1 FROM t1;
127
128 SELECT * FROM t1;
129 CHECK TABLE t2 EXTENDED;
130 DROP TABLE t2, t1, t0;
131
132 CREATE TABLE t1 (a VARCHAR(30), b INT, a2 VARCHAR(30) GENERATED ALWAYS AS (a) VIRTUAL);
133
134 --error ER_DUP_FIELDNAME
135 CREATE INDEX idx ON t1(a2(10), b, a2(20));
136
137 DROP TABLE t1;
138
139 --echo #
140 --echo # MDEV-17540 Server crashes in row_purge after TRUNCATE TABLE
141 --echo #
142
143 # Note: this test case is nondeterministic and should depend on
144 # MDEV-12288 to trigger the needed purge activity.
145 # The test does not seem to repeat the bug on MariaDB 10.2.
146
147 CREATE TABLE t1 (a BIT(14)) ENGINE=InnoDB;
148 INSERT INTO t1 VALUES
149 (b'01110110101011'),(b'01100111111000'),(b'00001011110100'),
150 (b'01110110111010'),(b'10001010101011'),(b'01100111001111');
151
152 CREATE TABLE t2 (
153 pk INT DEFAULT 1,
154 b YEAR,
155 c BIT(14),
156 d YEAR AS (b),
157 e BIT(14) AS (c),
158 UNIQUE(pk),
159 KEY(e)
160 ) ENGINE=InnoDB;
161
162 # Run a few times in order to improve the chances of triggering the bug.
163 --disable_query_log
164 let $n=10;
165 while ($n) {
166 REPLACE INTO t2 (c) SELECT a FROM t1;
167 TRUNCATE TABLE t2;
168 dec $n;
169 }
170 --enable_query_log
171
172 DROP TABLE t1, t2;
173
174 --echo #
175 --echo # MDEV-30024 InnoDB: tried to purge non-delete-marked record
176 --echo # of an index on a virtual column prefix
177 --echo #
178
179 CREATE TABLE t(a BINARY(8), b CHAR(8) AS (a) VIRTUAL, KEY(b(4)))
180 CHARACTER SET utf8 ENGINE=InnoDB;
181 INSERT INTO t (a) VALUES (''),('');
182 UPDATE t SET a = 'x';
183 UPDATE t SET a = '';
184 SET GLOBAL innodb_max_purge_lag_wait=0;
185 CHECK TABLE t EXTENDED;
186 DROP TABLE t;
187
188 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;
189
190 --source include/wait_until_count_sessions.inc