15

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.

Improve this question
1
  • Any feedback? [15 chars required to comment]
    – Mihai Limbăşan
    Commented May 12, 2009 at 15:16

5 Answers 5

Reset to default
27

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.

Improve this answer
6
  • 1
    Yes I was just trying the command line switch. The reason that I thought it had an effect was that it gave a max allowed packet too small error when I set it to something smaller. I will try your suggestion on Monday. Thanks
    – Jeremy French
    Commented May 8, 2009 at 15:54
  • You're welcome - I'm almost certain this will work. Unfortunately, like with many (okay, most) other software written in the last 50 or so years, MySQL's error messages tend to suck and be misleading if you're not already familiar with the way the developer's mind works :).
    – Mihai Limbăşan
    Commented May 8, 2009 at 15:56
  • Glad to hear that - have fun.
    – Mihai Limbăşan
    Commented May 13, 2009 at 15:44
  • Very very awesome. I had this same problem, came here to ask the question and bam! solved. @Mihai - whatever you get paid; it's not enough :)
    – Steven Evers
    Commented Oct 20, 2009 at 16:27
  • This solution helped us when we got this error after moving our MySQL from localhost to a remote server. Thank you very much!
    – Lea Cohen
    Commented Dec 27, 2011 at 10:52
1

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.

Improve this answer
0

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)

Improve this answer
0

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.

Improve this answer
-1

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

mysql -u username -p -d dbase < c:\dump.sql
Improve this answer
1
  • Oh - probably not, since it seems to have failed a ways into the import.
    – Brent
    Commented May 8, 2009 at 15:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .