Lost connection to MySQL server during query

[Shalex]

Step 2 - Instantiates a MySqlConnection using connection string 2, and closes it. This step can take several hours. For the particular exception that we encountered, this step took > 8 hours, which is (surprise) the default wait_timeout value as set on the msyql server.

You mean that this connection was in opened idle state > 8 hours before you called Close(), don't you? This is a designed behaviour in this case. Please call Close() to put connection to the pool after every its usage.

Exception occurs in step 3 when step 2 > wait_timeout. The server terminates connection 1, but the pool still returns this connection. You can even call "MySqlConnection.Open()" successfully, but when a command is executed, we get the infamous "lost connection to MySql server during query".

Probably, you have retrieved connection from the pool after it was terminated by the server for some reason and before the next validation of it by the pool manager. As we mentioned above, connection in the pool is validated every ~60 seconds. And it is not validated before retrieving because it will affect the performance. You can use the Ping() method to validate it yourself.

Another alternative would be for us to invalidate the pool after Step2, but, as you said before, there is a 30 second wait time out.

We have implemented the MySqlConnection.ClearAllPools(bool force) method in the 5.70 version of dotConnect for MySQL. If force=true, all pools are cleaned without timeout. Here is this thread: http://www.devart.com/forums/viewtopic.php?t=17229.

Please tell me if I've got something wrong in your post.

[tduong]

Hi Shalex,

In step 2, connection #2 is in open and *active* state because we are doing database operations with it for those 8+ hours. Connection #1, which belongs to a different pool, was closed in step 1 (but the server sees it as idle). After 8+ hours are over, and we're done using connection #2 for step 2, we instantiate a new MySqlConnection object, passing in the connection string that yielded connection #1. This newly instantiated connection gives the "lost connection to MySql server during query".

#Step 1
MySqlConnection firstConn = new MySqlConnection(connString1)
firstConn.Open();
// Perform some small operations
firstConnection.Close();

#Step 2
MySqlConnection secondConn = new MySqlConnection(connString2)
secondConn.Open();
// Perform set of operations for 8+ hours (no problems)
secondConn.Close();

#Step 3
firstConn = new MySqlConnection(connString1)
firstConn.Open();
// Exception occurs hereafter.

[Shalex]

As I understand, the problem is that pool manager doesn't clear pool from connections (it is firstConn from your pseudo code) that are disconnected by the server according to the wait_timeout value. I have tried to reproduce the mentioned scenario using the following code with the latest (5.80.180) version of dotConnect for MySQL:

Code: Select all

            MySqlMonitor monitor = new MySqlMonitor();
            monitor.IsActive = true;
            string connStr = "server=localhost;database=test;uid=root;pwd=root;";
            MySqlConnection conn1 = new MySqlConnection(connStr);
            conn1.Open();
            MySqlCommand cmd = conn1.CreateCommand();
            cmd.CommandText = "set wait_timeout=30"; //sets wait_timeout for current session
            cmd.ExecuteNonQuery();
            conn1.Close();

            System.Threading.Thread.Sleep(180000); //waiting 3 minutes

            //will retrieve connection from the pool if there is a valid one,
            //but it will create a new connection (see Events in dbMonitor)
            MySqlConnection conn2 = new MySqlConnection(connStr);
            conn2.Open();
            cmd = conn2.CreateCommand();
            cmd.CommandText = "select 4";
            Console.WriteLine("Is connection from pool valid? - {0}.",(Convert.ToInt32(cmd.ExecuteScalar()).Equals(4)));
            Console.ReadLine();
            conn2.Close();

Output:
Is connection from pool valid? - True.

So, the behaviour is correct. Please try the latest version (5.80.180) and tell us how we should modify this sample to reproduce the problem.

Please trace the pool state with dbMonitor tool (can be used with the Professional and Developer editions of dotConnect for MySQL):
http://www.devart.com/dbmonitor/dbmon3.exe
http://www.devart.com/dotconnect/mysql/ ... nitor.html

[flafleur]

I got the same error. I got it when I open a second connection while the first is open and then close the second and after the first.
My first connection is dropped by the server after 120 seconds (wait_timeout=120). I think the first connection was keep in the pool but isn't removed by the pool manager.

This is my test code to reproduce the problem.




Devart.Data.MySql.MySqlMonitor monitor = new Devart.Data.MySql.MySqlMonitor();
monitor.IsActive = true;
string connStr = "server=localhost;database=test;uid=root;pwd=root;";
MySqlConnection conn1 = new MySqlConnection(connStr);
conn1.Open();
MySqlCommand cmd = conn1.CreateCommand();
cmd.CommandText = "set wait_timeout=120"; //sets wait_timeout for current session
cmd.ExecuteNonQuery();

MySqlConnection conn2 = new MySqlConnection(connStr);
conn2.Open();
cmd = conn2.CreateCommand();
cmd.CommandText = "select 4";
cmd.ExecuteScalar();

//Close the connection 2 before the first
conn2.Close();

//Close Connection 1 after the connection 2
conn1.Close();



System.Threading.Thread.Sleep(300000); //waiting 5 minutes

//will retrieve connection from the pool if there is a valid one,
//but it will create a new connection (see Events in dbMonitor)
MySqlConnection conn3 = new MySqlConnection(connStr);
conn3.Open();
cmd = conn3.CreateCommand();
cmd.CommandText = "select 4";
Console.WriteLine("Is connection from pool valid? - {0}.", (Convert.ToInt32(cmd.ExecuteScalar()).Equals(4)));
Console.ReadLine();
conn3.Close();

Thanks for your help,
François

[Shalex]

flafleur, we have reproduced the Lost connection to MySQL server during query error with the 5.80.190 version of dotConnect for MySQL. I will notify you about the results of our investigation.

[Shalex]

We have fixed the Lost connection to MySQL server during query problem (pool manager deleted a valid connection from the pool instead of the invalid one). Additionally, we have implemented the Validate Connection connection string parameter. If Validate Connection=false; (default) - connection is validated only in the pool every ~30 seconds; if Validate Connection=true; - connection is validated only on retrieving it from the pool (less performance, should be used in the environment with unstable connections).

The fix and the Validate Connection feature will be available in the next build. I will post here when it is available for download.

[flafleur]

The performance when we use the "Validate Connection=true" is it same that "Pooling=false" or it's better.

I saw that you put the new fixes in the version 6.0. Can I download this version if a bought the version 5.80?

Thanks!

[Shalex]

I have performed a small test in my environment. Here is the time of opening connection the second time (conn.Open() -> conn.Close() -> conn.Open()):
a) Pooling=true; Validate Connection=false; (default settings)
0.0004997
b) Pooling=true; Validate Connection=true;
0.0016840
c) Pooling=false; (Validate Connection does not make difference in this case)
0.0060159

To upgrade to the latest version of dotConnect for MySQL, please use the credentials (URL, account name, and password) that were sent to you after the purchase of our product. If you didn't receive this information, please contact our sales department at (sales at devart.com).

[Shalex]

The 'Validate Connection' connection string parameter for validating connection on its retrieving from pool is implemented in dotConnect for MySQL starting from the 6.00.58 build.