Error Code: 2006

[JFlindt]

Hi

I'm currently trying to import a rather large SQL file (~80mb) into a local MySQL database on my laptop.

I'm trying to do so by using SQLyog 5.22 Trial but I keep getting this error-message:

– Error code 2006: MySQL server has gone away

I would believe it has something to do with a time-out on the connection but since I'm working on a local db that shouldn't cause any problems.

Does any of you have a tip or solution to this problem?

/ Jesper Flindt – DK

[peterlaursen]

Please tell:

The MySQL server version (Is it recent?)? How did you install and configure it? Using the installers for Windows from mysql.com?

Operating system?  No 'virtual environment' involved?

How do you connect (on your local I would expect 'direct conenction' but please confirm that there is no HTTP involved!)

80 MB is not a horrible big database.  And size should not matter.

With some server versions there can be issues with the data themselves.  

For instance most servers will crash when configured to use UCS2 as default and client that has not tries to connect ….

There is no timeout in SQLyog and MySQL will not start counting down for timeout as long as there is activity on the connection.  

I think we should start clarifying:

1: Has the server CRASHED when you get that 'Gone Away' error message?

2: When does this error message occur? At exactly the same point every time? (You can see how much data imported before connection was lost!)?

[JFlindt]

peterlaursen wrote on Jan 10 2007, 03:36 PM:

Please tell:

The MySQL server version (Is it recent?)? 

I'm using a MySQL Server 5.0

Quote:

How did you install and configure it? Using the installers for Windows from mysql.com?

Yes

Quote:

Operating system?  No 'virtual environment' involved?

Windows XP Pro SP2 – no virtual stuff

Quote:

How do you connect (on your local I would expect 'direct conenction' but please confirm that there is no HTTP involved!)

Direct connection – no HTTP  

Quote:

I think we should start clarifying:

1: Has the server CRASHED when you get that 'Gone Away' error message?

No that I know of – I have the MySQL System Tray Monitor running and it seems the database is up all the time.

Quote:

2: When does this error message occur? At exactly the same point every time? (You can see how much data imported before connection was lost!)?

Yes it is around line 26 every time.

Hope that helped….

[peterlaursen]

two comments ..

1) a MySQL server 5.0 can be everything from 5.0.1 and onwards.

Get the exact version by issuing “select version();'

latest COMMUNITY (free) release is 5.0.27.  If it not at least 5.0.24a I would start upgrading (very simple on windows)

2) “Yes it is around line 26 every time. ”  I would like to see that line 26 .. or rather the first 50-100 lines!

>> Do you have an editor that can handle such big files?

3) BTW: is it a 'mysqldump-dump' or a 'SQLyog-dump' (or a 'phpmyadmin-dump' or something else) ?

4) What happens with the mySQL command line client?  It is in start-menu .. all programs .. mysql .. mysql server version

a: Start it!  It prompts for the root@localhost password.

b: you must issue the staaament “use your_database” if there is no “use …. ” statement in the file.  Also create a database for the import if there is no 'create database' statement

c: now import your file with “source the_path_to_the_file;” (avoid spaces in the path)

>> Does it import or does server go on vacation again …

[JFlindt]

peterlaursen wrote on Jan 10 2007, 04:30 PM:

two comments ..

1) a MySQL server 5.0 can be everything from 5.0.1 and onwards.

Get the exact version by issuing “select version();'

latest COMMUNITY (free) release is 5.0.27. If it not at least 5.0.24a I would start upgrading (very simple on windows)

2) “Yes it is around line 26 every time. ” I would like to see that line 26 .. or rather the first 50-100 lines!

>> Do you have an editor that can handle such big files?

3) BTW: is it a 'mysqldump-dump' or a 'SQLyog-dump' (or a 'phpmyadmin-dump' or something else) ?

4) What happens with the mySQL command line client? It is in start-menu .. all programs .. mysql .. mysql server version

a: Start it! It prompts for the root@localhost password.

b: you must issue the staaament “use your_database” if there is no “use …. ” statement in the file. Also create a database for the import if there is no 'create database' statement

c: now import your file with “source the_path_to_the_file;” (avoid spaces in the path)

>> Does it import or does server go on vacation again …

1. Version is 5.0.24

2. Yes I can handle the file with UltraEdit, but since its for my work, I can't show you the data.

3. The dump has been made with SQLyog (it was the guy who sent me the file that recommended the program for importing it)

4. If I use the Command Line Client to import I get the same error (The MySQL server has gone away) but it tries to reconnect 3 times and then quits.

[peterlaursen]

Also SQLyog tries to reconnect, but it just does not advertise it!

MySQL server should not 'go away'.  And it does in line 26.

It is not easy to help without some kind of test case!

But …

this could be an issue with the max_allowed_packet setting in MySQL.  If you did not change configuration it is 1 MB only.  And if you did not use 'BULK INSERTS' every table is inserted with a single INSERT statement!

Please first read:

1) http://www.webyog.com/faq/24_101_en.html?highlight=chunks

and try to:

2) raise the max_allowed_packet drastically (64 MB for instance) and try again.

(you can do from MySQL Administrator .. edit configuration .. 'Advanceb Networking' tab.  Restart MySQL for the change to take effect!

[JFlindt]

peterlaursen wrote on Jan 10 2007, 04:55 PM:

Also SQLyog tries to reconnect, but it just does not advertise it!

MySQL server should not 'go away'. And it does in line 26.

It is not easy to help without some kind of test case!

But …

this could be an issue with the max_allowed_packet setting in MySQL. If you did not change configuration it is 1 MB only. And if you did not use 'BULK INSERTS' every table is inserted with a single INSERT statement!

Please first read:

1) http://www.webyog.com/faq/24_101_en.html?highlight=chunks

and try to:

2) raise the max_allowed_packet drastically (64 MB for instance) and try again.

(you can do from MySQL Administrator .. edit configuration .. 'Advanceb Networking' tab. Restart MySQL for the change to take effect!

I've now tried changing the max_allowed_packet to 90MB and using BULK (10000 rows) as well as CHUNKS (90000K:cool: – but still get the same error.

[peterlaursen]

Code:

I've now tried changing the max_allowed_packet to 90MB and using BULK (10000 rows) as well as CHUNKS (90000K:cool: – but still get the same error.

hmmm …  

1) CHUNKS only have effect with HTTP-tunneled connections

2) BULKs are specified in KB's … not rows!

If you raise max_allowed_packet to 90MB you should specify a LOWER valaue for the BULK.

Now .. you are perfectly aware that the BULK setting only works when exporting, so you will need another dump from the other server to change BU

Ks ??

I think that there is still a chance that max_allowed_packet is the issue.  Also because command line client has the same problem, and the error occurs with the first row of data.  I think all statements (CREATE etc.) b

fore the first INSERT statement are executed correctly?

Please try to:

1) keep the max_allowed_packet as 90MB

2) export with a BULK of much lower .. 1 MB for instance.  That means you must enter 1000 in the dialogue box.

Actually I can reproduce the “Error Code: 2006 – MySQL server has gone away” by setting BULK = 100K, export, change server config to “Max-allowed-packet = 10K”, drop the database and try to import.  I think something messed up for you!  Did you restart MySQL after changing the configuration?

[Author]

Posts