Honestly it baffles me that with a completely default installation of mysql if I run mysqldump with default parameters it generates a SQL file that can't be imported into another completely default installation of mysql. From what I can gather it's got something to do with the max_allowed_packet setting and/or the net_buffer_length setting. I've read a bunch about this, and tried tweaking it a bunch of ways on both the export and import sides, but it still doesn't work. I keep getting the packet too big error on import. From everything I've read, here's my best guess:

mysqldump --net_buffer_length=50000 myschema > giant_file.sql

Because I read here that mysqldump refers to max_allowed_packet as net_buffer_length because ... uhh ... anyway. Then to import

mysql --max_allowed_packet=999999 myschema < giant_file.sql

But this still doesn't work. How do I export / import the database???

  • 999999 ~ 1M? How did you tweak max_allowed_packet, in /etc/my.cnf, under [mysqldump] section? Are you importing on the same host? What exactly error did you get? – quanta Nov 12 '11 at 2:34

You may have to make the supreme sacrifice for mysqldumps: bypass extended INSERTs.

This makes each row happen in its own INSERT.

It also increase the size of the mysqldump output.

mysqldump --skip-extended-insert --net_buffer_length=50000 myschema > gigantic_file.sql

Give it a Try !!!

  • i still get packet bigger than max_allowed_packet bytes even after bypassing extended inserts ? Could there be any other factors deciding this ? – Baaju Dec 16 '13 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.