MySQL Bugs: #72110: Exception for closed connections

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:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.7.4	OS:	Any
Assigned to:	Francisco Alberto Tirado Zavala	CPU Architecture:	Any

Description:
Hi,

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

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.

Test App

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

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.

Solution:

A: is it supposed to re-connect?
B: no other way to solve this?

Hi,

Any update on this?

Thanks,

Hello Jorge.

We'll notify you any news on this report, we're checking this one and others.

Thanks,

I'll wait!

Hi Francisco,

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

Thanks in advanced,

Hello Jorge.

I'll notify you any update shortly.

Thanks for your time.

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()
{
   if(!_conn.Ping())
   {
     _conn.Open();
   }
}
. . .

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
  CheckConnectionState();
  var cmd = new MySqlCommand("select 1", _conn);
  cmd.ExecuteNonQuery();
}
. . .

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.

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
    MyCon.Open()
End If

So now I can use just one global connection for the app.
Simple, thanks!

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.

Not at all, please do, and thank's for your help!