Debian Codegraph @

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

raw
1 --source include/have_debug.inc
2 --source include/have_debug_sync.inc
3 --source include/have_lowercase0.inc
4 --source include/have_innodb_16k.inc
5
6 --echo # Scenario 1: Bug 33788578 for simple table
7 --echo # 1.1: Redundant
8 CREATE TABLE t1_redundant (a TEXT) ENGINE=INNODB, ROW_FORMAT=REDUNDANT;
9 INSERT INTO t1_redundant (a) VALUES ('foo');
10 --let $table_name = t1_redundant
11 --let $pk_col = b
12 --let $upd_col = b
13 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
14 --source suite/innodb/include/bug33788578.inc
15
16 --echo # 1.2: Compact
17 CREATE TABLE t1_compact (a TEXT) ENGINE=INNODB, ROW_FORMAT=COMPACT;
18 INSERT INTO t1_compact (a) VALUES ('foo');
19 --let $table_name = t1_compact
20 --let $pk_col = b
21 --let $upd_col = b
22 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
23 --source suite/innodb/include/bug33788578.inc
24
25 --echo # 1.3: Dynamic
26 CREATE TABLE t1_dynamic (a TEXT) ENGINE=INNODB, ROW_FORMAT=DYNAMIC;
27 INSERT INTO t1_dynamic (a) VALUES ('foo');
28 --let $table_name = t1_dynamic
29 --let $pk_col = b
30 --let $upd_col = b
31 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
32 --source suite/innodb/include/bug33788578.inc
33
34 --echo # Scenario 2: Delete scenarios
35 --echo # 2.1 Redundant
36 CREATE TABLE t1_redundant (a TEXT) ENGINE=INNODB, ROW_FORMAT=REDUNDANT;
37 INSERT INTO t1_redundant VALUES ('foo');
38 ALTER TABLE t1_redundant ADD COLUMN col INT DEFAULT 20, ALGORITHM=INSTANT;
39 INSERT INTO t1_redundant VALUES ('row2', 22);
40 --let $table_name = t1_redundant
41 --let $pk_col = b
42 --let $del_col = col
43 --let $del_val = 22
44 --let $sel_col = a
45 --let $sel_val = row2
46 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
47 --source suite/innodb/include/bug33788578_del.inc
48
49 --echo # 2.2 Compact
50 CREATE TABLE t1_compact (a TEXT) ENGINE=INNODB, ROW_FORMAT=COMPACT;
51 INSERT INTO t1_compact VALUES ('foo');
52 ALTER TABLE t1_compact ADD COLUMN col INT DEFAULT 20, ALGORITHM=INSTANT;
53 INSERT INTO t1_compact VALUES ('row2', 22);
54 --let $table_name = t1_compact
55 --let $pk_col = b
56 --let $del_col = col
57 --let $del_val = 22
58 --let $sel_col = a
59 --let $sel_val = row2
60 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
61 --source suite/innodb/include/bug33788578_del.inc
62
63 --echo # 2.3 Dynamic
64 CREATE TABLE t1_dynamic (a TEXT) ENGINE=INNODB, ROW_FORMAT=DYNAMIC;
65 INSERT INTO t1_dynamic VALUES ('foo');
66 ALTER TABLE t1_dynamic ADD COLUMN col INT DEFAULT 20, ALGORITHM=INSTANT;
67 INSERT INTO t1_dynamic VALUES ('row2', 22);
68 --let $table_name = t1_dynamic
69 --let $pk_col = b
70 --let $del_col = col
71 --let $del_val = 22
72 --let $sel_col = a
73 --let $sel_val = row2
74 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
75 --source suite/innodb/include/bug33788578_del.inc
76
77 --echo # Scenario 2: Upgrade from previous instant algorithm
78 --echo # Table was created in version 8.0.28 with:
79 --echo # -----------------------------------------
80 --echo # DROP DATABASE IF EXISTS test;
81 --echo # CREATE DATABASE IF NOT EXISTS test;
82 --echo # USE test;
83 --echo #
84 --echo # CREATE TABLE t1_redundant (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=REDUNDANT;
85 --echo # ALTER TABLE t1_redundant ADD COLUMN c2 INT DEFAULT 0, ALGORITHM=INSTANT;
86 --echo # INSERT INTO t1_redundant VALUES ("c2", 2);
87 --echo #
88 --echo # CREATE TABLE t1_dynamic (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=DYNAMIC;
89 --echo # ALTER TABLE t1_dynamic ADD COLUMN c2 INT DEFAULT 0, ALGORITHM=INSTANT;
90 --echo # INSERT INTO t1_dynamic VALUES ("c2", 2);
91 --echo #
92 --echo # CREATE TABLE t1_compact (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=COMPACT;
93 --echo # ALTER TABLE t1_compact ADD COLUMN c2 INT DEFAULT 0, ALGORITHM=INSTANT;
94 --echo # INSERT INTO t1_compact VALUES ("c2", 2);
95 --echo #
96 --echo # CREATE TABLE t2_redundant (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=REDUNDANT;
97 --echo # ALTER TABLE t2_redundant ADD COLUMN c2 INT DEFAULT 0, ALGORITHM=INSTANT;
98 --echo # INSERT INTO t2_redundant VALUES ("c2", 2);
99 --echo #
100 --echo # CREATE TABLE t2_dynamic (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=DYNAMIC;
101 --echo # ALTER TABLE t2_dynamic ADD COLUMN c2 INT DEFAULT 0, ALGORITHM=INSTANT;
102 --echo # INSERT INTO t2_dynamic VALUES ("c2", 2);
103 --echo #
104 --echo # CREATE TABLE t2_compact (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=COMPACT;
105 --echo # ALTER TABLE t2_compact ADD COLUMN c2 INT DEFAULT 0, ALGORITHM=INSTANT;
106 --echo # INSERT INTO t2_compact VALUES ("c2", 2);
107 --echo #
108 --echo # CREATE TABLE t3_redundant (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=REDUNDANT;
109 --echo # INSERT INTO t3_redundant VALUES ('foo');
110 --echo # ALTER TABLE t3_redundant ADD COLUMN c2 INT DEFAULT 20, ALGORITHM=INSTANT;
111 --echo # INSERT INTO t3_redundant VALUES ('row2', 22);
112 --echo #
113 --echo # CREATE TABLE t3_dynamic (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=DYNAMIC;
114 --echo # INSERT INTO t3_dynamic VALUES ('foo');
115 --echo # ALTER TABLE t3_dynamic ADD COLUMN c2 INT DEFAULT 20, ALGORITHM=INSTANT;
116 --echo # INSERT INTO t3_dynamic VALUES ('row2', 22);
117 --echo #
118 --echo # CREATE TABLE t3_compact (c1 TEXT) ENGINE=INNODB, ROW_FORMAT=COMPACT;
119 --echo # INSERT INTO t3_compact VALUES ('foo');
120 --echo # ALTER TABLE t3_compact ADD COLUMN c2 INT DEFAULT 20, ALGORITHM=INSTANT;
121 --echo # INSERT INTO t3_compact VALUES ('row2', 22);
122 --echo #
123 --echo # CREATE TABLE t4_redundant (c1 text, c2 int default 20, c3 int not null) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
124 --echo # ALTER TABLE t4_redundant ADD COLUMN c4 INT NOT NULL DEFAULT 40, ALGORITHM=INSTANT;
125 --echo # INSERT INTO t4_redundant VALUES ("row1", 2, 3, 4);
126 --echo # INSERT INTO t4_redundant (c1, c3) VALUES ("row2", 33);
127 --echo # INSERT INTO t4_redundant VALUES ("row3", 222, 333, 444);
128
129 --echo # -----------------------------------------
130 --echo # Replace data directory for upgrade
131 --source include/shutdown_mysqld.inc
132 --copy_file $MYSQLTEST_VARDIR/std_data/instant_ddl/bug33788578.zip $MYSQL_TMP_DIR/bug33788578.zip
133 --file_exists $MYSQL_TMP_DIR/bug33788578.zip
134 --exec unzip -qo $MYSQL_TMP_DIR/bug33788578.zip -d $MYSQL_TMP_DIR
135 let $DATADIR = $MYSQL_TMP_DIR/data;
136
137 --echo # Restart the server
138 --replace_result $DATADIR DATADIR
139 --let $restart_parameters = restart: --datadir=$DATADIR
140 --let $wait_counter=3000
141 --source include/start_mysqld.inc
142
143 SHOW TABLES;
144
145 --echo # Upgrade Scenario 1: Bug 33788578 for upgraded table
146 --echo # 1.1 Dynamic
147 --let $table_name = t1_dynamic
148 --let $pk_col = b
149 --let $upd_col = b
150 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
151 --source suite/innodb/include/bug33788578.inc
152
153 --echo # 1.2 Compact
154 --let $table_name = t1_compact
155 --let $pk_col = b
156 --let $upd_col = b
157 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
158 --source suite/innodb/include/bug33788578.inc
159
160 --echo # 1.3 Redundant
161 --let $table_name = t1_redundant
162 --let $pk_col = b
163 --let $upd_col = b
164 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
165 --source suite/innodb/include/bug33788578.inc
166
167 --echo # Upgrade Scenario 2: set primary key as previous instant added
168 --echo # 2.1 Dynamic
169 --let $table_name = t2_dynamic
170 --let $pk_col = b
171 --let $upd_col = c2
172 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
173 --source suite/innodb/include/bug33788578.inc
174
175 --echo # 2.2 Compact
176 --let $table_name = t2_compact
177 --let $pk_col = b
178 --let $upd_col = c2
179 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
180 --source suite/innodb/include/bug33788578.inc
181
182 --echo # 2.3 Redundant
183 --let $table_name = t2_redundant
184 --let $pk_col = b
185 --let $upd_col = c2
186 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
187 --source suite/innodb/include/bug33788578.inc
188
189 --echo # Upgrade scenario 3: delete during online DDL
190 --echo # 3.1 Dynamic
191 --let $table_name = t3_dynamic
192 --let $pk_col = b
193 --let $del_col = c2
194 --let $del_val = 22
195 --let $sel_col = c1
196 --let $sel_val = row2
197 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
198 --source suite/innodb/include/bug33788578_del.inc
199
200 --echo # 3.2 Compact
201 --let $table_name = t3_compact
202 --let $pk_col = b
203 --let $del_col = c2
204 --let $del_val = 22
205 --let $sel_col = c1
206 --let $sel_val = row2
207 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
208 --source suite/innodb/include/bug33788578_del.inc
209
210 --echo # 3.3 Redundant
211 --let $table_name = t3_redundant
212 --let $pk_col = b
213 --let $del_col = c2
214 --let $del_val = 22
215 --let $sel_col = c1
216 --let $sel_val = row2
217 --eval ALTER TABLE $table_name ADD COLUMN $pk_col INT DEFAULT 0, ALGORITHM=INSTANT;
218 --source suite/innodb/include/bug33788578_del.inc
219
220 --echo # Redundant related scenarios
221 --echo # 1. index->table->has_row_versions() is false
222 SELECT * FROM t4_redundant;
223 SHOW CREATE TABLE t4_redundant;
224 CHECK TABLE t4_redundant;
225
226 connect (con1,localhost,root,,);
227
228 connection con1;
229 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL update_now WAIT_FOR update_done';
230 send;
231 ALTER TABLE t4_redundant ADD PRIMARY KEY (c3);
232
233 connection default;
234 SET DEBUG_SYNC='now WAIT_FOR update_now';
235
236 SELECT * FROM t4_redundant;
237 SHOW CREATE TABLE t4_redundant;
238 CHECK TABLE t4_redundant;
239
240 UPDATE t4_redundant SET c2 = 2222;
241
242 SELECT * FROM t4_redundant;
243 SHOW CREATE TABLE t4_redundant;
244 CHECK TABLE t4_redundant;
245
246 SET DEBUG_SYNC='now SIGNAL update_done';
247
248 connection con1;
249 reap;
250
251 connection default;
252 disconnect con1;
253
254 SELECT * FROM t4_redundant;
255 SHOW CREATE TABLE t4_redundant;
256 CHECK TABLE t4_redundant;
257
258 DROP TABLE t4_redundant;
259
260 --echo # 2. index->table->has_row_versions() is true and rec_new_temp_is_versioned(rec) is false
261
262 CREATE TABLE t5_redundant (c1 TEXT, c2 INT DEFAULT 20, c3 INT NOT NULL) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
263
264 INSERT INTO t5_redundant VALUES ("row1", 2, 3);
265 INSERT INTO t5_redundant (c1, c3) VALUES ("row2", 33);
266
267 ALTER TABLE t5_redundant ADD COLUMN c4 INT NOT NULL DEFAULT 40, ALGORITHM=INSTANT;
268
269 SELECT * FROM t5_redundant;
270 SHOW CREATE TABLE t5_redundant;
271 CHECK TABLE t5_redundant;
272
273 connect (con1,localhost,root,,);
274
275 connection con1;
276 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL update_now WAIT_FOR update_done';
277 send;
278 ALTER TABLE t5_redundant ADD PRIMARY KEY (c3);
279
280 connection default;
281 SET DEBUG_SYNC='now WAIT_FOR update_now';
282
283 SELECT * FROM t5_redundant;
284 SHOW CREATE TABLE t5_redundant;
285 CHECK TABLE t5_redundant;
286
287 UPDATE t5_redundant SET c2 = 2222;
288
289 SELECT * FROM t5_redundant;
290 SHOW CREATE TABLE t5_redundant;
291 CHECK TABLE t5_redundant;
292
293 SET DEBUG_SYNC='now SIGNAL update_done';
294
295 connection con1;
296 reap;
297
298 connection default;
299 disconnect con1;
300
301 SELECT * FROM t5_redundant;
302 SHOW CREATE TABLE t5_redundant;
303 CHECK TABLE t5_redundant;
304
305 DROP TABLE t5_redundant;
306
307 --echo # 3. index->table->has_row_versions() is true and rec_new_temp_is_versioned(rec) is true
308
309 CREATE TABLE t6_redundant (c1 TEXT, c2 INT DEFAULT 20, c3 INT NOT NULL) ROW_FORMAT=REDUNDANT, ENGINE=INNODB;
310
311 ALTER TABLE t6_redundant ADD COLUMN c4 INT NOT NULL DEFAULT 40, ALGORITHM=INSTANT;
312
313 INSERT INTO t6_redundant VALUES ("row1", 2, 3, 4);
314 INSERT INTO t6_redundant (c1, c3) VALUES ("row2", 33);
315
316 SELECT * FROM t6_redundant;
317 SHOW CREATE TABLE t6_redundant;
318 CHECK TABLE t6_redundant;
319
320 connect (con1,localhost,root,,);
321
322 connection con1;
323 SET DEBUG_SYNC='innodb_inplace_alter_table_enter SIGNAL update_now WAIT_FOR update_done';
324 send;
325 ALTER TABLE t6_redundant ADD PRIMARY KEY (c3);
326
327 connection default;
328 SET DEBUG_SYNC='now WAIT_FOR update_now';
329
330 SELECT * FROM t6_redundant;
331 SHOW CREATE TABLE t6_redundant;
332 CHECK TABLE t6_redundant;
333
334 UPDATE t6_redundant SET c2 = 2222;
335
336 SELECT * FROM t6_redundant;
337 SHOW CREATE TABLE t6_redundant;
338 CHECK TABLE t6_redundant;
339
340 SET DEBUG_SYNC='now SIGNAL update_done';
341
342 connection con1;
343 reap;
344
345 connection default;
346 disconnect con1;
347
348 SELECT * FROM t6_redundant;
349 SHOW CREATE TABLE t6_redundant;
350 CHECK TABLE t6_redundant;
351
352 DROP TABLE t6_redundant;
353
354 --echo # Cleanup
355 --echo # Shutdown server
356 --source include/shutdown_mysqld.inc
357
358 --echo # Remove temp files used
359 --file_exists $MYSQL_TMP_DIR/bug33788578.zip
360 --force-rmdir $MYSQL_TMP_DIR/data
361 --remove_file $MYSQL_TMP_DIR/bug33788578.zip
362
363 --echo # Restart server to restore server state
364 let $restart_parameters =;
365 --source include/start_mysqld.inc
366
367 --echo # End