Debian Codegraph @

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

raw
1 # Case 1. Extern BLOB with sec index on v_col undergoes UPDATE during online DDL
2 CREATE TABLE t1 (c1 INT, c2 INT, c3 BLOB, c4 INT AS (c1 + 1), INDEX id(c4) );
3 INSERT INTO t1 VALUES (1, 1, REPEAT('rocalrulcrcaurcuccoolrouuocacaooaucauualcucuoucucclolcllloocuarcoorlaccarocouuaoorcolloucraoaaooc', 281), DEFAULT);
4 SELECT c1, c2, c4 FROM t1;
5 c1 c2 c4
6 1 1 2
7 SHOW CREATE TABLE t1;
8 Table Create Table
9 t1 CREATE TABLE `t1` (
10 `c1` int DEFAULT NULL,
11 `c2` int DEFAULT NULL,
12 `c3` blob,
13 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
14 KEY `id` (`c4`)
15 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
16 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL online WAIT_FOR upd';
17 ALTER TABLE t1 ADD PRIMARY KEY (c1);
18 SET DEBUG_SYNC='now WAIT_FOR online';
19 SELECT c1, c2, c4 FROM t1;
20 c1 c2 c4
21 1 1 2
22 UPDATE t1 SET c2=2;
23 SELECT c1, c2, c4 FROM t1;
24 c1 c2 c4
25 1 2 2
26 SET DEBUG_SYNC='now SIGNAL upd';
27 SELECT c1, c2, c4 FROM t1;
28 c1 c2 c4
29 1 2 2
30 CHECK TABLE t1;
31 Table Op Msg_type Msg_text
32 test.t1 check status OK
33 SHOW CREATE TABLE t1;
34 Table Create Table
35 t1 CREATE TABLE `t1` (
36 `c1` int NOT NULL,
37 `c2` int DEFAULT NULL,
38 `c3` blob,
39 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
40 PRIMARY KEY (`c1`),
41 KEY `id` (`c4`)
42 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
43 DROP TABLE t1;
44 # Case 2. BLOB with sec index on v_col undergoes UPDATE during online DDL
45 CREATE TABLE t2 (c1 INT, c2 INT, c3 BLOB, c4 INT AS (c1 + 1), INDEX id(c4) );
46 INSERT INTO t2 VALUES (1, 1, REPEAT('A', 256), DEFAULT);
47 SELECT c1, c2, c4 FROM t2;
48 c1 c2 c4
49 1 1 2
50 SHOW CREATE TABLE t2;
51 Table Create Table
52 t2 CREATE TABLE `t2` (
53 `c1` int DEFAULT NULL,
54 `c2` int DEFAULT NULL,
55 `c3` blob,
56 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
57 KEY `id` (`c4`)
58 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
59 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL online WAIT_FOR upd';
60 ALTER TABLE t2 ADD PRIMARY KEY (c1);
61 SET DEBUG_SYNC='now WAIT_FOR online';
62 SELECT c1, c2, c4 FROM t2;
63 c1 c2 c4
64 1 1 2
65 UPDATE t2 SET c2=2;
66 SELECT c1, c2, c4 FROM t2;
67 c1 c2 c4
68 1 2 2
69 SET DEBUG_SYNC='now SIGNAL upd';
70 SELECT c1, c2, c4 FROM t2;
71 c1 c2 c4
72 1 2 2
73 CHECK TABLE t2;
74 Table Op Msg_type Msg_text
75 test.t2 check status OK
76 SHOW CREATE TABLE t2;
77 Table Create Table
78 t2 CREATE TABLE `t2` (
79 `c1` int NOT NULL,
80 `c2` int DEFAULT NULL,
81 `c3` blob,
82 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
83 PRIMARY KEY (`c1`),
84 KEY `id` (`c4`)
85 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
86 DROP TABLE t2;
87 # Case 3. (case 1) with update on base column of v_col
88 CREATE TABLE t3 (c1 INT, c2 INT, c3 BLOB, c4 INT AS (c1 + 1), INDEX id(c4) );
89 INSERT INTO t3 VALUES (1, 1, REPEAT('rocalrulcrcaurcuccoolrouuocacaooaucauualcucuoucucclolcllloocuarcoorlaccarocouuaoorcolloucraoaaooc', 281), DEFAULT);
90 SELECT c1, c2, c4 FROM t3;
91 c1 c2 c4
92 1 1 2
93 SHOW CREATE TABLE t3;
94 Table Create Table
95 t3 CREATE TABLE `t3` (
96 `c1` int DEFAULT NULL,
97 `c2` int DEFAULT NULL,
98 `c3` blob,
99 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
100 KEY `id` (`c4`)
101 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
102 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL online WAIT_FOR upd';
103 ALTER TABLE t3 ADD PRIMARY KEY (c1);
104 SET DEBUG_SYNC='now WAIT_FOR online';
105 SELECT c1, c2, c4 FROM t3;
106 c1 c2 c4
107 1 1 2
108 UPDATE t3 SET c1=10;
109 SELECT c1, c2, c4 FROM t3;
110 c1 c2 c4
111 10 1 11
112 SET DEBUG_SYNC='now SIGNAL upd';
113 SELECT c1, c2, c4 FROM t3;
114 c1 c2 c4
115 10 1 11
116 CHECK TABLE t3;
117 Table Op Msg_type Msg_text
118 test.t3 check status OK
119 SHOW CREATE TABLE t3;
120 Table Create Table
121 t3 CREATE TABLE `t3` (
122 `c1` int NOT NULL,
123 `c2` int DEFAULT NULL,
124 `c3` blob,
125 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
126 PRIMARY KEY (`c1`),
127 KEY `id` (`c4`)
128 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
129 DROP TABLE t3;
130 # Case 4. (case 2) with update on base column of v_col
131 CREATE TABLE t4 (c1 INT, c2 INT, c3 BLOB, c4 INT AS (c1 + 1), INDEX id(c4) );
132 INSERT INTO t4 VALUES (1, 1, REPEAT('A', 256), DEFAULT);
133 SELECT c1, c2, c4 FROM t4;
134 c1 c2 c4
135 1 1 2
136 SHOW CREATE TABLE t4;
137 Table Create Table
138 t4 CREATE TABLE `t4` (
139 `c1` int DEFAULT NULL,
140 `c2` int DEFAULT NULL,
141 `c3` blob,
142 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
143 KEY `id` (`c4`)
144 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
145 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL online WAIT_FOR upd';
146 ALTER TABLE t4 ADD PRIMARY KEY (c1);
147 SET DEBUG_SYNC='now WAIT_FOR online';
148 SELECT c1, c2, c4 FROM t4;
149 c1 c2 c4
150 1 1 2
151 UPDATE t4 SET c1=10;
152 SELECT c1, c2, c4 FROM t4;
153 c1 c2 c4
154 10 1 11
155 SET DEBUG_SYNC='now SIGNAL upd';
156 SELECT c1, c2, c4 FROM t4;
157 c1 c2 c4
158 10 1 11
159 CHECK TABLE t4;
160 Table Op Msg_type Msg_text
161 test.t4 check status OK
162 SHOW CREATE TABLE t4;
163 Table Create Table
164 t4 CREATE TABLE `t4` (
165 `c1` int NOT NULL,
166 `c2` int DEFAULT NULL,
167 `c3` blob,
168 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
169 PRIMARY KEY (`c1`),
170 KEY `id` (`c4`)
171 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
172 DROP TABLE t4;
173 # Case 5. (case 1) with update on BLOB without changing extern status
174 CREATE TABLE t5 (c1 INT, c2 INT, c3 BLOB, c4 INT AS (c1 + 1), INDEX id(c4) );
175 INSERT INTO t5 VALUES (1, 1, REPEAT('rocalrulcrcaurcuccoolrouuocacaooaucauualcucuoucucclolcllloocuarcoorlaccarocouuaoorcolloucraoaaooc', 281), DEFAULT);
176 SELECT c1, c2, c4 FROM t5;
177 c1 c2 c4
178 1 1 2
179 SHOW CREATE TABLE t5;
180 Table Create Table
181 t5 CREATE TABLE `t5` (
182 `c1` int DEFAULT NULL,
183 `c2` int DEFAULT NULL,
184 `c3` blob,
185 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
186 KEY `id` (`c4`)
187 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
188 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL online WAIT_FOR upd';
189 ALTER TABLE t5 ADD PRIMARY KEY (c1);
190 SET DEBUG_SYNC='now WAIT_FOR online';
191 SELECT c1, c2, c4 FROM t5;
192 c1 c2 c4
193 1 1 2
194 UPDATE t5 SET c3=REPEAT('abcdefghcrcaurcuccoolrouuocacaooaucauualcucuoucucclolcllloocuarcoorlaccarocouuaoorcolloucraoaaooc', 281);
195 UPDATE t5 SET c2=2;
196 UPDATE t5 SET c1=10;
197 SELECT c1, c2, c4 FROM t5;
198 c1 c2 c4
199 10 2 11
200 SET DEBUG_SYNC='now SIGNAL upd';
201 SELECT c1, c2, c4 FROM t5;
202 c1 c2 c4
203 10 2 11
204 CHECK TABLE t5;
205 Table Op Msg_type Msg_text
206 test.t5 check status OK
207 SHOW CREATE TABLE t5;
208 Table Create Table
209 t5 CREATE TABLE `t5` (
210 `c1` int NOT NULL,
211 `c2` int DEFAULT NULL,
212 `c3` blob,
213 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
214 PRIMARY KEY (`c1`),
215 KEY `id` (`c4`)
216 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
217 DROP TABLE t5;
218 # Case 6. (case 2) with update on BLOB without changing extern status
219 CREATE TABLE t6 (c1 INT, c2 INT, c3 BLOB, c4 INT AS (c1 + 1), INDEX id(c4) );
220 INSERT INTO t6 VALUES (1, 1, REPEAT('A', 256), DEFAULT);
221 SELECT c1, c2, c4 FROM t6;
222 c1 c2 c4
223 1 1 2
224 SHOW CREATE TABLE t6;
225 Table Create Table
226 t6 CREATE TABLE `t6` (
227 `c1` int DEFAULT NULL,
228 `c2` int DEFAULT NULL,
229 `c3` blob,
230 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
231 KEY `id` (`c4`)
232 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
233 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL online WAIT_FOR upd';
234 ALTER TABLE t6 ADD PRIMARY KEY (c1);
235 SET DEBUG_SYNC='now WAIT_FOR online';
236 SELECT c1, c2, c4 FROM t6;
237 c1 c2 c4
238 1 1 2
239 UPDATE t6 SET c3=REPEAT('B', 256);
240 UPDATE t6 SET c2=2;
241 UPDATE t6 SET c1=10;
242 SELECT c1, c2, c4 FROM t6;
243 c1 c2 c4
244 10 2 11
245 SET DEBUG_SYNC='now SIGNAL upd';
246 SELECT c1, c2, c4 FROM t6;
247 c1 c2 c4
248 10 2 11
249 CHECK TABLE t6;
250 Table Op Msg_type Msg_text
251 test.t6 check status OK
252 SHOW CREATE TABLE t6;
253 Table Create Table
254 t6 CREATE TABLE `t6` (
255 `c1` int NOT NULL,
256 `c2` int DEFAULT NULL,
257 `c3` blob,
258 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
259 PRIMARY KEY (`c1`),
260 KEY `id` (`c4`)
261 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
262 DROP TABLE t6;
263 # Case 7. (case 1) with update on BLOB inverting BLOB's extern status
264 CREATE TABLE t7 (c1 INT, c2 INT, c3 BLOB, c4 INT AS (c1 + 1), INDEX id(c4) );
265 INSERT INTO t7 VALUES (1, 1, REPEAT('rocalrulcrcaurcuccoolrouuocacaooaucauualcucuoucucclolcllloocuarcoorlaccarocouuaoorcolloucraoaaooc', 281), DEFAULT);
266 SELECT c1, c2, c4 FROM t7;
267 c1 c2 c4
268 1 1 2
269 SHOW CREATE TABLE t7;
270 Table Create Table
271 t7 CREATE TABLE `t7` (
272 `c1` int DEFAULT NULL,
273 `c2` int DEFAULT NULL,
274 `c3` blob,
275 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
276 KEY `id` (`c4`)
277 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
278 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL online WAIT_FOR upd';
279 ALTER TABLE t7 ADD PRIMARY KEY (c1);
280 SET DEBUG_SYNC='now WAIT_FOR online';
281 SELECT c1, c2, c4 FROM t7;
282 c1 c2 c4
283 1 1 2
284 UPDATE t7 SET c3=REPEAT('B', 256);
285 UPDATE t7 SET c2=2;
286 UPDATE t7 SET c1=10;
287 SELECT c1, c2, c4 FROM t7;
288 c1 c2 c4
289 10 2 11
290 SET DEBUG_SYNC='now SIGNAL upd';
291 SELECT c1, c2, c4 FROM t7;
292 c1 c2 c4
293 10 2 11
294 CHECK TABLE t7;
295 Table Op Msg_type Msg_text
296 test.t7 check status OK
297 SHOW CREATE TABLE t7;
298 Table Create Table
299 t7 CREATE TABLE `t7` (
300 `c1` int NOT NULL,
301 `c2` int DEFAULT NULL,
302 `c3` blob,
303 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
304 PRIMARY KEY (`c1`),
305 KEY `id` (`c4`)
306 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
307 DROP TABLE t7;
308 # Case 8. (case 2) with update on BLOB inverting BLOB's extern status
309 CREATE TABLE t8 (c1 INT, c2 INT, c3 BLOB, c4 INT AS (c1 + 1), INDEX id(c4) );
310 INSERT INTO t8 VALUES (1, 1, REPEAT('A', 256), DEFAULT);
311 SELECT c1, c2, c4 FROM t8;
312 c1 c2 c4
313 1 1 2
314 SHOW CREATE TABLE t8;
315 Table Create Table
316 t8 CREATE TABLE `t8` (
317 `c1` int DEFAULT NULL,
318 `c2` int DEFAULT NULL,
319 `c3` blob,
320 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
321 KEY `id` (`c4`)
322 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
323 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL online WAIT_FOR upd';
324 ALTER TABLE t8 ADD PRIMARY KEY (c1);
325 SET DEBUG_SYNC='now WAIT_FOR online';
326 SELECT c1, c2, c4 FROM t8;
327 c1 c2 c4
328 1 1 2
329 UPDATE t8 SET c3=REPEAT('abcdefghcrcaurcuccoolrouuocacaooaucauualcucuoucucclolcllloocuarcoorlaccarocouuaoorcolloucraoaaooc', 281);
330 UPDATE t8 SET c2=2;
331 UPDATE t8 SET c1=10;
332 SELECT c1, c2, c4 FROM t8;
333 c1 c2 c4
334 10 2 11
335 SET DEBUG_SYNC='now SIGNAL upd';
336 SELECT c1, c2, c4 FROM t8;
337 c1 c2 c4
338 10 2 11
339 CHECK TABLE t8;
340 Table Op Msg_type Msg_text
341 test.t8 check status OK
342 SHOW CREATE TABLE t8;
343 Table Create Table
344 t8 CREATE TABLE `t8` (
345 `c1` int NOT NULL,
346 `c2` int DEFAULT NULL,
347 `c3` blob,
348 `c4` int GENERATED ALWAYS AS ((`c1` + 1)) VIRTUAL,
349 PRIMARY KEY (`c1`),
350 KEY `id` (`c4`)
351 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
352 DROP TABLE t8;