MySQL Bugs: #116353: Unexpected creation of an large index, causing an inaccessible table

Bug #116353	Unexpected creation of an large index, causing an inaccessible table
Submitted:	14 Oct 2024 9:57	Modified:	10 Mar 10:58
Reporter:	Huaxiong Song (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.39	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The REDUNDANT and COMPACT row formats support a maximum index key prefix length of 767 bytes. However, we can break this limitation by setting 'innodb_default_row_format' when creating a table without explicitly specifying row_format, and the table will be inaccessible when an overly long index is the primary key.

I retrieved the commit information and I saw that MySQL had fixed this problem(c84a1b9a7f31f8b0f9c8c226f748d23f6bd8c462), but apparently, the problem was not completely solved.

How to repeat:
Run MTR with followed case:

--echo #
--echo # 1) Prepare
--echo #
SET GLOBAL innodb_default_row_format=COMPACT;
CREATE TABLE t_pk_compact (a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)) ENGINE=INNODB;
ALTER TABLE t_pk_compact MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;

CREATE TABLE t_k_compact (a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ENGINE=INNODB;
ALTER TABLE t_k_compact MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;

SET GLOBAL innodb_default_row_format=REDUNDANT;
CREATE TABLE t_pk_redundant (a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)) ENGINE=INNODB;
ALTER TABLE t_pk_redundant MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;

CREATE TABLE t_k_redundant (a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ENGINE=INNODB;
ALTER TABLE t_k_redundant MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;

--echo # The data and matadata of t_compact and t_redundant can be accessed, because the table definitions are cached.
SHOW CREATE TABLE t_pk_compact;
SHOW CREATE TABLE t_k_compact;
SHOW CREATE TABLE t_pk_redundant;
SHOW CREATE TABLE t_k_redundant;

SELECT COUNT(*) FROM t_pk_compact;
SELECT COUNT(*) FROM t_k_compact;
SELECT COUNT(*) FROM t_pk_redundant;
SELECT COUNT(*) FROM t_k_redundant;

--echo #
--echo # 2) Restart mysql-server
--echo #
--let $restart_parameters = restart:
--source include/restart_mysqld.inc

--echo # The matadata of t_compact and t_redundant can be accessed, but the data of t_compact and t_redundant can not be accessed, and we can not drop the corrupted clustered index.
SHOW CREATE TABLE t_pk_compact;
SHOW CREATE TABLE t_k_compact;
SHOW CREATE TABLE t_pk_redundant;
SHOW CREATE TABLE t_k_redundant;

--error ER_INDEX_CORRUPT
SELECT COUNT(*) FROM t_pk_compact;
--error ER_INDEX_CORRUPT
SELECT COUNT(*) FROM t_k_compact;
--error ER_INDEX_CORRUPT
SELECT COUNT(*) FROM t_pk_redundant;
--error ER_INDEX_CORRUPT
SELECT COUNT(*) FROM t_k_redundant;

--echo # No-clustered index can be dropped for commit#c84a1b(Bug#35869747), this is as expected.
ALTER TABLE t_k_compact DROP INDEX a;
ALTER TABLE t_k_redundant DROP INDEX a;

--echo # Clustered index can not be dropped, this is an issue that needs to be fixed
--error ER_TABLE_CORRUPT
ALTER TABLE t_pk_compact DROP PRIMARY KEY;
--error ER_TABLE_CORRUPT
ALTER TABLE t_pk_redundant DROP PRIMARY KEY;

--echo #
--echo # 3) Cleanup
--echo #
DROP TABLE t_pk_compact;
DROP TABLE t_k_compact;
DROP TABLE t_pk_redundant;
DROP TABLE t_k_redundant;

Suggested fix:
1. Considering compatibility, the clustered index problem should be corrected.
2. More importantly, this behavior of creating large indexes should be avoided.

Hello Huaxiong,

Thank you for the report and feedback.

regards,
Umesh

-- 
./mtr --nocheck-testcases bug116353
Logging: ./mtr  --nocheck-testcases bug116353
MySQL Version 8.0.39
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/mysql-8.0.39/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
#
# 1) Prepare
#
SET GLOBAL innodb_default_row_format=COMPACT;
CREATE TABLE t_pk_compact (a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)) ENGINE=INNODB;
ALTER TABLE t_pk_compact MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
CREATE TABLE t_k_compact (a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ENGINE=INNODB;
ALTER TABLE t_k_compact MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
SET GLOBAL innodb_default_row_format=REDUNDANT;
CREATE TABLE t_pk_redundant (a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, PRIMARY KEY (a)) ENGINE=INNODB;
ALTER TABLE t_pk_redundant MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
CREATE TABLE t_k_redundant (a VARCHAR(100) CHARACTER SET utf8mb4 NOT NULL, KEY (a)) ENGINE=INNODB;
ALTER TABLE t_k_redundant MODIFY a VARCHAR(500) CHARACTER SET utf8mb4 NOT NULL;
# The data and matadata of t_compact and t_redundant can be accessed, because the table definitions are cached.
SHOW CREATE TABLE t_pk_compact;
Table	Create Table
t_pk_compact	CREATE TABLE `t_pk_compact` (
  `a` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t_k_compact;
Table	Create Table
t_k_compact	CREATE TABLE `t_k_compact` (
  `a` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t_pk_redundant;
Table	Create Table
t_pk_redundant	CREATE TABLE `t_pk_redundant` (
  `a` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t_k_redundant;
Table	Create Table
t_k_redundant	CREATE TABLE `t_k_redundant` (
  `a` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT COUNT(*) FROM t_pk_compact;
COUNT(*)
0
SELECT COUNT(*) FROM t_k_compact;
COUNT(*)
0
SELECT COUNT(*) FROM t_pk_redundant;
COUNT(*)
0
SELECT COUNT(*) FROM t_k_redundant;
COUNT(*)
0
#
# 2) Restart mysql-server
#
# restart:
# The matadata of t_compact and t_redundant can be accessed, but the data of t_compact and t_redundant can not be accessed, and we can not drop the corrupted clustered index.
SHOW CREATE TABLE t_pk_compact;
Table	Create Table
t_pk_compact	CREATE TABLE `t_pk_compact` (
  `a` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t_k_compact;
Table	Create Table
t_k_compact	CREATE TABLE `t_k_compact` (
  `a` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t_pk_redundant;
Table	Create Table
t_pk_redundant	CREATE TABLE `t_pk_redundant` (
  `a` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SHOW CREATE TABLE t_k_redundant;
Table	Create Table
t_k_redundant	CREATE TABLE `t_k_redundant` (
  `a` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
SELECT COUNT(*) FROM t_pk_compact;
ERROR HY000: Index t_pk_compact is corrupted
SELECT COUNT(*) FROM t_k_compact;
ERROR HY000: Index a is corrupted
SELECT COUNT(*) FROM t_pk_redundant;
ERROR HY000: Index t_pk_redundant is corrupted
SELECT COUNT(*) FROM t_k_redundant;
ERROR HY000: Index a is corrupted
# No-clustered index can be dropped for commit#c84a1b(Bug#35869747), this is as expected.
ALTER TABLE t_k_compact DROP INDEX a;
ALTER TABLE t_k_redundant DROP INDEX a;
# Clustered index can not be dropped, this is an issue that needs to be fixed
ALTER TABLE t_pk_compact DROP PRIMARY KEY;
ERROR HY000: Operation cannot be performed. The table 'test.t_pk_compact' is missing, corrupt or contains bad data.
ALTER TABLE t_pk_redundant DROP PRIMARY KEY;
ERROR HY000: Operation cannot be performed. The table 'test.t_pk_redundant' is missing, corrupt or contains bad data.
#
# 3) Cleanup
#
DROP TABLE t_pk_compact;
DROP TABLE t_k_compact;
DROP TABLE t_pk_redundant;
DROP TABLE t_k_redundant;
[ 50%] main.bug116353                            [ fail ]  Found warnings/errors in error log file!
        Test ended at 2024-10-14 12:54:29
include/load_error_log.inc
line
2024-10-14T10:54:28.889536Z 8 [ERROR] [MY-014073] [InnoDB] Index PRIMARY of test.t_pk_compact exceeds limit of 767 bytes per column.
2024-10-14T10:54:28.892744Z 8 [ERROR] [MY-014073] [InnoDB] Index a of test.t_k_compact exceeds limit of 767 bytes per column.
2024-10-14T10:54:28.894666Z 8 [ERROR] [MY-014073] [InnoDB] Index PRIMARY of test.t_pk_redundant exceeds limit of 767 bytes per column.
2024-10-14T10:54:28.896647Z 8 [ERROR] [MY-014073] [InnoDB] Index a of test.t_k_redundant exceeds limit of 767 bytes per column.
^ Found warnings in /export/home/tmp/ushastry/mysql-8.0.39/mysql-test/var/log/mysqld.1.err

Posted by developer:
 
Added the following note to the MySQL Server 8.0.42, 8.4.5, and 9.3.0 release notes:
	
Under certain circumstances, using ALTER TABLE with INPLACE to modify the size of a column 
could result in an index which exceeds the valid size limit (767 bytes). 
This occurred for tables with a row format of Redundant or Compact and the row format was not 
explicitly defined in the table creation.

As of this release, a validation is performed and an error returned by any ALTER TABLE, INPLACE 
operation which will result in an invalid index size.