mysql [Err] 1118 - Row size too large (＞ 8126).

秒速五厘米 2022-09-09 10:56 81阅读 0赞

错误代码： 1118   
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.

## 问题 ##

mysql5.7 在执行创建表或者增加字段时，发现row size长度过长，导致出现以下错误。

[Err] 1118 - 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.

## 解决方案 ##

row size 其实就是所有字段的长度的总和。**在不进行拆表的前提下解决（我们不讨论是否设计的合理性）**： 知识贴： [https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html][https_dev.mysql.com_doc_refman_8.0_en_column-count-limit.html]

You may want to take a look at this article which explains a lot about MySQL row sizes. It's important to note that even if you use TEXT or BLOB fields, your row size could still be over 8K (limit for InnoDB) because it stores the first 768 bytes for each field inline in the page.
    
    The simplest way to fix this is to use the Barracuda file format with InnoDB. This basically gets rid of the problem altogether by only storing the 20 byte pointer to the text data instead of storing the first 768 bytes.

方法一：改变一些字段varchar为TEXT or BLOB。 无效，不能解决问题。

**最终解决方案**

[https://stackoverflow.com/questions/22637733/mysql-error-code-1118-row-size-too-large-8126-changing-some-columns-to-te][https_stackoverflow.com_questions_22637733_mysql-error-code-1118-row-size-too-large-8126-changing-some-columns-to-te]

查询系统参数：

show variables like '%innodb_strict_mode%';  
    
    show variables like '%innodb_log_file_size%';

修改前：

innodb_strict_mode	ON
    innodb_log_file_size	536870912

修改mysql的配置文件， vi /etc/my.cnf

innodb_log_file_size=1024M
    innodb_strict_mode=0

innodb\_strict\_mode=0 这个一定不能漏，否则不能生效。 重启后：

innodb_strict_mode	OFF
    innodb_log_file_size	1073741824

it work 。 卡了很久，最终靠这个方案解决了。

[https_dev.mysql.com_doc_refman_8.0_en_column-count-limit.html]: https://dev.mysql.com/doc/refman/8.0/en/column-count-limit.html
[https_stackoverflow.com_questions_22637733_mysql-error-code-1118-row-size-too-large-8126-changing-some-columns-to-te]: https://stackoverflow.com/questions/22637733/mysql-error-code-1118-row-size-too-large-8126-changing-some-columns-to-te