Thursday, June 19, 2014

MySql db restore from dump failing

Solution : increase max_allowed_packet

Details :
Recently, I had a strange problem. While doing source dbdump.sql (dbdump.sql was generated by mysqldump) - initial data was loaded fine but after a while data was not being restored properly due to foreign key checks failing.

The dump file was disabling foreign key checks at the top and enabling again at the bottom  - so it was highly surprising.

To debug this I wrote a php script which executed queries line by line from the dump. I observed that it failed at a query where the data being inserted was huge which made database connection go away. And when it came back the foreign key check was enabled again - which in turn made a lot of things fail.

So I increased max_allowed_packet to 64M in my.ini and restarted mysql server. Solved.

No comments:

Blog Archive