MySQL给表增加字段的时候出现Table is full的报错处理

MySQL给表增加字段的时候出现Table is full的报错处理

一、问题描述

今日在发布服务时，在服务Init阶段出现的报错，错误信息如下：

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67) ~[hibernate-core-5.2.17.Final.jar!/:5.2.17.Final]
	.....
	Caused by: java.sql.SQLException: The table 'xxx' is full
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.47.jar!/:5.1.47]

服务架构：springboot+data jpa
给一张数据表增加了两个字段，该报错是在执行DDL语句时出错的

二、错误原因溯源

复现错误

在mysql-shell中执行该DDL语句
提示报错 [err] 1114 - the table ‘billboard’ is full
官方对该报错提示如下：

Error number:  `1114` ; Symbol: [`ER_RECORD_FILE_FULL`]SQLSTATE:  `HY000` 
Message: The table '%s' is full
 `InnoDB`  reports this error when the system tablespace runs out of free space. Reconfigure the system tablespace to add a new data file.

错误说明是表空间不足，会导致这个错误。

分析

通过执行命令查看下这张表的信息:

show table status like "xxx";

结果如下：
这是一张百万行的数据表，Data_lengh约是258MB

数据库版本

show table status like "xxx";

结果：

在 MySQL5.7版本中 InnoDB是如何执行一个DDL语句，相信流程可见下图：

总结一下，大致的过程是，

1.锁住表结构文件（frm文件），解析DDL语义，创建新的表结构文件，新的表文件（ibd文件）和 相关的日志文件。
2.读取源文件的数据，重建数据和对应索引到新的表文件中，再把增量的新数据写入到新的表文件里。
3.重建完成后再移除源文件，把新的表结构文件，新的表文件重命名为源文件。

至此，得到一个朴素的推断，由于执行DDL语句，InnoDB会生成新的文件来重建数据库文件，那么造成临时表空间不足的原因可能会有：
1.MySQL的配置对表空间的大小做了限制，不允许数据库文件超过某个值 。
2.MySQL的数据文件所在磁盘空间不足。

对于猜想1：
可以看到该配置被设置为自动增加。

show VARIABLES like "INNODB_data_%";

对于猜想2
进入部署MySQL数据文件所在磁盘上，执行查询

可以看到当前磁盘剩余空间为 168MB 小于 258MB

错误修复

先进行硬盘扩容
MySQL扩容完成后，再次执行DDL语句，报错依旧产生。
此时已经满足了重建表空间的磁盘空间要求，但还是产生错误。
依靠猜想，读写操作一定磁盘上的数据读取到内存中进行转换操作，然后进行批量写磁盘（如果是读一条写一次磁盘会造成IO资源性能的低下），进行批操作的前提是有足够的内存空间。

show GLOBAL VARIABLES like "%table_size%";

默认的情况下 tmp_table_size 和 max_heap_table_size 大小为16MB
查看当前的主机，内存还有4.2G的空闲，我们将该值设置为 256M后，重启MySQL服务
并再次执行DDL语句，经过约10S左右，该语句执行成功。
执行成功后，再将相关参数改回。

总结

1 回过头再来考虑一个问题，如果我们要执行DDL的数据表是线上运行的一个千万级的大表，那么我们应该怎么处理？
应该避免在业务高峰期执行DDL的操作
如果需要热执行DDL，可以考虑配置相关的降级策略，减少对该表的事务操作，避免在重建后，还要处理增量数据。
2 MySQL 5.6版本之前是不支持在线修改DDL，即在完成DDL之前，会锁表，在以后的版本才支持在线DDL
3 执行了DDL语句之后，InnoDB重建数据表文件过程中，整理了空间碎片，所以重建后的idb的体积可能比重建前小