Bug #64991 Connection state reports opened after killing connection by server
Submitted: 16 Apr 2012 8:18 Modified: 21 May 2012 14:07
Reporter: Dennis Minderhoud Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.5.4 OS:Windows
Assigned to: CPU Architecture:Any
Tags: Connection state

[16 Apr 2012 8:18] Dennis Minderhoud
Description:
When I create a new database connection in C# and call the method .Open() on the instance, the connection state is 'Open'.
If the connection is created and then killed on the server side, the connection state is not updated and therefore it still reports that it state is 'Open'.
We tried this with and without connection pooling on mysql server 5.1 and 5.5.
A workaround could be disconnecting/closing the 'Open' connection and open it again, but this will create performance loss on every request to the server.

How to repeat:
Create and open a connection. Kill it on the serverside and make e request to the server with the just created connection.

Suggested fix:
Update the connectionstate on a serverside event
[16 Apr 2012 8:21] Dennis Minderhoud
Updated with OS: MS Windows
[26 Apr 2012 9:24] Dennis Minderhoud
Upgrade to Critcal problem.
Overhead of workaround is too much time expensive on a high load system.
[7 May 2012 6:51] Michiel van Vaardegem
I would really want to see some action on this bug. Checking (with for example Ping()) if the connection is open, is very time consuming.
In my opinion either the state_changed event should be fired or the State property should change if the connection is killed.
[21 May 2012 14:07] Bogdan Degtyariov
This is not a bug.
When a connection is killed on the server side the client is not notified 
about that in any way. There is no "State" or "Variable" that would reliably
reflect the moment when the connection became invalid. This is so because
network cannot sense that it has been disconnected from a remote server
unless there is a direct wire connection without routers, other servers in
between.

The only way to make sure the connection is alive is to send something to the
server and get the server reply (implement ping or something).

Imagine a situation when your client is connected to the server and someone 
cuts the cable. Without pinging the server all the time you would not be able 
to catch the moment when it happens. As you mentioned, ping is a rather
expensive method of connection verification. 

In addition, keeping an idle connection open is a bad practice. You should 
never do that. The idle connection is a subject to many risks of being 
terminated by firewalls, timeouts, network issues and so on.

If opening/closing the real connections is not fast enough you can use
the Connection Pooling technique. The pool manager is doing the connection
verification before giving it to the client and the client has to make sure
the connection is returned to the pool as soon as possible (by calling
Connection.Close() method).