Bug #72110 Exception for closed connections
Submitted: 24 Mar 2014 14:15 Modified: 22 May 2014 15:43
Reporter: Jorge Bastos Email Updates:
Status: Not a Bug Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.7.4 OS:Any
Assigned to: Francisco Alberto Tirado Zavala CPU Architecture:Any

[24 Mar 2014 14:15] Jorge Bastos

If I have a global connection, to with pooling, and if I kill the connection in MySQL (or imagine that a network failure happen for some time), when i try to use it on winforms, an exception occorrs, and if I check the state if it, it says that it still is open.

Is this normal or something that needs to be tune up?

How to repeat:
Kill an active connection
[24 Mar 2014 18:27] Jorge Bastos
A more practical example is, in the winform app, i have the connection open, and if for some reason someone restarts the server, when i try to use the connection, i get the error.
I'll post an example and error ASAP.
[3 Apr 2014 21:44] Jorge Bastos
Test App

Attachment: testeclosedcon.zip (application/x-zip-compressed, text), 243.39 KiB.

[3 Apr 2014 21:49] Jorge Bastos
Ok, procedures are:

Open my APP, click connect, a connection will be open.
Please note that I'm using connection pooling.
So far so good, the connection remains active, BUT, if for some reason the connection is droped (ex: the server was restarted, the own server or the mysql server) ), and i click the 2nd button, as if it things the connection is still open, an error will ocorr.


A: is it supposed to re-connect?
B: no other way to solve this?
[10 Apr 2014 10:49] Jorge Bastos

Any update on this?

[10 Apr 2014 12:18] Francisco Alberto Tirado Zavala
Hello Jorge.

We'll notify you any news on this report, we're checking this one and others.
[10 Apr 2014 17:51] Jorge Bastos

I'll wait!
[8 May 2014 15:03] Jorge Bastos
Hi Francisco,

Sorry to pressure, any update on this?
The MSSQL ADO.NET has this and it reconnects if this happens!

Thanks in advanced,
[8 May 2014 15:35] Francisco Alberto Tirado Zavala
Hello Jorge.

I'll notify you any update shortly.

Thanks for your time.
[21 May 2014 15:40] Francisco Alberto Tirado Zavala
Hello Jorge.

When you are working with a Client-Server application all the connections are performed by Sockets, when you create a connection you open a Socket between your client and your server, and an Id/ProccesId is assigned/reserved in the server for the connection requested by the client. If the server is restarted the Id given to the client is lost, so you need to create a new valid connection opening a new socket to get a valid Id from the server. 

Also you must consider that the server can terminate or close a connection if the connection is inactive after some time. If you have your connection open also could affect your connections pool, because a connection open means less connections for your pool: having 10 clients connected at the same time are 10 connection less from the Server available connections.

If you can't change the design in your application (having a global connection), as a workaround you can have a method to validate that the connection is valid, for example the following method ping the server and if the ping fails it open the connection again:
. . .
MySqlConnection _conn = new MySqlConnection("MyConnectionString");
private void CheckConnection()
. . .

As you can see the code is very simple and it works in the following way: when the connection try to ping the server if the connection is invalid, internally the stream that has the socket information will be terminated and the connection will be set on status closed as well as the invalid connection will be removed from the connections pool if exists, then opening the connection again will create a new connection which means a new valid socket. If the ping succeed so you will continue using the same connection.

To use it you can call it before trying to perform an action to the database, and the connection will be re-opened just when is invalid:
. . .
private void button1_Click(object sender, EventArgs e)
  //assuming that the connection is already open
  var cmd = new MySqlCommand("select 1", _conn);
. . .

This is just a workaround, and as far I know all the connections to any database server will have the same behavior: if the server is restarted any connection will be lost, as well as the server will clean every unused connection.

Thanks for your time.
[21 May 2014 20:01] Jorge Bastos
Hi Francisco,

Ya, this solves my "problem".
I use one global function to insert data (INSERT/UPDATE/DELETE/REPLACE), everything that doesn't return rows's, and one global function to retrieve a datatable with the selected data, and in the function, is has two parameters

Function Insert_Sql(byval _sql as string,option _mcon as mysqlconnection=nothing)
End Function

When I use connection pooling, in looping operations, I use the 2nd parameter, so I just have to add an additional:

If Not MyCon.Ping Then
End If

So now I can use just one global connection for the app.
Simple, thanks!
[22 May 2014 14:08] Francisco Alberto Tirado Zavala
Hello Jorge.

I'm glad that you find a solution for your problem.
Are you ok if I change the status of this report as Not a Bug?

Thanks for your time.
[22 May 2014 15:40] Jorge Bastos
Not at all, please do, and thank's for your help!