How to Resolve MySQL "Index column size too large" (Error 1709) After Upgrading to 8.0.21

Background

A colleague reported that a table in the test environment could not be accessed; any SELECT, DML, or DDL returned ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes. The error was suspected to be related to the compact/redundant row format.

Because the environment is a single‑node test instance and the data is not critical, the initial options were to rebuild the database from backup or discard the table, but the backup tool xtrabackup might also fail.

The goal is to understand how to avoid the problem proactively.

Cause Investigation

The issue was reproduced by upgrading MySQL:

MySQL 5.6.21 → 5.7.20 (in‑place upgrade)

shell>/mysql/mysql-5.7.20/bin/mysqld_safe ... &
shell>/mysql/mysql-5.7.20/bin/mysql_upgrade ...
mysql>shutdown;
shell>/mysql/mysql-5.7.20/bin/mysqld_safe ... &

MySQL 5.7.20 → 8.0.21 (in‑place upgrade)

mysql>/mysql/mysql-8.0.21/bin/mysqld_safe ... &
mysql>shutdown;
shell>/mysql/mysql-8.0.21/bin/mysqld_safe ... &

Creating a table and adding an index in 8.0.21

The table uses the default utf8 charset.

mysql> alter table sky.test add column test_col varchar(500);
Query OK, 0 rows affected (10.09 sec)

mysql> alter table sky.test add index idx_test_col(test_col);
Query OK, 0 rows affected (0.02 sec)

Normally this index should fail with ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes because MySQL 5.7/8.0 default to dynamic row format, and even explicitly setting row_format=compact still triggers the error.

mysql> create table sky1 (id int);
Query OK, 0 rows affected (0.05 sec)

mysql> alter table sky1 add column test_col varchar(500);
Query OK, 0 rows affected (0.03 sec)

mysql> alter table sky1 add index idx_test_col(test_col);
Query OK, 0 rows affected (0.03 sec)

mysql> create table sky2(id int) row_format=compact;
Query OK, 0 rows affected (0.06 sec)

mysql> alter table sky2 add column test_col varchar(500);
Query OK, 0 rows affected (0.04 sec)

mysql> alter table sky2 add index idx_test_col(test_col);
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Before restarting the server the table could still be accessed.

Restarting the database

systemctl stop mysqld_3306

systemctl start mysqld_3306

Checking the table after restart

mysql> select * from sky.test limit 1;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

mysql> alter table sky.test row_format=dynamic;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

mysql> alter table sky.test engine=innodb;
ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

mysql> check table sky.test;
+------------------+--------+----------+--------------------------------------------------------------+
| Table            | Op     | Msg_type | Msg_text                                                     |
+------------------+--------+----------+--------------------------------------------------------------+
| sky.test         | check  | Error    | Index column size too large. The maximum column size is 767 bytes. |
| sky.test         | check  | Error    | Table 'sky.test' doesn't exist                               |
| sky.test         | check  | error    | Corrupt                                                      |
+------------------+--------+----------+--------------------------------------------------------------+

Inspecting row format information

mysql> select TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS from information_schema.tables where table_schema='sky';
+--------------+------------+------------+--------------------+
| TABLE_SCHEMA | TABLE_NAME | ROW_FORMAT | CREATE_OPTIONS     |
+--------------+------------+------------+--------------------+
| sky          | test       | Compact    |                    |
| sky          | sky1       | Dynamic    |                    |
| sky          | sky2       | Compact    | row_format=COMPACT |
+--------------+------------+------------+--------------------+

The difference is that sky2 was created with an explicit create_options of compact , while test inherited the compact format implicitly from MySQL 5.6. In MySQL 8.0 the default row format is Dynamic , which does not have the 767‑byte limit.

A search in the MySQL bug database revealed Bug #99791, which describes the same situation. The bug was fixed in MySQL 8.0.22: implicit compact/redundant tables now reject oversized index columns with ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes .

Solution

Based on the analysis, the following mitigation steps are recommended:

When upgrading from MySQL 5.6 to 8.0.21, avoid in‑place upgrades; instead provision a fresh MySQL 8.0.21 instance, import data logically, and set innodb_default_row_format=Dynamic so tables are created with the Dynamic format automatically.

Upgrade to a version newer than 8.0.21 (e.g., 8.0.22 or later) where the bug is resolved.

If you already performed an in‑place upgrade to 8.0.21, run the following SQL to locate tables whose index columns exceed 767 bytes and whose row format is Compact/Redundant: select s.table_schema,s.table_name,s.index_name,s.column_name from information_schema.statistics s join information_schema.columns c on s.table_name=c.table_name and s.table_schema=c.table_schema and s.column_name=c.column_name join information_schema.tables i on s.table_name=i.table_name and s.table_schema=i.table_schema where i.row_format in ('Redundant','Compact') and (s.sub_part is null or s.sub_part>255) and c.character_octet_length > 767;

For tables identified as using Compact/Redundant formats, explicitly change the row format, e.g., alter table xx row_format=dynamic or alter table xx row_format=compact as appropriate. select TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS from information_schema.tables where ROW_FORMAT in ('Compact','Redundant') and CREATE_OPTIONS='';

These steps help avoid the 767‑byte limitation and prevent the backup tool from failing.

References

Bug #99791: https://bugs.mysql.com/bug.php?id=99791

MySQL 8.0.22 Release Notes: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html