Help! Getting Lots Of Error No. 2013 And Error No. 2006

[Aidan]

Hello,

I'm using SQLyog Enterprise v8.05. I've been using it to sync up a database table that currently has about 48,000 rows. I have scheduled a sync to run every hour. It was working reasonably well for a few months. I got the occasional error 2006 (MySQL server has gone away) or 2013 (Lost connection to MySQL server during query) now and again, but I was not too concerned about that as long as it would work on the next sync attempt. However, these errors became more and more regular and now have not had a single successful sync since July 10th (11 days ago) when the database had about 45,000 rows.

Today I tried doing it direct from the Wizard and here's the result from the wizard:

Sync started at Tue Jul 21 11:54:58 2009

Table SrcRows TgtRows Inserted Updated Deleted

========================= ======= ======= ======== ======= =======

`ft_form_1` 49107 45391 There was an error while executing a query. The query and the error has been logged in the log file.

0 6 0

There was an error while executing a query. The query and the error has been logged in the log file.

Check C:UsersAidanAppDataRoamingSQLyogsja.log for complete error details.

And here's the result form the error log:

Sync started at Tue Jul 21 11:54:58 2009

`ft_form_1` 49107 45391 Error No. 2006

MySQL server has gone away

0 6 0

Error No. 2006

MySQL server has gone away

I am connected to the source using HTTP tunnelling and to the target directly.

I would really appreciate if someone can shed some light on this problem.

Regards,

Aidan

[navyashree.r]

Quote:

2013 (Lost connection to MySQL server during query) now and again,

Hi,

2013 error is describe briefly in MySQL docs,

http://dev.mysql.com/doc/refman/5.1/en/err…_cr_server_lost

“Lost connection to MySQL server during query”

One more FAQ link:

http://www.webyog.com/faq/content/16/66/en…-at-an-isp.html

Quote:

I got the occasional error 2006 (MySQL server has gone away)

The 'gone away' error message. It is likely because you have a row that is larger than the 'max_allowed_packet' setting in server configuration. If you have access to server configuration try insert this line in the [mysqld] section of the server configuration file on the server

max_allowed_packet = 100M

(default is as low as 1M if not explicitly set)

… and restart the server for the change in configuration to take effect. The setting defines the max. length size of a SQL-statement that the server will allow a client to send. If it is exceeded the server will drop the connection to the client.

The error message is explained in MySQL documentation here:

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

Regards,

Navya

[Aidan]

Thank you for replying Navya.

The 2013 error is the one I get mostly, and just occasionally I get 2006.

The fact that 2013 means “Lost connection to MySQL server during query” doesn't help me much – I need to figure out why and how to rectify. In the document you linked, it mentions this error in the context of SSH tunnelling but I am not using SSH tunnelling so I do not think that is applicable in my case.

Is there a way to determine which MySQL server it lost the connection to, the source or the target?

I do not have direct access to the servers, they are both on shared hosting. I have Plesk access to the source and Cpanel access to the target (with phpMyAdmin on both).

I can try requesting server config changes through support if that is what is needed but I would need to know exactly what to request.

I've checked the max_allowed_packet for both source and target and it is similar on both – 16777216 on the target and 16776192 on the source.

When I run a sync directly from the wizard, it seems to work for updating a few rows initially as I can see the Updating row: messages in the output area. I just tried it again not and it got to about 25 rows before it fails. When I run it again, it does the same thing, updates a few rows and then fails after a minute or a few (varies).

Is there a way to set SQLyog sync to try again if the connection fails so that if it is only able to do a few updates at a time it can keep reconnecting until it eventually gets them all?

[Aidan]

some additional info that may shed more light:

I have been trying some sync operations direct from the wizard. It now seems like I am getting more of the 2006 error whereas before (from scheduled sync), I was getting more of the 2013 errors. It also just hangs sometimes and does nothing so I have to click Terminate.

I have tried it in 'generate script' mode instead of direct sync. It also fails in this case. Looking at the output SQL, there are a few update statements in it after it fails.

[peterlaursen]

“Is there a way to determine which MySQL server it lost the connection to, the source or the target?”

If you will let us try from our end we can see that if we run from inside our debugging environment (Visual Studio). If so please create a private ticket for exchanging server details. But I do not believe we can do much about it .. only maybe document some problems that you may be able to use when discussing with hosting support.

But it is a very valid observation that with Data Sync (where we are connected to 2 servers at a time) the log should not only print the error but also from which of the servers/connection the error originate. Currently we don't.

[navyashree.r]

Quote:

But it is a very valid observation that with Data Sync (where we are connected to 2 servers at a time) the log should not only print the error but also from which of the servers/connection the error originate. Currently we don't.

This is added here,

http://code.google.com/p/sqlyog/issues/detail?id=1125

Thank You.

—

Navya

[Aidan]

peterlaursen wrote on Jul 22 2009, 08:06 PM:

If you will let us try from our end we can see that if we run from inside our debugging environment (Visual Studio). If so please create a private ticket for exchanging server details.

Thank you Peter, I have submitted a ticket. It will be great if you can at least determine if there is a specific problem with configurations, etc. at either one of the hosting services so that I can address the issue with them.

I'm wondering if the size of the db could be a problem, since it was working with only occasional failure when the db was smaller, then failures became more frequent until now it not working at all?

Thanks,

Aidan

[Author]

Posts