Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I received multiple Packet too Large errors.

I restarted mysql with a much larger max_allowed_packet (i.e.- 1000M) and still received the error. I even attempted setting the max_allowed_packet in the import file, still received the error.

Is there a way to ensure the max_allowed_packet is set and/or use mysqldump that will create a file that does not cause this problem?

For reference:

the uncompressed mysqldump file is ~2GB

database type is INNODB

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

The first I thought about was what max_allowed_packet actually controls. Here is what I found:

According to the page 99 of "Understanding MySQL Internals" (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining it:

MySQL network communication code was written under the assumption that queries are always reasonably short, and therefore can be sent to and processed by the server in one chunk, which is called a packet in MySQL terminology. The server allocates the memory for a temporary buffer to store the packet, and it requests enough to fit it entirely. This architecture requires a precaution to avoid having the server run out of memory---a cap on the size of the packet, which this option accomplishes.

The code of interest in relation to this option is found in sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay particular attention to net_realloc().

This variable also limits the length of a result of many string functons. See sql/field.cc and sql/intem_strfunc.cc for details.

Given that definition of max_allowed_packet, I then discovered something else from ServerFault: innodb_log_file_size and innodb_log_buffer_size combined must be larger than ten times your biggest blob object if you have a lot of large ones

Keeping these two things in mind, I would increase innodb_log_file_size in /etc/my.cnf to the max size allowed for it, 2047M. This of course requires the following

service mysql stop
rm -f /var/lib/mysql/ib_logfile*
service mysql start

This will accommodate any big blobs you may have in your data.

share|improve this answer
    
Curious as to where that 10x comes from - is it just a rule of thumb or is there code in MySQL that say allocates 10 fixed buffers so you need 10 to ensure that 1 of them is big enough? –  Gaius Apr 27 '11 at 11:11
add comment

MySQL's max_allowed_packet still has to be within the limits of the shell that started it - does ulimit -a show that data seg size is unlimited?

share|improve this answer
add comment

For some reason, max_allowed_packet is ignored by mysqldump -- by design? The actual complement is net_buffer_length. So instead try

mysqldump --net_buffer_length=100k -u root -p databasename > dump.sql
share|improve this answer
    
That's very enlightening !!! Since the bug report discusses the extended insert in conjunction with this problem, maybe doing --skip-extended-insert may compensate some but will surely generate a larger mysqldump. +1 for You finding this diamond in the rough that Oracle will leave for dead !!! –  RolandoMySQLDBA Jun 10 '11 at 3:01
    
--skip-extended-insert definitely works, but on my database slowed down restore by 100x making it unusable. –  Leopd Jun 10 '11 at 20:31
    
I'm sorry, my condolences. Maybe have mysqldump create CSV files and try loading them back using LOAD DATA INFILE and increasing bulk_insert_buffer_size to 1G or 2G. Hey, you never know !!! –  RolandoMySQLDBA Jun 10 '11 at 20:36
add comment

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.