记录一次Mysql:Row size too large (＞ 8126)错误解决

由于正式数据库迁移，运维将数据版本由5.6升级到5.7，引入了不少问题，某些索引失效导致慢查询，当这些问题解决完后以为事情都结束了，其实不然，当我们用户进行发佣操作时数据库报错了： nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

【导致问题的原因】

总结了下原因是因为mysql-innodb是按照page存储数据的，每个page max size是16k，然后每个page两行数据，所以每行最大8k数据。如果你的字段是blob之类的话，会存储在page之外的溢出区里。

但是innodb默认的approach（羚羊）存储格式会把每个blob字段的前864个字节存储在page里，所以你的blob超过一定数量的话，单行大小就会超过8k，所以就报错了

【解决思路】

解决方式是使用innodb的Barracuda（梭鱼） 存储格式

这种格式对blob字段的处理方式是在page里头只存储一个20byte大小的指针，其它全存在溢出区，所以你轻易超不了8k

 

解决步骤：

1. 查询mysql每张表都是独立存储空间的开关是否打开

SHOW variables LIKE '%per_table%';

1.1 如果未打开则设置打开
SET GLOBAL innodb_file_per_table = ON;

 

2. 将数据存储格式设置成Barracuda（梭鱼） 存储格式

SHOW GLOBAL VARIABLES LIKE '%file_format%';

SET GLOBAL innodb_file_format = 'Barracuda';

 

3. 设置对应表的属性：ROW_FORMAT=COMPRESSED

3.1  通过SQL查询出数据库row_format的格式

SELECT table_schema, table_name, row_format FROM information_schema.TABLES WHERE table_schema IN ( '数据库名称')

3.2 使用CONCAT函数进行SQL拼接 ，通过SQL生成更改语句

SELECT CONCAT( "ALTER TABLE `", table_schema, "`.`", table_name, "` ROW_FORMAT =COMPRESSED ;" ) FROM information_schema.TABLES WHERE table_schema IN ( '数据库名称' );

 

Compressed与Dynamic行记录格式

InnoDB Plugin引入了新的文件格式（file format，可以理解为新的页格式），对于以前支持的Compact和Redundant格式将其称为Antelope文件格式，新的文件格式称为Barracuda。Barracuda文件格式下拥有两种新的行记录格式Compressed和Dynamic两种。新的两种格式对于存放BLOB的数据采用了完全的行溢出的方式，在数据页中只存放20个字节的指针，实际的数据都存放在BLOB Page中，而之前的Compact和Redundant两种格式会存放768个前缀字节。

下图是Barracuda文件格式的溢出行：

Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能进行非常有效的存储。