MYSQL - Got a packet bigger than 'max_allowed_packet' bytes

By jdln on 20 Dec 2010 at 18:10 UTC

I was about to update to the new drupal core so I backed up my site's database before starting to turn off contributed modules. Id turned off over half and after a save I suddenly started getting a white screen.

I went to my phpMyAdmin (MAMP) and emptied the database so I could load in my backup. However im getting this error:

Error
SQL query:

INSERT INTO `cache_menu` VALUES('links:admin_menu:tree-data:cec4b1e806ff573441fab0ae40803c91', 0x613a323a7b733a343a2274726565223b613a31313a7b693a3332353b613a323a7b733a343a226c696e6b223b613a33373a7b733a31343a226c6f61645f66756e6374696f6e73223b4e3b733a31363a22746f5f6172675f66756e6374696f6e73223b4e3b733a31353a226163636573735f63616c6c6261636b223b4e3b733a31363a226163636573735f617267756d656e7473223b4e3b733a31333a22706167655f63616c6c6261636b223b4e3b733a31343a22706167655f617267756d656e7473223b4e3b733a353a227469746c65223b4e3b733a31343a227469746c655f63616c6c6261636b223b4e3b733a31353a227469746c655f617267756d656e7473223b4e3b733a343a2274797065223b4e3b733a31313a226465736372697074696f6e223b4e3b733a393a226d656e755f6e616d65223b733a31303a2261646d696e5f6d656e75223b733a343a226d6c6964223b733a333a22333235223b733a343a22706c6964223b733a313a2230223b733a393a226c696e6b5f70617468223b733a373a223c66726f6e743e223b733a31313a22726f757465725f70617468223b733a303a22223b733a31303a226c696e6b5f7469746c65223b733a3132383a223c[...]

MySQL said:
#1153 - Got a packet bigger than 'max_allowed_packet' bytes

Ive never had an error like this before. Im my local environment I have php.ini with 'memory_limit = 128M', and ive tried up to 1024B. The database is about 15MB but looking at previous backups others are about the same size.

Is this definably a lack of memory error? How can I check if my php.ini is working?
Thanks

Comments

Export SQL with extended inserts off

Anonymous (not verified) commented 20 December 2010 at 20:29

Hi,

Did you export your SQL with extended inserts? When you export using phpMyAdmin, try unchecking the "Extended inserts" box in the Data panel on the Export page. This will create individual INSERT statements for each record in the tables, rather than one big INSERT for each table. Import that and see if it still chokes.

Good luck,
Ed

Ah, I dont have the original

jdln commented 20 December 2010 at 20:40

Ah, I dont have the original so I cant re-export the database. Thanks anyway though.

Found some instructions

Anonymous (not verified) commented 20 December 2010 at 22:24

Hi,

I found this link that may help -- make sure to read the whole thread.

http://stackoverflow.com/questions/680571/mysql-import-database-error-be...

Regards,
Ed

Reply to comment Export SQL with extended inserts off

tverrbjelke commented 6 August 2012 at 11:01

thx, you helped me withthat!

I solved this error today.

Ramesh_Thakor commented 14 December 2012 at 10:43

Use a backup_migrate module and backup by this module.. after you will easily database backup found and after also import your zip file in phpmyadmin.
module link backup_migrate

i was use this module and after easily backup my drupal site.

viva

WillHall commented 20 December 2010 at 22:29

viva bigdump...
http://www.ozerov.de/bigdump.php

Ive tried big dump and I

jdln commented 21 December 2010 at 11:40

Ive tried big dump and I get:

Error
0x613a323a7b733a343a2274726565223b613a.......
Query: INSERT INTO `cache_menu` VALUES('links:admin_menu:tree-data:cec4b1e806ff573441fab0ae40803c91', 0x613a323a7b733a343............
MySQL: Got a packet bigger than 'max_allowed_packet' bytes

Note, ..... means Ive abbreviated a really long string of characters. So I seem to be getting the same problem.

Thanks for your help but any other ideas?

yeah - delete those entries

WillHall commented 21 December 2010 at 15:53

You must have some big menus - you can try deleting those entries from the sql file.

If its just cache who cares.

Sounds promising. How can I

jdln commented 21 December 2010 at 18:12

Sounds promising. How can I work out everything that's cache and delete it?

"INSERT INTO `cache_menu`"

WillHall commented 4 January 2011 at 20:18

"INSERT INTO `cache_menu`"

Is that a command for a php

jdln commented 4 January 2011 at 21:30

Is that a command for a php my admin interface or similar? As my database is not currently loaded, its just in a sql file, how can I run a command that like?

The site does have quite a lot of hierarchical menus so this does sound promising.

Thanks

Why don't you edit the sql

jpcwebb commented 7 January 2011 at 15:05

Why don't you edit the sql file and delete all the cache table entries?

How do I know which are the

jdln commented 7 January 2011 at 15:15

How do I know which are the cache tables?
Thanks

they are generally labelled

jpcwebb commented 7 January 2011 at 15:43

they are generally labelled as such, e.g. drupal_cache, drupal_cache_menu, drupal_cache_content etc
The only caveat is dependent on how you've emptied your database - did you drop the tables or just clear their contents? If the former, you'll need to leave the code in the sql file that recreates the structure, but just remove the data part, which is a bit more of a pain.

I tried importing it to a

jdln commented 8 January 2011 at 12:32

I tried importing it to a database on my hosting and it worked fine. As it was just my local host (MAMP) which wouldnt work I fixed it with a my.cnf file:
http://forum.mamp.info/viewtopic.php?f=2&t=6306
Thanks