MySQL错误日志解读及行大小超限等问题咨询

我在MySQL错误日志里反复碰到这个警告：

2018-05-16T05:00:09.031837Z 624962 [Warning] InnoDB: Cannot add field `jobcontrac14__REPLACEMENT_PERIOD_319` in table `tmp`.`#sql_4c85_0` because after adding it, the row size is 8134 which is greater than maximum allowed size (8126) for a record on index leaf page.

对应的JOB_CONTRACTS表建表语句和REPLACEMENT_PERIOD字段长度统计如下：

CREATE TABLE `JOB_CONTRACTS` ( 
  `ID` int(11) NOT NULL AUTO_INCREMENT, 
  `REPLACEMENT_PERIOD` varchar(25) DEFAULT NULL, 
  `FEE_STRUCTURE` varchar(20) DEFAULT NULL, 
  `POTENTIAL_BILLING` varchar(20) DEFAULT NULL, 
  `INVOICE_AFTER_DAYS` varchar(10) DEFAULT NULL, 
  `OFFERED_COMPENSATION` double DEFAULT NULL, 
  `FLAT_FEE` varchar(10) DEFAULT NULL, 
  `JOB_ID` int(11) DEFAULT NULL, 
  PRIMARY KEY (`ID`), 
  KEY `fk_JOB_ID_CONTRACTS_indx` (`JOB_ID`), 
  CONSTRAINT `fk_JOB_ID_CONTRACT_1` FOREIGN KEY (`JOB_ID`) REFERENCES `JOBS` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION 
) ENGINE=InnoDB AUTO_INCREMENT=4871 DEFAULT CHARSET=utf8;

SELECT COUNT(*), REPLACEMENT_PERIOD, LENGTH(REPLACEMENT_PERIOD) FROM JOB_CONTRACTS GROUP BY REPLACEMENT_PERIOD; 
+----------+--------------------+----------------------------+ 
| COUNT(*) | REPLACEMENT_PERIOD | LENGTH(REPLACEMENT_PERIOD) | 
+----------+--------------------+----------------------------+ 
| 2 | 0 | 1 | 
| 2 | 1 year | 6 | 
| 249 | 120 | 3 | 
| 5 | 120 days | 8 | 
| 13 | 15 | 2 | 
| 87 | 180 | 3 | 
| 28 | 180 days | 8 | 
| 127 | 30 | 2 | 
| 24 | 30 days | 7 | 
| 155 | 45 | 2 | 
| 815 | 60 | 2 | 
| 33 | 60 days | 7 | 
| 5 | 75 | 2 | 
| 2483 | 90 | 2 | 
| 770 | 90 days | 7 | 
| 2 | None | 4 | 
| 3 | Other | 5 | 
+----------+--------------------+----------------------------+

这个字段实际最大数据长度只有8，下面来逐个解答你的疑问：

1. 为何仍会出现行大小超过允许最大值的错误？ #

这里的关键是InnoDB计算行大小的逻辑和你想的不一样：

• 虽然你的REPLACEMENT_PERIOD实际存的数据最长是8，但表定义里它是varchar(25)。InnoDB在创建临时表（比如#sql_4c85_0这种）的时候，会按照字段的定义长度来估算行大小，而不是实际数据长度——因为临时表创建时还不知道后续要存的具体数据。
• 你的表用的是utf8字符集，每个字符最多占3字节，所以varchar(25)会被估算为25*3=75字节的开销。
• 另外，这个临时表大概率是执行ALTER TABLE或者复杂查询（比如带排序、分组、多表JOIN的查询）时自动生成的中间表。这类临时表可能会把所有字段都放在索引叶子页里，这时候所有字段的定义长度加起来，再加上InnoDB本身的行头、NULL标记位、指针等额外开销，就很容易超过8126字节的限制（这是InnoDB默认16KB页大小下，索引叶子页单条记录的最大允许长度，大概是页大小的一半）。

2. 能否通过jobcontrac14__REPLACEMENT_PERIOD_319定位对应数据行？ #

抱歉，这个字段名是MySQL内部生成的临时标识，没办法直接定位到原表的具体数据行：

• jobcontrac14应该是原表JOB_CONTRACTS的缩写，_319可能是内部的字段ID或者哈希值，没有直接对应原表数据的意义。
• 而且临时表tmp.#sql_4c85_0在对应的操作完成后会被MySQL自动删除，你也没办法直接查询这个临时表的内容。

3. 如何定位触发该错误的查询语句？ #

可以试试这几个方法，大概率能找到问题：

• 开启通用查询日志：在MySQL配置文件里设置general_log = 1，指定general_log_file的路径，重启MySQL后，所有执行的查询都会被记录下来。你可以根据错误日志里的时间戳（2018-05-16T05:00:09左右）去匹配对应的查询。注意这个日志增长很快，找到问题后记得关闭。
• 用慢查询日志兜底：如果触发错误的查询耗时较长，或者你不想开通用日志，可以设置slow_query_log = 1，把long_query_time设为0（这样会记录所有查询），同样根据时间戳去查找。
• 实时查看进程列表：如果这个警告是周期性出现的（比如每天凌晨5点），可以在接近这个时间的时候执行SHOW FULL PROCESSLIST;，看看当时正在运行的查询，尤其是涉及JOB_CONTRACTS表的ALTER TABLE或者复杂SELECT语句。
• 检查服务器定时任务：很多凌晨的数据库操作都是通过定时任务（比如Linux的crontab）执行的，你可以查看服务器上的定时任务，看看有没有对JOB_CONTRACTS表做修改或者统计的脚本。

4. MySQL错误日志解读的官方文档 #

你可以参考MySQL官方文档中关于错误日志的章节，里面详细说明了各种错误和警告的含义，包括InnoDB行大小限制这类问题的排查思路。官方文档还涵盖了日志格式、常见警告的处理方法等内容。

内容的提问来源于stack exchange，提问作者Sonal