Debian Codegraph @

mysql-test/suite/innodb/include/instant_ddl_update.inc

raw
1 ##########################################################################
2 # Test script to test UPDATE on table having INSTANT ADD/DROP columns.
3 #
4 # $row_format is to be set to the row_format on which test is to be run.
5 ##########################################################################
6
7 --eval CREATE TABLE t1 (c1 char(20), c2 char(20)) ROW_FORMAT=$row_format
8 INSERT INTO t1 values ("row1_c1", "row1_c2");
9
10 SHOW CREATE TABLE t1;
11 --let $table_name=t1
12 --source suite/innodb/include/print_instant_metadata.inc
13
14 --echo # ------------------------------------------------------------
15 --echo # ADD AN INSTANT COLUMN At the end [c1, c2, +c3]
16 --echo # ------------------------------------------------------------
17 ALTER TABLE t1 ADD COLUMN c3 char(20) default "c3_def", ALGORITHM=INSTANT;
18 --let $table_name=t1
19 --source suite/innodb/include/print_instant_metadata.inc
20 SELECT * FROM t1 ORDER BY c1;
21
22 INSERT INTO t1 values ("row2_c1", "row2_c2", "row2_c3");
23 SELECT * FROM t1 ORDER BY c1;
24
25 --echo # INPLACE UPDATE
26 UPDATE t1 SET c1="r1_c1" where c1="row1_c1";
27 SELECT * FROM t1 ORDER BY c1;
28
29 --echo # NOT INPLACE UPDATE
30 UPDATE t1 SET c3="row1_c3" where c1="r1_c1";
31 SELECT * FROM t1 ORDER BY c1;
32
33 --echo # ------------------------------------------------------------
34 --echo # ADD AN INSTANT COLUMN At the first [+c0, c1, c2, c3]
35 --echo # ------------------------------------------------------------
36 ALTER TABLE t1 ADD COLUMN c0 char(20) default "c0_def" FIRST, ALGORITHM=INSTANT;
37 --let $table_name=t1
38 --source suite/innodb/include/print_instant_metadata.inc
39 SELECT * FROM t1 ORDER BY c1;
40
41 INSERT INTO t1 values ("row3_c0", "row3_c1", "row3_c2", "row3_c3");
42 SELECT * FROM t1 ORDER BY c1;
43
44 --echo # INPLACE UPDATE
45 UPDATE t1 SET c1="r2_c1" where c1="row2_c1";
46 SELECT * FROM t1 ORDER BY c1;
47
48 --echo # NOT INPLACE UPDATE
49 UPDATE t1 SET c0="row2_c0" where c1="r2_c1";
50 SELECT * FROM t1 ORDER BY c1;
51
52 --echo # ------------------------------------------------------------
53 --echo # ADD AN INSTANT COLUMN in between [c0, c1, +c4, c2, c3]
54 --echo # ------------------------------------------------------------
55 ALTER TABLE t1 ADD COLUMN c4 char(20) default "c4_def" AFTER c1, ALGORITHM=INSTANT;
56 --let $table_name=t1
57 --source suite/innodb/include/print_instant_metadata.inc
58 SELECT * FROM t1 ORDER BY c1;
59
60 INSERT INTO t1 values ("row4_c0", "row4_c1", "row4_c4", "row4_c2", "row4_c3");
61 SELECT * FROM t1 ORDER BY c1;
62
63 --echo # INPLACE UPDATE
64 UPDATE t1 SET c1="r3_c1" where c1="row3_c1";
65 SELECT * FROM t1 ORDER BY c1;
66
67 --echo # NOT INPLACE UPDATE
68 UPDATE t1 SET c4="row3_c4" where c1="r3_c1";
69 SELECT * FROM t1 ORDER BY c1;
70
71 --echo # ------------------------------------------------------------
72 --echo # DROP A COLUMN in between [c0, c1, c4, -c2, c3]
73 --echo # ------------------------------------------------------------
74 ALTER TABLE t1 DROP COLUMN c2, ALGORITHM=INSTANT;
75 --let $table_name=t1
76 --source suite/innodb/include/print_instant_metadata.inc
77 SELECT * FROM t1 ORDER BY c1;
78
79 INSERT INTO t1 values ("row5_c0", "row5_c1", "row5_c4", "row5_c3");
80 SELECT * FROM t1 ORDER BY c1;
81
82 --echo # INPLACE UPDATE
83 UPDATE t1 SET c1="r4_c1" where c1="row4_c1";
84 SELECT * FROM t1 ORDER BY c1;
85
86 --echo # NOT INPLACE UPDATE
87 UPDATE t1 SET c4="row1_c4" where c1="r1_c1";
88 SELECT * FROM t1 ORDER BY c1;
89
90 --echo # ------------------------------------------------------------
91 --echo # DROP A COLUMN at the end [c0, c1, c4, ~c2, -c3]
92 --echo # ------------------------------------------------------------
93 ALTER TABLE t1 DROP COLUMN c3, ALGORITHM=INSTANT;
94 --let $table_name=t1
95 --source suite/innodb/include/print_instant_metadata.inc
96 SELECT * FROM t1 ORDER BY c1;
97
98 INSERT INTO t1 values ("row6_c0", "row6_c1", "row6_c4");
99 SELECT * FROM t1 ORDER BY c1;
100
101 --echo # INPLACE UPDATE
102 UPDATE t1 SET c1="r5_c1" where c1="row5_c1";
103 SELECT * FROM t1 ORDER BY c1;
104
105 --echo # NOT INPLACE UPDATE
106 UPDATE t1 SET c0="row1_c0" where c1="r1_c1";
107 SELECT * FROM t1 ORDER BY c1;
108
109 --echo # ------------------------------------------------------------
110 --echo # DROP A COLUMN at the beginning [-c0, c1, c4, ~c2, ~c3]
111 --echo # ------------------------------------------------------------
112 ALTER TABLE t1 DROP COLUMN c0, ALGORITHM=INSTANT;
113 --let $table_name=t1
114 --source suite/innodb/include/print_instant_metadata.inc
115 SELECT * FROM t1 ORDER BY c1;
116
117 INSERT INTO t1 values ("row7_c1", "row7_c4");
118 SELECT * FROM t1 ORDER BY c1;
119
120 --echo # INPLACE UPDATE
121 UPDATE t1 SET c1="r6_c1" where c1="row6_c1";
122 SELECT * FROM t1 ORDER BY c1;
123
124 --echo # NOT INPLACE UPDATE
125 UPDATE t1 SET c4="row2_c4" where c1="r2_c1";
126 SELECT * FROM t1 ORDER BY c1;
127
128 --echo # ------------------------------------------------------------
129 --echo # Rebuild table [c1, c4]
130 --echo # ------------------------------------------------------------
131 ALTER TABLE t1 FORCE;
132
133 --let $table_name=t1
134 --source suite/innodb/include/print_instant_metadata.inc
135 SELECT * FROM t1 ORDER BY c1;
136
137 INSERT INTO t1 values ("row8_c1", "row8_c4");
138 SELECT * FROM t1 ORDER BY c1;
139
140 if ($is_debug)
141 {
142 DROP TABLE t1;
143 --echo # ------------------------------------------------------------
144 --echo # ONLINE DDL on table having INSTANT ADD/DROP Column
145 --echo # ------------------------------------------------------------
146 --echo
147 --echo # ---------------------------
148 --echo # ONLINE DDL 1 : NO PK CHANGE
149 --echo # ---------------------------
150 --source include/count_sessions.inc
151
152 --eval CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=$row_format
153 INSERT INTO t1 VALUES ("r1c1", "r1c2");
154 SELECT * FROM t1;
155
156 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "def_c0" FIRST, ALGORITHM=INSTANT;
157 INSERT INTO t1 VALUES ("r2c0", "r2c1", "r2c2");
158 SELECT * FROM t1;
159
160 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT;
161 INSERT INTO t1 VALUES ("r3c0", "r3c2");
162 SELECT * FROM t1;
163
164
165 --echo # Make alter table wait
166 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2';
167
168 --echo # Rebuild the table
169 --send ALTER TABLE t1 FORCE, ALGORITHM=INPLACE
170
171 connect (con1,localhost,root,,);
172 --echo # connection con1
173 connection con1;
174
175 SET DEBUG_SYNC = 'now WAIT_FOR s1';
176
177 --echo # Insert query
178 INSERT INTO t1 VALUES ("r4c0", "r4c2");
179
180 --echo # Update query
181 --echo # (NOT INPLACE update for row1, IN-PLACE update for row2 an row3)
182 UPDATE t1 SET c0="c0_upd";
183
184 --echo # Let Alter table continue
185 SET DEBUG_SYNC = 'now SIGNAL s2';
186
187 --echo # connection default
188 connection default;
189 --reap
190
191 SELECT * FROM t1;
192
193 DISCONNECT con1;
194
195 # Wait till all disconnects are completed
196 --source include/wait_until_count_sessions.inc
197 DROP TABLE t1;
198
199 --echo # ---------------------------
200 --echo # ONLINE DDL 2 : PK CHANGE
201 --echo # ---------------------------
202 --source include/count_sessions.inc
203
204 --eval CREATE TABLE t1 (c1 CHAR(10), c2 CHAR(10)) ROW_FORMAT=$row_format
205 INSERT INTO t1 VALUES ("r1c1", "r1c2");
206 SELECT * FROM t1;
207
208 ALTER TABLE t1 ADD COLUMN c0 CHAR(10) DEFAULT "def_c0" FIRST, ALGORITHM=INSTANT;
209 INSERT INTO t1 VALUES ("r2c0", "r2c1", "r2c2");
210 SELECT * FROM t1;
211
212 ALTER TABLE t1 DROP COLUMN c1, ALGORITHM=INSTANT;
213 INSERT INTO t1 VALUES ("r3c0", "r3c2");
214 SELECT * FROM t1;
215
216
217 --echo # Make alter table wait
218 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2';
219
220 --echo # Rebuild the table
221 --send ALTER TABLE t1 ADD PRIMARY KEY (c2), ALGORITHM=INPLACE
222
223 connect (con1,localhost,root,,);
224 --echo # connection con1
225 connection con1;
226
227 SET DEBUG_SYNC = 'now WAIT_FOR s1';
228
229 --echo # Insert query
230 INSERT INTO t1 VALUES ("r4c0", "r4c2");
231
232 --echo # Update query
233 --echo # (NOT INPLACE update for row1, IN-PLACE update for row2 an row3)
234 UPDATE t1 SET c0="c0_upd";
235
236 --echo # Let Alter table continue
237 SET DEBUG_SYNC = 'now SIGNAL s2';
238
239 --echo # connection default
240 connection default;
241 --reap
242
243 SELECT * FROM t1;
244
245 DISCONNECT con1;
246
247 # Wait till all disconnects are completed
248 --source include/wait_until_count_sessions.inc
249 DROP TABLE t1;
250
251 --echo # -----------------------
252 --echo # ONLINE DDL 3 :
253 --echo # -----------------------
254 --eval create table t1 (c1 char(10), c2 char(10)) ROW_FORMAT=$row_format
255 Insert into t1 values ("r1c1", "r1c2");
256 Select * from t1;
257
258 alter table t1 add column c3 char(10) default "c3_def", algorithm=instant;
259 Select * from t1;
260
261 Insert into t1 values ("r2c1", "r2c2", "r2c3");
262 Select * from t1;
263
264 Alter table t1 add column c4 char(10) default "c4_def" first, algorithm=INSTANT;
265 Select * from t1;
266
267 Insert into t1 values ("r3c4", "r3c1", "r3c2", "r3c3");
268 Select * from t1;
269
270 --echo # Make alter table wait
271 SET DEBUG_SYNC = 'row_log_table_apply1_before SIGNAL s1 WAIT_FOR s2';
272
273 --echo # Rebuild the table
274 --send_eval ALTER TABLE t1 force, algorithm=inplace;
275
276 connect (con1,localhost,root,,);
277 --echo # connection con1
278 connection con1;
279
280 SET DEBUG_SYNC = 'now WAIT_FOR s1';
281
282 --echo # Update query
283 eval UPDATE t1 SET c1="c1_upd";
284
285 --echo # Let Alter table continue
286 SET DEBUG_SYNC = 'now SIGNAL s2';
287
288 --echo # connection default
289 connection default;
290 --reap
291
292 SELECT * FROM t1;
293
294 DISCONNECT con1;
295
296 # Wait till all disconnects are completed
297 --source include/wait_until_count_sessions.inc
298 }
299
300 --echo ###########
301 --echo # CLEANUP #
302 --echo ###########
303 DROP TABLE t1;