Debian Codegraph @

mysql-test/suite/innodb/t/bug33788578_ddl.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 # Additional test cases to verify that ALTER ADD PRIMARY KEY works fine
7 --echo #
8 --echo # What this test is doing:
9 --echo #
10 --echo # Upgrade server to latest
11 --echo # Keep a zipped copy of upgraded datadir
12 --echo #
13 --echo # For all possible primary key columns
14 --echo # --let $pk_col = [c3, c7, c11, c5, c9, c13] {
15 --echo #
16 --echo # Shutdown server, unzip upgraded datadir
17 --echo # Now we can process $pk_col
18 --echo #
19 --echo # For all row formats
20 --echo # --let $row_format = [redundant, compact, dynamic] {
21 --echo # --source suite/innodb/include/bug33788578_ddl_prepare.inc
22 --echo #
23 --echo # For all tables
24 --echo # --let $table_name = [ta1b1, ta1b2, ta1b4, ta2b1, ta2b2, ta2b4, ta4b1 ta4b2, ta4b4] _$row_format {
25 --echo # Add the primary key
26 --echo # --source suite/innodb/include/bug33788578_ddl.inc
27 --echo # }
28 --echo # }
29 --echo #
30 --echo # }
31 --echo # Check comments near end of file to see what a1b1, a2b4, etc., means
32
33 --echo # This column will be made as the primary key. TODO: include cases for each possible pk_col - test may take too long
34 --let $pk_col = c3
35
36 --echo # Upgrade previous tables to current
37 --source include/shutdown_mysqld.inc
38 --copy_file $MYSQLTEST_VARDIR/std_data/instant_ddl/bug33788578_ddl.zip $MYSQL_TMP_DIR/bug33788578_ddl.zip
39 --file_exists $MYSQL_TMP_DIR/bug33788578_ddl.zip
40 --exec unzip -qo $MYSQL_TMP_DIR/bug33788578_ddl.zip -d $MYSQL_TMP_DIR
41 let $DATADIR = $MYSQL_TMP_DIR/data;
42
43 --echo # Restart the server
44 --replace_result $DATADIR DATADIR
45 --let $restart_parameters = restart: --datadir=$DATADIR
46 --let $wait_counter=3000
47 --source include/start_mysqld.inc
48
49 SHOW TABLES;
50
51 --echo # For all REDUNDANT tables:
52 --let $row_format = redundant
53 --echo # Prepare the table
54 --source suite/innodb/include/bug33788578_ddl_prepare.inc
55 --echo # For each table, run the test
56 --echo # Run for ta1b1
57 --let $table_name = ta1b1_$row_format
58 --source suite/innodb/include/bug33788578_ddl.inc
59
60 --echo # Run for ta1b2
61 --let $table_name = ta1b2_$row_format
62 --source suite/innodb/include/bug33788578_ddl.inc
63
64 --echo # Run for ta1b4
65 --let $table_name = ta1b4_$row_format
66 --source suite/innodb/include/bug33788578_ddl.inc
67
68 --echo # Run for ta2b1
69 --let $table_name = ta2b1_$row_format
70 --source suite/innodb/include/bug33788578_ddl.inc
71
72 --echo # Run for ta2b2
73 --let $table_name = ta2b2_$row_format
74 --source suite/innodb/include/bug33788578_ddl.inc
75
76 --echo # Run for ta2b4
77 --let $table_name = ta2b4_$row_format
78 --source suite/innodb/include/bug33788578_ddl.inc
79
80 --echo # Run for ta4b1
81 --let $table_name = ta4b1_$row_format
82 --source suite/innodb/include/bug33788578_ddl.inc
83
84 --echo # Run for ta4b2
85 --let $table_name = ta4b2_$row_format
86 --source suite/innodb/include/bug33788578_ddl.inc
87
88 --echo # Run for ta4b4
89 --let $table_name = ta4b4_$row_format
90 --source suite/innodb/include/bug33788578_ddl.inc
91
92 --echo # For all COMPACT tables:
93 --let $row_format = compact
94 --echo # Prepare the table
95 --source suite/innodb/include/bug33788578_ddl_prepare.inc
96 --echo # For each table, run the test
97 --echo # Run for ta1b1
98 --let $table_name = ta1b1_$row_format
99 --source suite/innodb/include/bug33788578_ddl.inc
100
101 --echo # Run for ta1b2
102 --let $table_name = ta1b2_$row_format
103 --source suite/innodb/include/bug33788578_ddl.inc
104
105 --echo # Run for ta1b4
106 --let $table_name = ta1b4_$row_format
107 --source suite/innodb/include/bug33788578_ddl.inc
108
109 --echo # Run for ta2b1
110 --let $table_name = ta2b1_$row_format
111 --source suite/innodb/include/bug33788578_ddl.inc
112
113 --echo # Run for ta2b2
114 --let $table_name = ta2b2_$row_format
115 --source suite/innodb/include/bug33788578_ddl.inc
116
117 --echo # Run for ta2b4
118 --let $table_name = ta2b4_$row_format
119 --source suite/innodb/include/bug33788578_ddl.inc
120
121 --echo # Run for ta4b1
122 --let $table_name = ta4b1_$row_format
123 --source suite/innodb/include/bug33788578_ddl.inc
124
125 --echo # Run for ta4b2
126 --let $table_name = ta4b2_$row_format
127 --source suite/innodb/include/bug33788578_ddl.inc
128
129 --echo # Run for ta4b4
130 --let $table_name = ta4b4_$row_format
131 --source suite/innodb/include/bug33788578_ddl.inc
132
133 --echo # For all DYNAMIC tables:
134 --let $row_format = dynamic
135 --echo # Prepare the table
136 --source suite/innodb/include/bug33788578_ddl_prepare.inc
137 --echo # For each table, run the test
138 --echo # Run for ta1b1
139 --let $table_name = ta1b1_$row_format
140 --source suite/innodb/include/bug33788578_ddl.inc
141
142 --echo # Run for ta1b2
143 --let $table_name = ta1b2_$row_format
144 --source suite/innodb/include/bug33788578_ddl.inc
145
146 --echo # Run for ta1b4
147 --let $table_name = ta1b4_$row_format
148 --source suite/innodb/include/bug33788578_ddl.inc
149
150 --echo # Run for ta2b1
151 --let $table_name = ta2b1_$row_format
152 --source suite/innodb/include/bug33788578_ddl.inc
153
154 --echo # Run for ta2b2
155 --let $table_name = ta2b2_$row_format
156 --source suite/innodb/include/bug33788578_ddl.inc
157
158 --echo # Run for ta2b4
159 --let $table_name = ta2b4_$row_format
160 --source suite/innodb/include/bug33788578_ddl.inc
161
162 --echo # Run for ta4b1
163 --let $table_name = ta4b1_$row_format
164 --source suite/innodb/include/bug33788578_ddl.inc
165
166 --echo # Run for ta4b2
167 --let $table_name = ta4b2_$row_format
168 --source suite/innodb/include/bug33788578_ddl.inc
169
170 --echo # Run for ta4b4
171 --let $table_name = ta4b4_$row_format
172 --source suite/innodb/include/bug33788578_ddl.inc
173
174 --echo # Cleanup
175 --echo # Shutdown server
176 --source include/shutdown_mysqld.inc
177
178 --echo # Remove temp files used
179 --file_exists $MYSQL_TMP_DIR/bug33788578_ddl.zip
180 --force-rmdir $MYSQL_TMP_DIR/data
181 --remove_file $MYSQL_TMP_DIR/bug33788578_ddl.zip
182
183 --echo # Restart server to restore server state
184 let $restart_parameters =;
185 --source include/start_mysqld.inc
186
187 # Tables created in 8.0.28:
188 # ======
189 # 8.0.28
190 # ======
191 #
192 # DROP DATABASE IF EXISTS test; CREATE DATABASE test;
193 # USE test;
194 #
195 # -- ta1b1
196 # CREATE TABLE ta1b1 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
197 #
198 # INSERT INTO ta1b1 VALUES (1, "row1", 2, 3, 4, 5);
199 # INSERT INTO ta1b1 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
200 #
201 # -- no alter in 8.0.28 --
202 # -- <UPGRADE>
203 #
204 # -- ta1b2
205 # CREATE TABLE ta1b2 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
206 #
207 # INSERT INTO ta1b2 VALUES (1, "row1", 2, 3, 4, 5);
208 # INSERT INTO ta1b2 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
209 #
210 # -- no alter in 8.0.28 --
211 # -- <UPGRADE>
212 #
213 # -- ta1b4
214 # CREATE TABLE ta1b4 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
215 #
216 # INSERT INTO ta1b4 VALUES (1, "row1", 2, 3, 4, 5);
217 # INSERT INTO ta1b4 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
218 #
219 # -- no alter in 8.0.28 --
220 # -- <UPGRADE>
221 #
222 # -- ta2b1
223 # CREATE TABLE ta2b1 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
224 #
225 # INSERT INTO ta2b1 VALUES (1, "row1", 2, 3, 4, 5);
226 # INSERT INTO ta2b1 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
227 #
228 # -- alter add in 8.0.28 --
229 # ALTER TABLE ta2b1 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
230 # ALTER TABLE ta2b1 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
231 #
232 # -- <UPGRADE>
233 #
234 # -- ta2b2
235 # CREATE TABLE ta2b2 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
236 #
237 # INSERT INTO ta2b2 VALUES (1, "row1", 2, 3, 4, 5);
238 # INSERT INTO ta2b2 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
239 #
240 # -- alter add in 8.0.28 --
241 # ALTER TABLE ta2b2 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
242 # ALTER TABLE ta2b2 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
243 #
244 # -- <UPGRADE>
245 #
246 # -- ta2b4
247 # CREATE TABLE ta2b4 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
248 #
249 # INSERT INTO ta2b4 VALUES (1, "row1", 2, 3, 4, 5);
250 # INSERT INTO ta2b4 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
251 #
252 # -- alter add in 8.0.28 --
253 # ALTER TABLE ta2b4 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
254 # ALTER TABLE ta2b4 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
255 #
256 # -- <UPGRADE>
257 #
258 # -- ta4b1
259 # CREATE TABLE ta4b1 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
260 #
261 # INSERT INTO ta4b1 VALUES (1, "row1", 2, 3, 4, 5);
262 # INSERT INTO ta4b1 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
263 #
264 # -- alter add in 8.0.28 --
265 # ALTER TABLE ta4b1 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
266 # ALTER TABLE ta4b1 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
267 #
268 # INSERT INTO ta4b1 VALUES (3, "row3", 222, 333, 444, 555, 666, 777, 888, 999);
269 # INSERT INTO ta4b1 (c0, c1, c3, c5, c7, c9) VALUES (4, "row4", 3333, 5555, 7777, 9999);
270 #
271 # -- <UPGRADE>
272 #
273 # -- ta4b2
274 # CREATE TABLE ta4b2 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
275 #
276 # INSERT INTO ta4b2 VALUES (1, "row1", 2, 3, 4, 5);
277 # INSERT INTO ta4b2 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
278 #
279 # -- alter add in 8.0.28 --
280 # ALTER TABLE ta4b2 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
281 # ALTER TABLE ta4b2 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
282 #
283 # INSERT INTO ta4b2 VALUES (3, "row3", 222, 333, 444, 555, 666, 777, 888, 999);
284 # INSERT INTO ta4b2 (c0, c1, c3, c5, c7, c9) VALUES (4, "row4", 3333, 5555, 7777, 9999);
285 #
286 # -- <UPGRADE>
287 #
288 # -- ta4b4
289 # CREATE TABLE ta4b4 (c0 INT, c1 TEXT, c2 INT, c3 INT NOT NULL, c4 INT DEFAULT 40, c5 INT NOT NULL DEFAULT 50) ENGINE=INNODB;
290 #
291 # INSERT INTO ta4b4 VALUES (1, "row1", 2, 3, 4, 5);
292 # INSERT INTO ta4b4 (c0, c1, c3, c5) VALUES (2, "row2", 33, 55);
293 #
294 # -- alter add in 8.0.28 --
295 # ALTER TABLE ta4b4 ADD COLUMN c6 INT, ADD COLUMN c7 INT NOT NULL, ALGORITHM=INSTANT;
296 # ALTER TABLE ta4b4 ADD COLUMN c8 INT DEFAULT 80, ADD COLUMN c9 INT NOT NULL DEFAULT 90, ALGORITHM=INSTANT;
297 #
298 # INSERT INTO ta4b4 VALUES (3, "row3", 222, 333, 444, 555, 666, 777, 888, 999);
299 # INSERT INTO ta4b4 (c0, c1, c3, c5, c7, c9) VALUES (4, "row4", 3333, 5555, 7777, 9999);
300 #
301 # -- <UPGRADE>
302 #
303 # What does an, bn mean:
304 #
305 # [mysql-8.0.28-release] Table definition
306 # a1. Table: instant rows: n & instant cols: n
307 # a2. Table: instant rows: n & instant cols: y
308 # a3. Table: instant rows: y & instant cols: n------------<<< not possible
309 # a4. Table: instant rows: y & instant cols: y
310 #
311 # [mysql-trunk] Table definition
312 # b1. Table: version rows: n & version cols: n
313 # b2. Table: version rows: n & version cols: y
314 # b3. Table: version rows: y & version cols: n------------<< not possible
315 # b4. Table: version rows: y & version cols: y
316 #
317 # [Combinations of tables] Table after upgrade:
318 # a1 [a1 - b1] Table: instant rows: n & instant cols: n && version rows: n & version cols: n
319 # [a1 - b2] Table: instant rows: n & instant cols: n && version rows: n & version cols: y
320 # [a1 - b3] Table: instant rows: n & instant cols: n && version rows: y & version cols: n------------<< not possible
321 # [a1 - b4] Table: instant rows: n & instant cols: n && version rows: y & version cols: y
322 # a2 [a2 - b1] Table: instant rows: n & instant cols: y && version rows: n & version cols: n
323 # [a2 - b2] Table: instant rows: n & instant cols: y && version rows: n & version cols: y
324 # [a2 - b3] Table: instant rows: n & instant cols: y && version rows: y & version cols: n------------<< not possible
325 # [a2 - b4] Table: instant rows: n & instant cols: y && version rows: y & version cols: y
326 # a3 [a3 - b1] Table: instant rows: y & instant cols: n && version rows: n & version cols: n------------<< not possible
327 # [a3 - b2] Table: instant rows: y & instant cols: n && version rows: n & version cols: y------------<< not possible
328 # [a3 - b3] Table: instant rows: y & instant cols: n && version rows: y & version cols: n------------<< not possible
329 # [a3 - b4] Table: instant rows: y & instant cols: n && version rows: y & version cols: y------------<< not possible
330 # a4 [a4 - b1] Table: instant rows: y & instant cols: y && version rows: n & version cols: n
331 # [a4 - b2] Table: instant rows: y & instant cols: y && version rows: n & version cols: y
332 # [a4 - b3] Table: instant rows: y & instant cols: y && version rows: y & version cols: n------------<< not possible
333 # [a4 - b4] Table: instant rows: y & instant cols: y && version rows: y & version cols: y
334 #
335 # Final tables: [a1b1, a1b2, a1b4, a2b1, a2b2, a2b4, a4b1, a4b2, a4b4]
336 #
337 # End