Bug #54012 MySql Connector/NET is not hardened to deal with ThreadAbortException
Submitted: 27 May 2010 0:50 Modified: 13 Jul 2010 15:34
Reporter: Bassam Tabbara Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.2.3, trunk OS:Any
Assigned to: Vladislav Vaintroub CPU Architecture:Any

[27 May 2010 0:50] Bassam Tabbara
Description:
A common usage of the Connector/NET is within ASP.NET which relies on Thread.Abort() to cancel HTTP requests that are taking to long, or during shutdown. Connector/NET does not seem to handle ThreadAbort nicely. For example, the query currently executing on the MySql server is not cancelled (as it does with command timeouts). The end result is that the MySql Server continues to run queries for HTTP requests that have timed out or were canceled.

How to repeat:
Here is a snippet of code that reproduces the problem:

            Thread t = new Thread((o) =>
            {
                using (MySqlConnection conn = new MySqlConnection(builder.ToString()))
                {
                    conn.Open();

                    MySqlUtil.ExecuteNonQuery(conn, "SELECT BENCHMARK(10000000000,ENCODE('hello','goodbye'))");
                }
            });
            t.Start();
            Thread.Sleep(5000);
            t.Abort();
            t.Join();

At this point the SELECT BENCHMARK query is still running, despite the thread being aborted. For comparison, the Microsoft SQL Server ADO.NET provider will cancel the query in such situations.

Suggested fix:
Attached is a small patch that helps fix the problem. Its modeled after timeout handling in 6.2.3 releases. I'm sure more can be done here, but I leave it to the experts to deal with this.
[27 May 2010 0:52] Bassam Tabbara
Patch for handling ThreadAbortException

Attachment: 5c7ca124e4ad484385b81cfb5845e6e6.patch (application/octet-stream, text), 2.52 KiB.

[28 May 2010 8:48] Tonci Grgin
Hi Bassam and thanks for a nice catch (although I'd prefer a complete test case).

Verified as described:
    public class ServerClass
    {
        // The method that will be called when the thread is started.
        public void InstanceMethod()
        {
            MySql.Data.MySqlClient.MySqlCommand cmd;
            cmd = new MySqlCommand();
            try
            {
                cmd.Connection = GetConn();
                cmd.CommandText = "SELECT BENCHMARK(10000000000,ENCODE('hello','goodbye'))";
                cmd.ExecuteNonQuery();

                Console.WriteLine("DBG");

                cmd.Connection.Close();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            }
        }

        public static void StaticMethod()
        {
            try
            {
		//Not used this time
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            }
        }

        public static MySql.Data.MySqlClient.MySqlConnection GetConn()
        {
            MySql.Data.MySqlClient.MySqlConnection conn;
            conn = new MySql.Data.MySqlClient.MySqlConnection();
            conn.ConnectionString = "server=xx;uid=root;pwd=xx;database=test;Minimum Pool Size=2; Maximum Pool Size=10;logging=true;pooling=true; ProcedureCacheSize=5;allow batch=true";
            conn.Open();
            return conn;
        }
    }

    public class Simple
    {
        public static int Main(String[] args)
        {
            Console.WriteLine("Thread Simple Sample");
            System.Collections.ArrayList threads = new System.Collections.ArrayList();

            ServerClass serverObject = new ServerClass();
            Thread StaticCaller = new Thread(new ThreadStart(ServerClass.StaticMethod));
            new Thread(new ThreadStart(serverObject.InstanceMethod)).Start();

            ThreadStart ts = new ThreadStart(ServerClass.StaticMethod);
			Thread wt1 = new Thread(ts);
            wt1.Name = "WorkerThread1";
			threads.Add(wt1);
			wt1.Start();

            Thread.Sleep(5000);
            wt1.Abort();

            // wait for the threads to end
            int x = 0;
            while (!(threads[0] as Thread).IsAlive)
            {
                x = 0;
            }
            Console.WriteLine("Just a separator");
            return 0;
        }
    }
produces:
mysql> show processlist;
+----+------+--------------------------------------+------+---------+------+----
---+------------------+
| Id | User | Host                                 | db   | Command | Time | Sta
te | Info             |
+----+------+--------------------------------------+------+---------+------+----
---+------------------+
|  1 | root | QCW2K8.dummy.porta.siemens.net:50615 | NULL | Query   |    0 | NUL
L  | show processlist |
+----+------+--------------------------------------+------+---------+------+----
---+------------------+
1 row in set (0.00 sec)

mysql> show processlist;
+----+------+--------------------------------------+------+---------+------+----
-------+---------------------------------------------------------+
| Id | User | Host                                 | db   | Command | Time | Sta
te     | Info                                                    |
+----+------+--------------------------------------+------+---------+------+----
-------+---------------------------------------------------------+
|  1 | root | QCW2K8.dummy.porta.siemens.net:50615 | NULL | Query   |    0 | NUL
L      | show processlist                                        |
| 10 | root | QCW2K8.dummy.porta.siemens.net:50697 | test | Query   |    4 | exe
cuting | SELECT BENCHMARK(10000000000,ENCODE('hello','goodbye')) |
| 11 | root | QCW2K8.dummy.porta.siemens.net:50698 | test | Sleep   |    4 |
       | NULL                                                    |
+----+------+--------------------------------------+------+---------+------+----
-------+---------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show processlist;
+----+------+--------------------------------------+------+---------+------+----
-------+---------------------------------------------------------+
| Id | User | Host                                 | db   | Command | Time | Sta
te     | Info                                                    |
+----+------+--------------------------------------+------+---------+------+----
-------+---------------------------------------------------------+
|  1 | root | QCW2K8.dummy.porta.siemens.net:50615 | NULL | Query   |    0 | NUL
L      | show processlist                                        |
| 10 | root | QCW2K8.dummy.porta.siemens.net:50697 | test | Query   |   18 | exe
cuting | SELECT BENCHMARK(10000000000,ENCODE('hello','goodbye')) |
| 11 | root | QCW2K8.dummy.porta.siemens.net:50698 | test | Sleep   |   18 |
       | NULL                                                    |
+----+------+--------------------------------------+------+---------+------+----
-------+---------------------------------------------------------+
3 rows in set (0.00 sec)

Reggie?
[21 Jun 2010 17:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/111717
[28 Jun 2010 22:06] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/112387

865 Vladislav Vaintroub	2010-06-29
      Cancel query in ThreadAbortException handler (Bug#54012)
[1 Jul 2010 22:58] Vladislav Vaintroub
pushed to 6.2, 6.3
[13 Jul 2010 15:34] Tony Bedford
An entry has been added to the 6.2.4 and 6.3.3 changelogs:

MySQL Connector/NET did not process Thread.Abort() correctly, and failed to cancel queries currently running on the server.