MySQL Bugs: #57842: ADOConnection.State Incorrect After MySQL Server Has "Gone Away"

Bug #57842	ADOConnection.State Incorrect After MySQL Server Has "Gone Away"
Submitted:	29 Oct 2010 12:04	Modified:	3 Nov 2010 8:46
Reporter:	Peter McWha	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	MySQL Server 5.1	OS:	Windows (Vista/7)
Assigned to:		CPU Architecture:	Any
Tags:	ADO ODBC Connection

Description:
We have an enterprise critical application which runs on redundant servers.  At a specific point in the middle of the night, a specific function must be run.

It may be possible, on holiday weekends for example, there will be no data traffic for up to 24 hours.

Our application continuously checks the ADODB.Connection.State to ensure the connection is not closed.  If it IS closed, the application re-establishes a new connection.

One of the purposes is to ensure that when MySql arbitrarily "goes away", we will still be able to perform the nightly function.

We cannot control the ability of someone in the enterprise from changing the "go away" time slice to a small number.  We have to defend against ill-founded human interaction.

The application crashed this morning at 04:10.  MySql had "gone away".  Yet the value of ADODB.Connection.State was 1, meaning "connected" and not "0" meaning "closed".

How to repeat:
. start application and connect to database via ODBC
. wait a suitable time until MySql "goes away"
. check the .state value of the previously established connection

Suggested fix:
When MySql goes away, change the .state value of all existing connections to 0.

Hi Peter,

I am afraid that your understanding of the connection state property is not completely correct. It does not indicate the connection health, ADODB.Connection.State merely confirms the fact that connection has been opened before.

The only reliable way of making sure that connection is working is to send something to the server and receive server's reply. Please note that this approach works only for short-living connections because if connection is opened for long time it can be broken for thousands different reasons (firewall, server timeouts, physical network problems etc.). Therefore, the application must never keep idle connections (perhaps your program does so and that is why you get the "server gone away" errors).

Pinging the server from the driver as you suggested would not be good because it creates problems with thread synchronization, generates additional traffic etc. In fact, it has never been implemented in any connector for any database product. There is a much better way to deal with the problem:

The best practices of application design work as follows:

1. open connection
2. do all immediate operations
3. close connection
4. wait if necessary
5. go to [1] if new database
operations are to be performed

Connecting to the database might take considerable time, so it is a good idea to use connection pooling, which dramatically improves the connection time. The pool holds few open connections ready for incoming connect requests [1] and gives the connection to the program after verifying that pooled connection is ok. If the connection is broken the pool manager opens a new connection. All these operations are performed transparently to the client and you do not need to write any additional code. When client requests closing the connection [3], it is not closed, but moved to the pool for re-using.

Check the connection pooling options for connector/NET:

http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html

I am marking this report as "not a bug".