Bug #77421 Connection Properties don't appear to reset due to pooling
Submitted: 21 Jun 2015 0:29 Modified: 4 Apr 2016 22:25
Reporter: Zackary Geers Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.9.6.0 OS:Microsoft Windows
Assigned to:

[21 Jun 2015 0:29] Zackary Geers
Description:
I was writing an app, some of the code using "SET autocommit=0;".  I always dispose of my connections and commands after I'm done with each query, I never use a connection for more then one command.  I make use of the built-in connection pooling

I found that some of my transactions weren't committing to the server, and after much debugging, I finally found out why.  It seems that if you set autocommit=0 in one connection, it will leak over into future connections.  This doesn't match how connection pooling works with System.Data.SqlClient.SqlConnection.

How to repeat:
    public class Program
    {
        public static void Main(string[] args)
        {
            Console.WriteLine("Starting...");

            ServicePointManager.DefaultConnectionLimit = 8;

            for (int i = 0; i < 50; i++)
            {
                using (var connection = CreateConnection())
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "select @@autocommit;";
                    Console.WriteLine("Expected to be 1: " + command.ExecuteScalar());
                }
            }

            for (int i = 0; i < 50; i++)
            {
                using (var connection = CreateConnection())
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "SET autocommit=0;";
                    command.ExecuteNonQuery();
                    Console.WriteLine("Doing something on a connection.  This should have no impact on future connections");
                }
            }

            for (int i = 0; i < 50; i++)
            {
                using (var connection = CreateConnection())
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = "select @@autocommit;";
                    Console.WriteLine("Expected to be 1: " + command.ExecuteScalar());
                }
            }

            Console.WriteLine("Test complete.  Expected is that commands run in previous connections have no impact on future connections.");
            Console.ReadKey();
        }

        private static MySqlConnection CreateConnection()
        {
            MySqlConnection retval;
            retval = new MySqlConnection("a valid connection string");
            retval.Open();
            return retval;
        }
    }

Suggested fix:
Connections should have all of their properties reset between commands, so you don't have settings changes leaking between connections.
[21 Jun 2015 0:47] Zackary Geers
Expected behavior:  Setting a connection-bound property shouldn't affect future connections.

Actual behavior:  Setting a connection-bound property affected a future connection.
[21 Jun 2015 0:57] Zackary Geers
select version() returns 5.6.22-log
[22 Jun 2015 9:28] Chiranjeevi Battula
Hello Zackary Geers,

Thank you for the bug report.
Verified this behavior on Console Application in Visual Studio 2013 (C#.Net) with  MySQL Connector/Net 6.9.6.

Thanks,
Chiranjeevi.
[22 Jun 2015 9:29] Chiranjeevi Battula
test results

Attachment: 77421.txt (text/plain), 6.27 KiB.

[4 Apr 2016 21:11] Bradley Grainger
This appears to be by design in the MySQL connector, according to the connection string documentation about connection pooling: https://dev.mysql.com/doc/connector-net/en/connector-net-connection-options.html#idm139937...

You should be able to work around the bug by explicitly specifying "ConnectionReset=false" when creating the connection string.
[4 Apr 2016 21:12] Bradley Grainger
Sorry; typo in last comment. I meant "ConnectionReset=true".

("ConnectionReset=false" reproduces the bug.)
[4 Apr 2016 22:13] Zackary Geers
I will switch my connection string setting.

Perhaps the default should be changed?  I would not expect state to be carried over between different Connection objects.  If you pull an entry out of the connection pool, you don't know what state it is in, it could be in any state, so you need to reset it.

Resetting the connection settings each time an connection is pulled out of the pool is automatically done in System.Data.SqlClient, so this is unlike how the other .Net data providers handle connection pooling.
[4 Apr 2016 22:25] Zackary Geers
Also, tracing down bugs caused by this issue are very tricky, another reason I think the default should be to reset between connections.

The issue I found was because one thread set the AutoCommit to 0, while the rest of the application used the default of autocommit to 1, in a multi-threaded app.  Some of the data wasn't getting updated in the database, and it took me a long time to figure out that it was set to autocommit 0, and longer to figure out that the pooling by default carries state over.