Home / server / Questions / 6354
Accepted
Jeremy French
Asked: 2009-05-09 07:36:39 +0800 CST

"MySQL server has gone away" when attempting to import a large dump file

  • 772

I am trying to import a MySQL dump file.

The file was created on a Linux server, I am trying to import on windows

I logged into the command line and ran:

SOURCE c:/dump.sql

But this seems to have thrown up some character set problems (specifically with smart quotes and other non standard punctuation).

It was suggested to me that I run:

mysql -u username -d dbase < c:\dump.sql

When I try this I get the error

ERROR 2006 (HY000) at line 149351: MySQL server has gone away

A bit of googling suggested that this was to do with the max_allowed_packet switch but I have tried this and it hasn't worked. Has anyone any idea what this could be?

If anyone has a suggestion about the character set issue that would be helpful too.

mysql

5 Answers

  1. Best Answer

    My first instinct after reading the error message in the question title was to suggest increasing max_allowed_packet. You mentioned that you tried "that switch" it and it hasn't worked. Can you confirm that you have correctly modified the server's configuration file? Your phrasing makes it sound like you've tried to use that as a command line switch on the mysql.exe client command line, which wouldn't cause the server to alter behavior.

    So, in short, what you should try to do is locate and edit the my.cnf file your server is currently using. In the [mysqld] section alter the max_allowed_packet settings to something like

    [mysqld]
max_allowed_packet=32M

    Don't forget to restart the server after altering the configuration.

    I've used 32M (a ridicuously large value) as an example. Since your query seems to be enourmous you should try this value (or perhaps even 64M if you've got enough RAM) to see whether it works.

    Another option is to leave the server as-is and alter the behavior of the client used to generate the SQL dump. Tell it to limit the size of the individual queries to under 1 MB - that should also do the trick.

    For more details, see B.1.2.10. Packet too large in the MySQL manual.

    • 27

  2. On MySQL 5.7.24, it was due to old config from MySQL 5.6 

    sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION.

    Below option is mandatory if SQL version is same 5.7.5 or above.

    sql_mode=TRADITIONAL

    Also there are two max_allowed_packet parameters in /etc/my.cnf, other one is for mysqldump. Please take note of the same.

    max_allowed_packet=<1G>

    My installation is on CentOS7.

    • 1

  3. Are you able to access the MySQL instance on the windows server from the linux box?

    If so, can you run the command on the linux box, with the -h switch to connect to the server on the windows box?

    mysql -u username -d dbase -h <windows host> < dump.sql

    This might solve your character set issues, since you are using the Linux client on the original box to do the import. (just a guess)

    • 0

  4. What does the MySQL log say?

    Sometimes this error is reported on the client if there is an uncorrectable error on the server. This can be an indicator of on disk corruption of the database tables.

    • 0

  5. Did your account have a password? Perhaps you need to add the -p switch:

    mysql -u username -p -d dbase < c:\dump.sql
    • -1