Debian Codegraph @

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

raw
1 --source include/have_debug_sync.inc
2 --source include/have_debug.inc
3
4
5
6 CREATE TABLE `t` (
7 `a` BLOB,
8 `b` BLOB,
9 `c` BLOB 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', 16000), REPEAT('x', 16000), DEFAULT, "kk", 1);
15 INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, "mm", 2);
16
17 CREATE INDEX idx ON t(c(100));
18
19 SET global debug="+d,ib_purge_virtual_index_callback";
20 UPDATE t SET a = REPEAT('m', 16000) WHERE a like "aaa%";
21 select sleep(3);
22 SET global debug="-d,ib_purge_virtual_index_callback";
23 DROP TABLE t;
24
25
26 CREATE TABLE t (
27 a TINYBLOB,
28 b TINYBLOB,
29 c TINYBLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
30 h VARCHAR(10) DEFAULT NULL,
31 i INT
32 ) ROW_FORMAT=COMPACT ENGINE=InnoDB;
33
34 INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 100), DEFAULT, "kk", 1);
35 INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2);
36
37 CREATE INDEX idx ON t(c(100));
38
39 SET global debug="+d,ib_purge_virtual_index_callback";
40 UPDATE t SET a = REPEAT('m', 100) WHERE a like "aaa%";
41 select sleep(3);
42 SET global debug="-d,ib_purge_virtual_index_callback";
43 DROP TABLE t;
44
45
46 CREATE TABLE t1 (
47 id INT NOT NULL,
48 store_id INT NOT NULL,
49 x INT GENERATED ALWAYS AS (id + store_id)
50 )
51 PARTITION BY RANGE (store_id) (
52 PARTITION p0 VALUES LESS THAN (6),
53 PARTITION p1 VALUES LESS THAN (11),
54 PARTITION p2 VALUES LESS THAN (16),
55 PARTITION p3 VALUES LESS THAN (21)
56 );
57
58 insert into t1 values(1, 2, default);
59 insert into t1 values(3, 4, default);
60
61 insert into t1 values(3, 12, default);
62 insert into t1 values(4, 18, default);
63
64 CREATE INDEX idx ON t1(x);
65
66 SET global debug="+d,ib_purge_virtual_index_callback";
67 UPDATE t1 SET id = 10 WHERE id = 1;
68 select sleep(3);
69 SET global debug="-d,ib_purge_virtual_index_callback";
70 DROP TABLE t1;
71
72 #
73 # BUG#22082762 RE-ENABLE SUPPORT FOR ADDING VIRTUAL INDEX WHILE DROPPING VIRTUAL COLUMN
74 #
75 --source include/count_sessions.inc
76
77 connect (con1,localhost,root,,);
78 connection default;
79
80 # Test adding virtual index on newly added virtual column
81 CREATE TABLE t1 (a INT, b INT);
82
83 INSERT INTO t1(a, b) VALUES (1, 1), (2, 2), (3, 3);
84
85 connection con1;
86 --echo # disable purge
87 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
88 BEGIN; SELECT * FROM t0;
89
90 connection default;
91 DELETE FROM t1 WHERE a = 1;
92
93 UPDATE t1 SET a = 4, b = 4 WHERE a = 3;
94
95 INSERT INTO t1(a, b) VALUES (5, 5);
96
97 SET DEBUG_SYNC= 'inplace_after_index_build SIGNAL uncommitted WAIT_FOR purged';
98 --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
99 ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE;
100 send ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=SHARED;
101
102 connection con1;
103 SET DEBUG_SYNC= 'now WAIT_FOR uncommitted';
104
105 --echo # enable purge
106 COMMIT;
107
108 # Note, the alter table would increase trx_sys->rw_max_trx_id
109 --echo # wait for purge to process the deleted records.
110 set global innodb_purge_run_now=ON;
111 select sleep(1);
112
113 SET DEBUG_SYNC= 'now SIGNAL purged';
114
115 connection default;
116 --echo /* connection default */ ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=SHARED;
117 --reap
118 SHOW CREATE TABLE t1;
119
120 SELECT * FROM t1;
121
122 DROP TABLE t1;
123
124 # Test adding virutal index on existing virtual column
125 CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b));
126
127 INSERT INTO t1(a, b) VALUES (1, 1), (2, 2), (3, 3), (4, 4);
128
129 connection con1;
130 --echo # disable purge
131 BEGIN; SELECT * FROM t0;
132
133 connection default;
134 DELETE FROM t1 WHERE a = 1;
135
136 UPDATE t1 SET a = 2, b = 2 WHERE a = 5;
137
138 INSERT INTO t1(a, b) VALUES (6, 6);
139
140 SET DEBUG_SYNC= 'inplace_after_index_build SIGNAL uncommitted WAIT_FOR purged';
141 send ALTER TABLE t1 ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE;
142
143 connection con1;
144 SET DEBUG_SYNC= 'now WAIT_FOR uncommitted';
145
146 DELETE FROM t1 WHERE a = 3;
147
148 UPDATE t1 SET a = 7, b = 7 WHERE a = 4;
149
150 INSERT INTO t1(a, b) VALUES (8, 8);
151
152 --echo # enable purge
153 COMMIT;
154
155 # Note, the alter table would increase trx_sys->rw_max_trx_id
156 --echo # wait for purge to process the deleted/updated records.
157 set global innodb_purge_run_now=ON;
158 select sleep(1);
159
160 SET DEBUG_SYNC= 'now SIGNAL purged';
161
162 disconnect con1;
163
164 connection default;
165 --echo /* connection default */ ALTER TABLE t1 ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE;
166 --reap
167 SHOW CREATE TABLE t1;
168
169 SELECT * FROM t1;
170
171 DROP TABLE t0, t1;
172
173
174 --echo #
175 --echo # Bug#21869656 UNDO LOG DOES NOT CONTAIN ENOUGH INFORMATION
176 --echo # ON INDEXED VIRTUAL COLUMNS
177 --echo #
178
179 CREATE TABLE t1 (a INT, b INT,
180 a1 INT GENERATED ALWAYS AS (a) VIRTUAL, INDEX(a1)
181 ) ENGINE=InnoDB;
182
183 INSERT INTO t1 (a,b) VALUES(1,1);
184
185 connect (con1,localhost,root,,);
186 # disable purge
187 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
188 BEGIN; SELECT * FROM t0;
189
190 connection default;
191 # write the problematic update_undo log record
192 UPDATE t1 SET a=0;
193
194 ALTER TABLE t1 DROP COLUMN a1, ALGORITHM=INPLACE;
195 ALTER TABLE t1 ADD COLUMN b1 INT GENERATED ALWAYS AS (b) VIRTUAL, ADD
196 INDEX(b1),
197 ALGORITHM=INPLACE;
198
199 connection con1;
200 # enable purge
201 COMMIT;
202 UPDATE t1 SET a=1;
203
204 connection default;
205 # wait for purge to process the update_undo record.
206 # Note, the alter table would increase trx_sys->rw_max_trx_id
207 set global innodb_purge_run_now=ON;
208 select sleep(1);
209
210 CHECK TABLE t1;
211 SELECT b1 FROM t1;
212
213
214 # Create multi-virtual column, more ADD/DROP to test it
215 ALTER TABLE t1
216 ADD COLUMN a1 INT GENERATED ALWAYS AS (a) VIRTUAL,
217 ADD COLUMN a2 INT GENERATED ALWAYS AS (a + b) VIRTUAL,
218 ADD COLUMN a3 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
219 ADD COLUMN a4 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
220 ADD INDEX(a1), ADD INDEX(a2), ADD INDEX(a3), ALGORITHM=INPLACE;
221
222 CREATE TABLE t2 (
223 a BLOB,
224 b BLOB,
225 c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
226 h VARCHAR(10) DEFAULT NULL
227 ) ENGINE=InnoDB;
228
229 INSERT INTO t2 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, 'kk');
230
231 INSERT INTO t2 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
232
233 CREATE INDEX idx ON t2(c(100));
234
235 INSERT INTO t1 (a, b) VALUES(1,1);
236
237 connection con1;
238 # disable purge
239 BEGIN; SELECT * FROM t0;
240
241 connection default;
242 --enable_info
243
244 # write the problematic update_undo log record
245 UPDATE t1 SET a=0;
246 UPDATE t1 SET b=0;
247
248 ALTER TABLE t1 DROP COLUMN a3, ALGORITHM=INPLACE;
249
250 UPDATE t1 SET a=2;
251 ALTER TABLE t1 DROP COLUMN a2, ALGORITHM=INPLACE;
252 UPDATE t1 SET b=3;
253
254 ALTER TABLE t1 ADD COLUMN b2 INT GENERATED ALWAYS AS (b) VIRTUAL,
255 ADD INDEX(b2), ALGORITHM=INPLACE;
256 UPDATE t1 SET b=9;
257
258 ALTER TABLE t1 ADD COLUMN b3 INT GENERATED ALWAYS AS (a) VIRTUAL,
259 ADD INDEX(b3), ALGORITHM=INPLACE;
260
261 UPDATE t1 SET b=10;
262
263 ALTER TABLE t2 DROP COLUMN c;
264
265 UPDATE t2 SET a = REPEAT('s', 6000) WHERE a like 'aaa%';
266
267 ALTER TABLE t2 ADD COLUMN x1 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
268 ADD COLUMN x2 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
269 ADD INDEX(x1(100), x2(120)), ADD INDEX (x1(20));
270
271 UPDATE t1 SET a=5;
272
273 UPDATE t2 SET a = REPEAT('m', 16000) WHERE a like 'sss%';
274
275 ALTER TABLE t1 DROP COLUMN b2, ALGORITHM=INPLACE;
276
277 UPDATE t1 SET a=6;
278
279 ALTER TABLE t2 DROP COLUMN x1, DROP COLUMN x2, ALGORITHM=INPLACE;
280
281 UPDATE t2 SET a = REPEAT('x', 1000) WHERE a like 'mmm%';
282
283 ALTER TABLE t1 DROP INDEX b3;
284 UPDATE t1 SET a=100;
285 --disable_info
286
287 connection con1;
288 # enable purge
289 COMMIT;
290 disconnect con1;
291
292 connection default;
293 # wait for purge to process the update_undo record.
294 set global innodb_purge_run_now=ON;
295 select sleep(1);
296
297 CHECK TABLE t1;
298 SELECT b1 FROM t1;
299
300 SELECT * FROM t1;
301 CHECK TABLE t2;
302 DROP TABLE t2, t1, t0;
303
304 CREATE TABLE t1 (a VARCHAR(30), b INT, a2 VARCHAR(30) GENERATED ALWAYS AS (a) VIRTUAL);
305
306 --error ER_DUP_FIELDNAME
307 CREATE INDEX idx ON t1(a2(10), b, a2(20));
308
309 DROP TABLE t1;
310
311 --source include/wait_until_count_sessions.inc