MySQL Bugs: #47153: Connector/NET fails to reset connection when encoding has changed

Bug #47153	Connector/NET fails to reset connection when encoding has changed
Submitted:	6 Sep 2009 4:18	Modified:	28 Jun 2010 14:32
Reporter:	Bassam Tabbara	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.2	OS:	Any
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
When "Connection Reset=true" is specified on the connection string I observe the following behavior:

- A connection is picked up from the pool to execute a SELECT command
- If the last column in the SELECT list is of COLLATION unicode then the connection (more specifically the stream and packet) remain set Unicode encoding.
- Another connection.Open happens and Reset is called
- Reset tries to invoke COM_CHANGE_USER and writes the strings in unicode
- The server returns 1047 - command unknown

How to repeat:
Set "Connection Reset=true"
Run a SELECT with last column selected in unicode collation
Run another command

Suggested fix:
I've patched NativeDriver.cs as follows:

        public override void Reset()
        {
            stream.Encoding = this.encoding;  // New code
            stream.SequenceByte = 0;
            packet.Clear();
            packet.WriteByte((byte)DBCmd.CHANGE_USER);
            Authenticate();
        }

Note that the connection's encoding is reset as well.

This seems to fix the problem for me.

Hi Bassam and thanks for your report.

Would you mind attaching complete test case (including DDL/DML statements) that shows this problem every time it is run? I would also like to see output of "SHOW VARIABLES LIKE '%charset%'", "SHOW CREATE DATABASE 'your_failing_database'" and "SHOW CREATE TABLE 'your_failing_table'" commands.

Here's a repro on 5.2.7:

DDL/DML:

CREATE DATABASE `Test` DEFAULT CHARACTER SET ucs2 COLLATE ucs2_bin;

CREATE TABLE T
(
  `A` int NOT NULL,
  `B` varchar(100) COLLATE ucs2_bin DEFAULT NULL,
  PRIMARY KEY (`A`)
);

INSERT T VALUES(1, 'Hello');
INSERT T VALUES(2, 'World');

Code:
using System;
using MySql.Data.MySqlClient;

namespace MySqlBug
{
    class Program
    {
        static string connectionString = "server=dev;database=Test;user id=root;password=;Connection Reset=true;";

        static void Main(string[] args)
        {
            ConnectionReset();
        }

        static void ConnectionReset()
        {
            using (MySqlConnection cn = new MySqlConnection(connectionString))
            {
                cn.Open();

                using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM T", cn))
                {
                    using (MySqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                        }
                    }
                }
            }

            using (MySqlConnection cn = new MySqlConnection(connectionString))
            {
                cn.Open();

                using (MySqlCommand cmd = new MySqlCommand("SELECT 1", cn))
                {
                    using (MySqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while (rdr.Read())
                        {
                        }
                    }
                }
            }
        }
    }
}

Exception thrown:

MySql.Data.MySqlClient.MySqlException was unhandled
  Message="⌰㡓〱啮歮潷渠捯浭慮�"
  Source="MySql.Data"
  ErrorCode=-2147467259
  Number=1047
  StackTrace:
       at MySql.Data.MySqlClient.MySqlStream.OpenPacket() in C:\OldLaptop\Sources\mysqlconnector-bazaar\5.2\MySql.Data\Provider\Source\MySqlStream.cs:line 177
       at MySql.Data.MySqlClient.NativeDriver.Authenticate411() in C:\OldLaptop\Sources\mysqlconnector-bazaar\5.2\MySql.Data\Provider\Source\NativeDriver.cs:line 408
       at MySql.Data.MySqlClient.NativeDriver.Authenticate() in C:\OldLaptop\Sources\mysqlconnector-bazaar\5.2\MySql.Data\Provider\Source\NativeDriver.cs:line 438
       at MySql.Data.MySqlClient.NativeDriver.Reset() in C:\OldLaptop\Sources\mysqlconnector-bazaar\5.2\MySql.Data\Provider\Source\NativeDriver.cs:line 450
       at MySql.Data.MySqlClient.MySqlPool.GetPooledConnection() in C:\OldLaptop\Sources\mysqlconnector-bazaar\5.2\MySql.Data\Provider\Source\MySqlPool.cs:line 131
       at MySql.Data.MySqlClient.MySqlPool.TryToGetDriver() in C:\OldLaptop\Sources\mysqlconnector-bazaar\5.2\MySql.Data\Provider\Source\MySqlPool.cs:line 226
       at MySql.Data.MySqlClient.MySqlPool.GetConnection() in C:\OldLaptop\Sources\mysqlconnector-bazaar\5.2\MySql.Data\Provider\Source\MySqlPool.cs:line 237
       at MySql.Data.MySqlClient.MySqlConnection.Open() in C:\OldLaptop\Sources\mysqlconnector-bazaar\5.2\MySql.Data\Provider\Source\Connection.cs:line 496
       at MySqlBug.Program.ConnectionReset() in C:\Users\bassam\Documents\Visual Studio 2008\Projects\MySqlBug\MySqlBug\Program.cs:line 47
       at MySqlBug.Program.Main(String[] args) in C:\Users\bassam\Documents\Visual Studio 2008\Projects\MySqlBug\MySqlBug\Program.cs:line 12
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

My suggested fix works fine when added.

SHOW VARIABLES LIKE '%cha%';

'character_set_client', 'utf8'
'character_set_connection', 'utf8'
'character_set_database', 'ucs2'
'character_set_filesystem', 'binary'
'character_set_results', 'utf8'
'character_set_server', 'latin1'
'character_set_system', 'utf8'
'character_sets_dir', '/usr/share/mysql/charsets/'
'innodb_change_buffering', 'inserts'

Verified just as described with test case provided by Bassam. The problem is repeatable with 5.2 branch while 6.0 works as expected (ie. no error) which makes pretty acceptable workaround, upgrade to c/NET 6.

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/88029

714 Reggie Burnett	2009-10-23
      - fixed connection reset that was attempting to use the last encoding to do the authentication.  This
        would fail, for example, if UCS2 was used to read the last column before reset (bug #47153)

Fixed in 5.2.8.  This is not a bug in any of the 6.0 releases

An entry has been added to the 5.2.8 changelog:

When the connection string option “Connection Reset = True” was used, a connection reset used the previously used encoding for the subsequent authentication operation. This failed, for example, if UCS2 was used to read the last column before the reset.

This happens as described on 6.2 builds as well. The same patch seems to fix it.

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/112254

820 Reggie Burnett	2010-06-25
      - applied patch from 5.x series that reset the connection to the base encoding when doing a 
        connection reset (bug #47153)

Fixed in 6.0.7, 6.1.5, 6.2.4, and 6.3.3+

Changelog entry updated to include versions 6.0.7, 6.1.5, 6.2.4, and 6.3.3.