Debian Codegraph @

mysql-test/suite/innodb/r/virtual_debug_purge.result

raw
1 CREATE TABLE `t` (
2 `a` BLOB,
3 `b` BLOB,
4 `c` BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
5 `h` VARCHAR(10) DEFAULT NULL,
6 `i` int
7 ) ENGINE=InnoDB;
8 INSERT INTO t VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, "kk", 1);
9 INSERT INTO t VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, "mm", 2);
10 CREATE INDEX idx ON t(c(100));
11 SET global debug="+d,ib_purge_virtual_index_callback";
12 UPDATE t SET a = REPEAT('m', 16000) WHERE a like "aaa%";
13 select sleep(3);
14 sleep(3)
15 0
16 SET global debug="-d,ib_purge_virtual_index_callback";
17 DROP TABLE t;
18 CREATE TABLE t (
19 a TINYBLOB,
20 b TINYBLOB,
21 c TINYBLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
22 h VARCHAR(10) DEFAULT NULL,
23 i INT
24 ) ROW_FORMAT=COMPACT ENGINE=InnoDB;
25 INSERT INTO t VALUES (REPEAT('g', 100), REPEAT('x', 100), DEFAULT, "kk", 1);
26 INSERT INTO t VALUES (REPEAT('a', 100), REPEAT('b', 100), DEFAULT, "mm", 2);
27 CREATE INDEX idx ON t(c(100));
28 SET global debug="+d,ib_purge_virtual_index_callback";
29 UPDATE t SET a = REPEAT('m', 100) WHERE a like "aaa%";
30 select sleep(3);
31 sleep(3)
32 0
33 SET global debug="-d,ib_purge_virtual_index_callback";
34 DROP TABLE t;
35 CREATE TABLE t1 (
36 id INT NOT NULL,
37 store_id INT NOT NULL,
38 x INT GENERATED ALWAYS AS (id + store_id)
39 )
40 PARTITION BY RANGE (store_id) (
41 PARTITION p0 VALUES LESS THAN (6),
42 PARTITION p1 VALUES LESS THAN (11),
43 PARTITION p2 VALUES LESS THAN (16),
44 PARTITION p3 VALUES LESS THAN (21)
45 );
46 insert into t1 values(1, 2, default);
47 insert into t1 values(3, 4, default);
48 insert into t1 values(3, 12, default);
49 insert into t1 values(4, 18, default);
50 CREATE INDEX idx ON t1(x);
51 SET global debug="+d,ib_purge_virtual_index_callback";
52 UPDATE t1 SET id = 10 WHERE id = 1;
53 select sleep(3);
54 sleep(3)
55 0
56 SET global debug="-d,ib_purge_virtual_index_callback";
57 DROP TABLE t1;
58 CREATE TABLE t1 (a INT, b INT);
59 INSERT INTO t1(a, b) VALUES (1, 1), (2, 2), (3, 3);
60 # disable purge
61 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
62 BEGIN;
63 SELECT * FROM t0;
64 a
65 DELETE FROM t1 WHERE a = 1;
66 UPDATE t1 SET a = 4, b = 4 WHERE a = 3;
67 INSERT INTO t1(a, b) VALUES (5, 5);
68 SET DEBUG_SYNC= 'inplace_after_index_build SIGNAL uncommitted WAIT_FOR purged';
69 ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE;
70 ERROR 0A000: LOCK=NONE is not supported. Reason: ADD COLUMN col...VIRTUAL, ADD INDEX(col). Try LOCK=SHARED.
71 ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=SHARED;
72 SET DEBUG_SYNC= 'now WAIT_FOR uncommitted';
73 # enable purge
74 COMMIT;
75 # wait for purge to process the deleted records.
76 set global innodb_purge_run_now=ON;
77 select sleep(1);
78 sleep(1)
79 0
80 SET DEBUG_SYNC= 'now SIGNAL purged';
81 /* connection default */ ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS(a+b), ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=SHARED;
82 SHOW CREATE TABLE t1;
83 Table Create Table
84 t1 CREATE TABLE `t1` (
85 `a` int DEFAULT NULL,
86 `b` int DEFAULT NULL,
87 `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
88 KEY `idx` (`c`)
89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
90 SELECT * FROM t1;
91 a b c
92 2 2 4
93 4 4 8
94 5 5 10
95 DROP TABLE t1;
96 CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b));
97 INSERT INTO t1(a, b) VALUES (1, 1), (2, 2), (3, 3), (4, 4);
98 # disable purge
99 BEGIN;
100 SELECT * FROM t0;
101 a
102 DELETE FROM t1 WHERE a = 1;
103 UPDATE t1 SET a = 2, b = 2 WHERE a = 5;
104 INSERT INTO t1(a, b) VALUES (6, 6);
105 SET DEBUG_SYNC= 'inplace_after_index_build SIGNAL uncommitted WAIT_FOR purged';
106 ALTER TABLE t1 ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE;
107 SET DEBUG_SYNC= 'now WAIT_FOR uncommitted';
108 DELETE FROM t1 WHERE a = 3;
109 UPDATE t1 SET a = 7, b = 7 WHERE a = 4;
110 INSERT INTO t1(a, b) VALUES (8, 8);
111 # enable purge
112 COMMIT;
113 # wait for purge to process the deleted/updated records.
114 set global innodb_purge_run_now=ON;
115 select sleep(1);
116 sleep(1)
117 0
118 SET DEBUG_SYNC= 'now SIGNAL purged';
119 /* connection default */ ALTER TABLE t1 ADD INDEX idx (c), ALGORITHM=INPLACE, LOCK=NONE;
120 SHOW CREATE TABLE t1;
121 Table Create Table
122 t1 CREATE TABLE `t1` (
123 `a` int DEFAULT NULL,
124 `b` int DEFAULT NULL,
125 `c` int GENERATED ALWAYS AS ((`a` + `b`)) VIRTUAL,
126 KEY `idx` (`c`)
127 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
128 SELECT * FROM t1;
129 a b c
130 2 2 4
131 7 7 14
132 6 6 12
133 8 8 16
134 DROP TABLE t0, t1;
135 #
136 # Bug#21869656 UNDO LOG DOES NOT CONTAIN ENOUGH INFORMATION
137 # ON INDEXED VIRTUAL COLUMNS
138 #
139 CREATE TABLE t1 (a INT, b INT,
140 a1 INT GENERATED ALWAYS AS (a) VIRTUAL, INDEX(a1)
141 ) ENGINE=InnoDB;
142 INSERT INTO t1 (a,b) VALUES(1,1);
143 CREATE TABLE t0 (a INT) ENGINE=InnoDB;
144 BEGIN;
145 SELECT * FROM t0;
146 a
147 UPDATE t1 SET a=0;
148 ALTER TABLE t1 DROP COLUMN a1, ALGORITHM=INPLACE;
149 ALTER TABLE t1 ADD COLUMN b1 INT GENERATED ALWAYS AS (b) VIRTUAL, ADD
150 INDEX(b1),
151 ALGORITHM=INPLACE;
152 COMMIT;
153 UPDATE t1 SET a=1;
154 set global innodb_purge_run_now=ON;
155 select sleep(1);
156 sleep(1)
157 0
158 CHECK TABLE t1;
159 Table Op Msg_type Msg_text
160 test.t1 check status OK
161 SELECT b1 FROM t1;
162 b1
163 1
164 ALTER TABLE t1
165 ADD COLUMN a1 INT GENERATED ALWAYS AS (a) VIRTUAL,
166 ADD COLUMN a2 INT GENERATED ALWAYS AS (a + b) VIRTUAL,
167 ADD COLUMN a3 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
168 ADD COLUMN a4 INT GENERATED ALWAYS AS (a - b) VIRTUAL,
169 ADD INDEX(a1), ADD INDEX(a2), ADD INDEX(a3), ALGORITHM=INPLACE;
170 CREATE TABLE t2 (
171 a BLOB,
172 b BLOB,
173 c BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
174 h VARCHAR(10) DEFAULT NULL
175 ) ENGINE=InnoDB;
176 INSERT INTO t2 VALUES (REPEAT('g', 16000), REPEAT('x', 16000), DEFAULT, 'kk');
177 INSERT INTO t2 VALUES (REPEAT('a', 16000), REPEAT('b', 16000), DEFAULT, 'mm');
178 CREATE INDEX idx ON t2(c(100));
179 INSERT INTO t1 (a, b) VALUES(1,1);
180 BEGIN;
181 SELECT * FROM t0;
182 a
183 UPDATE t1 SET a=0;
184 affected rows: 2
185 info: Rows matched: 2 Changed: 2 Warnings: 0
186 UPDATE t1 SET b=0;
187 affected rows: 2
188 info: Rows matched: 2 Changed: 2 Warnings: 0
189 ALTER TABLE t1 DROP COLUMN a3, ALGORITHM=INPLACE;
190 affected rows: 0
191 info: Records: 0 Duplicates: 0 Warnings: 0
192 UPDATE t1 SET a=2;
193 affected rows: 2
194 info: Rows matched: 2 Changed: 2 Warnings: 0
195 ALTER TABLE t1 DROP COLUMN a2, ALGORITHM=INPLACE;
196 affected rows: 0
197 info: Records: 0 Duplicates: 0 Warnings: 0
198 UPDATE t1 SET b=3;
199 affected rows: 2
200 info: Rows matched: 2 Changed: 2 Warnings: 0
201 ALTER TABLE t1 ADD COLUMN b2 INT GENERATED ALWAYS AS (b) VIRTUAL,
202 ADD INDEX(b2), ALGORITHM=INPLACE;
203 affected rows: 0
204 info: Records: 0 Duplicates: 0 Warnings: 0
205 UPDATE t1 SET b=9;
206 affected rows: 2
207 info: Rows matched: 2 Changed: 2 Warnings: 0
208 ALTER TABLE t1 ADD COLUMN b3 INT GENERATED ALWAYS AS (a) VIRTUAL,
209 ADD INDEX(b3), ALGORITHM=INPLACE;
210 affected rows: 0
211 info: Records: 0 Duplicates: 0 Warnings: 0
212 UPDATE t1 SET b=10;
213 affected rows: 2
214 info: Rows matched: 2 Changed: 2 Warnings: 0
215 ALTER TABLE t2 DROP COLUMN c;
216 affected rows: 0
217 info: Records: 0 Duplicates: 0 Warnings: 0
218 UPDATE t2 SET a = REPEAT('s', 6000) WHERE a like 'aaa%';
219 affected rows: 1
220 info: Rows matched: 1 Changed: 1 Warnings: 0
221 ALTER TABLE t2 ADD COLUMN x1 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
222 ADD COLUMN x2 BLOB GENERATED ALWAYS AS (CONCAT(a,b)) VIRTUAL,
223 ADD INDEX(x1(100), x2(120)), ADD INDEX (x1(20));
224 affected rows: 0
225 info: Records: 0 Duplicates: 0 Warnings: 0
226 UPDATE t1 SET a=5;
227 affected rows: 2
228 info: Rows matched: 2 Changed: 2 Warnings: 0
229 UPDATE t2 SET a = REPEAT('m', 16000) WHERE a like 'sss%';
230 affected rows: 1
231 info: Rows matched: 1 Changed: 1 Warnings: 0
232 ALTER TABLE t1 DROP COLUMN b2, ALGORITHM=INPLACE;
233 affected rows: 0
234 info: Records: 0 Duplicates: 0 Warnings: 0
235 UPDATE t1 SET a=6;
236 affected rows: 2
237 info: Rows matched: 2 Changed: 2 Warnings: 0
238 ALTER TABLE t2 DROP COLUMN x1, DROP COLUMN x2, ALGORITHM=INPLACE;
239 affected rows: 0
240 info: Records: 0 Duplicates: 0 Warnings: 0
241 UPDATE t2 SET a = REPEAT('x', 1000) WHERE a like 'mmm%';
242 affected rows: 1
243 info: Rows matched: 1 Changed: 1 Warnings: 0
244 ALTER TABLE t1 DROP INDEX b3;
245 affected rows: 0
246 info: Records: 0 Duplicates: 0 Warnings: 0
247 UPDATE t1 SET a=100;
248 affected rows: 2
249 info: Rows matched: 2 Changed: 2 Warnings: 0
250 COMMIT;
251 set global innodb_purge_run_now=ON;
252 select sleep(1);
253 sleep(1)
254 0
255 CHECK TABLE t1;
256 Table Op Msg_type Msg_text
257 test.t1 check status OK
258 SELECT b1 FROM t1;
259 b1
260 10
261 10
262 SELECT * FROM t1;
263 a b b1 a1 a4 b3
264 100 10 10 100 90 100
265 100 10 10 100 90 100
266 CHECK TABLE t2;
267 Table Op Msg_type Msg_text
268 test.t2 check status OK
269 DROP TABLE t2, t1, t0;
270 CREATE TABLE t1 (a VARCHAR(30), b INT, a2 VARCHAR(30) GENERATED ALWAYS AS (a) VIRTUAL);
271 CREATE INDEX idx ON t1(a2(10), b, a2(20));
272 ERROR 42S21: Duplicate column name 'a2'
273 DROP TABLE t1;