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;".
}
}
}
}