MySQL "server has gone away" when reloading database dump due to "max_allowed_packet" default

OpenSubmitted by Ben Sturmfels.
Details
2 participants
  • Ben Sturmfels
  • Ludovic Courtès
Owner
unassigned
Severity
normal
B
B
Ben Sturmfels wrote on 11 Jan 2017 01:10
Download
(address . bug-guix@gnu.org)
20170111111003.5e8ae2c8@stumbles.id.au
Download
Hi there!
I'm using GuixSD 0.12.0 and ran `guix package --install=mysql` (whichI gather gave me MariaDB 10.1.19), then created a database and tried toreload a database dump of a decent sized existing database:
zcat dump.sql.gz | mysql DBNAME
which failed with:
ERROR 2006 (HY000) at line 2266: MySQL server has gone away
Reloading the same dump works for me on default MySQL in Trisquel 7.The database dump was created on Debian with `mysqldump --no-create-dbDBNAME`.
On Guix, I was able to reload the dump by running mysqld with a customconfig file with max_allowed_packet=16M under [mysqld].
It appears that the default value for `max_allowed_packet` underDebian/Trisquel is "16M", where under Guix it is "4M". The mysqldumpdefaults bunche a whole lot of records into a single insert statementfor performance, which is why I'm getting a value > 4M.
Could it be worth setting max_allowed_packet to 16M in Guix's`mysql-configuration-file` function for consistency with Debian?
Regards,Ben
L
L
Ludovic Courtès wrote on 12 Jan 2017 15:22
Download
(name . Ben Sturmfels)(address . ben@stumbles.id.au)(address . 25415@debbugs.gnu.org)
87wpe02w9c.fsf@gnu.org
Download
Hi Ben,
Ben Sturmfels <ben@stumbles.id.au> skribis:
Toggle quote (15 lines)> Reloading the same dump works for me on default MySQL in Trisquel 7.> The database dump was created on Debian with `mysqldump --no-create-db> DBNAME`.>> On Guix, I was able to reload the dump by running mysqld with a custom> config file with max_allowed_packet=16M under [mysqld].>> It appears that the default value for `max_allowed_packet` under> Debian/Trisquel is "16M", where under Guix it is "4M". The mysqldump> defaults bunche a whole lot of records into a single insert statement> for performance, which is why I'm getting a value > 4M.>> Could it be worth setting max_allowed_packet to 16M in Guix's> `mysql-configuration-file` function for consistency with Debian?
Definitely. I would add a ‘max-allowed-packet’ field in<mysql-configuration> in (gnu services databases) and make sure it’shonored.
Would you like to give it a try?
Thanks for your report!
Ludo’.
B
B
Ben Sturmfels wrote on 13 Jan 2017 00:29
Download
(name . Ludovic Courtès)(address . ludo@gnu.org)(address . 25415@debbugs.gnu.org)
58781150.3060202@stumbles.id.au
Download
On 13/01/17 01:22, Ludovic Courtès wrote:
Toggle quote (9 lines)>> Could it be worth setting max_allowed_packet to 16M in Guix's>> `mysql-configuration-file` function for consistency with Debian?> > Definitely. I would add a ‘max-allowed-packet’ field in> <mysql-configuration> in (gnu services databases) and make sure it’s> honored.> > Would you like to give it a try?
Sure, I'll give it a shot!
It looks as though the MariaDB source comes with a settings file forDebian that includes max-allowed-packet=16M:
mariadb-XX.XX.XX/debian/additions/my.cnf
Would you recommend adding just max-allowed-packet, or would it be worthapplying all these settings in this file?
Ben
Attachment: signature.asc
L
L
Ludovic Courtès wrote on 13 Jan 2017 09:03
Download
(name . Ben Sturmfels)(address . ben@stumbles.id.au)(address . 25415@debbugs.gnu.org)
87shonl736.fsf@gnu.org
Download
Ben Sturmfels <ben@stumbles.id.au> skribis:
Toggle quote (21 lines)> On 13/01/17 01:22, Ludovic Courtès wrote:>>>> Could it be worth setting max_allowed_packet to 16M in Guix's>>> `mysql-configuration-file` function for consistency with Debian?>> >> Definitely. I would add a ‘max-allowed-packet’ field in>> <mysql-configuration> in (gnu services databases) and make sure it’s>> honored.>> >> Would you like to give it a try?>> Sure, I'll give it a shot!>> It looks as though the MariaDB source comes with a settings file for> Debian that includes max-allowed-packet=16M:>> mariadb-XX.XX.XX/debian/additions/my.cnf>> Would you recommend adding just max-allowed-packet, or would it be worth> applying all these settings in this file?
I’m not famliar with MySQL/MariaDB, but any setting that sounds usefulto you is welcome.
Thanks,Ludo’.
?
Your may also send email to 25415@debbugs.gnu.org to comment.