首页 » MySQL/TiDB/GoldenDB » Troubleshooting MySQL ERROR 1709 (HY000): Index column size too large.

Troubleshooting MySQL ERROR 1709 (HY000): Index column size too large.

  • 2024/07/18
  • MySQL/TiDB/GoldenDB
  • 52 views
  • Troubleshooting MySQL ERROR 1709 (HY000): Index column size too large.已关闭评论

在MySQL 5.6或从5.6 升级到8.0后有可能会遇到ERROR 1071 (42000):Specified key was too long; max key length is 767 bytes 报错，提示是遇到了索引KEY的最大上限767 字节。在本文中，我将解释此错误发生的原因以及如何解决它。

错误原因

在旧版 MySQL 表格式中，VARCHAR 或 blob 列上的索引的最大大小为 767 个字节, 当您使用 utf8 编码创建 ROW_FORMAT=COMPACT 表时，这会导致表行过大发生此错误,当字符集为 utf8mb4 时，每个字符最多可以占用这 767 个字节中的 4 个，因此您可以使用前缀长度 floor(767/4) 或 191 来定义索引。发生这种情况的原因是，在 MySQL 服务器中，InnoDB表的默认行格式为“ COMPACT ”，而在 MySQL 5.7 及 8.0 , 和MariaDB 版本 10.2.2（或更高版本）中，默认行格式为“DYNAMIC”, 可以发现参数innodb-default-row-format=compact改为innodb-default-row-format=dynamic。

Bug #99791 MySQL 8 orphaned table due to unchecked non-existent row format check.

Tables created in versions < MySQL 8 which use row_format COMPRESSED or REDUNDANT, where row_format is not set explicitly in the Table DDL allow users create un-prefixed indexes on fields which exceed the maximum column size of 767 bytes.

Notes:
– Tables created with default innodb_default_row_format=redundant on MySQL 8 do not show this behavior. They are handled correctly.
– This only seems to happen for tables created on prior versions. i.e. shows up if index is added after upgrade.
– The default row_format in 5.7+ is dynamic but prior to that it was COMPACT so this is more likely to effect tables which were originally created on v < 5.7 OR 57 DBs where innodb_default_row_format value has been changed to COMPACT or REDUNDANT. Its a ‘silent killer’ so tables may be affected by this without the DBA knowing. Upon reboot innodb will not be able to open the table.
– The only way to recover from this seems to be a backup restore. Table is undroppable in recovery mode too.

https://bugs.mysql.com/bug.php?id=99791 记录了完成的复现过程,不再复述。当从 row_format COMPRESSED or REDUNDANT创建的表,原地升级到5.7或8.0后，重启后可能导致表无法查询。

在oracle中索引最大长度

在oracle中可以参考 ORA-01450。总索引长度计算为所有索引列的宽度加上索引列的数量之和。最大长度依赖于block size Oracle 将索引键限制为 DB 块大小的80% 左右。因此，如果 db_block_size 为 8192（顺便说一下，这是默认值），则最大键长度为 6398。如果必须使用超过该限制的键长度，则使用更大的 db_block_size 以适应索引可以解决该错误。我在2021 APACOUC分享的online index create中也有分享在rebuild online 使用的IOT时，index key 最大 ORA-01450实际又是40%. 另外也受nls_length_semantics的byte 还是char，当使用多字节字符集(NLS_CHARACTERSET)时可能更容易发生, 尤其是AL32UTF8。

MySQL row_format函数

在 MySQL 中，ROW_FORMAT 选项允许您指定表行的物理存储格式。这样可以影响数据库的性能和存储效率。以下是 MySQL 中不同的 ROW_FORMAT 选项：

  1. DEFAULT（默认）：使用存储引擎定义的默认行格式。对于 InnoDB，5.6默认行格式是 COMPACT，5.7以后默认是 DYNAMIC ，具体取决于 MySQL 版本和配置。
  2. DYNAMIC（动态）：该格式旨在更高效地处理可变长度列。长的可变长度列值（如 BLOB 或 TEXT）会与主记录分开存储。
  3. COMPACT（紧凑）：这是一种更紧凑的行格式，可以在较小的空间内存储数据。它与 DYNAMIC 类似，但处理长的可变长度列的方式不同，通常在较小数据集上具有更好的存储效率。
  4. REDUNDANT（冗余）：这是一种较旧的行格式，主要用于向后兼容。它比 COMPACT 和 DYNAMIC 效率低。
  5. COMPRESSED（压缩）：这种格式通过压缩表的数据来节省空间。它可以提高存储效率并减少 I/O 操作，但可能需要更多的 CPU 资源来压缩和解压缩数据。
  6. ROW_FORMAT=COMPRESSED, KEY_BLOCK_SIZE=N：该选项允许您为压缩表指定块大小。N 的值可以是 1、2、4、8 或 16，代表块大小（以 KB 为单位）。
  7. PAGED：这是由一些存储引擎（如 ARCHIVE）支持的另一种存储格式。

您可以在创建或修改表时使用 ROW_FORMAT 选项来指定行格式。例如：

CREATE TABLE my_table (
id INT PRIMARY KEY,
data TEXT
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;ALTER TABLE my_table ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

选择合适的行格式取决于您的具体使用场景，包括您存储的数据类型、数据大小以及性能需求。

解决方法

如果已经遇到了上面的bug，DDL操作都是失败的，似乎只能备份恢复。或升级到 MySQL 8.0.22 更高的版本.

从5.6升级时，为了摆脱的前缀索引长度限制，表需要是 InnoDB 并使用 DYNAMIC（或 COMPRESSED）ROW_FORMAT，将数据逻辑导入5.7或8.0.

如果当前还未重启，无需从头开始重建旧表，您可以通过以下方式进行转换。

ALTER TABLE whatever ENGINE=InnoDB, ROW_FORMAT=DYNAMIC;

监控预防

要修复上述错误，您需要将 InnoDB 表的默认行格式设置为“DYNAMIC”。这可以通过多种方式完成。

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 的表

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

修改

ALTER TABLE tablename ROW_FORMAT=DYNAMIC
打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：