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.
