# # Bug#31479542 : MYSQL 8 ORPHANED TABLE DUE TO UNCHECKED NON-EXISTENT ROW FORMAT CHECK. # # SETUP # Test 1 : Upgrade from 5.7 having table with no row_format specified SHOW VARIABLES LIKE "%innodb_default_row_format%"; Variable_name Value innodb_default_row_format dynamic # Stop the running the server # Copy and unzip the datadir. # Restart the server against the unzipped datadir # restart: --datadir=DATADIR --innodb-page-size=16k SHOW VARIABLES LIKE "%innodb_default_row_format%"; Variable_name Value innodb_default_row_format dynamic # Verify tables after upgrade SHOW CREATE TABLE test.t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` int unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '', PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1"; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t1 InnoDB Redundant SELECT * FROM test.t1; c1 c2 # Use default algorithm (INPLACE). Table not rebuilt. Expect error. ALTER TABLE test.t1 ADD INDEX idx(C2); ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Use algorithm INPLACE. Table not rebuilt. Expect error. ALTER TABLE test.t1 ADD INDEX idx(C2), ALGORITHM=INPLACE; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Use algorithm COPY. Table rebuilt. Expect success. ALTER TABLE test.t1 ADD INDEX idx(C2), ALGORITHM=COPY; SELECT * FROM test.t1; c1 c2 # Restart server # restart: --datadir=DATADIR --innodb-page-size=16k SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1"; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t1 InnoDB Dynamic SELECT * FROM test.t1; c1 c2 # Cleanup DROP TABLE t1; # Shutdown server # Remove copied files # Restarting server to restore server state # restart: # Test 2 : Test behavior of ALTER with different algorithm and create # table options. SET @innodb_default_row_format_saved = @@global.innodb_default_row_format; # --------------- DEFAULT ALGORITHM ----------------- SET @@global.innodb_default_row_format=REDUNDANT; CREATE TABLE t_explicit_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=REDUNDANT; CREATE TABLE t_default_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=DEFAULT; CREATE TABLE t_implicit_row_format (C1 INT KEY, C2 INT); SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_row_format" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_default_row_format InnoDB Redundant test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT test t_implicit_row_format InnoDB Redundant SET @@global.innodb_default_row_format=DYNAMIC; # Add index. Table won't be rebuit. So no format change. ALTER TABLE t_explicit_row_format ADD INDEX IDX_DEFAULT(C2); ALTER TABLE t_default_row_format ADD INDEX IDX_DEFAULT(C2); ALTER TABLE t_implicit_row_format ADD INDEX IDX_DEFAULT(C2); SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_row_format" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_default_row_format InnoDB Redundant test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT test t_implicit_row_format InnoDB Redundant ALTER TABLE t_explicit_row_format DROP INDEX IDX_DEFAULT; ALTER TABLE t_default_row_format DROP INDEX IDX_DEFAULT; ALTER TABLE t_implicit_row_format DROP INDEX IDX_DEFAULT; # Add index with ROW_FORMAT. Table will be rebuilt. So format change for all. ALTER TABLE t_explicit_row_format ADD INDEX IDX_DEFAULT(C2), ROW_FORMAT=DYNAMIC; ALTER TABLE t_default_row_format ADD INDEX IDX_DEFAULT(C2), ROW_FORMAT=DYNAMIC; ALTER TABLE t_implicit_row_format ADD INDEX IDX_DEFAULT(C2), ROW_FORMAT=DYNAMIC; SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_row_format" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_default_row_format InnoDB Dynamic row_format=DYNAMIC test t_explicit_row_format InnoDB Dynamic row_format=DYNAMIC test t_implicit_row_format InnoDB Dynamic row_format=DYNAMIC DROP TABLE t_explicit_row_format, t_default_row_format, t_implicit_row_format; # --------------- INPLACE ALTER --------------------- SET @@global.innodb_default_row_format=REDUNDANT; CREATE TABLE t_explicit_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=REDUNDANT; CREATE TABLE t_default_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=DEFAULT; CREATE TABLE t_implicit_row_format (C1 INT KEY, C2 INT); SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_row_format" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_default_row_format InnoDB Redundant test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT test t_implicit_row_format InnoDB Redundant SET @@global.innodb_default_row_format=DYNAMIC; # Add index. Table won't be rebuilt. So no format change. ALTER TABLE t_explicit_row_format ADD INDEX IDX_INPLACE(C2), ALGORITHM=INPLACE; ALTER TABLE t_default_row_format ADD INDEX IDX_INPLACE(C2), ALGORITHM=INPLACE; ALTER TABLE t_implicit_row_format ADD INDEX IDX_INPLACE(C2), ALGORITHM=INPLACE; SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_row_format" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_default_row_format InnoDB Redundant test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT test t_implicit_row_format InnoDB Redundant ALTER TABLE t_explicit_row_format DROP INDEX IDX_INPLACE; ALTER TABLE t_default_row_format DROP INDEX IDX_INPLACE; ALTER TABLE t_implicit_row_format DROP INDEX IDX_INPLACE; # Add index with FORCE. Table will be rebuilt. So format change for implicit and default. ALTER TABLE t_explicit_row_format ADD INDEX IDX_INPLACE(C2), FORCE, ALGORITHM=INPLACE; ALTER TABLE t_default_row_format ADD INDEX IDX_INPLACE(C2), FORCE, ALGORITHM=INPLACE; ALTER TABLE t_implicit_row_format ADD INDEX IDX_INPLACE(C2), FORCE, ALGORITHM=INPLACE; SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_row_format" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_default_row_format InnoDB Dynamic test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT test t_implicit_row_format InnoDB Dynamic DROP TABLE t_explicit_row_format, t_default_row_format, t_implicit_row_format; # --------------- COPY ALTER ------------------------ SET @@global.innodb_default_row_format=REDUNDANT; CREATE TABLE t_explicit_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=REDUNDANT; CREATE TABLE t_default_row_format (C1 INT KEY, C2 INT) ROW_FORMAT=DEFAULT; CREATE TABLE t_implicit_row_format (C1 INT KEY, C2 INT); SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_row_format" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_default_row_format InnoDB Redundant test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT test t_implicit_row_format InnoDB Redundant SET @@global.innodb_default_row_format=DYNAMIC; # Add index. Table will be rebuilt. So format change for implicit and default. ALTER TABLE t_explicit_row_format ADD INDEX IDX_COPY(C2), ALGORITHM=COPY; ALTER TABLE t_default_row_format ADD INDEX IDX_COPY(C2), ALGORITHM=COPY; ALTER TABLE t_implicit_row_format ADD INDEX IDX_COPY(C2), ALGORITHM=COPY; SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_row_format" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_default_row_format InnoDB Dynamic test t_explicit_row_format InnoDB Redundant row_format=REDUNDANT test t_implicit_row_format InnoDB Dynamic # CLEANUP DROP TABLE t_explicit_row_format; DROP TABLE t_default_row_format; DROP TABLE t_implicit_row_format; # Test 3 : Test behavior of ALTER with different algorithm and create # table options while try to add an index on tables which # voilates length restriction. SET @@global.innodb_default_row_format=REDUNDANT; CREATE TABLE t_explicit_redundant ( C1 INT UNSIGNED NOT NULL AUTO_INCREMENT KEY, C2 VARCHAR(767) NOT NULL DEFAULT '' ) ROW_FORMAT=REDUNDANT; CREATE TABLE t_implicit_redundant ( C1 INT UNSIGNED NOT NULL AUTO_INCREMENT KEY, C2 VARCHAR(767) NOT NULL DEFAULT '' ); SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_redundant" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT test t_implicit_redundant InnoDB Redundant SET @@global.innodb_default_row_format=DYNAMIC; SET @sql_mode_saved = @@sql_mode; SET sql_mode= ''; # --------------- DEFAULT ALGORITHM (INPLACE) ----------------- ALTER TABLE t_explicit_redundant ADD INDEX IDX(C2); Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes SHOW CREATE TABLE t_explicit_redundant; Table Create Table t_explicit_redundant CREATE TABLE `t_explicit_redundant` ( `C1` int unsigned NOT NULL AUTO_INCREMENT, `C2` varchar(767) NOT NULL DEFAULT '', PRIMARY KEY (`C1`), KEY `IDX` (`C2`(191)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ALTER TABLE t_implicit_redundant ADD INDEX IDX(C2); ERROR HY000: Index column size too large. The maximum column size is 767 bytes. SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_redundant" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT test t_implicit_redundant InnoDB Redundant ALTER TABLE t_explicit_redundant DROP INDEX IDX; # --------------- INPLACE ALTER --------------------- ALTER TABLE t_explicit_redundant ADD INDEX IDX(C2), ALGORITHM=INPLACE; Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes SHOW CREATE TABLE t_explicit_redundant; Table Create Table t_explicit_redundant CREATE TABLE `t_explicit_redundant` ( `C1` int unsigned NOT NULL AUTO_INCREMENT, `C2` varchar(767) NOT NULL DEFAULT '', PRIMARY KEY (`C1`), KEY `IDX` (`C2`(191)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ALTER TABLE t_implicit_redundant ADD INDEX IDX(C2), ALGORITHM=INPLACE; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_redundant" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT test t_implicit_redundant InnoDB Redundant ALTER TABLE t_explicit_redundant DROP INDEX IDX; # --------------- DEFAULT ALGORITHM (COPY) ----------------- ALTER TABLE t_explicit_redundant ADD INDEX IDX(C2), MODIFY COLUMN C1 INT; Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes SHOW CREATE TABLE t_explicit_redundant; Table Create Table t_explicit_redundant CREATE TABLE `t_explicit_redundant` ( `C1` int NOT NULL, `C2` varchar(767) NOT NULL DEFAULT '', PRIMARY KEY (`C1`), KEY `IDX` (`C2`(191)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ALTER TABLE t_implicit_redundant ADD INDEX IDX(C2), MODIFY COLUMN C1 INT; SHOW CREATE TABLE t_implicit_redundant; Table Create Table t_implicit_redundant CREATE TABLE `t_implicit_redundant` ( `C1` int NOT NULL, `C2` varchar(767) NOT NULL DEFAULT '', PRIMARY KEY (`C1`), KEY `IDX` (`C2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_redundant" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT test t_implicit_redundant InnoDB Dynamic DROP TABLE t_explicit_redundant, t_implicit_redundant; SET @@global.innodb_default_row_format=REDUNDANT; CREATE TABLE t_explicit_redundant ( C1 INT UNSIGNED NOT NULL AUTO_INCREMENT KEY, C2 VARCHAR(767) NOT NULL DEFAULT '' ) ROW_FORMAT=REDUNDANT; CREATE TABLE t_implicit_redundant ( C1 INT UNSIGNED NOT NULL AUTO_INCREMENT KEY, C2 VARCHAR(767) NOT NULL DEFAULT '' ); SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_redundant" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT test t_implicit_redundant InnoDB Redundant SET @@global.innodb_default_row_format=DYNAMIC; # --------------- COPY ALTER ------------------------ ALTER TABLE t_explicit_redundant ADD INDEX IDX(C2), ALGORITHM=COPY; Warnings: Warning 1071 Specified key was too long; max key length is 767 bytes SHOW CREATE TABLE t_explicit_redundant; Table Create Table t_explicit_redundant CREATE TABLE `t_explicit_redundant` ( `C1` int unsigned NOT NULL AUTO_INCREMENT, `C2` varchar(767) NOT NULL DEFAULT '', PRIMARY KEY (`C1`), KEY `IDX` (`C2`(191)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=REDUNDANT ALTER TABLE t_implicit_redundant ADD INDEX IDX(C2), ALGORITHM=COPY; SHOW CREATE TABLE t_implicit_redundant; Table Create Table t_implicit_redundant CREATE TABLE `t_implicit_redundant` ( `C1` int unsigned NOT NULL AUTO_INCREMENT, `C2` varchar(767) NOT NULL DEFAULT '', PRIMARY KEY (`C1`), KEY `IDX` (`C2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, ROW_FORMAT, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE "t_%_redundant" ORDER BY TABLE_NAME; TABLE_SCHEMA TABLE_NAME ENGINE ROW_FORMAT CREATE_OPTIONS test t_explicit_redundant InnoDB Redundant row_format=REDUNDANT test t_implicit_redundant InnoDB Dynamic ALTER TABLE t_explicit_redundant DROP INDEX IDX; ALTER TABLE t_implicit_redundant DROP INDEX IDX; # CLEANUP DROP TABLE t_explicit_redundant, t_implicit_redundant; SET sql_mode=@sql_mode_saved; SET @@global.innodb_default_row_format=@innodb_default_row_format_saved; # # Bug#31912685 : ASSERTION FAILURE:HANDLER0ALTER.CC: # 5194:OLD_TABLE->S->ROW_TYPE == ROW_TYPE_DEFA # SET @orig_innodb_file_per_table= @@innodb_file_per_table; DROP TABLE IF EXISTS t_compressed; Warnings: Note 1051 Unknown table 'test.t_compressed' CREATE TABLE t_compressed (a INT) ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2; SHOW CREATE TABLE t_compressed; Table Create Table t_compressed CREATE TABLE `t_compressed` ( `a` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 SELECT TABLE_ID INTO @TID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t_compressed%"; SET GLOBAL innodb_file_per_table = 0; ALTER TABLE t_compressed add index idx(a), ALGORITHM=INPLACE; SHOW CREATE TABLE t_compressed; Table Create Table t_compressed CREATE TABLE `t_compressed` ( `a` int DEFAULT NULL, KEY `idx` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2 SELECT TABLE_ID = @TID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME LIKE "%t_compressed%"; TABLE_ID = @TID 1 # CLEANUP SET GLOBAL innodb_file_per_table = @orig_innodb_file_per_table; DROP TABLE t_compressed; # # Bug#35869747: Cannot drop index from upgraded instance # # Upgrade from 5.7.31 having tables with redundant row format and # index longer than supported 767 bytes. # Stop the running the server. # Unzip the datadir. # Restart the server against the unzipped datadir. # restart: --datadir=DATADIR --lower_case_table_names=1 # Verify tables after upgrade. # Table with single index of invalid length. SHOW CREATE TABLE test.t1; Table Create Table t1 CREATE TABLE `t1` ( `fld1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, KEY `idx1` (`fld1`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 # Table with composite index of invalid length. SHOW CREATE TABLE test.t2; Table Create Table t2 CREATE TABLE `t2` ( `fld1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `fld2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, KEY `idx1` (`fld1`,`fld2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 # Table with two indexes of invalid length. SHOW CREATE TABLE test.t3; Table Create Table t3 CREATE TABLE `t3` ( `fld1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, `fld2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, KEY `idx1` (`fld1`), KEY `idx2` (`fld2`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 # Table with prefix index of invalid length. SHOW CREATE TABLE test.t4; Table Create Table t4 CREATE TABLE `t4` ( `fld1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL, KEY `idx1` (`fld1`(250)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 # CHECK TABLE should flag indexes as corrupt after fix. CHECK TABLE test.t1; Table Op Msg_type Msg_text test.t1 check Warning InnoDB: Index idx1 is marked as corrupted test.t1 check error Corrupt CHECK TABLE test.t2; Table Op Msg_type Msg_text test.t2 check Warning InnoDB: Index idx1 is marked as corrupted test.t2 check error Corrupt CHECK TABLE test.t3; Table Op Msg_type Msg_text test.t3 check Warning InnoDB: Index idx1 is marked as corrupted test.t3 check Warning InnoDB: Index idx2 is marked as corrupted test.t3 check error Corrupt CHECK TABLE test.t4; Table Op Msg_type Msg_text test.t4 check Warning InnoDB: Index idx1 is marked as corrupted test.t4 check error Corrupt # TRUNCATE TABLE reports an index too long error since it is DROP + CREATE. TRUNCATE TABLE test.t1; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. TRUNCATE TABLE test.t2; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. TRUNCATE TABLE test.t3; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. TRUNCATE TABLE test.t4; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # SELECT statement which uses the index errors out flagging the corruption. SELECT * FROM test.t1 FORCE INDEX (idx1); ERROR HY000: Index idx1 is corrupted SELECT * FROM test.t2 FORCE INDEX (idx1); ERROR HY000: Index idx1 is corrupted # SELECT statement which does not use the corrupted index succeeds. SELECT * FROM test.t3; fld1 fld2 t3abc t3efg SELECT * FROM test.t4; fld1 t4abc # DROP INDEX succeeds after fix. ALTER TABLE test.t1 DROP INDEX idx1; ALTER TABLE test.t2 DROP INDEX idx1; # DROP TABLE succeeds after fix. DROP TABLE test.t3; DROP TABLE test.t4; # Cleanup DROP TABLE test.t1; DROP TABLE test.t2; # Shutdown server # Clean up data dir # Restarting server to restore server state # restart: # # Bug#37168132: Unexpected creation of an large index, causing an inaccessible # table SET @saved_innodb_default_row_format= @@global.innodb_default_row_format; # Case 1: When row format is not mentioned in create table. SET GLOBAL innodb_default_row_format='compact'; CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); # Expect an error to occur during an INPLACE ALTER operation. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Coverage test for the remaining options of algorithm clause. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. SET GLOBAL innodb_default_row_format='redundant'; CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); # Expect an error to occur during an INPLACE ALTER operation. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Coverage test for the reamining options of algorithm clause. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. SET GLOBAL innodb_default_row_format='dynamic'; CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; # Coverage test for the reamining options of algorithm clause. CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; CREATE TABLE t5(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); ALTER TABLE t5 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; CREATE TABLE t6(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); ALTER TABLE t6 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; # Clean up DROP TABLE t1, t2, t3, t4, t5, t6; # Case 2: When row format mentioned is default. SET GLOBAL innodb_default_row_format='compact'; CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ROW_FORMAT=DEFAULT; # Expect an error to occur during an INPLACE ALTER operation. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Coverage test for the remaining options of algorithm clause ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. SET GLOBAL innodb_default_row_format='redundant'; CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ROW_FORMAT=DEFAULT; # Expect an error to occur during an INPLACE ALTER operation. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Coverage test for the remaining options of algorithm clause ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. SET GLOBAL innodb_default_row_format='dynamic'; CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ROW_FORMAT=DEFAULT; ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; # Coverage test for the remaining options of algorithm clause CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; CREATE TABLE t5(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); ALTER TABLE t5 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; CREATE TABLE t6(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); ALTER TABLE t6 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; # Clean up DROP TABLE t1, t2, t3, t4, t5, t6; # Case 3: Changing the 'innodb_default_row_format' before ALTER TABLE operation # Switch from COMPACT to DYNAMIC SET GLOBAL innodb_default_row_format='compact'; CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ROW_FORMAT=DEFAULT; CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ROW_FORMAT=DEFAULT; CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ROW_FORMAT=DEFAULT; CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ROW_FORMAT=DEFAULT; SET GLOBAL innodb_default_row_format='dynamic'; # Expect an error to occur during an ALTER operation using INPLACE, DEFAULT # algorithm and also when no algorithm clause is specified. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # ALTER TABLE using COPY algorithm will succeed since it rebuilds table # with new row format 'DYNAMIC' ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; # Clean up DROP TABLE t1, t2, t3, t4; # Switch from DYNAMIC to COMPACT. SET GLOBAL innodb_default_row_format='dynamic'; CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)); SET GLOBAL innodb_default_row_format='compact'; # ALTER TABLE operation succeeds when using INPLACE, DEFAULT algorithm and # also when no algorithm is used.. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; # ALTER TABLE operation fails with COPY algorithm, since the table is rebuilt # with compact format. ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Clean up DROP TABLE t1, t2, t3, t4; # Case 4: Tables with prefixed indexes. # Test where prefixed index exceeds limit. SET GLOBAL innodb_default_row_format='compact'; CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, b VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a(10), b)); CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, b VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a(10), b)); CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, b VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a(10), b)); CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, b VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a(10), b)); # Expect an error to occur during an INPLACE ALTER operation. ALTER TABLE t1 MODIFY b VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Coverage test for the remaining options of algorithm clause ALTER TABLE t2 MODIFY b VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t3 MODIFY b VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. ALTER TABLE t4 MODIFY b VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; ERROR HY000: Index column size too large. The maximum column size is 767 bytes. # Clean up DROP TABLE t1, t2, t3, t4; # Test for coverage, where the prefix index is within limit. SET GLOBAL innodb_default_row_format='redundant'; CREATE TABLE t1(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY(a(10))); CREATE TABLE t2(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY(a(10))); CREATE TABLE t3(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY(a(10))); CREATE TABLE t4(a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY(a(10))); # ALTER TABLE operation using INPLACE, COPY, DEFAULT algorithm and no # algorithm clause succeeds. ALTER TABLE t1 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=INPLACE; ALTER TABLE t2 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=COPY; ALTER TABLE t3 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL, ALGORITHM=DEFAULT; ALTER TABLE t4 MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL; # Clean up. DROP TABLE t1, t2, t3, t4; SET GLOBAL innodb_default_row_format= @saved_innodb_default_row_format;