5

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

4

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.

  • +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
0

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

  • Thanks for docs, but if employing -c switch together with -e one still needs to lower net_buffer_length. – Vesper Apr 7 '14 at 9:39

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.