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.

If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find..

[mysqld]
max_allowed_packet = 1M

[mysqldump]
max_allowed_packet = 16M

What is the point?

Because when you export a dump using mysqldump with max_allowed_packet that might not able import successfully to the current mysql? E.g. http://stackoverflow.com/questions/93128/mysql-error-1153-got-a-packet-bigger-than-max-allowed-packet-bytes

share|improve this question
    
I wrote about what the MySQL Packet is back in April 2011 : dba.stackexchange.com/a/2383/877. Please read it. –  RolandoMySQLDBA Dec 31 '12 at 19:09

2 Answers 2

The max_allowed_packet variable limits the size of a single result set. In the [mysqld] section, any normal connection can only get 1M worth of data in a single query. In mysqldump you typically produce "extended INSERT" queries, where you list multiple rows within the same INSERT command. It's better, then, to have this variable set high.

The above is a brief explanation. Now, as for your question "What is the point?" -- there is no clear answer. The my-XXXXX.cnf set of files are long since outdated, and have their issues, including recommendations which are commonly not agreed upon (like the amount of memory you should allocate to the innodb_buffer_pool_size). So take it as a general starting point. In particular, these settings relate to systems with very low memory available (like 128M RAM), so the numbers don't really apply to today's hardware.

One could argue that you won't necessarily import the dump into same server, so possible to another server with higher setting for max_allowed_packet.

As a side note, I usually set max_allowed_packet to very high values, like 32M or 64M. As opposed to other variables like sort_buffer_size, with max_allowed_packet you only pay for what you need. So a connection will not allocate the full 64M if it doesn't have to.

A 1M limit means you could have some row with some TEXT or BLOB, possibly larger than 1M, and in such case, you would actually be unable to read that row.

share|improve this answer
    
+1 for just setting it higher. I usually like setting to 256M for starters. I rarely have to exceed that. –  RolandoMySQLDBA Dec 31 '12 at 19:07

According to the doc [1]:

When creating multiple-row INSERT statements 
(as with the --extended-insert or --opt option), mysqldump creates rows 
up to net_buffer_length length.

And since the limit of net_buffer_length is 1M, the configuration above is perfectly fine even the max_allowed_packet of mysqldump is 16M .

[1] http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_net_buffer_length

share|improve this answer
    
Thanks for docs, but if employing -c switch together with -e one still needs to lower net_buffer_length. –  Vesper Apr 7 at 9:39

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.