Description:
For InnoDB tables using redundant row format, index size greater than 767 bytes is not allowed.
"The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character. "
https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html
In MySQL 5.7 (tested in 5.7.31 and 5.7.33) the error (Specified key was too long; max key length is 767 bytes) comes only when the table created with redundant format and global value of innodb_default_row_format is also set to redundant. Based on my understanding , the check performed to push the error depends on the value of innodb_default_row_format variable. Here, its checking the global value of the innodb_default_row_format and if its dynamic, then its allowing the creation of index with larger size for tables created with redundant row format.
Example:
mysql> show global variables like 'innodb_default_row_format';
+---------------------------+-----------+
| Variable_name | Value |
+---------------------------+-----------+
| innodb_default_row_format | redundant |
+---------------------------+-----------+
1 row in set (0.00 sec)
mysql> create index idx123 on test123 (`comment`);
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql>
mysql> set global innodb_default_row_format='dynamic';
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+
1 row in set (0.01 sec)
mysql> create index idx123 on test123 (`comment`);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
Upgrading to any 8.0.x version will make this table in accessible:
Example:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.22 |
+-----------+
1 row in set (0.00 sec)
mysql> ####
+--------------+------------+-------------+--------------+------------+-----------+
| table_schema | table_name | column_name | COLUMN_TYPE | index_name | INDEX_LEN |
+--------------+------------+-------------+--------------+------------+-----------+
| test | test123 | comment | varchar(255) | idx123 | 1020 |
+--------------+------------+-------------+--------------+------------+-----------+
1 row in set (0.01 sec)
mysql> select count(1) from test.test123;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.
mysql>
Its similar to another bug reported earlier by my colleague in [https://bugs.mysql.com/bug.php?id=99791]. However, this is related to creation of index in 8.0. Here, the index creation with higher length allowed in 5.7 version only (tested in 5.7.33 version)
How to repeat:
create database test;
use test;
set global innodb_default_row_format='redundant';
CREATE TABLE `test123` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test123(comment) values('kncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncvlkasnfcklanskldnlaksndklasndlkanslkdnaslkndlkasndklanslkdnkncfosdncfvlknsadkvnalksdnvlkansdlkvcnalkdsncq');
create index idx123 on test123 (`comment`); ## <-- This will give error
set global innodb_default_row_format='dynamic';
create index idx123 on test123 (`comment`); ## <-- This will complete as expected without any warnings
> upgrade to any 8.0.x version (tested on 8.0.22) and the table become inaccessible.
Suggested fix:
Do not allow creation of index larger than 767 bytes for InnoDB tables with redundant row format in MySQL 5.7 versions.
