Bug #59571 "Query execution was interrupted" when using MySqlCommand.Cancel()
Submitted: 17 Jan 2011 22:34 Modified: 25 Feb 2011 16:06
Reporter: Andrey Belykh Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.3.6 OS:Windows
Assigned to: CPU Architecture:Any

[17 Jan 2011 22:34] Andrey Belykh
Description:
When the sample code (see "how to repeat") is run with MySQL v5.5.8 backend, the 2nd ExecuteReader throws "Query execution was interrupted" exception.

Workaround: turn off connection pooling.

Same code works fine with MySQL v5.1.39.

How to repeat:
    internal class Program
    {
        private static void Main(string[] args)
        {
            string connectionString = "Data Source=192.168.0.32;User ID=root;Password=secret;Initial Catalog=employees;";
            //connectionString += "Pooling=false;";

            RunQuery(connectionString);

            // If the connection pooling is not disabled, fails with "Query execution was interrupted"
            RunQuery(connectionString);
        }

        private static void RunQuery(string connectionString)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand("SELECT * FROM departments", connection))
                {
                    using (MySqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess | CommandBehavior.SingleResult))
                    {
                        if (reader.Read())
                        {
                            object o = reader[0];
                        }
                    }
                    command.Cancel();
                }
            }
        }
    }

Suggested fix:
When the connection is returned to the pool, reset its status (call something like sp_reset_connection in MS SQL Server).
[20 Jan 2011 14:20] Santo Leto
Thank you for a problem report.

I was able to repeat the exception with MySQL 5.5.8. No error when connecting to version 5.1.54. When program runs over version 5.5.8, no exception occurs if connection pooling is off (connectionString += "Pooling=false;";).
[18 Feb 2011 20:04] Reggie Burnett
This is a server bug in 5.5.8 (and 5.5.9).  You can verify this by opening 2 command line clients connected to the same server and do the following:

1.  create a table (test) and insert a few rows.  
2.  On client A, do select * from test
3.  On client B, do kill query <id> where id is the process num of client A
4.  On client A, do insert into test values (newvalue)

You will see it report query execution interrupted which is wrong.
[25 Feb 2011 16:06] Andrey Belykh
Will this be fixed in next server version? Is there a corresponding server bug? Thanks.
[13 Aug 2011 19:35] Santo Leto
Hi Andrey,

The corresponding server bug is Bug #45679.
See also Bug #61501.