Devart Forums

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