代码改变世界

欺骗性的警告信息：InnoDB 行大小限制的奇特案例

2024-09-01 17:01  abce  阅读(103)  评论(0)  编辑  收藏  举报

记得之前在测试环境遇到过类似的问题，让开发缩短了列的长度，但是没有进一步深究，今天看到这个文章，又做了一下模拟，发觉问题还存在，需要继续关注。以下是模拟学习的过程。原文地址：https://www.percona.com/blog/when-warnings-deceive-the-curious-case-of-innodbs-row-size-limitation/

 

神奇的警告

最近，我参与了一项问题分析，目的是找出出现如下警告信息的原因：

[Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `db1`.`test` because after adding it, the row size is 8484 which is greater than maximum allowed size (8126) for a record on index leaf page.

错误信息看起来很清楚，不是吗？问题在于，这个特殊的表已经好几年没有改动过了，因此没有涉及 DDL（ALTER）查询。此外，最近甚至都没有新的数据写入这个表，但这个警告却偶尔出现。

 

重现尝试

在尝试重现该问题时，首先遇到的障碍是甚至无法创建上述表，因为它违反了最大行大小的规定。下面我使用一个简单的测试用例表定义来说明实际问题：

>create table abce (
    -> c1 varchar(255) not null,
    -> c2 varchar(255) default null,
    -> c3 varchar(255) default null,
    -> c4 varchar(255) default null,
    -> c5 varchar(255) default null,
    -> c6 varchar(255) default null,
    -> c7 varchar(255) default null,
    -> c8 varchar(255) default null,
    -> c9 varchar(255) default null,
    -> c10 varchar(255) default null,
    -> c11 varchar(255) default null,
    -> primary key (c1(40))
    -> )engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci row_format=compact;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.

由于表使用的是传统的 compact 行格式，对最大行大小的限制低于现代 mysql 版本中默认使用的 dynamic 格式，因此更改行格式后 create 成功。

不过，客户表的行格式仍然是 compact。此外，innodb_strict_mode 已开启，这就阻止了此类表的创建。

值得注意的是，严格模式是 mysql 5.7 才默认启用的，所以这个表可能是过去在 mysql 5.6 下创建的。

因此，当我用违反行格式限制的定义创建表格时，我们会收到预期的警告：

>set session innodb_strict_mode=0;
Query OK, 0 rows affected (0.01 sec)

>CREATE TABLE `test` (
    -> `c1` varchar(255) NOT NULL,
    -> `c2` varchar(255) DEFAULT NULL,
    -> `c3` varchar(255) DEFAULT NULL,
    -> `c4` varchar(255) DEFAULT NULL,
    -> `c5` varchar(255) DEFAULT NULL,
    -> `c6` varchar(255) DEFAULT NULL,
    -> `c7` varchar(255) DEFAULT NULL,
    -> `c8` varchar(255) DEFAULT NULL,
    -> `c9` varchar(255) DEFAULT NULL,
    -> `c10` varchar(255) DEFAULT NULL,
    -> `c11` varchar(255) DEFAULT NULL,
    -> PRIMARY KEY (`c1`(40))
    -> ) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci row_format=compact;
Query OK, 0 rows affected, 1 warning (0.04 sec)

>show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 139
Message: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
1 row in set (0.01 sec)

在错误日志中打印了一个警告，这已经有点令人困惑了，因为我实际上不是在添加字段，而是在创建表：

2024-09-01T15:23:59.140137+08:00 29 [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `myabc`.`test` because after adding it, the row size is 8872 which is greater than maximum allowed size (8126) for a record on index leaf page.

 

表定义缓存

无论 InnoDB 采用何种严格模式，之后向表中写入内容都不会触发任何警告或错误。那么，为什么错误日志中偶尔会出现警告呢？我们发现，只要表对象被载入表定义缓存，就会出现警告！因此，随着时间的推移，只要缓存变得太小，无法容纳所有用户表，就会出现这种情况。缓存会在重启时被清除，所以当我重启 MySQL 实例，然后尝试访问同一个表时，就会出现这种情况：

>select c1 from myabc.test limit 1;
Empty set, 1 warning (0.03 sec)

>show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 139
Message: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline.
1 row in set (0.01 sec)

现在，在简单查询，错误日志中也会出现与 CREATE 时完全相同的警告：

2024-09-01T15:32:15.199937+08:00 8 [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `myabc`.`test` because after adding it, the row size is 8872 which is greater than maximum allowed size (8126) for a record on index leaf page.

这是一条令人困惑的信息，绝对不是 SELECT 语句所预期的。

一旦表加载到定义缓存中，下面的查询就不会再引起警告！比如，我再次执行查询语句，就不会遇到该警告：

>select c1 from myabc.test limit 1;
Empty set (0.14 sec)

因此，只要表定义不在缓存中，任何访问它的尝试，甚至运行 SHOW CREATE TABLE 都会触发同样的提示信息，提示我们正在添加一个字段...

 

有趣的是，当我删除该列使表符合限制条件时，尽管 MySQL 执行命令时没有任何警告，但还是会再次出现令人困惑的错误日志：(作者遇到了，但是我的环境没有复现出来，error日志中没有发现警告信息)

>alter table test drop column c11;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

>show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `c1` varchar(255) NOT NULL,
  `c2` varchar(255) DEFAULT NULL,
  `c3` varchar(255) DEFAULT NULL,
  `c4` varchar(255) DEFAULT NULL,
  `c5` varchar(255) DEFAULT NULL,
  `c6` varchar(255) DEFAULT NULL,
  `c7` varchar(255) DEFAULT NULL,
  `c8` varchar(255) DEFAULT NULL,
  `c9` varchar(255) DEFAULT NULL,
  `c10` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`c1`(40))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=COMPACT
1 row in set (0.06 sec)

 

还有一个奇怪的现象：尝试重新添加问题列时，会触发两个而不是一个警告，其中一个警告指向 ALTER 时创建的临时表：

>set session innodb_strict_mode=0;
Query OK, 0 rows affected (0.08 sec)

>alter table test add column `c11` varchar(100);
Query OK, 0 rows affected, 2 warnings (0.73 sec)
Records: 0  Duplicates: 0  Warnings: 2

root@localhost myabc>show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                             |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning |  139 | Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. |
| Warning |  139 | Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, BLOB prefix of 768 bytes is stored inline. |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.20 sec)

以下是error日志中的警告信息：

2024-09-01T15:56:37.791052+08:00 8 [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `myabc`.`#sql-ib1099-1584751861` because after adding it, the row size is 8484 which is greater than maximum allowed size (8126) for a record on index leaf page.
2024-09-01T15:56:37.822849+08:00 8 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=172, thread_id=8, space_id=38, index_id=202, page_no=4]
2024-09-01T15:56:37.822989+08:00 8 [Note] [MY-012478] [InnoDB] DDL log delete : 172
2024-09-01T15:56:37.824482+08:00 8 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=173, thread_id=8, table_id=1099]
2024-09-01T15:56:37.861259+08:00 8 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=174, thread_id=8, space_id=37, old_file_path=./myabc/#sql-ib1100-1584751862.ibd, new_file_path=./myabc/test.ibd]
2024-09-01T15:56:37.861378+08:00 8 [Note] [MY-012478] [InnoDB] DDL log delete : 174
2024-09-01T15:56:37.885568+08:00 8 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=175, thread_id=8, table_id=1099, old_file_path=myabc/#sql-ib1100-1584751862, new_file_path=myabc/test]
2024-09-01T15:56:37.885873+08:00 8 [Note] [MY-012478] [InnoDB] DDL log delete : 175
2024-09-01T15:56:37.930876+08:00 8 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=176, thread_id=8, space_id=38, old_file_path=./myabc/test.ibd, new_file_path=./myabc/#sql-ib1099-1584751861.ibd]
2024-09-01T15:56:37.931176+08:00 8 [Note] [MY-012478] [InnoDB] DDL log delete : 176
2024-09-01T15:56:37.963155+08:00 8 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=177, thread_id=8, table_id=1100, old_file_path=myabc/test, new_file_path=myabc/#sql-ib1099-1584751861]
2024-09-01T15:56:37.963472+08:00 8 [Note] [MY-012478] [InnoDB] DDL log delete : 177
2024-09-01T15:56:38.022020+08:00 8 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=178, thread_id=8, table_id=1099]
2024-09-01T15:56:38.022304+08:00 8 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=179, thread_id=8, space_id=37, old_file_path=./myabc/#sql-ib1100-1584751862.ibd]
2024-09-01T15:56:38.196831+08:00 8 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 8
2024-09-01T15:56:38.197136+08:00 8 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=179, thread_id=8, space_id=37, old_file_path=./myabc/#sql-ib1100-1584751862.ibd]
2024-09-01T15:56:38.251965+08:00 8 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=178, thread_id=8, table_id=1099]
2024-09-01T15:56:38.252225+08:00 8 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=173, thread_id=8, table_id=1099]
2024-09-01T15:56:38.305030+08:00 8 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 8
2024-09-01T15:56:38.305630+08:00 8 [Warning] [MY-011825] [InnoDB] Cannot add field `c11` in table `myabc`.`test` because after adding it, the row size is 8484 which is greater than maximum allowed size (8126) for a record on index leaf page.

部分原因是，之前有人报告过上述信息的一些怪异之处： https://bugs.mysql.com/bug.php?id=113695

作者又提交一份补充材料： https://bugs.mysql.com/bug.php?id=115954

 

最后

尽管错误信息可能会暗示什么，但有时原因可能很出人意料。千万不要盲目相信它，而是要设法找到问题的核心。