Mysql Error 1153: Got a Packet Bigger than 'max_allowed_packet' bytes

Asked

Viewed 5,408 times

5

When trying to import a database from a dump with 1.2GB, the following error occurred:

ERROR 1153 (08S01) at line 727: Got a Packet Bigger than 'max_allowed_packet' bytes

The command I am using for import is:

mysql -u usuario -p banco < dump.sql

How to solve this?

mysql

  • Your dump.sql had the consultations of INSERT with X limit rows ? Or a INSERT dealt with all the ROWS ?

  • I have a INSERT with several VALUES per table (this was generated by mysqldump). As I see if there is a limit of X Rows?

  • When they are very large tables, the INSERT are repeated every 50 rows for example. Otherwise a INSERT with several VALUES exceeds package limits. Several INSERT gives smaller packages and becomes more viable.

  • My file is hard to consult because it is so large, but I have now discovered that there are indeed several INSERTs per table. I can’t tell how many lines per INSERT. Still it was giving the error until I increased the max_allowed_packet.

  • I make use of the gvim to query text files larger than 1GB. So far manage to open an 8GB without problems on Linux :)

  • I’m using the vim too, on Mac, but when rolling sometimes it skips a "screen" whole (I think it’s because the line is too big).

  • These mega lines are probably the reason why the package limit is exceeded!

  • I also think, but the solution I posted below solved (which makes sense). If you know of a solution at the other end, that forces the mysqldump to generate smaller packages, it would be very useful!

Show 3 more comments

1 answer

7


I found the answer in Michael Pryor’s answer on Stack Overflow.

According to him, it is necessary to change a Mysql configuration, and pass an extra parameter to the client on the command line, setting a high heat to max_allowed_packet (he uses 100M).

Change in the my.cnf (or my.ini on Windows) 

max_allowed_packet=100M

Alternatively, you can run the following commands on the server:

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

Change in client call

mysql --max_allowed_packet=100M -u usuario -p banco < dump.sql

(I restarted the server before calling the client.)

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.