MySQL charset=UTF-8 with ConnectionReset=True is not working

Question

I've a connection string like below

"Server=localhost;Uid=root;Pwd='abcd';Database=testdb;charset=utf8;ConnectionReset=True;"

Any insertion to a database table column with unicode character is inserted as special characters. Everything works fine if i remove "ConnectionReset=True;" from my connection string. Any idea whats going on here?

Note: I believe my code is fine because insertion of unicode is fine when I remove ConnectionReset=True; part from the connection string.

Any idea whats going on here? No!... Where is the connection string defined is it a programming language? — Raymond Nijland, Nov 08 '17 at 17:20
You should include some C# code where you use this connection string.. i've already updated the question with the extra tags. — Raymond Nijland, Nov 08 '17 at 18:01
My issue is some conflict between charset and connectionreset properties — Manu Mohan, Nov 08 '17 at 18:08
"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." https://bugs.mysql.com/bug.php?id=85185 — Raymond Nijland, Nov 08 '17 at 18:10

score 5 · Accepted Answer · answered Nov 13 '17 at 16:47

You are encountering MySQL Bug #85185: "ConnectionReset pooling option does not preserve CharSet option value".

It's a good idea to use ConnectionReset=true; however, this has the side-effect of resetting the connection character set to the server default, which isn't what you want. I can think of two workarounds:

Use MySqlConnector, a replacement ADO.NET MySQL connector library that fixes this (and many other) Connector/NET bugs
Execute connection.ExecuteNonQuery("SET NAMES utf8mb4"); after each call to connection.Open(). This will fix the connection character set after a connection is reset when it's returned from the pool.

Finally, the MySQL utf8 character set is not actually UTF-8, so use utf8mb4 instead.

MySQL charset=UTF-8 with ConnectionReset=True is not working

1 Answers1

Linked