Bug #85185 ConnectionReset pooling option does not preserve CharSet option value
Submitted: 26 Feb 2017 10:11 Modified: 27 Feb 2017 7:06
Reporter: Alberto Pastore Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.9.9 OS:Debian
Assigned to: CPU Architecture:Any

[26 Feb 2017 10:11] Alberto Pastore
Description:
It seems that the .NET connector is resetting CharSet option to its default settings when reacquiring a connection from a pool with ConnectionReset=true.

I'm working with utf-8 and I have CharSet=utf8 in my connection string. When I reuse a connection by reopnening it with Pooling=true and ConnectionReset=true, I get bad utf-8 characters in my database tables.

MySQL server version is 5.5.53 on Debian wheezy.
Client Connector / NET is 6.9.9 running on Mono (Debian Wheezy and Mac OS Sierra).

The database has a default charater set "utf8" and "ut8_general_ci" collation.

When ConnectionReset=false, the issue is not occurring.

How to repeat:
Try to run the following c# console code (adjust connection string accordingly):

public static void Main(string[] args)
{
	const string connstring = "HOST=localhost;" +
		"USERNAME=xxx;" +
		"PASSWORD=yyy;" +
		"DATABASE=testdb;" +
		"ALLOWBATCH=true;" +
		"ALLOWUSERVARIABLES=true;" +
		"CHARSET=utf8;" +
		"CONNECTIONTIMEOUT=15;" +
		"CONVERTZERODATETIME=true;" +
		"DEFAULTCOMMANDTIMEOUT=10;" +
		"POOLING=true;" +
		"CACHESERVERPROPERTIES=true;" +
		"MAXIMUMPOOLSIZE=16384;"
		"CONNECTIONRESET=true";

	MySqlConnection conn;
	MySqlCommand cmd;

	// first round: create table & first insert

	conn = new MySqlConnection(connstring);
	conn.Open();
	cmd = conn.CreateCommand();

	cmd.CommandText = "DROP TABLE IF EXISTS test; CREATE TABLE test(id int(11) NOT NULL AUTO_INCREMENT, v varchar(128) NOT NULL, PRIMARY KEY(id)) ENGINE = InnoDB DEFAULT CHARSET = utf8";
	cmd.ExecuteNonQuery();

	cmd.CommandText = "INSERT INTO test (v) VALUES ('UTF8 first insert: Modalità')";
	cmd.ExecuteNonQuery();

	conn.Close();

	// second round: second insert
	conn = new MySqlConnection(connstring);
	conn.Open();
	cmd = conn.CreateCommand();

	cmd.CommandText = "INSERT INTO test (v) VALUES ('UTF8 second insert: Modalità')";
	cmd.ExecuteNonQuery();

	conn.Close();

	// third round: read & print previously inserted rows

	conn = new MySqlConnection(connstring);
	conn.Open();
	cmd = conn.CreateCommand();

	cmd.CommandText = "SELECT id, v FROM test";
	MySqlDataReader reader = cmd.ExecuteReader();

	while (reader.Read())
	{
		Console.WriteLine("id:{0} v:{1}", reader["id"], reader["v"]);
	}

	conn.Close();
}

This is the correct expected output (which I get when ConnectionReset = false) :

id:1 v:UTF8 first insert: Modalità
id:2 v:UTF8 second insert: Modalità

This is the output I get when ConnectionReset = true:

id:1 v:UTF8 first insert: Modalità
id:2 v:UTF8 second insert: ModalitÃ
[26 Feb 2017 10:21] Alberto Pastore
Sorry guys, I forgot a "+" sign after the line

"MAXIMUMPOOLSIZE=16384;"

when pasting the sample code, the line should read

"MAXIMUMPOOLSIZE=16384;" +

to compile correctly
[27 Feb 2017 7:06] Chiranjeevi Battula
Hello  Alberto Pastore,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.9 version.

Thanks,
Chiranjeevi.
[27 Feb 2017 7:07] Chiranjeevi Battula
ConnectionReset = false

id:1 v:UTF8 first insert: Modalità
id:2 v:UTF8 second insert: Modalità

ConnectionReset = true 
id:1 v:UTF8 first insert: Modalità
id:2 v:UTF8 second insert: ModalitA 
[16 Mar 2017 22:04] Bradley Grainger
I ran into this when implementing the MySqlConnector ADO.NET connector: https://github.com/mysql-net/MySqlConnector/issues/66

It's not explicitly specified in the MySQL documentation that resetting the connection charset is a side effect of resetting session variables, but since it seems to be by design, I didn't bother filing a bug against MySQL Server itself; see https://github.com/mysql-net/MySqlConnector/pull/65#issuecomment-249451909

You might be able to work around this by switching to MySqlConnector (https://www.nuget.org/packages/MySqlConnector) but we don't yet support all the connection string options you're using (see https://github.com/mysql-net/MySqlConnector/issues/105) so it might not be a viable option for you yet.