MySql 5.6 Packet for query is too large

2017-01-09 14:09:04来源:oschina作者:寻知者人点击


Packet for query is too large

数据库:mysql5.6


framework: play framework 1.2.4

近日处理批量数据的insert,update,涉及的保存更新sql大概有18w。我的操作如下:


1)每次取1000条数据进行相关逻辑判断,以及需要的保存操作;


2)每100条sql做一次transaction.commit。


今天在执行过程中,保存含有些Blob字段的数据表时,报错如下:


Packetforquery is too large (1663020>1048576). You can change this value on the server by setting the max_allowed_packet'variable.


关于max_allowed_packet,官方解释如下:


The maximum size of one packet or any generated/intermediate string, or any parameter sent by themysql_stmt_send_long_data()C API function. The default is 4MB as of MySQL 5.6.6, 1MB before that.


The packet message buffer is initialized tonet_buffer_lengthbytes, but can grow up tomax_allowed_packetbytes when needed. This value by default is small, to catch large (possibly incorrect) packets.


You must increase this value if you are using largeBLOBcolumns or long strings. It should be as big as the largestBLOByou want to use. The protocol limit formax_allowed_packetis 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.


大致上看,max_allowed_packet是字符串缓冲区的最大长度。MySQL5.6.6默认值是4MB,之前版本默认值是1MB。如果我们使用blob类型或者其它常字符串类型字段,那么必须将max_allowed_packet值设置成我们需要的最大长度。


设置方式官方,具体如下:


1)查看自己的max_allowed_packet,进入mysql console:mysql>show VARIABLES like '%max_allowed_packet%';


2.1)修改配置文件方式:unix,mac,linux的配置文件my.cnf,windows的配置文件是my.cnf。配置中添加"max_allowed_packet=xxx",xxx为需要设置的长度,单位默认是比特。比如:max_allowed_packet=1024*1024,即max_allowed_packer设置成1MB。


2.2)命令行配置:set global max_allowed_packet = 1024*1024;


3)最后都需要重启mysql服务才能生效。

以上是我查到了关于mysql设置选项max_allowed_packet的相关,我本地试过用2.2方式修改,成功了。


但是后来我发现我真正的问题是另一个字段设计上的遗漏,就是我给一张表加了2个blob的字段,对应到数据库中是longtext类型。这两个字段的值相同,然后没条数据在保存的时候,都会向mysql字符缓冲区中放入这两个blob,这才导致了数据packet过大,超过buffer限制。后来我删了2个字段中的那个测试字段,job妥妥的执行下去了,也没有卡住,或者越跑越慢的现象。


总之,又学到了一点。如果有什么理解错的,务必请帮忙纠正,谢谢。

最后,贴一张上面的报错截图:



Mysql 5.6 centOS /etc/my.conf


# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html


[mysqld] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock port=3306 character-set-server=utf8mb4 default-time-zone = '+8:00' max_allowed_packet = 20M


# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0


# Recommended in standard MySQL setup sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES


[mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid


[client] port=3306 socket=/var/lib/mysql/mysql.sock default-character-set=utf8mb4


[mysql] no-auto-rehash default-character-set=utf8mb4

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台