If a query times out, the connection gets closed.

If a query times out, the connection gets closed.

Postby benutzer » Thu 28 Jul 2011 19:22

If you set the timeout for a MySqlCommand and run a query that takes longer than that timeout, an exception is thrown after the timeout expires. This behavior is expected. But when the exception is thrown, the MySqlConnection is also closed.

This happens with dotConnect for mySQL version 6.30.185. However, in version 3.5, the MySqlConnection was not closed when the exception is thrown. The behavior has been changed between 3.5 and 6.30.185, but I did not see anything related to this in the version history.

This is a problem because when the MySqlConnection is closed, the thread that is executing the query is still running on the MySQL server. Since the MySqlConnection is closed, calling Kill() or Close() on it has no effect. The thread on the MySQL server has been leaked.

Here is a code sample that demonstrates the problem:

Code: Select all
using System;
using Devart.Data.MySql;

namespace ConnectionLeakSample
{
    static class ConnectionLeakSample
    {
        ///
        /// A sample program to demonstrate that if a MySqlCommand times out
        /// while it is being executed, the query is still running in a thread on
        /// the MySQL server, and calling Kill() on the MySqlConnection
        /// does not kill the thread on the MySQL server.
        ///
        /// Occurs with dotConnect for mySQL version 6.30.185.
        ///
        /// This bug did not occur with MySQLDirect version 3.5. The behavior has changed since then.
        ///

        static void Main()
        {
            /*
             * NOTE: YOU MUST FILL IN THESE CONSTANTS TO MATCH YOUR MYSQL SERVER.
             */
            const string host = ;
            const string database = ;
            const string userId = ;
            const string password = ;
            const int port = ;



            // Create a database connection.
            MySqlConnection connection = new MySqlConnection();
            connection.Host = host;
            connection.Database = database;
            connection.UserId = userId;
            connection.Password = password;
            connection.Port = port;
            // Enable support for UTF8 encoding.
            connection.Unicode = true;
            // Disable connection pooling.
            connection.ConnectionString += "Pooling=false;";

            connection.Open();

         try
         {
            // Create a query that takes a long time.
                MySqlCommand mySqlCommand = new MySqlCommand("SELECT Sleep(1000)", connection);
                // Set a short timeout for the query.
                mySqlCommand.CommandTimeout = 5;

                // Execute the query. This method will time out after a few seconds and throw an exception.
                MySqlDataReader mySqlResultSet = mySqlCommand.ExecuteReader();
         }
         catch (Exception exception)
         {
                // If the query timed out, this exception will say "Lost connection to MySQL server during query".
                // NOTE: The query is still running on the MySQL server at this point,
                // but the Devart library has closed the MySqlConnection object.
                Console.Out.WriteLine("Exception caught: " + exception.Message);
                Console.Out.WriteLine("MySqlConnection object state: " + connection.State);

                // Store the old database connection.
                MySqlConnection oldConnection = connection;

                // Create a new database connection.
                MySqlConnection newConnection = new MySqlConnection();
                newConnection.Host = host;
                newConnection.Database = database;
                newConnection.UserId = userId;
                newConnection.Password = password;
                newConnection.Port = port;
                // Enable support for UTF8 encoding.
                newConnection.Unicode = true;
                // Disable connection pooling.
                newConnection.ConnectionString += "Pooling=false;";

                newConnection.Open();

                // Use the new database connection to kill the old connection.
                Console.Out.WriteLine("Killing old database connection.");
                newConnection.Kill(oldConnection);

                // NOTE: Killing the old connection does not do anything. The query
                // is still running on the MySQL server. You can still see the query
                // if you open a MySQL command prompt and do "SHOW PROCESSLIST;".
            }
        }
    }
}
benutzer
 
Posts: 2
Joined: Thu 28 Jul 2011 19:10

Postby Shalex » Mon 01 Aug 2011 13:25

We have reproduced the behaviour when query on old connection is still running after newConnection.Kill(oldConnection) with the latest (6.30.196) version of dotConnect for MySQL. We will investigate the issue and notify you about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7537
Joined: Thu 14 Aug 2008 12:44

Postby benutzer » Tue 16 Aug 2011 14:49

Is your team still investigating this issue? Have you decided whether it will be fixed? Thank you for your attention to this matter.
benutzer
 
Posts: 2
Joined: Thu 28 Jul 2011 19:10

Postby Shalex » Fri 19 Aug 2011 10:19

We are investigating the issue. We will post here about the results as soon as possible.
Shalex
Devart Team
 
Posts: 7537
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Mon 29 Aug 2011 10:29

The bug with MySqlConnection.Kill() method is fixed. We will post here when the corresponding build of dotConnect for MySQL is available for download.
Shalex
Devart Team
 
Posts: 7537
Joined: Thu 14 Aug 2008 12:44

Postby Shalex » Wed 07 Sep 2011 11:43

New version of dotConnect for MySQL 6.50 is released!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .

For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21943 .
Shalex
Devart Team
 
Posts: 7537
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL