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Ã
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Ã