"2006 Mysql Server Has Gone Away" ? Help Needed ?

[nomaam]

Hello:

I am using SQLyog 5.19 and I am trying to use the “Database Synchronization Wizard”. At first the program works well but when I try to do an update of over 6 million rows I start to get connection problems.

Below is a condensed version of my error log:

—- Start of Error Log

---

Error No. 2006 MySQL server has gone away

Error No. 2006 MySQL server has gone away

…

Error No. 0

Error No. 0

…

Error No. 2006 MySQL server has gone away

Error No. 0

…

Error No. 0

Error No. 2013 Lost connection to MySQL server during query

Error No. 2013 Lost connection to MySQL server during query

Error No. 2006 MySQL server has gone away

Error No. 2006 MySQL server has gone away

…

Error No. 2006 MySQL server has gone away

MySQL server has gone away – Could not get FIELD information for '`test2`' in the SOURCE database

—- End of Error Log

---

Could someone please help me in fixing this problem? I do not know how to ensure the connection remains open so the transactions can complete.

Thanks.

[peterlaursen]

I think the situation is one of those:

1) unstable connection or server

2) the max_allowed_packet setting in MySQL configuration is too low! Default is 1 M only and a single BLOB can be 64 M !

If you have acces to MySQL configuration then insert a line like this in the [mysqld] section of the configuration file (my.ini/my.cnf)

max_allowed_packet = 100M

The MySQL server must be restarted for the configuration change to take effect.

[nomaam]

Thank you for your reply.

I changed the max_allowed_packet setting to 106M and I am still getting the same error (after server restart).

The two computers I am using are on the same LAN and in the same room connected through a router, so I do not believe it is a wired connection problem.

Also I am not sending any BLOBs, I am just doing row insertions with minimal text and int values. The only problem is that there are a lot of rows. I did not see any option within the sync wizard to try and do the updates in sessions to try and maybe avoid this problem.

Can anyone think of any other solutions to my ongoing problem? I am unable to locate any other possible solutions.

[peterlaursen]

I see you are using version 5.19.

I think you should first try a later version. Could you install the latest trial. If you had a trial installed before and it has expired, then please create a ticket and we will find a solution!

You don't experience the same with large batch jobs like imports?

[Myx.Ostankin]

'peterlaursen' wrote:

I think the situation is one of those:

1) unstable connection or server

2) the max_allowed_packet setting in MySQL configuration is too low! Default is 1 M only and a single BLOB can be 64 M !

If you have acces to MySQL configuration then insert a line like this in the [mysqld] section of the configuration file (my.ini/my.cnf)

max_allowed_packet = 100M

The MySQL server must be restarted for the configuration change to take effect.

Googled this page when came across the same problem – this solution worked for me, thanks a lot!

[Author]

Posts