MySQL 报错 ERROR 1709: Index column size too large 引发的思考

MySQL 5.6 升级遇到 1709 报错该怎么办？

作者：王田田，DBA，擅长发呆，偶尔热爱分享。

爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。

本文约 1500 字，预计阅读需要 5 分钟。

背景

某日同事突然找到我说测试环境中有张表无法访问，SELECT、DML 和 DDL 执行均报错 ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.。

其实看到 767 这个数字，大家可能会猜想这估计和 compact/redundant 行格式有关系，后续也确实证实了和这个有点关系。

问题发生了就要想办法处理，当时第一反应是能不能有些“特殊操作”调整一下元数据，但能力有限无法实现。由于是测试环境，数据没那么重要，而且还是单节点，后续处理无非是利用备份重做这套库；若不想重做，而且该表不重要，也可以直接废弃该表，但是 xtrabackup 备份可能会报错。

既然问题一旦发生，只能通过备份恢复来解决，那么我们应该探究一下如何提前避免该问题。

原因探究

以下为测试环境复现过程：

MySQL 5.6.21 原地升级至5 .7.20

先调整数据库配置文件，以下为简要升级步骤：

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

先调整数据库配置文件，以下为简要升级步骤：

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

8.0.21 数据库添加字段并添加索引

表默认字符集为 utf8

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

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

正常情况下，这个索引理应无法创建成功，会立即抛出错误 ERROR 1071 (42000):Specified key was too long; max key length is 767 bytes。当然一方面原因是 MySQL 5.7 及 8.0 默认行格式为 dynamic，另一方面即使显式指定 row_format=compact，也会立即抛出错误。示例如下：

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)
Records: 0  Duplicates: 0  Warnings: 0

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

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)
Records: 0  Duplicates: 0  Warnings: 0

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

数据库重启前，该表可正常访问。

重启数据库

systemctl stop mysqld_3306

systemctl start mysqld_3306 

查看表情况

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                                                            |
+------------------+--------+----------+--------------------------------------------------------------------+
3 rows in set (0.01 sec)

查看相关信息

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 |
+--------------+------------+------------+--------------------+

找不同的粗略猜想

sky2 表比 test 多了一个 create_options 选项，所以不会触发 bug。而且 create_options 是建表时显式指定的行格式 compact，而 test 表是在 5.6 版本隐式创建的行格式 compact；8.0默认创建表的行格式为 Dynamic（由 innodb_default_row_format 参数控制），Dynamic 行格式不会存在 767bytes 的限制。

碰到这样奇奇怪怪的问题，第一反应就是不走运碰到了 bug，因此先去 bug 库中搜索一番，果不其然搜到了Bug #99791，与我们测试环境的情况极为类似。

Bug #99791 中表明官方在 MySQL 8.0.22 版本修复了 非显式定义的 redundant 行格式表允许创建的索引列大小超 767 bytes 的 bug。实际上笔者在测试环境验证了一下 MySQL 8.0.22 确实已解决该问题，即隐式创建的 compact 行格式表在待创建的索引列超 767bytes 时直接返回错误 ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes。因此猜想虽然该 bug 行格式与笔者本次环境对不上，但应该解决的是同一个问题，都是为了解决因隐式定义compact/redundant行格式而导致的问题。

解决方案

综上所述，我们可以得出以下解决方案：

1. MySQL 5.6 升级至 MySQL 8.0.21 时，避免使用原地升级的方案，可新建一个 MySQL 8.0.21 的环境，将数据逻辑导入并搭建复制关系；若 8.0.21 环境设置 innodb_default_row_format=Dynamic 参数，在逻辑导入/复制时新环境会自动将行格式转为 Dynamic。

2. 升级时选择高于 MySQL 8.0.21 版本的数据库，避免触发该 bug。

3. 若当前已经存在 MySQL 5.6 原地升级至 MySQL 8.0.21 的环境。

• 可通过以下 SQL 语句排查是否存在超过 767bytes 的问题表；若存在，可以趁现在数据库未重启，改造涉及的索引。

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;

• 筛选出隐式创建行格式为 compact/redundant 的表，并显式指定，如 alter table xx row_format=dynamic/compact 。相关 SQL 如下：

select TABLE_SCHEMA,TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS from information_schema.tables where ROW_FORMAT in ('Compact','Redundant') and CREATE_OPTIONS='';

由于笔者能力实在有限，如有错误还望大家能够批评指正。

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

✨ Github：https://github.com/actiontech/sqle

📚 文档：https://actiontech.github.io/sqle-docs/

💻 官网：https://opensource.actionsky.com/sqle/

👥 微信群：请添加小助手加入 ActionOpenSource

🔗 商业支持：https://www.actionsky.com/sqle