Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

Question

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. https://stackoverflow.com/questions/93128/mysql-error-1153-got-a-packet-bigger-than-max-allowed-packet-bytes

Answer 1

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.

Answer 2

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