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

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.

Solution 1:

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.

Solution 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.