Why MySQL 8.0.21 Upgrade Triggers ERROR 1709

Background

A colleague reported that a table in the test environment became inaccessible, with all SELECT, DML, and DDL operations returning the error:ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

The number "767" immediately suggested an issue related to MySQL's compact or redundant row formats. Subsequent investigation confirmed this suspicion.

Root Cause Analysis

The following steps reproduce the issue in a test environment:

1. Upgrade MySQL 5.6.21 to 5.7.20‍

Adjust the database configuration file and run:

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 ... &  

‍

2. Upgrade MySQL 5.7.20 to 8.0.21‍

Adjust the configuration file and run:

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

‍

3. Add a Column and Index

mysql> alter table sky.test add column test_col varchar(500);  
mysql> alter table sky.test add index idx_test_col(test_col);  

‍

Normally, creating this index should fail with:ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes.

4. Database Restart

systemctl stop mysqld_3306  
systemctl start mysqld_3306  

‍

5. Post-Restart Behavior

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

‍

Key Observations

• The table sky.test uses an implicit compact row format (created in MySQL 5.6).
• MySQL 8.0 defaults to dynamic row format, which avoids the 767-byte limit.
• Tables explicitly created with row_format=compact do not trigger this issue.

Bug Reference

This issue aligns with MySQL Bug #99791, resolved in MySQL 8.0.22. The fix prevents implicit compact tables from allowing index columns exceeding 767 bytes.

Solutions

1. Avoid In-Place Upgrades‍

Migrate data to a new MySQL 8.0.21 environment using logical imports and replication. Ensure innodb_default_row_format=dynamic is set.

2. Upgrade to MySQL 8.0.22 or Later:

This version includes the bug fix, preventing the issue.

3. Identify Problem Tables‍

Run this SQL to detect tables with potential index size issues:

SELECT s.table_schema, s.table_name, s.index_name, s.column_name  
FROM information_schema.statistics s,  
     information_schema.columns c,  
     information_schema.tables i  
WHERE s.table_name = c.table_name  
  AND s.table_schema = c.table_schema  
  AND c.column_name = s.column_name  
  AND s.table_name = i.table_name  
  AND s.table_schema = i.table_schema  
  AND i.row_format IN ('Redundant', 'Compact')  
  AND (s.sub_part IS NULL OR s.sub_part > 255)  
  AND c.character_octet_length > 767;  

‍

4. Explicitly Define Row Format‍

For tables with implicit compact or redundant row formats, explicitly set the row format:

ALTER TABLE table_name ROW_FORMAT=DYNAMIC;  

‍

Conclusion

Understanding MySQL's row format behavior during upgrades is critical to avoiding issues like ERROR 1709. Always validate configurations and test upgrades in non-production environments.

Note: The content above is a translation and adaptation of the original article, tailored for IT professionals familiar with MySQL. Code formatting and technical accuracy have been preserved.