MySQL Bugs: #19360: mysql server has gone away

Bug #19360	mysql server has gone away
Submitted:	25 Apr 2006 23:34	Modified:	29 May 2013 12:12
Reporter:	Hongyi Gao	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Linux (Linux (mysql 4) & win2000 (ODBC))
Assigned to:		CPU Architecture:	Any

Description:
we had 3.51.06 before on our windows 2000 server. It worked just fine for years with our 4.1.7 databases.

We recently upgrade the ODBC to be 3.51.12 because we need it for one of our recently upgraded database server (5.0.16).

This version works fine with the mysql5 server. However, it gives us "mysql server has gone away" error occasionally with the other mysql 4.1.7 server. At the same time, the server side logged alot of timeout connections.

How to repeat:
hard

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

I don't think my description is not detailed enough. However, I'd like to show how to repeat it for fun.

1. Our previous working environment:  
3 machines -- one win2000 server with mysql ODBC 3.51.06 + two machines with mysql 4.1.7 and gentoo linux. Worked perfectly.

2. changes: 
upgrade ODBC 3.51.06 to 3.51.12 on win2000, because one of the mysql server is upgraded to mysql 5.0.16

3. issues:
Now, ODBC 3.51.12 works fine with mysql 5.0.16 server, however, with the other mysql 4.1.7 linux server, the ODBC on win2000 gives us "MySQL has gone away" error ocasionally. Sometimes it is no problem. Sometimes it has this issue. Meanwhile, the server side logged alot of timeout connections.

4. challenge:
I don't think this is the timeout issue because both mysql servers (5.0.16 and 4.1.7) are of identical configs. If it is timeout with 4.1.7 machine, it should have the same problem with 5.0.16 one too. Nor does the network package size issue (since all of them are set to 16M and my qureies are all small)

5. to repeat:
setup 3 servers as what I described. You will get the fun.
Also, we have a test environment setup this way. If you like, we can open the access to you and let you help us go through this.

thank you very much. appreciate for your patience.

cannot change the status to OPEN. So I close it and repost a new one. :D

Please don't open a new bug report. I am assign it to my co-work.
Thanks.

everytime the "MySQL has gone away" error happens, restart IIS won't correct it.
However, if I kill the related DLLHOST, the issue went away immediately.
Just so you know.

Hi. 
Thanks for your problem report. 
My test environment is:
 - SuSE 10.1, MySQL 4.1.13
 - WINXP PRO SP2, MySQL 4.1.18 nt max, MySQL 5.0.19
 and MySQL 5.1.7
 - WIN2k SP3, MySQL 4.1.18 nt max, MySQL 4.0.1 alpha and MySQL 5.1.7
 - MyODBC 3.51.12 (latest version and ver. available from dl part of MySQL site)

Result: I was unable to reproduce reported behavior from several ODBC clients.
Suggestion: Please try to upgrade to, at least, 4.1.13. Quote from MyODBC changes file, ver. 3.51.12: "binaries made using MySQL 4.1.13"

As you have noticed ("However, if I kill the related DLLHOST, the issue went away immediately) there could be something undocumented going on in this COM surrogate. 

If upgrading MySQL 4.1.7 to at least MySQL 4.1.13 don't help, I will have to ask you for small but complete test case producing this error.

Hi. It seems that it's not related to upgrade of MyODBC but to MS SP4. Please see http://bugs.mysql.com/bug.php?id=15277.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Hi,

I have been getting the error: "MySQL server has gone away" and I cannot find any information on how to solve this problem. I am running mySQL 5.0.18 and connect to my server from a windows application (ODBC). This problem occurs when my machine has been idle for more that 3 minutes. It sounds like a time-out issue but my wait-timeout var is set to the standard "8 hours".

Anyone?

Hi, can you please check the size of myodbc3.dll in windir\system32? It should be 1.48 MB (1,552,384 bytes).

yes. the size matches.

Hi, can you please post output of netstat -a in time of lockup? I would also like to see my.cnf and hostname.err files attached to this report. I don't think server actually crashed but that there's lockup in IIS.

First a gripe:  It is very difficult to search on the error message "MySQL server has done away" when the bug search happily anounces, "The following words were ignored: has, gone, away."  Why this is annoying is left as an exercise for the reader.

Now, to business: I believe that this is NOT an ODBC issue.  I have seen it also with QueryBrowser without ODBC between client and server.  

The apparent curcumstances are the same whether with ODBC or direct connection to the server.  If you continue to provide activity on the connection, whether read or write, everything is fine.  When a connection has sat idle for a few minutes, the problems begin.  Once the problem begins it will not go away except to create a new connection.  This seems to be a major issue when connection pooling is being used.  If a "gone away" connection is in the pool, it will continue to be assigned to processes.

The effect is also the same whether ODBC or not.  Select statements continue to work.  Manually entered update statements continue to work.  Any use of prepared statements to do an update does not work.  In ODBC this is when you use a recordset Update methode.  In QueryBrowser it is when you use the 'Edit' button to make changes and click 'Apply Changes'.  I do not know what is different about updating rows this way, but something is different.

Hi Lisbeth. I've found two interesting discussions regarding this problem. Maybe it will prove helpfull:
Connection is not closed in given scenario due to using one of pooling types -
oledb resource pooling or odbc connection pooling.

Connection & resource pooling are recommended techniques, especially for web
applications, since they allow to serve multiple clients by opening minimum
number of DB connections (less then clients) and saving time on creating
connection to DB.

In given scenario connection will we closed 60 seconds after the last retrieval
of .asp page. notice that multiple retrievals will not create additional
connections, but will reuse initial one.

Pooling can be disabled if necessary in 2 stages:
1. Disable oledb resource pooling for MSDASQL provider (OLEDB provider for ODBC
drivers). Set
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}
\OLEDB_SERVICES equal to 0xfffffffe.
After this pooling is totally controlled on ODBC level.
2. To disable ODBC connection pooling, open ODBC Data Source Manager
(odbcad32.exe). Go to Connection pooling tab. Double click a driver in the list
and disable pooling.

To check that pooling is disabled. Restart IIS and open the same .asp page.
Connection to database will be closed immidiatelly after page is generated.

-----------------------------------------------------------------------

The problem is caused by, when the IIS server's "executionTimeout" for
httpRequest is reached, IIS will call Thread.Abort() to stop the
processing thread for the Request.

For instance, If a web page is requested, meanwhile the database is
very busy, the Database can't return all the result to the web
application within 40 sec (the default value of executionTimeout in
machine.config), this thread will be aborted!

"TIPS, LIMITATIONS AND KNOWN ISSUES":

8. Thread.Abort() should not be used, as unmanaged resources may remain
unreleased properly, which can potentially cause memory leaks and
hangs.

It seems that we can just avoid the problem by setting the
executionTimeout value to a reasonablily large value. However, this
will greatly affect the web server performance as some Threads will be
hold and they are not able to serve other requests."

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

We also have a long-standing issue with this problem ("MySQL server has gone away") during shortish periods of inactivity using the ODBC connector. Our set-up is 4.1.11 and 4.1.12 on the server (it varies with the client) and MyODBC 3.51.12 (we have downgraded to 3.51.11-2 on some clients, after reading reports that that might help).

The symptom is that described in the bug report - period of inactivity, followed by lost connections. We have tested an SqlYog on the same machine, and followed the processes on the server, and the SqlYog stays active, the ODBC client loses its connection. Thus, we suspect the problem is related to the ODBC driver.

I'm just adding the note (as if it were needed, a web search shows that this is a pretty common problem) to keep this bug open. It would be nice to have its priority raised too, since in situations where network connectivity isn't perfect, this can result in very frequent deconnections.

I have the same server has gone away problem.  I was using MySQL ODBC Connector 3.51.12.  Per comments in this bug, I downgraded to version 3.51.06 and the problem is gone.  Using a MySQL version 4.1.14-nt on a Windows 2000 SP4 server.

I also have connection gone away issues with MySql Administrator 1.2.4 rc.  I must reconnect if I remain idle for more than 5 minutes or so.

I have the same problem.
Access as front end to a MySQL back end server. Linked tables with MyODBC 3.51.14
After an idle time the error comes up. However after a few minutes I can open the tables without reconnecting nor logging again. 
I tried with just a few tables but I guess it can be reproduced with only 1 table.
The only difficulty to repeat this problem is the time. I cannot say how long should be the idle time to repeat. May be 4 or 5 minutes. After getting the error may be another 5 min to recover.

This could be connected to ODBC pooling... Please turn it off and use MyODBC 3.51.15 with Reconnect turned on. Checking last posts in Bug#28387 could also prove useful.

From http://www.thescripts.com/forum/thread504683.html:

add "max_allowed_packet=16M" to your my.ini file and restart the service

I had the exact same problem with the ODBC driver trying to connect MS Access 2003 running on Windows XP SP3 to MySQL 5.0 over a WAN connection.  

I resolved it simply by checking the "Enable Automatic Reconnect" check box on the "Flags 1" tab of the "Details >>" section of the ODBC configuration screen.

Fix:
1) Go To "Control Panel"
2) Choose "Administrative Tools"
3) Choose "Data Sources (ODBC)"
4) Choose your ODBC Driver (depends on your setup... User DSN, System DSN, etc.)
5) Choose click "Configure"
6) Choose "Details >>" at the bottom left of the dialog
7) Choose or the Flags1 Tab
8) Fill in the check box beside "Enable Automatic Reconnect"
9) Click "Okay"

Unfortunately, the comments do not explain what exactly happened and how the problem can be repeated. I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.