Error No. 2006 Mysql Server Has Gone Away

[vygi]

I am wondering why I start to get never-ending Error No. 2006 MySQL server has gone away when I start some query and then kill it from the command line?

I'm quite sure that previuos versions always reconnect in this case. Now with RC3 I have to close connection and connect again manually.

To reproduce:

– start eg. data sorting of some very big table from SQLyog (SELECT * FROM big_table ORDER BY unindexed_field LIMIT 10);

– kill this mysql thread using other client;

– SQLyog says that connection was lost – this is always the case, and it's OK;

– after them SQLyog permanently says that “MySQL server has gone away” – this is new to me…

[peterlaursen]

With 5.11 FINAL I get (doing the same exercise)

Error Code : 2006

MySQL server has gone away

(0 ms taken)

and nothing more. Not the popup 'Connection was lost'. I'll have to reconnect maually.

[vygi]

peterlaursen wrote on Apr 24 2006, 03:26 PM:

With 5.11 FINAL I get (doing the same exercise)

Error Code : 2006

MySQL server has gone away

(0 ms taken)

and nothing more. Not the popup 'Connection was lost'. I'll have to reconnect maually.

I don't care about “Connection was lost” message which pops up in some cases (sometime it was only shown in the message tab).

I basically ask whether manual reconnection is really necessary in that case?

I am pretty sure that some time ago SQLyog was reconnectic automatically (like eg. the standard mysql CLI client is doing).

Mysql thread killing may be the only option in some cases, eg. when some big table is locked in MySQL4 and “show table status” cannot be executed. I this case, SQLyog hangs when you try to change the database.

The latest SQLyog now needs manual reconnection when connection to the sever is lost and that is really annoying.

[peterlaursen]

Quote:

The latest SQLyog now needs manual reconnection when connection to the sever is lost and that is really annoying.

I am not sure about details (hard to remember everything) – if there is a change or not. Ritesh must tell.

But I agree that when clicking an object in the object browser for instance it should attempt to reconnect. And maybe even try one or a few reconnects automatically when connection is lost. I think the tunneller does.

[vygi]

peterlaursen wrote on Apr 24 2006, 03:52 PM:

I am not sure about details (hard to remember everything) – if there is a change or not. Ritesh must tell.

There MUST be a change.

Today I came into the office and I must manually reconnect to all MySQL severs because of time out over the night.

It was never needed before!!

It's really boring because I have to save all changed queries as otherwise it will be lost (I have to close and re-open every connection).

I will try 5.12 BETA2 now and will be back with my complaints if it also needs manual reconnect.

[vygi]

Same problem with 5.12 BETA2:

– start some SELECT * FROM big_table ORDER BY unindexed_field LIMIT 10;

– switch to another client, make SHOW PROCESSLIST and kill this SELECT query;

– this error message will appear once: “Error No. 1053 Server shutdown in progress”

– this error message will appear every time as long as you don't manually reconnect:

“Error No. 2006 MySQL server has gone away”.

Sorry but it makes 5.11 RC2 and 5.12 BETA2 barely useful to me as our MySQL server has often locked tables and it is not always possible to kill a query from SQLyog (we have to do it via commandline client). And because of time out we have now to reconnect manually next business day at the latest.

[peterlaursen]

I have two local and two remote servers i can connect to with HTTP-tunnel. And I don't have this issue. Do you have a chabce to conect to some other server, just to find out if the problem is related to one specific server or the problems shall be found on the client side?

[vygi]

peterlaursen wrote on Apr 25 2006, 08:45 AM:

I have two local and two remote servers i can connect to with HTTP-tunnel. And I don't have this issue. Do you have a chabce to conect to some other server, just to find out if the problem is related to one specific server or the problems shall be found on the client side?

Hey ,

yesterday you said “I'll have to reconnect maually” and today “don't have this issue”, so what is right please?

I have at least 4 “local” servers here (1x MySQL5 and 3x MySQL4) and this is definitive a client-side problem (new since some late 5.1 beta).

[peterlaursen]

I am sorry ..

I guess I simply wrote this the wrong place! 🙁

[Ritesh]

We have not changed any code regarding reconnection. It must be something else. Right now I am in MySQL UC 2006 in Santa Clara and will take a look into it after reaching India on 2nd.

I have anyway asked my developers back in India to reproduce the case but they are unable to.

Is it possible to provide temporary access to your server so that we can check it out?

[vygi]

Ritesh wrote on Apr 26 2006, 01:23 PM:

We have not changed any code regarding reconnection. It must be something else. Right now I am in MySQL UC 2006 in Santa Clara and will take a look into it after reaching India on 2nd.

I have anyway asked my developers back in India to reproduce the case but they are unable to.

Is it possible to provide temporary access to your server so that we can check it out?

Hello Ritesh,

thanks for replying!

Unfortunately, all MySQL servers are locatedin our intranet and it doesn't have any connections from outside.

It is possible that something happened while upgrading of SQLyog?

Should I try to make fresh installation or try something else?

There were guaranteed no changes on the server side (and I use at least 4 diferent MySQL servers).

Vygi

[vygi]

addendum:

I have removed my SQLyog, deleted all files from my personal folder and from program files, then installed SQLyog 5.12 Beta2 from the scratch and still have same problem: I need to re-connect manually as soon as connection to the server is lost.

Then I have install an older version (free 5.0 from Oct/Nov 2005) …. and it works just fine! “1053 Server shutdown in progress” pops up only once but after them SQLyog reconnects in the background because I can continue to work on this server (having new connection ID).

After them back to 5.12 Beta — and “2006 MySQL server has gone away” back again 🙁

I don't have any 5.11 beta installers so I was unable to test with them.

Hope it helps,

Vygi

[ronjeremy_69]

Ritesh wrote on Apr 26 2006, 01:23 PM:

We have not changed any code regarding reconnection. It must be something else. Right now I am in MySQL UC 2006 in Santa Clara and will take a look into it after reaching India on 2nd.

I have anyway asked my developers back in India to reproduce the case but they are unable to.

Is it possible to provide temporary access to your server so that we can check it out?

I am also experiencing this issue after upgrading to 5.11 final (and now 5.12 BETA). Ritesh, if you still cant reproduce I can grant temporary server access.

[peterlaursen]

I don't Ritesh will need that.

This is an issue on the client side – no a server issue.

I just think that things have been a liitle too 'tense' the last week for Ritesh fully to consider this – due to the MySQL Users Conference and the crashing bug.

[ronjeremy_69]

peterlaursen wrote on Apr 27 2006, 02:37 PM:

I don't Ritesh will need that.

This is an issue on the client side – no a server issue.

I just think that things have been a liitle too 'tense' the last week for Ritesh fully to consider this – due to the MySQL Users Conference and the crashing bug.

Sounds good, just wanted to offer. The fact Ritesh has kept up with forum replies while at the conference is impressive in itself!

[Yuri]

Really annoying,

Since the 5.11 version my connection is lost for about every 2 minutes or such. It happens over my intranet and over the internet with several mysql versions.

I'm going back to the previous version untill this bug is solved.

b.t.w. i usually connect to LARGE databases, “rebuilding tag files” takes a LONG time, is it really neccecary to do this every time i connect?.

Does this have anything to do with the “gone away” bug?

[peterlaursen]

about REBUILDING TAGS:

you can disable the actomatic REBUILD from menu .. tools .. preference and do it manually from powertools. You can also completely disable autocomplete.

It it not the size of the DB that influences then BUILD time. No. of rows does not matter. It is the number of objects (tables, columns etc). In other words STRUCTURE/METADATA and not DATA.

Also if SQLyog is the only client used for modifying the structure there is no need to rebuild (except for possible repair). For instance if SQLyog does an ALTER TABLE that adds a column to a table, it also does a INSERT to the emebdded database – or rather to the cached copy. And this cached copy is saved when program closes (but not if it crashes, of course …)

[vygi]

Hey here,

I just wanted to say that I've upgraded to the latest 5.12 beta and still have this boring problem (need to close and re-open all connections every morning after they time out over the night).

[peterlaursen]

You should shut down your computer when leaving your job in my opinion!

What an irresponsible waste of energy 🙁

[vygi]

peterlaursen wrote on May 3 2006, 08:15 AM:

You should shut down your computer when leaving your job in my opinion!

What an irresponsible waste of energy 🙁

OK OK OK,

I know I know I know,

CO2 and rain forest and so on….

but seriuosly: sometimes I start some long taking scipts and processes when I left my office and I even CAN'T stop my PC, so I also left SQLyog up and running.

BTW did you see that SQLyog has scheduled tasks? They also require an (almost) permanently running client, isn't? And they may fail because of some timeout and unavility to reconnect automatically?

color=”red”]X[/color <-- nail here to get a new monitor

[peterlaursen]

Where will I get my new monitor?

[Ritesh]

http://www.webyog.com/forums/index.php?sho…view=getnewpost

[peterlaursen]

just checked it, but no new monitor there 🙁

[Suzette]

Ritesh wrote on May 4 2006, 02:34 AM:

http://www.webyog.com/forums/index.php?sho…view=getnewpost

I am getting the same 2006 error. Some background. Using Migration Toolkit to connect with ODBC to IBM Redbrick database on Windows server. I have a very large table (500 columns, 1M rows).

Test #1. I let toolkit create the table and load the data. I am using a WHERE clause to limit to 100K records. Works just dandy.

Test #2. I drop this table. I recreate the table using MySQL 5.1 so I can use partitions. I create the table with 10 partitions using KEY() as the partition. I use Migration Toolkit. Go into Advanced to click on import data only (don't want toolkit dropping table and recreating because I need these partitions). I use the same WHERE clause as in Test #1 above. This is what I get:

*********************************************************

SQLyog Job Agent Version 5.12

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Job started at Thu May 18 12:20:04 2006

DBMS Information: RED BRICK WAREHOUSE

Importing table schema: UNIT_FACT… Successful…

Importing table foreign keys: UNIT_FACT… Successful…

Importing table data: UNIT_FACT…

ERROR: 2006, MySQL server has gone away

Table:UNIT_FACT

Check C:Program FilesSQLyog Enterprisesja.log for complete error details.

ERROR: 2006, MySQL server has gone away

Table:UNIT_FACT

Check C:Program FilesSQLyog Enterprisesja.log for complete error details.

ERROR: Import aborted…

Check C:Program FilesSQLyog Enterprisesja.log for complete error details.

Total time taken – 11 sec(s)

*********************************************************

Test #3: I create a smaller table which only has 25 fields and 1M records. I create 10 partitions using KEY(). I use Migration Toolkit, click on import data only. No WHERE clause. The data comes over fine and the 10 partition files are what I would expect.

Question: What's happening? It seems like the partitioning of tables with a large number of columns is what is making it exit abnormally. Any hints? This is a major stumbling block in our endeavor to migrate to MySQL so your help would be much appreciated.

Thanks….SBW

[peterlaursen]

No, it is not related to partitioning, I think. 'Sorting data into partitions' is done by the MySQL server itself. The SQL that SQLyog must generate is the same with and without partitions on the target.

“ERROR: 2006, MySQL server has gone away”

Are you using 5.12 FINAL and not a beta??

5.12 will reconnect if connection is lost. But of course there are limits to how unstable connection can be for it to work.

How do you connect to MySQL. Direct, HTTP, SSH ?

[Suzette]

peterlaursen wrote on May 18 2006, 05:15 PM:

No, it is not related to partitioning, I think. 'Sorting data into partitions' is done by the MySQL server itself. The SQL that SQLyog must generate is the same with and without partitions on the target.

“ERROR: 2006, MySQL server has gone away”

Are you using 5.12 FINAL and not a beta??

5.12 will reconnect if connection is lost. But of course there are limits to how unstable connection can be for it to work.

How do you connect to MySQL. Direct, HTTP, SSH ?

This is probably a dumb question, but how do I know if BETA or FINAL? I received an upgrade email today from Webyog giving me a link for the enterprise download (http://www.webyog.com/sqlyog/183E427A-808F-4CB9-BE5B-06T0D2C8CC48/SQLyog512Ent.exe).

I'm connecting directly to my localhost MySQL database from SQLYOG. I don't believe any HTTP or SSH. From SQLYOG I use Migration Tookit using ODBC to connect to RedBrick database to get the data.

This is the same connection method for all my tests I indicated in previous reply.

[peterlaursen]

” but how do I know if BETA or FINAL?”

program help .. about tells. But if you DL'ed from that link today it is the FINAL.

The problem is the connection to MySQL. And it is on 'localhost'.

hmmm … it can be hardware related (motherboard, harddisk) or software (protocol stack) related. It also can be an issue with MySQL 5.1 or SQLyog. Very hard to tell. Thinking ….

Running jobs like Migration, Sync etc will always demand a more stable connection that GUI operations do. Do you have a chance to test on another PC?

[Suzette]

peterlaursen wrote on May 18 2006, 05:33 PM:

” but how do I know if BETA or FINAL?”

program help .. about tells. But if you DL'ed from that link today it is the FINAL.

The problem is the connection to MySQL. And it is on 'localhost'.

hmmm … it can be hardware related (motherboard, harddisk) or software (protocol stack) related. It also can be an issue with MySQL 5.1 or SQLyog. Very hard to tell. Thinking ….

Running jobs like Migration, Sync etc will always demand a more stable connection that GUI operations do. Do you have a chance to test on another PC?

All these tests were on my laptop with 1G RAM. I also tested on desktop with 2G RAM. Same thing happens. I'm thinking it has to do with partitioning in conjunction with large number of columns? As I said earlier, without migrating to a partitioned table, imports fine. It is only when I add the partition to the table that I get this error. hmmm…perhaps i should send email to mysql 5.1 folks? Is there a way to run this from the command line?

[peterlaursen]

“without migrating to a partitioned table, imports fine” and not to a partitioned …

It must then be an issue with MySQL itself. There is nothing here that SQLyog can 'influence' . All partions operations are performed on the server side alone.

I think you can import ito a non-partitioned table a create partions after import. Can you handle that yourself or need help?

I don't think MySQL will response positively to a bug.report from you using a third-party client. They will ask you to reproduce with command-line client! Better if we do then! After all we are 'MySQL Gold Certified Partners' . But we will need some debugging info to be able to.

Wait for Ritesh to comment on it … But probably we will need the table definition of the partitioned table and also some data to reproduce with.

“Is there a way to run this from the command line?” What ??

If you are talking about adding partitions after import you can use SQLyog/SJA 'Notifications Services' for that.

[Ritesh]

Quote:

5.12 will reconnect if connection is lost. But of course there are limits to how unstable connection can be for it to work

Nope. It will only reconnect when you execute query from the query window. SJA does not uses the reconnection mechanism.

Quote:

MySQL server has gone away

The error definitely seems to be due to the large number of columns but I cannot be 100% sure.

To debug I will suggest to perform the following steps:

1.) Import the schema only(no data). Thus it will only create the table structure i.e. an empty table. Is this step successful?

2.) Use 'Table Data' tab to fill in dummy values (reasonably close to the actual data) and see if you are able to insert. MySQL will throw “Server has gone away” too for this query itself. SQLyog will try to reconnect from the GUI, two times and then give up.

Just follow the above two steps for both partition and non-partition tables.

Basically, SQLyog as well as SJA, sends INSERT INTO queries to MySQL to enter data to the db. If it fails with SJA, it will defintely fail with SQLyog and the MySQL command line tool also and thus we will have a test case that can be submitted to MySQL as bugs (if its a bug with MySQL).

[peterlaursen]

@Ritesh

Well then

1) It will only reconnect when you execute query from the query window.

2) Use 'Table Data' tab to fill in dummy values …. SQLyog will try to reconnect from the GUI, two times and exit.

to me that sounds contradictory. 1) reconnect only from query window 2) will recomnnect from 'table data' GUI

What does the term 'query window' cover here then?

[Ritesh]

Sorry for the confusion. What I meant was that SQLyog will only reconnect when a query is executed by the GUI (both user executed and SQLyog generated). It will not reconnect for any query that are executed by SJA.

[vygi]

Hey here,

now one more (small one) problem:

when I have several open connections and they time out over the night — all works now fine when I try to access the servers directly by sending queries or selecting objects (THANKS for fixing) but is still doesn't work when I try to copy a table to another server:

I still get “Error No. 2006 Mysql Server Has Gone Away” error when I try to copy a table to a server with inactive (expired or killed) connection. I have to switch to that server (to re-initiate connection) and then retry.

[Ritesh]

Working on it.

Will revert back soon.

[nicmar]

I suddenly got the same problem. I usually connect using tunneler, but to one server i connect directly.

After around 1-2 minute I get it and have to reconnect.

But one thing here that's interesting. I have an access application that connects to the same database, it never had any problems before, but when I wait 1-2 minutes, the application says ODBC Error: Request failed (translated from swedish)

So I figure it might be server related after all, or the Access application (which is a bought program, not made by myself) has the same error as SQLYog..

Any clues about this?

I use SQLYog 5.12 Beta (Don't know if it's 1 or 2, doesn't say in Help-text)

[peterlaursen]

@nicmar

you should first of all upgrade SQLyog!

[johng]

I am posting this because others might benefit who could be over-loading the system when migrating data to MySql from an ODBC data source (maybe just VFP but could affect others)

Upgraded to 5.15 and received the gone away error. Returned to version 5.01 and got same error but with a bit more detail:

ERROR: 2006, MySQL server has gone away

ERROR: [Microsoft][ODBC Visual FoxPro Driver]SQL: Statement too long.

Found out from various places that I was trying to update too many fields, or File DSN connection set to “Fetch data in background”. Looked at Options in ODBC setup, saw that “Fetch data…” box is unchecked which is correct. Tried again – no good. Table merge involves 145 fields so I used an XML editor to cut down the query by half (keeping the link field ID in the remaining script). 'Saved as' the new version to a new file. Then Saved as another version of the XML file and pasted the cut section + put the link field in that file as well.

Tried again – now it runs fine with two XML files. No problems.

[Author]

Posts