Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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 ran

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.

share|improve this question
    
Any feedback? [15 chars required to comment] –  Mihai Limbăşan May 12 '09 at 15:16

4 Answers 4

up vote 15 down vote accepted

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.

share|improve this answer
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 May 8 '09 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 May 8 '09 at 15:56
    
Yes, this works. Thanks. –  Jeremy French May 13 '09 at 14:05
    
Glad to hear that - have fun. –  Mihai Limbăşan May 13 '09 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 :) –  SnOrfus Oct 20 '09 at 16:27

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.

share|improve this answer

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)

share|improve this answer

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

mysql -u username -p -d dbase < c:\dump.sql
share|improve this answer
    
Oh - probably not, since it seems to have failed a ways into the import. –  Brent May 8 '09 at 15:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.