mysql Packet for query is too large (1808523 > 1048576)
程序员文章站
2024-03-12 17:08:20
...
1、问题描述
在跑spark任务,统计结果插入mysql数据库时出现一下异常
Packet for query is too large (13008675 > 4194304). You can change this value on the server by settin
2、问题原因
查询数据库返回的数据包太大,超过了默认值。
登录mysql,在控制台输入一下命令查询默认的max_allowed_packet值,发现只有4194304(4M)
原因:MySql插入数据的最大允许默认值4M,而返回的数据包大小为12.4M,大于默认值所有出现异常喽!
查看max_allowed_packet命令
show variables like 'max_allowed_packet'
3、解决方法
3.1方式一:命令方式
set max_allowed_packet = 100*1024*1024; #设置为100M
#运行提示一下错误:
ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value
错误信息提示read-only只读模式,建议设置GLOBAL的值,咱们只好根据它的提示来操作喽~
SET GLOBAL max_allowed_packet=100*1024*1024;
Query OK, 0 rows affected (0.00 sec)
嘿别说,提示操作成功! 再次查看修改后的值吧~
show variables like 'max_allowed_packet'
什么鬼!!你发现max_allowed_packet值还是和原来一样的
主要原因是,咱们修改全局变量的值,不会影响到已存在的数据库参数的,只会对修改后再创建的库有影响的。还是乖乖寻找其他方案吧~~~~不急,方案二中有些哦!
3.2方式二:修改配置文件my.cnf方式
控制台下输入以下命令,编辑my.cnf
sudo vim /etc/mysql/my.cnf
在[mysqId]下面添加
max_allowed_packet = 100M
退出编辑模式,重启mysql
[aaa@qq.com ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
重启成功后再次查看max_allowed_packet值,这次肯定是你期望的值了!
参考链接:https://forums.mysql.com/read.php?10,288159,288159#msg-288159
推荐阅读
-
mysql Packet for query is too large (1808523 > 1048576)
-
MySQL Packet for query is too large解决方法
-
com.mysql.jdbc.PacketTooBigException: Packet for query is too large (1086 > 1024).
-
Packet for query is too large (1986748 > 1048576). Yo_MySQL
-
mysql- Packet for query is too large (60 > -1).
-
com.mysql.jdbc.PacketTooBigException: Packet for query is too large 异常解决办法
-
使用mybatis generator 出现Packet for query is too large (5,150 > 1,024)错误[ubuntu系统]
-
MySQL Packet for query is too large 问题及解决方法
-
mysql: Packet for query is too large 和Data too long for column
-
mysql: Packet for query is too large 和Data too long for column