Debian Codegraph @

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

raw
1
2 ####################################
3 # 1. Use default as ROW_FORMAT=REDUNDANT
4 SET GLOBAL innodb_default_row_format = REDUNDANT;
5 SELECT @@innodb_default_row_format;
6 @@innodb_default_row_format
7 redundant
8 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB;
9 === information_schema.innodb_tables and innodb_tablespaces ===
10 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
11 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
12 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
13 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
14 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
15 test/t1 test/t1 0 5 Redundant 0 Single
16 DROP TABLE t1;
17
18 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
19 === information_schema.innodb_tables and innodb_tablespaces ===
20 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
21 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
22 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
23 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
24 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
25 test/t1 test/t1 33 5 Dynamic 0 Single
26 DROP TABLE t1;
27
28 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=COMPACT;
29 === information_schema.innodb_tables and innodb_tablespaces ===
30 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
31 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
32 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
33 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
34 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
35 test/t1 test/t1 1 5 Compact 0 Single
36 DROP TABLE t1;
37
38 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
39 === information_schema.innodb_tables and innodb_tablespaces ===
40 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
41 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
42 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
43 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
44 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
45 test/t1 test/t1 0 5 Redundant 0 Single
46 DROP TABLE t1;
47
48 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB
49 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
50 === information_schema.innodb_tables and innodb_tablespaces ===
51 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
52 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
53 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
54 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
55 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
56 test/t1 test/t1 35 5 Compressed 1024 Single
57 DROP TABLE t1;
58
59
60 ####################################
61 # 2. Use default as ROW_FORMAT=REDUNDANT (TEMPORARY)
62 SET GLOBAL innodb_default_row_format = REDUNDANT;
63 SELECT @@innodb_default_row_format;
64 @@innodb_default_row_format
65 redundant
66 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB;
67 SHOW CREATE TABLE test.t1;
68 Table Create Table
69 t1 CREATE TEMPORARY TABLE `t1` (
70 `c1` text,
71 `c2` blob
72 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
73 DROP TABLE t1;
74
75 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
76 SHOW CREATE TABLE test.t1;
77 Table Create Table
78 t1 CREATE TEMPORARY TABLE `t1` (
79 `c1` text,
80 `c2` blob
81 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
82 DROP TABLE t1;
83
84 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=COMPACT;
85 SHOW CREATE TABLE test.t1;
86 Table Create Table
87 t1 CREATE TEMPORARY TABLE `t1` (
88 `c1` text,
89 `c2` blob
90 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
91 DROP TABLE t1;
92
93 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
94 SHOW CREATE TABLE test.t1;
95 Table Create Table
96 t1 CREATE TEMPORARY TABLE `t1` (
97 `c1` text,
98 `c2` blob
99 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
100 DROP TABLE t1;
101
102
103
104 ####################################
105 # 3. Use default as ROW_FORMAT=COMPACT
106 SET GLOBAL innodb_default_row_format = COMPACT;
107 SELECT @@innodb_default_row_format;
108 @@innodb_default_row_format
109 compact
110 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB;
111 === information_schema.innodb_tables and innodb_tablespaces ===
112 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
113 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
114 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
115 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
116 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
117 test/t1 test/t1 1 5 Compact 0 Single
118 DROP TABLE t1;
119
120 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
121 === information_schema.innodb_tables and innodb_tablespaces ===
122 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
123 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
124 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
125 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
126 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
127 test/t1 test/t1 33 5 Dynamic 0 Single
128 DROP TABLE t1;
129
130 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=COMPACT;
131 === information_schema.innodb_tables and innodb_tablespaces ===
132 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
133 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
134 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
135 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
136 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
137 test/t1 test/t1 1 5 Compact 0 Single
138 DROP TABLE t1;
139
140 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
141 === information_schema.innodb_tables and innodb_tablespaces ===
142 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
143 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
144 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
145 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
146 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
147 test/t1 test/t1 0 5 Redundant 0 Single
148 DROP TABLE t1;
149
150 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB
151 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
152 === information_schema.innodb_tables and innodb_tablespaces ===
153 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
154 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
155 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
156 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
157 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
158 test/t1 test/t1 35 5 Compressed 1024 Single
159 DROP TABLE t1;
160
161
162 ####################################
163 # 4. Use default as ROW_FORMAT=COMPACT (TEMPORARY)
164 SET GLOBAL innodb_default_row_format = COMPACT;
165 SELECT @@innodb_default_row_format;
166 @@innodb_default_row_format
167 compact
168 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB;
169 SHOW CREATE TABLE test.t1;
170 Table Create Table
171 t1 CREATE TEMPORARY TABLE `t1` (
172 `c1` text,
173 `c2` blob
174 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
175 DROP TABLE t1;
176
177 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
178 SHOW CREATE TABLE test.t1;
179 Table Create Table
180 t1 CREATE TEMPORARY TABLE `t1` (
181 `c1` text,
182 `c2` blob
183 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
184 DROP TABLE t1;
185
186 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=COMPACT;
187 SHOW CREATE TABLE test.t1;
188 Table Create Table
189 t1 CREATE TEMPORARY TABLE `t1` (
190 `c1` text,
191 `c2` blob
192 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
193 DROP TABLE t1;
194
195 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
196 SHOW CREATE TABLE test.t1;
197 Table Create Table
198 t1 CREATE TEMPORARY TABLE `t1` (
199 `c1` text,
200 `c2` blob
201 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
202 DROP TABLE t1;
203
204
205
206 ####################################
207 # 5. Use default as ROW_FORMAT=DYNAMIC
208 SET GLOBAL innodb_default_row_format = DYNAMIC;
209 SELECT @@innodb_default_row_format;
210 @@innodb_default_row_format
211 dynamic
212 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB;
213 === information_schema.innodb_tables and innodb_tablespaces ===
214 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
215 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
216 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
217 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
218 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
219 test/t1 test/t1 33 5 Dynamic 0 Single
220 DROP TABLE t1;
221
222 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
223 === information_schema.innodb_tables and innodb_tablespaces ===
224 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
225 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
226 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
227 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
228 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
229 test/t1 test/t1 33 5 Dynamic 0 Single
230 DROP TABLE t1;
231
232 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=COMPACT;
233 === information_schema.innodb_tables and innodb_tablespaces ===
234 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
235 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
236 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
237 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
238 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
239 test/t1 test/t1 1 5 Compact 0 Single
240 DROP TABLE t1;
241
242 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
243 === information_schema.innodb_tables and innodb_tablespaces ===
244 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
245 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
246 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
247 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
248 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
249 test/t1 test/t1 0 5 Redundant 0 Single
250 DROP TABLE t1;
251
252 CREATE TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB
253 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1;
254 === information_schema.innodb_tables and innodb_tablespaces ===
255 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
256 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
257 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
258 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
259 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
260 test/t1 test/t1 35 5 Compressed 1024 Single
261 DROP TABLE t1;
262
263
264 ####################################
265 # 6. Use default as ROW_FORMAT=DYNAMIC(TEMPORARY)
266 SET GLOBAL innodb_default_row_format = DYNAMIC;
267 SELECT @@innodb_default_row_format;
268 @@innodb_default_row_format
269 dynamic
270 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB;
271 SHOW CREATE TABLE test.t1;
272 Table Create Table
273 t1 CREATE TEMPORARY TABLE `t1` (
274 `c1` text,
275 `c2` blob
276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
277 DROP TABLE t1;
278
279 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
280 SHOW CREATE TABLE test.t1;
281 Table Create Table
282 t1 CREATE TEMPORARY TABLE `t1` (
283 `c1` text,
284 `c2` blob
285 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC
286 DROP TABLE t1;
287
288 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=COMPACT;
289 SHOW CREATE TABLE test.t1;
290 Table Create Table
291 t1 CREATE TEMPORARY TABLE `t1` (
292 `c1` text,
293 `c2` blob
294 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
295 DROP TABLE t1;
296
297 CREATE TEMPORARY TABLE t1(c1 TEXT,c2 BLOB) ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
298 SHOW CREATE TABLE test.t1;
299 Table Create Table
300 t1 CREATE TEMPORARY TABLE `t1` (
301 `c1` text,
302 `c2` blob
303 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
304 DROP TABLE t1;
305
306
307
308 ####################################
309 # 7. Test ALTERs on NORMAL TABLES
310
311 ####################################
312 # Check if table rebuilding alter isn't affect if table is created
313 # with explicit row_format
314 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=INNODB;
315 INSERT INTO t1 VALUES (1, 'abc');
316 === information_schema.innodb_tables and innodb_tablespaces ===
317 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
318 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
319 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
320 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
321 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
322 test/t1 test/t1 1 5 Compact 0 Single
323 SET GLOBAL innodb_default_row_format=DYNAMIC;
324 ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
325 # Here we expect COMPACT because it was explicitly specified at CREATE
326 === information_schema.innodb_tables and innodb_tablespaces ===
327 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
328 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
329 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
330 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
331 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
332 test/t1 test/t1 1 6 Compact 0 Single
333 DROP TABLE t1;
334
335 ####################################
336 # Check if table rebuilding alter is affected when there is no
337 # row_format specified at CREATE TABLE.
338 SET GLOBAL innodb_default_row_format = COMPACT;
339 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
340 INSERT INTO t1 VALUES (1, 'abc');
341 === information_schema.innodb_tables and innodb_tablespaces ===
342 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
343 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
344 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
345 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
346 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
347 test/t1 test/t1 1 5 Compact 0 Single
348 SET GLOBAL innodb_default_row_format = DYNAMIC;
349 ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
350 # Here we expect DYNAMIC because there is no explicit ROW_FORMAT and the
351 # default_row_format is changed to DYNAMIC just before ALTER
352 === information_schema.innodb_tables and innodb_tablespaces ===
353 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
354 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
355 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
356 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
357 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
358 test/t1 test/t1 33 6 Dynamic 0 Single
359 DROP TABLE t1;
360
361 ####################################
362 # Check the row_format effect on ALTER, ALGORITHM=COPY
363 SET GLOBAL innodb_default_row_format = REDUNDANT;
364 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
365 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
366 === information_schema.innodb_tables and innodb_tablespaces ===
367 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
368 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
369 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
370 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
371 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
372 test/t1 test/t1 0 5 Redundant 0 Single
373 SET GLOBAL innoDB_default_row_format = COMPACT;
374 ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
375 # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table isn't
376 # created with explicit row_format, so we expect ROW_FORMAT=COMPACT
377 === information_schema.innodb_tables and innodb_tablespaces ===
378 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
379 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
380 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
381 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
382 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
383 test/t1 test/t1 1 6 Compact 0 Single
384 DROP TABLE t1;
385
386 ###################################
387 # Check the row_format effect on ALTER, ALGORITH=COPY on
388 # create table with explicit row_format
389 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=INNODB;
390 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
391 === information_schema.innodb_tables and innodb_tablespaces ===
392 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
393 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
394 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
395 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
396 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
397 test/t1 test/t1 0 5 Redundant 0 Single
398 SET GLOBAL innoDB_default_row_format = COMPACT;
399 ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
400 # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table is
401 # created with explicit row_format, so we expect original
402 # ROW_FORMAT=REDUNDANT
403 === information_schema.innodb_tables and innodb_tablespaces ===
404 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
405 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
406 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
407 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
408 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
409 test/t1 test/t1 0 6 Redundant 0 Single
410 DROP TABLE t1;
411
412 ##################################
413 # Check row_format on ALTER ALGORITHM=INPLACE
414 SET GLOBAL innodb_default_row_format=COMPACT;
415 CREATE TABLE t1 (a INT PRIMARY KEY, b TEXT, KEY k1(b(10))) ENGINE=INNODB;
416 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
417 === information_schema.innodb_tables and innodb_tablespaces ===
418 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
419 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
420 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
421 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
422 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
423 test/t1 test/t1 1 5 Compact 0 Single
424 SET GLOBAL innodb_default_row_format=DYNAMIC;
425 ALTER TABLE t1 DROP INDEX k1;
426 # Because it is in-place operation, there is no rebuild, so the
427 # original format has to be retained.
428 === information_schema.innodb_tables and innodb_tablespaces ===
429 Table Name Tablespace Table Flags Columns Row Format Zip Size Space Type
430 mtr/asserted_test_suppressions mtr/asserted_test_suppressions 33 4 Dynamic 0 Single
431 mtr/global_suppressions mtr/global_suppressions 33 4 Dynamic 0 Single
432 mtr/test_ignored_global_suppressions mtr/test_ignored_global_suppressions 33 4 Dynamic 0 Single
433 mtr/test_suppressions mtr/test_suppressions 33 4 Dynamic 0 Single
434 test/t1 test/t1 1 5 Compact 0 Single
435 DROP TABLE t1;
436
437 ####################################
438 # 8. Test ALTERs on TEMPORARY TABLES
439
440 ####################################
441 # Check if table rebuilding alter isn't affect if table is created
442 # with explicit row_format
443 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=INNODB;
444 INSERT INTO t1 VALUES (1, 'abc');
445 SHOW CREATE TABLE test.t1;
446 Table Create Table
447 t1 CREATE TEMPORARY TABLE `t1` (
448 `a` int NOT NULL,
449 `b` text,
450 PRIMARY KEY (`a`)
451 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
452 SET GLOBAL innodb_default_row_format=DYNAMIC;
453 ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
454 # Here we expect COMPACT because it was explicitly specified at CREATE
455 SHOW CREATE TABLE test.t1;
456 Table Create Table
457 t1 CREATE TEMPORARY TABLE `t1` (
458 `a` int NOT NULL,
459 `b` text,
460 `c` int NOT NULL,
461 PRIMARY KEY (`c`)
462 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
463 DROP TABLE t1;
464
465 ####################################
466 # Check if table rebuilding alter is affected when there is no
467 # row_format specified at CREATE TABLE.
468 SET GLOBAL innodb_default_row_format = COMPACT;
469 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
470 INSERT INTO t1 VALUES (1, 'abc');
471 SHOW CREATE TABLE test.t1;
472 Table Create Table
473 t1 CREATE TEMPORARY TABLE `t1` (
474 `a` int NOT NULL,
475 `b` text,
476 PRIMARY KEY (`a`)
477 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
478 SET GLOBAL innodb_default_row_format = DYNAMIC;
479 ALTER TABLE t1 DROP PRIMARY KEY, ADD COLUMN c INT PRIMARY KEY;
480 # Here we expect DYNAMIC because there is no explicit ROW_FORMAT and the
481 # default_row_format is changed to DYNAMIC just before ALTER
482 SHOW CREATE TABLE test.t1;
483 Table Create Table
484 t1 CREATE TEMPORARY TABLE `t1` (
485 `a` int NOT NULL,
486 `b` text,
487 `c` int NOT NULL,
488 PRIMARY KEY (`c`)
489 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
490 DROP TABLE t1;
491
492 ####################################
493 # Check the row_format effect on ALTER, ALGORITHM=COPY
494 SET GLOBAL innodb_default_row_format = REDUNDANT;
495 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b TEXT) ENGINE=INNODB;
496 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
497 SHOW CREATE TABLE test.t1;
498 Table Create Table
499 t1 CREATE TEMPORARY TABLE `t1` (
500 `a` int NOT NULL,
501 `b` text,
502 PRIMARY KEY (`a`)
503 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
504 SET GLOBAL innoDB_default_row_format = COMPACT;
505 ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
506 # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table isn't
507 # created with explicit row_format, so we expect ROW_FORMAT=COMPACT
508 SHOW CREATE TABLE test.t1;
509 Table Create Table
510 t1 CREATE TEMPORARY TABLE `t1` (
511 `a` int NOT NULL,
512 `b` text,
513 `c2` blob,
514 PRIMARY KEY (`a`)
515 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
516 DROP TABLE t1;
517
518 ###################################
519 # Check the row_format effect on ALTER, ALGORITH=COPY on
520 # create table with explicit row_format
521 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=INNODB;
522 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
523 SHOW CREATE TABLE test.t1;
524 Table Create Table
525 t1 CREATE TEMPORARY TABLE `t1` (
526 `a` int NOT NULL,
527 `b` text,
528 PRIMARY KEY (`a`)
529 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
530 SET GLOBAL innoDB_default_row_format = COMPACT;
531 ALTER TABLE t1 ADD COLUMN c2 BLOB, ALGORITHM=COPY;
532 # Because of ALGORITHM=COPY, there is TABLE REBUILD and the table is
533 # created with explicit row_format, so we expect original
534 # ROW_FORMAT=REDUNDANT
535 SHOW CREATE TABLE test.t1;
536 Table Create Table
537 t1 CREATE TEMPORARY TABLE `t1` (
538 `a` int NOT NULL,
539 `b` text,
540 `c2` blob,
541 PRIMARY KEY (`a`)
542 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
543 DROP TABLE t1;
544
545 ##################################
546 # Check row_format on ALTER ALGORITHM=INPLACE
547 SET GLOBAL innodb_default_row_format=COMPACT;
548 CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b TEXT, KEY k1(b(10))) ENGINE=INNODB;
549 INSERT INTO t1 VALUES (1, REPEAT('abc',1000));
550 SHOW CREATE TABLE test.t1;
551 Table Create Table
552 t1 CREATE TEMPORARY TABLE `t1` (
553 `a` int NOT NULL,
554 `b` text,
555 PRIMARY KEY (`a`),
556 KEY `k1` (`b`(10))
557 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
558 SET GLOBAL innodb_default_row_format=DYNAMIC;
559 ALTER TABLE t1 DROP INDEX k1;
560 # Because it is in-place operation, there is no rebuild, so the
561 # original format has to be retained.
562 SHOW CREATE TABLE test.t1;
563 Table Create Table
564 t1 CREATE TEMPORARY TABLE `t1` (
565 `a` int NOT NULL,
566 `b` text,
567 PRIMARY KEY (`a`)
568 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
569 DROP TABLE t1;