Debian Codegraph @

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

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