Debian Codegraph @

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

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