MySQL插入单行数据较大时报Row size too large错误

朴灿烈づ我的快乐病毒、 2022-05-28 06:46 600阅读 0赞

向目的MySQL写入数据时，单行数据较大，遇到插入失败，报错信息如下：

Error '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.' on query. ......

原因：

MySQL根据配置文件会限制Server端接受的数据包大小。当插入和更新的单行数据较大时，可能会受制于配置文件的参数限制max\_allowed\_packet，导致写入或者更新失败。  
配置文件/etc/my.cnf及时没有配置对应的参数，系统也会有默认限制。  
  
相关配置参数：  
max\_allowed\_packet: 服务器发送和接受的最大包长度，当单行数据较大时，需要调整该参数。  
innodb\_log\_file\_size: 该参数确定数据日志文件的大小，更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间。  
innodb\_log\_buffer\_size: 该参数确定日志文件所用的内存大小，设置时用M单位进行设置。缓冲区更大能提高性能，但意外的故障将会丢失数据。MySQL开发人员建议设置为1~8M之间的值。  
  
MariaDB 5.5.41 & MySQL 5.7.21在配置文件没有配置参数的情况下，系统的默认值为：

<table> 
 <tbody> 
  <tr> 
   <td>MariaDB5.5.41</td> 
   <td>MySQL5.7.21</td> 
  </tr> 
  <tr> 
   <td>max_allowed_packet = 1048576&nbsp; &nbsp; &nbsp; &nbsp;#1M</td> 
   <td>max_allowed_packet = 4194304&nbsp; &nbsp; &nbsp; &nbsp;#4M</td> 
  </tr> 
  <tr> 
   <td>innodb_log_file_size = 5242880&nbsp; &nbsp; &nbsp; &nbsp; #5M</td> 
   <td>innodb_log_file_size = 50331648&nbsp; &nbsp; &nbsp; #48M</td> 
  </tr> 
  <tr> 
   <td>innodb_log_buffer_size = 8388608&nbsp; &nbsp;#8M</td> 
   <td>innodb_log_buffer_size = 16777216&nbsp; #16M</td> 
  </tr> 
 </tbody> 
</table>

MariaDB \[(none)\]> select version();

\+--------------------+  
| version()          |  
\+--------------------+  
| 5.5.41-MariaDB-log |  
\+--------------------+  
1 row in set (0.00 sec)  
MariaDB \[(none)\]> show variables like '%max\_allowed\_packet%';  
\+--------------------------+------------+  
| Variable\_name            | Value      |  
\+--------------------------+------------+  
| max\_allowed\_packet       | 1048576    |  
| slave\_max\_allowed\_packet | 1073741824 |  
\+--------------------------+------------+  
2 rows in set (0.00 sec)  
  
MariaDB \[(none)\]> show variables like '%innodb\_log\_file\_size%';  
\+----------------------+---------+  
| Variable\_name        | Value   |  
\+----------------------+---------+  
| innodb\_log\_file\_size | 5242880 |  
\+----------------------+---------+  
1 row in set (0.00 sec)  
  
MariaDB \[(none)\]> show variables like '%innodb\_log\_buffer\_size%';  
\+------------------------+---------+  
| Variable\_name          | Value   |  
\+------------------------+---------+  
| innodb\_log\_buffer\_size | 8388608 |  
\+------------------------+---------+  
1 row in set (0.00 sec)  
  
MariaDB \[(none)\]>   
  
MySQL 5.7.21的默认值：  
mysql> select version();  
\+-----------+  
| version() |  
\+-----------+  
| 5.7.21    |  
\+-----------+  
1 row in set (0.00 sec)  
mysql> show variables like '%max\_allowed\_packet%';  
\+--------------------------+------------+  
| Variable\_name            | Value      |  
\+--------------------------+------------+  
| max\_allowed\_packet       | 4194304    |  
| slave\_max\_allowed\_packet | 1073741824 |  
\+--------------------------+------------+  
2 rows in set (0.01 sec)  
  
mysql> show variables like '%innodb\_log\_file\_size%';  
\+----------------------+----------+  
| Variable\_name        | Value    |  
\+----------------------+----------+  
| innodb\_log\_file\_size | 50331648 |  
\+----------------------+----------+  
1 row in set (0.00 sec)  
  
mysql> show variables like '%innodb\_log\_buffer\_size%';  
\+------------------------+----------+  
| Variable\_name          | Value    |  
\+------------------------+----------+  
| innodb\_log\_buffer\_size | 16777216 |  
\+------------------------+----------+  
1 row in set (0.01 sec)  
  
解决办法：  
vim /etc/my.cnf，根据实际情况进行参数调整：  
\[mysqld\]  
max\_allowed\_packet = 1G  
innodb\_log\_file\_size = 30M  
innodb\_log\_buffer\_size = 512M  
修改之后，重启mysql服务。