Debian Codegraph @

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

raw
1 #
2 # Bug#31479542 : MYSQL 8 ORPHANED TABLE DUE TO UNCHECKED NON-EXISTENT ROW FORMAT CHECK.
3 #
4 # SETUP
5
6 # Test 1 : Upgrade from 5.7 having table with no row_format specified
7
8 SHOW VARIABLES LIKE "%innodb_default_row_format%";
9 Variable_name Value
10 innodb_default_row_format dynamic
11 # Stop the running the server
12 # Copy and unzip the datadir.
13 # Restart the server against the unzipped datadir
14 # restart: --datadir=DATADIR --innodb-page-size=16k
15 SHOW VARIABLES LIKE "%innodb_default_row_format%";
16 Variable_name Value
17 innodb_default_row_format dynamic
18 # Verify tables after upgrade
19 SHOW CREATE TABLE test.t1;
20 Table Create Table
21 t1 CREATE TABLE `t1` (
22 `c1` int unsigned NOT NULL AUTO_INCREMENT,
23 `c2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
24 PRIMARY KEY (`c1`)
25 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
26 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
27 FROM INFORMATION_SCHEMA.TABLES
28 WHERE TABLE_NAME="t1";
29 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
30 test t1 InnoDB Redundant
31 SELECT * FROM test.t1;
32 c1 c2
33 # Use default algorithm (INPLACE). Table not rebuilt. Expect error.
34 ALTER TABLE test.t1 ADD INDEX idx(C2);
35 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
36 # Use algorithm INPLACE. Table not rebuilt. Expect error.
37 ALTER TABLE test.t1 ADD INDEX idx(C2), ALGORITHM=INPLACE;
38 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
39 # Use algorithm COPY. Table rebuilt. Expect success.
40 ALTER TABLE test.t1 ADD INDEX idx(C2), ALGORITHM=COPY;
41 SELECT * FROM test.t1;
42 c1 c2
43 # Restart server
44 # restart: --datadir=DATADIR --innodb-page-size=16k
45 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
46 FROM INFORMATION_SCHEMA.TABLES
47 WHERE TABLE_NAME="t1";
48 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
49 test t1 InnoDB Dynamic
50 SELECT * FROM test.t1;
51 c1 c2
52 # Cleanup
53 DROP TABLE t1;
54 # Shutdown server
55 # Remove copied files
56 # Restarting server to restore server state
57 # restart:
58
59 # Test 2 : Test behavior of ALTER with different algorithm and create
60 # table options.
61
62 SET @innodb_default_row_format_saved = @@global.innodb_default_row_format;
63 # --------------- DEFAULT ALGORITHM -----------------
64 SET @@global.innodb_default_row_format=REDUNDANT;
65 CREATE TABLE t_explicit_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=REDUNDANT;
66 CREATE TABLE t_default_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=DEFAULT;
67 CREATE TABLE t_implicit_row_format (C1 INT KEY, C2 INT);
68 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
69 FROM INFORMATION_SCHEMA.TABLES
70 WHERE TABLE_NAME LIKE "t_%_row_format"
71 ORDER BY TABLE_NAME;
72 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
73 test t_default_row_format InnoDB Redundant
74 test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT
75 test t_implicit_row_format InnoDB Redundant
76 SET @@global.innodb_default_row_format=DYNAMIC;
77 # Add index. Table won't be rebuit. So no format change.
78 ALTER TABLE t_explicit_row_format ADD INDEX IDX_DEFAULT(C2);
79 ALTER TABLE t_default_row_format ADD INDEX IDX_DEFAULT(C2);
80 ALTER TABLE t_implicit_row_format ADD INDEX IDX_DEFAULT(C2);
81 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
82 FROM INFORMATION_SCHEMA.TABLES
83 WHERE TABLE_NAME LIKE "t_%_row_format"
84 ORDER BY TABLE_NAME;
85 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
86 test t_default_row_format InnoDB Redundant
87 test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT
88 test t_implicit_row_format InnoDB Redundant
89 ALTER TABLE t_explicit_row_format DROP INDEX IDX_DEFAULT;
90 ALTER TABLE t_default_row_format DROP INDEX IDX_DEFAULT;
91 ALTER TABLE t_implicit_row_format DROP INDEX IDX_DEFAULT;
92 # Add index with ROW_FORMAT. Table will be rebuilt. So format change for all.
93 ALTER TABLE t_explicit_row_format ADD INDEX IDX_DEFAULT(C2), ROW_FORMAT=DYNAMIC;
94 ALTER TABLE t_default_row_format ADD INDEX IDX_DEFAULT(C2), ROW_FORMAT=DYNAMIC;
95 ALTER TABLE t_implicit_row_format ADD INDEX IDX_DEFAULT(C2), ROW_FORMAT=DYNAMIC;
96 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
97 FROM INFORMATION_SCHEMA.TABLES
98 WHERE TABLE_NAME LIKE "t_%_row_format"
99 ORDER BY TABLE_NAME;
100 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
101 test t_default_row_format InnoDB Dynamic row_format=DYNAMIC
102 test t_explicit_row_format InnoDB Dynamic row_format=DYNAMIC
103 test t_implicit_row_format InnoDB Dynamic row_format=DYNAMIC
104 DROP TABLE t_explicit_row_format, t_default_row_format, t_implicit_row_format;
105 # --------------- INPLACE ALTER ---------------------
106 SET @@global.innodb_default_row_format=REDUNDANT;
107 CREATE TABLE t_explicit_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=REDUNDANT;
108 CREATE TABLE t_default_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=DEFAULT;
109 CREATE TABLE t_implicit_row_format (C1 INT KEY, C2 INT);
110 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
111 FROM INFORMATION_SCHEMA.TABLES
112 WHERE TABLE_NAME LIKE "t_%_row_format"
113 ORDER BY TABLE_NAME;
114 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
115 test t_default_row_format InnoDB Redundant
116 test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT
117 test t_implicit_row_format InnoDB Redundant
118 SET @@global.innodb_default_row_format=DYNAMIC;
119 # Add index. Table won't be rebuilt. So no format change.
120 ALTER TABLE t_explicit_row_format ADD INDEX IDX_INPLACE(C2), ALGORITHM=INPLACE;
121 ALTER TABLE t_default_row_format ADD INDEX IDX_INPLACE(C2), ALGORITHM=INPLACE;
122 ALTER TABLE t_implicit_row_format ADD INDEX IDX_INPLACE(C2), ALGORITHM=INPLACE;
123 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
124 FROM INFORMATION_SCHEMA.TABLES
125 WHERE TABLE_NAME LIKE "t_%_row_format"
126 ORDER BY TABLE_NAME;
127 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
128 test t_default_row_format InnoDB Redundant
129 test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT
130 test t_implicit_row_format InnoDB Redundant
131 ALTER TABLE t_explicit_row_format DROP INDEX IDX_INPLACE;
132 ALTER TABLE t_default_row_format DROP INDEX IDX_INPLACE;
133 ALTER TABLE t_implicit_row_format DROP INDEX IDX_INPLACE;
134 # Add index with FORCE. Table will be rebuilt. So format change for implicit and default.
135 ALTER TABLE t_explicit_row_format ADD INDEX IDX_INPLACE(C2), FORCE, ALGORITHM=INPLACE;
136 ALTER TABLE t_default_row_format ADD INDEX IDX_INPLACE(C2), FORCE, ALGORITHM=INPLACE;
137 ALTER TABLE t_implicit_row_format ADD INDEX IDX_INPLACE(C2), FORCE, ALGORITHM=INPLACE;
138 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
139 FROM INFORMATION_SCHEMA.TABLES
140 WHERE TABLE_NAME LIKE "t_%_row_format"
141 ORDER BY TABLE_NAME;
142 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
143 test t_default_row_format InnoDB Dynamic
144 test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT
145 test t_implicit_row_format InnoDB Dynamic
146 DROP TABLE t_explicit_row_format, t_default_row_format, t_implicit_row_format;
147 # --------------- COPY ALTER ------------------------
148 SET @@global.innodb_default_row_format=REDUNDANT;
149 CREATE TABLE t_explicit_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=REDUNDANT;
150 CREATE TABLE t_default_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=DEFAULT;
151 CREATE TABLE t_implicit_row_format (C1 INT KEY, C2 INT);
152 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
153 FROM INFORMATION_SCHEMA.TABLES
154 WHERE TABLE_NAME LIKE "t_%_row_format"
155 ORDER BY TABLE_NAME;
156 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
157 test t_default_row_format InnoDB Redundant
158 test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT
159 test t_implicit_row_format InnoDB Redundant
160 SET @@global.innodb_default_row_format=DYNAMIC;
161 # Add index. Table will be rebuilt. So format change for implicit and default.
162 ALTER TABLE t_explicit_row_format ADD INDEX IDX_COPY(C2), ALGORITHM=COPY;
163 ALTER TABLE t_default_row_format ADD INDEX IDX_COPY(C2), ALGORITHM=COPY;
164 ALTER TABLE t_implicit_row_format ADD INDEX IDX_COPY(C2), ALGORITHM=COPY;
165 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
166 FROM INFORMATION_SCHEMA.TABLES
167 WHERE TABLE_NAME LIKE "t_%_row_format"
168 ORDER BY TABLE_NAME;
169 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
170 test t_default_row_format InnoDB Dynamic
171 test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT
172 test t_implicit_row_format InnoDB Dynamic
173 # CLEANUP
174 DROP TABLE t_explicit_row_format;
175 DROP TABLE t_default_row_format;
176 DROP TABLE t_implicit_row_format;
177
178 # Test 3 : Test behavior of ALTER with different algorithm and create
179 # table options while try to add an index on tables which
180 # voilates length restriction.
181
182 SET @@global.innodb_default_row_format=REDUNDANT;
183 CREATE TABLE t_explicit_redundant (
184 C1 INT UNSIGNED NOT NULL AUTO_INCREMENT KEY,
185 C2 VARCHAR(767) NOT NULL DEFAULT ''
186 ) ROW_FORMAT=REDUNDANT;
187 CREATE TABLE t_implicit_redundant (
188 C1 INT UNSIGNED NOT NULL AUTO_INCREMENT KEY,
189 C2 VARCHAR(767) NOT NULL DEFAULT ''
190 );
191 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
192 FROM INFORMATION_SCHEMA.TABLES
193 WHERE TABLE_NAME LIKE "t_%_redundant"
194 ORDER BY TABLE_NAME;
195 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
196 test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT
197 test t_implicit_redundant InnoDB Redundant
198 SET @@global.innodb_default_row_format=DYNAMIC;
199 SET @sql_mode_saved = @@sql_mode;
200 SET sql_mode= '';
201 # --------------- DEFAULT ALGORITHM (INPLACE) -----------------
202 ALTER TABLE t_explicit_redundant ADD INDEX IDX(C2);
203 Warnings:
204 Warning 1071 Specified key was too long; max key length is 767 bytes
205 SHOW CREATE TABLE t_explicit_redundant;
206 Table Create Table
207 t_explicit_redundant CREATE TABLE `t_explicit_redundant` (
208 `C1` int unsigned NOT NULL AUTO_INCREMENT,
209 `C2` varchar(767) NOT NULL DEFAULT '',
210 PRIMARY KEY (`C1`),
211 KEY `IDX` (`C2`(191))
212 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
213 ALTER TABLE t_implicit_redundant ADD INDEX IDX(C2);
214 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
215 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
216 FROM INFORMATION_SCHEMA.TABLES
217 WHERE TABLE_NAME LIKE "t_%_redundant"
218 ORDER BY TABLE_NAME;
219 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
220 test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT
221 test t_implicit_redundant InnoDB Redundant
222 ALTER TABLE t_explicit_redundant DROP INDEX IDX;
223 # --------------- INPLACE ALTER ---------------------
224 ALTER TABLE t_explicit_redundant ADD INDEX IDX(C2), ALGORITHM=INPLACE;
225 Warnings:
226 Warning 1071 Specified key was too long; max key length is 767 bytes
227 SHOW CREATE TABLE t_explicit_redundant;
228 Table Create Table
229 t_explicit_redundant CREATE TABLE `t_explicit_redundant` (
230 `C1` int unsigned NOT NULL AUTO_INCREMENT,
231 `C2` varchar(767) NOT NULL DEFAULT '',
232 PRIMARY KEY (`C1`),
233 KEY `IDX` (`C2`(191))
234 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
235 ALTER TABLE t_implicit_redundant ADD INDEX IDX(C2), ALGORITHM=INPLACE;
236 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
237 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
238 FROM INFORMATION_SCHEMA.TABLES
239 WHERE TABLE_NAME LIKE "t_%_redundant"
240 ORDER BY TABLE_NAME;
241 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
242 test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT
243 test t_implicit_redundant InnoDB Redundant
244 ALTER TABLE t_explicit_redundant DROP INDEX IDX;
245 # --------------- DEFAULT ALGORITHM (COPY) -----------------
246 ALTER TABLE t_explicit_redundant ADD INDEX IDX(C2), MODIFY COLUMN C1 INT;
247 Warnings:
248 Warning 1071 Specified key was too long; max key length is 767 bytes
249 SHOW CREATE TABLE t_explicit_redundant;
250 Table Create Table
251 t_explicit_redundant CREATE TABLE `t_explicit_redundant` (
252 `C1` int NOT NULL,
253 `C2` varchar(767) NOT NULL DEFAULT '',
254 PRIMARY KEY (`C1`),
255 KEY `IDX` (`C2`(191))
256 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
257 ALTER TABLE t_implicit_redundant ADD INDEX IDX(C2), MODIFY COLUMN C1 INT;
258 SHOW CREATE TABLE t_implicit_redundant;
259 Table Create Table
260 t_implicit_redundant CREATE TABLE `t_implicit_redundant` (
261 `C1` int NOT NULL,
262 `C2` varchar(767) NOT NULL DEFAULT '',
263 PRIMARY KEY (`C1`),
264 KEY `IDX` (`C2`)
265 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
266 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
267 FROM INFORMATION_SCHEMA.TABLES
268 WHERE TABLE_NAME LIKE "t_%_redundant"
269 ORDER BY TABLE_NAME;
270 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
271 test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT
272 test t_implicit_redundant InnoDB Dynamic
273 DROP TABLE t_explicit_redundant, t_implicit_redundant;
274 SET @@global.innodb_default_row_format=REDUNDANT;
275 CREATE TABLE t_explicit_redundant (
276 C1 INT UNSIGNED NOT NULL AUTO_INCREMENT KEY,
277 C2 VARCHAR(767) NOT NULL DEFAULT ''
278 ) ROW_FORMAT=REDUNDANT;
279 CREATE TABLE t_implicit_redundant (
280 C1 INT UNSIGNED NOT NULL AUTO_INCREMENT KEY,
281 C2 VARCHAR(767) NOT NULL DEFAULT ''
282 );
283 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
284 FROM INFORMATION_SCHEMA.TABLES
285 WHERE TABLE_NAME LIKE "t_%_redundant"
286 ORDER BY TABLE_NAME;
287 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
288 test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT
289 test t_implicit_redundant InnoDB Redundant
290 SET @@global.innodb_default_row_format=DYNAMIC;
291 # --------------- COPY ALTER ------------------------
292 ALTER TABLE t_explicit_redundant ADD INDEX IDX(C2), ALGORITHM=COPY;
293 Warnings:
294 Warning 1071 Specified key was too long; max key length is 767 bytes
295 SHOW CREATE TABLE t_explicit_redundant;
296 Table Create Table
297 t_explicit_redundant CREATE TABLE `t_explicit_redundant` (
298 `C1` int unsigned NOT NULL AUTO_INCREMENT,
299 `C2` varchar(767) NOT NULL DEFAULT '',
300 PRIMARY KEY (`C1`),
301 KEY `IDX` (`C2`(191))
302 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT
303 ALTER TABLE t_implicit_redundant ADD INDEX IDX(C2), ALGORITHM=COPY;
304 SHOW CREATE TABLE t_implicit_redundant;
305 Table Create Table
306 t_implicit_redundant CREATE TABLE `t_implicit_redundant` (
307 `C1` int unsigned NOT NULL AUTO_INCREMENT,
308 `C2` varchar(767) NOT NULL DEFAULT '',
309 PRIMARY KEY (`C1`),
310 KEY `IDX` (`C2`)
311 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
312 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS
313 FROM INFORMATION_SCHEMA.TABLES
314 WHERE TABLE_NAME LIKE "t_%_redundant"
315 ORDER BY TABLE_NAME;
316 TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS
317 test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT
318 test t_implicit_redundant InnoDB Dynamic
319 ALTER TABLE t_explicit_redundant DROP INDEX IDX;
320 ALTER TABLE t_implicit_redundant DROP INDEX IDX;
321 # CLEANUP
322 DROP TABLE t_explicit_redundant, t_implicit_redundant;
323 SET sql_mode=@sql_mode_saved;
324 SET @@global.innodb_default_row_format=@innodb_default_row_format_saved;
325 #
326 # Bug#31912685 : ASSERTION FAILURE:HANDLER0ALTER.CC:
327 # 5194:OLD_TABLE->S->ROW_TYPE == ROW_TYPE_DEFA
328 #
329 SET @orig_innodb_file_per_table= @@innodb_file_per_table;
330 DROP TABLE IF EXISTS t_compressed;
331 Warnings:
332 Note 1051 Unknown table 'test.t_compressed'
333 CREATE TABLE t_compressed (a INT) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
334 SHOW CREATE TABLE t_compressed;
335 Table Create Table
336 t_compressed CREATE TABLE `t_compressed` (
337 `a` int DEFAULT NULL
338 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
339 SELECT TABLE_ID INTO @TID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t_compressed%";
340 SET GLOBAL innodb_file_per_table = 0;
341 ALTER TABLE t_compressed add index idx(a), ALGORITHM=INPLACE;
342 SHOW CREATE TABLE t_compressed;
343 Table Create Table
344 t_compressed CREATE TABLE `t_compressed` (
345 `a` int DEFAULT NULL,
346 KEY `idx` (`a`)
347 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2
348 SELECT TABLE_ID = @TID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t_compressed%";
349 TABLE_ID = @TID
350 1
351 # CLEANUP
352 SET GLOBAL innodb_file_per_table = @orig_innodb_file_per_table;
353 DROP TABLE t_compressed;
354 #
355 # Bug#35869747: Cannot drop index from upgraded instance
356 #
357 # Upgrade from 5.7.31 having tables with redundant row format and
358 # index longer than supported 767 bytes.
359 # Stop the running the server.
360 # Unzip the datadir.
361 # Restart the server against the unzipped datadir.
362 # restart: --datadir=DATADIR --lower_case_table_names=1
363 # Verify tables after upgrade.
364 # Table with single index of invalid length.
365 SHOW CREATE TABLE test.t1;
366 Table Create Table
367 t1 CREATE TABLE `t1` (
368 `fld1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
369 KEY `idx1` (`fld1`)
370 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
371 # Table with composite index of invalid length.
372 SHOW CREATE TABLE test.t2;
373 Table Create Table
374 t2 CREATE TABLE `t2` (
375 `fld1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
376 `fld2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
377 KEY `idx1` (`fld1`,`fld2`)
378 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
379 # Table with two indexes of invalid length.
380 SHOW CREATE TABLE test.t3;
381 Table Create Table
382 t3 CREATE TABLE `t3` (
383 `fld1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
384 `fld2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
385 KEY `idx1` (`fld1`),
386 KEY `idx2` (`fld2`)
387 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
388 # Table with prefix index of invalid length.
389 SHOW CREATE TABLE test.t4;
390 Table Create Table
391 t4 CREATE TABLE `t4` (
392 `fld1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
393 KEY `idx1` (`fld1`(250))
394 ) ENGINE=InnoDB DEFAULT CHARSET=latin1
395 # CHECK TABLE should flag indexes as corrupt after fix.
396 CHECK TABLE test.t1;
397 Table Op Msg_type Msg_text
398 test.t1 check Warning InnoDB: Index idx1 is marked as corrupted
399 test.t1 check error Corrupt
400 CHECK TABLE test.t2;
401 Table Op Msg_type Msg_text
402 test.t2 check Warning InnoDB: Index idx1 is marked as corrupted
403 test.t2 check error Corrupt
404 CHECK TABLE test.t3;
405 Table Op Msg_type Msg_text
406 test.t3 check Warning InnoDB: Index idx1 is marked as corrupted
407 test.t3 check Warning InnoDB: Index idx2 is marked as corrupted
408 test.t3 check error Corrupt
409 CHECK TABLE test.t4;
410 Table Op Msg_type Msg_text
411 test.t4 check Warning InnoDB: Index idx1 is marked as corrupted
412 test.t4 check error Corrupt
413 # TRUNCATE TABLE reports an index too long error since it is DROP + CREATE.
414 TRUNCATE TABLE test.t1;
415 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
416 TRUNCATE TABLE test.t2;
417 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
418 TRUNCATE TABLE test.t3;
419 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
420 TRUNCATE TABLE test.t4;
421 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
422 # SELECT statement which uses the index errors out flagging the corruption.
423 SELECT * FROM test.t1 FORCE INDEX (idx1);
424 ERROR HY000: Index idx1 is corrupted
425 SELECT * FROM test.t2 FORCE INDEX (idx1);
426 ERROR HY000: Index idx1 is corrupted
427 # SELECT statement which does not use the corrupted index succeeds.
428 SELECT * FROM test.t3;
429 fld1 fld2
430 t3abc t3efg
431 SELECT * FROM test.t4;
432 fld1
433 t4abc
434 # DROP INDEX succeeds after fix.
435 ALTER TABLE test.t1 DROP INDEX idx1;
436 ALTER TABLE test.t2 DROP INDEX idx1;
437 # DROP TABLE succeeds after fix.
438 DROP TABLE test.t3;
439 DROP TABLE test.t4;
440 # Cleanup
441 DROP TABLE test.t1;
442 DROP TABLE test.t2;
443 # Shutdown server
444 # Clean up data dir
445 # Restarting server to restore server state
446 # restart:
447 #
448 # Bug#37168132: Unexpected creation of an large index, causing an inaccessible
449 # table
450 SET @saved_innodb_default_row_format= @@global.innodb_default_row_format;
451 # Case 1: When row format is not mentioned in create table.
452 SET GLOBAL innodb_default_row_format='compact';
453 CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
454 # Expect an error to occur during an INPLACE ALTER operation.
455 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
456 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
457 # Coverage test for the remaining options of algorithm clause.
458 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
459 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
460 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
461 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
462 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
463 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
464 SET GLOBAL innodb_default_row_format='redundant';
465 CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
466 # Expect an error to occur during an INPLACE ALTER operation.
467 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
468 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
469 # Coverage test for the reamining options of algorithm clause.
470 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
471 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
472 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
473 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
474 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
475 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
476 SET GLOBAL innodb_default_row_format='dynamic';
477 CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
478 ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
479 # Coverage test for the reamining options of algorithm clause.
480 CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
481 ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
482 CREATE TABLE t5(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
483 ALTER TABLE t5 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
484 CREATE TABLE t6(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
485 ALTER TABLE t6 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
486 # Clean up
487 DROP TABLE t1, t2, t3, t4, t5, t6;
488 # Case 2: When row format mentioned is default.
489 SET GLOBAL innodb_default_row_format='compact';
490 CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a))
491 ROW_FORMAT=DEFAULT;
492 # Expect an error to occur during an INPLACE ALTER operation.
493 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
494 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
495 # Coverage test for the remaining options of algorithm clause
496 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
497 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
498 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
499 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
500 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
501 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
502 SET GLOBAL innodb_default_row_format='redundant';
503 CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a))
504 ROW_FORMAT=DEFAULT;
505 # Expect an error to occur during an INPLACE ALTER operation.
506 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
507 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
508 # Coverage test for the remaining options of algorithm clause
509 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
510 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
511 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
512 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
513 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
514 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
515 SET GLOBAL innodb_default_row_format='dynamic';
516 CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a))
517 ROW_FORMAT=DEFAULT;
518 ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
519 # Coverage test for the remaining options of algorithm clause
520 CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
521 ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
522 CREATE TABLE t5(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
523 ALTER TABLE t5 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
524 CREATE TABLE t6(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
525 ALTER TABLE t6 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
526 # Clean up
527 DROP TABLE t1, t2, t3, t4, t5, t6;
528 # Case 3: Changing the 'innodb_default_row_format' before ALTER TABLE operation
529 # Switch from COMPACT to DYNAMIC
530 SET GLOBAL innodb_default_row_format='compact';
531 CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a))
532 ROW_FORMAT=DEFAULT;
533 CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a))
534 ROW_FORMAT=DEFAULT;
535 CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a))
536 ROW_FORMAT=DEFAULT;
537 CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a))
538 ROW_FORMAT=DEFAULT;
539 SET GLOBAL innodb_default_row_format='dynamic';
540 # Expect an error to occur during an ALTER operation using INPLACE, DEFAULT
541 # algorithm and also when no algorithm clause is specified.
542 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
543 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
544 ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
545 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
546 ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
547 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
548 # ALTER TABLE using COPY algorithm will succeed since it rebuilds table
549 # with new row format 'DYNAMIC'
550 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
551 # Clean up
552 DROP TABLE t1, t2, t3, t4;
553 # Switch from DYNAMIC to COMPACT.
554 SET GLOBAL innodb_default_row_format='dynamic';
555 CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
556 CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
557 CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
558 CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a));
559 SET GLOBAL innodb_default_row_format='compact';
560 # ALTER TABLE operation succeeds when using INPLACE, DEFAULT algorithm and
561 # also when no algorithm is used..
562 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
563 ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
564 ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
565 # ALTER TABLE operation fails with COPY algorithm, since the table is rebuilt
566 # with compact format.
567 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
568 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
569 # Clean up
570 DROP TABLE t1, t2, t3, t4;
571 # Case 4: Tables with prefixed indexes.
572 # Test where prefixed index exceeds limit.
573 SET GLOBAL innodb_default_row_format='compact';
574 CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, b VARCHAR(100) CHARACTER
575 SET utf8mb4 NOT NULL, KEY (a(10), b));
576 CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, b VARCHAR(100) CHARACTER
577 SET utf8mb4 NOT NULL, KEY (a(10), b));
578 CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, b VARCHAR(100) CHARACTER
579 SET utf8mb4 NOT NULL, KEY (a(10), b));
580 CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, b VARCHAR(100) CHARACTER
581 SET utf8mb4 NOT NULL, KEY (a(10), b));
582 # Expect an error to occur during an INPLACE ALTER operation.
583 ALTER TABLE t1 MODIFY b VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
584 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
585 # Coverage test for the remaining options of algorithm clause
586 ALTER TABLE t2 MODIFY b VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
587 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
588 ALTER TABLE t3 MODIFY b VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
589 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
590 ALTER TABLE t4 MODIFY b VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
591 ERROR HY000: Index column size too large. The maximum column size is 767 bytes.
592 # Clean up
593 DROP TABLE t1, t2, t3, t4;
594 # Test for coverage, where the prefix index is within limit.
595 SET GLOBAL innodb_default_row_format='redundant';
596 CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY(a(10)));
597 CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY(a(10)));
598 CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY(a(10)));
599 CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY(a(10)));
600 # ALTER TABLE operation using INPLACE, COPY, DEFAULT algorithm and no
601 # algorithm clause succeeds.
602 ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE;
603 ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY;
604 ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT;
605 ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
606 # Clean up.
607 DROP TABLE t1, t2, t3, t4;
608 SET GLOBAL innodb_default_row_format= @saved_innodb_default_row_format;