MySql's Exception : Variable 'character_set_client' can't be set to the value of 'utf16'

Question

Previously I use sql server 2005 as my website database, and everything works well. now I have changed to MySql server 5.5 database because it is open source.

I used Navicat Premium to transfer my data from sql server to mysql. I use mysql workbench and navicat to manage my database. Problems come when i declare the connection to mysql database. here is my code:

    MySqlCommand cmdselect;
    MySqlConnection conNDB;
    MySqlDataReader Mydtr;
    string server = "localhost";
    string database = "maindb";
    string uid = "root";
    string password = "abc123";
    string strCon = "SERVER=" + server + ";" + "DATABASE=" +
    database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
    string strSelect = "SELECT * FROM announcement"; 

    conNDB = new MySqlConnection(strCon);
    conNDB.Open();
    cmdselect = new MySqlCommand(strSelect, conNDB);
    Mydtr = cmdselect.ExecuteReader();

    rptAnnounce.DataSource = Mydtr;
    rptAnnounce.DataBind();

    Mydtr.Close();
    conNDB.Close();

Reference to MySql.Data already set. Here i got this error message :

Exception Details: MySql.Data.MySqlClient.MySqlException: Variable 'character_set_client' can't be set to the value of 'utf16' Error message stated this error occurs during connection.Open();

When i keep on refreshing the error page, i got another error sometime. here is it:

Exception Details: MySql.Data.MySqlClient.MySqlException: Expected end of data packet Error message stated this error occurs during Mydtr = cmdselect.ExecuteReader();

I am new to mysql. i don know what problem is this. i guess this problem comes from database's setting or data, not my source code.

anyone knows the solution? your help is greatly appreciated. i been trying for 4 days but cannot solve.

Solution 1

solved!! Thanks to @Etienne Rached and @jeremi

all the problems come from the character set problem.

solution: download navicat, to change the character set for database and every single table.

there are 2 places you need to check:

1) right-click on database eg. myDb. Then select properties and set the character set

2) right-click on a table and select design table. click every single row to change character set and finally go to "Option" tab and change the character set.

For your info: this is very rare case. I google it almost cannot find the solution. i created this mistake during installation of Mysql, I chose utf16 format ><

by the way, simple connectionstring will work. like

"server=localhost;database=maindb;uid=root;pwd=abc123;CharSet=utf8; port=3306";

Solution 2

Hi to add up to the above points.

As of MySQL 5.5 version, it doesn't support utf16 or utf32 character set encoding.

There are couple of things that need to be looked from both

1. Client making the connection to DB
2. DB itself

At client side, it invokes the query to DB with a query encoded in particular format and the server intact have to understand these queries and respond in particular format, that the client can understand in turn.

Point 1: So we need to set the charset at client level, in connection string as

Charset=utf8;

And in turn you have to check with DB and table (to which you are seeking a query transaction) charset and collation

There are four level of encoding charset and collation at MySQL server for flexibility

1. Charset for whole server server_character_set
2. Charset for Database
3. Charset for Table
4. Charset for Table columns as well

you can check these values using following query

show variables like 'character%';
show variables like '%collation%';

So, the point is DB server read these queries in the encoding specified in connection string.

Point 2 Instead of specifying the character set at connection string, you can also set the charset for client (in which the server interrupts ) at query after opening up connection

set names 'charset';
Ex: set names 'utf8';

There are the 3 server parameters significant in charset specific issues from client to server character_set_client - Server sets the specified charset for client - queries passed to character_set_connection - Server sets the specified charset for connection transaction character_set_results - Server sets the specified charset for returned results, error messages from DB to client

Point 3 Instead of Point 1 and 2, you can set these variables at DB to ensure proper client server transaction

Basically, the client and DB(server, db table, column) encoding and collation type must be similar to avoid data losses during DB transactions.

Hope this helps.....

Solution 3

Could be that you are trying to talk with utf16 but the database only sopports utf8 or some other.

Try adding this to your connection string:

Character Set=utf8

My connection string looks like this:

"server=" + settings.DatabaseHost + 
";User Id=" + settings.DatabaseUsername +
";password="+ settings.DatabasePassword +
";database="+ settings.DatabaseName+
";Persist Security Info=True" +
";Connect Timeout=5;Default Command Timeout=10" +
";Character Set=utf8";

If that does't work, try ASCII or latin1

Solution 4

This error "Exception Details: System.Collections.Generic.KeyNotFoundException:" may occur if you have not upgraded your MySql client library after upgrading your connection string character set to utf8mb4

Please update the MySql.data.dll library version to latest 6.6.5.0

Hope this should solve key not found exception.

View more solutions

Comments

[Loonb]

Thanks for the help..I have tried it, but i get another error. i have googled it. but cannot find any solution to this : Exception Details: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.