Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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???

share|improve this question
    
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

1 Answer 1

up vote 0 down vote accepted

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 !!!

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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