本文介绍了当遇到MySQL中Packet Too Large错误时的解决方法，包括调整客户端和服务器的max_allowed_packet参数设置。
mysql 插入文档内容过大
Packet for query is too large (5058927 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.
The largest possible packet that can be transmitted to or from a MySQL 5.7 server or client is 1GB.
When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.
Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.
If you are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:
shell> mysql --max_allowed_packet=32M
That sets the packet size to 32MB.
The server's default max_allowed_packet value is 4MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOBcolumns). For example, to set the variable to 16MB, start the server like this:
shell> mysqld --max_allowed_packet=16M
You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:
[mysqld]
max_allowed_packet=16M
It is safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you do not run out of memory by using large packets accidentally.
You can also get strange problems with large packets if you are using large BLOB values but have not given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restarting mysqld.
PDF (A4) - 37.5Mb
PDF (RPM) - 37.0Mb
EPUB - 10.5Mb
HTML Download (TGZ) - 10.3Mb
HTML Download (Zip) - 10.3Mb
HTML Download (RPM) - 9.0Mb
Eclipse Doc Plugin (TGZ) - 11.2Mb
Eclipse Doc Plugin (Zip) - 13.4Mb
Man Pages (TGZ) - 203.9Kb
Man Pages (Zip) - 309.1Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
B.5.2.10 Packet Too Large
A communication packet is a single SQL statement sent to the MySQL server, a single row that is sent to the client, or a binary log event sent from a master replication server to a slave.
The largest possible packet that can be transmitted to or from a MySQL 5.7 server or client is 1GB.
When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection. With some clients, you may also get a Lost connection to MySQL server during query error if the communication packet is too large.
Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server.
If you are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:
shell>mysql --max_allowed_packet=32Mmysql --max_allowed_packet=32M
That sets the packet size to 32MB.
The server's default max_allowed_packet value is 4MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOBcolumns). For example, to set the variable to 16MB, start the server like this:
shell>mysqld --max_allowed_packet=16Mmysqld --max_allowed_packet=16M
You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:
[mysqld]
max_allowed_packet=16M
It is safe to increase the value of this variable because the extra memory is allocated only when needed. For example, mysqld allocates more memory only when you issue a long query or when mysqld must return a large result row. The small default value of the variable is a precaution to catch incorrect packets between the client and server and also to ensure that you do not run out of memory by using large packets accidentally.
You can also get strange problems with large packets if you are using large BLOB values but have not given mysqld access to enough memory to handle the query. If you suspect this is the case, try adding ulimit -d 256000 to the beginning of the mysqld_safe script and restarting mysqld.
------------------------------------------------------------
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
------------------------------------------------------------
change "max_allowed_packet = 1M" to "max_allowed_packet = 32M"
I've found it helps to NOT use extended insert statements.
The server would go away for no reason. Switching the exporter to use regular inserts solved this problem.
ie. unchecking "Create bulk INSERT statement(s)" in SQLyog or by checking No EXTENDED INSERTS in MySQL Administrator
RE: Packet-too-large
RE: Windows MySQL Administrator
RE: my.ini
RE: max_allowed_packet
I had tried without success at trying to update max_allowed_packet by editing the my.ini file in Windows Vista. Seemed like nothing would work, there was no effect.
At the MySQL> command prompt, you can type:
show variables like 'max_allowed_packet'
to see if the changes you made are working.
Then I found that the values of my.ini could be changed using the MySQL Administrator. This didn't work either. It could not stop the MySQL server and every time I would click apply changes, it would say it could not apply the changes, Error 1.
Finally, I found that since I'm using Vista (bad words should be placed here), I had to run MySQL Administrator as a Windows Administrator (right click, choose Run as Administrator). Then suddenly I had the power to stop the server, make the changes I wanted and sure enough, it worked.
To change max_allowed_packet, under Startup Variables, go to the networking tab. (Took me forever to find it.)
I navigated in Windows Explorer to C:\Program Files (x86)\MySQL\MySQL Tools for 5.0\, right clicked, MySQLAdministrator.exe, choose properties and set it so it would always run as an Administrator. Now I don't have to remember.
It should be noted that MySQL install puts files in both C:\Program Files (x86)\MySQL and in C:\Program Files\MySQL
using Vista 64-bit, MySQL Administrator 1.2.12, MySQL 5.0.67
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
It helps me very much.
But I want add my esperience here.
In some servers mysql socket is in different location.
socket = /tmp/mysql.sock (if this location is different database error will come up)make sure you write your location correct.
wait_timeout=28800
interactive_timeout = 28800
max_allowed_packet=64M
socket = /tmp/mysql.sock
skip-locking
Then I just restarted the MySQL service from Administrative Tools & everything worked fine(I was able to insert huge data).
Make sure you check if the parameter values are changed by typing in the command (ex.)show parameters like 'max_allowed_packet' from MySQL client(default).
Thanks
Ayaz
show variables like 'max_allowed_packet';
...worked for me.
扫一扫
1678
到【灌水乐园】发言
------------------------------------------------------------
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
------------------------------------------------------------
change "max_allowed_packet = 1M" to "max_allowed_packet = 32M"
I've found it helps to NOT use extended insert statements.
The server would go away for no reason. Switching the exporter to use regular inserts solved this problem.
ie. unchecking "Create bulk INSERT statement(s)" in SQLyog or by checking No EXTENDED INSERTS in MySQL Administrator
RE: Packet-too-large
RE: Windows MySQL Administrator
RE: my.ini
RE: max_allowed_packet
I had tried without success at trying to update max_allowed_packet by editing the my.ini file in Windows Vista. Seemed like nothing would work, there was no effect.
At the MySQL> command prompt, you can type:
show variables like 'max_allowed_packet'
to see if the changes you made are working.
Then I found that the values of my.ini could be changed using the MySQL Administrator. This didn't work either. It could not stop the MySQL server and every time I would click apply changes, it would say it could not apply the changes, Error 1.
Finally, I found that since I'm using Vista (bad words should be placed here), I had to run MySQL Administrator as a Windows Administrator (right click, choose Run as Administrator). Then suddenly I had the power to stop the server, make the changes I wanted and sure enough, it worked.
To change max_allowed_packet, under Startup Variables, go to the networking tab. (Took me forever to find it.)
I navigated in Windows Explorer to C:\Program Files (x86)\MySQL\MySQL Tools for 5.0\, right clicked, MySQLAdministrator.exe, choose properties and set it so it would always run as an Administrator. Now I don't have to remember.
It should be noted that MySQL install puts files in both C:\Program Files (x86)\MySQL and in C:\Program Files\MySQL
using Vista 64-bit, MySQL Administrator 1.2.12, MySQL 5.0.67
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
It helps me very much.
But I want add my esperience here.
In some servers mysql socket is in different location.
socket = /tmp/mysql.sock (if this location is different database error will come up)make sure you write your location correct.
wait_timeout=28800
interactive_timeout = 28800
max_allowed_packet=64M
socket = /tmp/mysql.sock
skip-locking
Then I just restarted the MySQL service from Administrative Tools & everything worked fine(I was able to insert huge data).
Make sure you check if the parameter values are changed by typing in the command (ex.)show parameters like 'max_allowed_packet' from MySQL client(default).
Thanks
Ayaz
show variables like 'max_allowed_packet';
...worked for me.