| Bug #9753 | mysqldump ignores max_allowed_packet | ||
|---|---|---|---|
| Submitted: | 8 Apr 2005 11:06 | Modified: | 25 May 2011 17:05 |
| Reporter: | Axel Schwenke | Email Updates: | |
| Status: | Won't fix | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
| Version: | 5.0.4 | OS: | Linux (Linux) |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[8 Apr 2005 18:32]
Sergei Golubchik
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://www.mysql.com/documentation/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php Additional info: See http://dev.mysql.com/doc/mysql/en/mysqldump.html ... net_buffer_length The initial size of the buffer for client/server communication. When creating multiple-row-insert statements (as with option --extended-insert or --opt), mysqldump creates rows up to net_buffer_length length.
[7 Feb 2008 12:32]
MySQL Verification Team
If max_allowed_packet does not effect mysqldump, which seems to be the case, then the manual should be updated to reflect that, and this becomes a documentation bug. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
[7 Feb 2008 13:46]
Sergei Golubchik
What do you think needs to be changed in the documentation ?
[8 Mar 2008 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[18 Apr 2008 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[4 Apr 2011 23:45]
Rick James
The problem exists in 5.1.48. It would be nice if it were fixed, rather than documenting around it. To document around it: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_allowed_pac... (And similar sections in earlier versions.) Remove the section, or replace the body with "This parameter is accepted, but ignored. When building batch INSERT statements, the number of rows is limited to what will fit in 1MB." (I did not experiment with what happens when trying to dump a row with a BLOB or TEXT that is bigger than 1MB.)
[25 May 2011 17:05]
Paul DuBois
Closing. There was no reply to Sergei's request for clarification.
Description: mysqldump ignores the max_allowed_packet variable when creating extended INSERT statements. Seems the size is fix at ~1MB. This bug exists in earlier versions too (tested with 4.1.7). How to repeat: #check if mysqldump parses the command line parameter ~ $mysqldump --max_allowed_packet=10k --help | egrep ^max_all max_allowed_packet 9216 ~ $mysqldump --max_allowed_packet=20k --help | egrep ^max_all max_allowed_packet 19456 ~ $mysqldump --max_allowed_packet=50k --help | egrep ^max_all max_allowed_packet 50176 --> looks good #now we dump a table with 10k records (~100k text) with varying packet sizes ~ $mysqldump --max_allowed_packet=10k test bar | fgrep INSERT | wc 1 10005 100028 ~ $mysqldump --max_allowed_packet=20k test bar | fgrep INSERT | wc 1 10005 100028 ~ $mysqldump --max_allowed_packet=50k test bar | fgrep INSERT | wc 1 10005 100028 --> bad; mysqldump always creates a single INSERT statement with ~100k length Suggested fix: Workaround: max_allowed_packet must not drop below 1MB for mysqld and any tool that works with dumps created by mysqldump. However this workaround may not always be possible.