MySql Exception: variable 'character_set_client' cannot be set to 'utf16'

Question

I used to use SQL Server 2005 as the database of my site, and everything works fine. Now I switched to the 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 arise when I declare a connection to the 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(); 

The link to MySql.Data is already installed. Here I received this error message:

Exception Details: MySql.Data.MySqlClient.MySqlException: The variable 'character_set_client' cannot set the value to 'utf16' An error message indicates that this error occurs during connection. Open ();

When I continue to refresh the error page, I sometimes get another error. there he is:

Exception Details: MySql.Data.MySqlClient.MySqlException: expected end of the data packet An error message indicates that this error occurred during Mydtr = cmdselect.ExecuteReader ();

I am new to mysql. I do not know what's the problem. I assume this problem is related to database setup or data, and not to my source code.

Does anyone know a solution? Your help is greatly appreciated. I tried for 4 days, but cannot decide.

Answer 1

solved !! Thanks to @Etienne Rached and @jeremi

all problems arise from the character set task.

: Download navicat to change the character set for the database and each individual table.

There are 2 places to check:

1) right click on the database, for example. MyDB. Then select properties and set the character set

2) right-click on the table and select the design table. click each line to change the character set and finally go to the "Variant" tab and change the character set.

For your information: this is a very rare case. I google almost can't find a solution. I created this error during the installation of Mysql, I chose the format utf16> <

By the way, a simple connection string will work. as

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

Answer 2

Hello to add to the above points.

As with MySQL 5.5, it does not support utf16 or utf32 character encoding.

There are a few things to look for from

• Client connecting to DB
• DB itself

On the client side, it calls a query to the database with the request encoded in a specific format, and the intact server must understand these requests and respond in a specific format, which the client can understand in turn.

Point 1:. Therefore, we need to set the encoding at the client level, in the connection string, as

 Charset=utf8; 

And in turn, you need to check with DB and the table (in which you are looking for a query transaction) charset and collation

There are four levels of encoding encoding and matching on the MySQL server for flexibility

• Charset for the whole server server_character_set
• Charset for database
• Codes for the table
• Shell for table columns also

you can check these values ​​using the following query

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

So, the point is the DB server that reads these queries in the encoding specified on the connection string.

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

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

There are 3 server parameters that are significant in specific problems with the encoding from client to server character_set_client - The server sets the specified encoding for the client - requests sent to character_set_connection - The server sets the specified encoding for the character_set_results connection transaction - The server sets the specified encoding for the returned results, error messages from DB to the client

Point 3 Instead of points 1 and 2, you can set these variables in the database to ensure proper client server transaction

Basically, the encoding and type of client-database matching (server, db table, column) should be similar in order to avoid data loss during database transactions.

Hope this helps .....

Answer 3

You may be trying to talk to utf16, but the database only supports utf8 or some others.

Try adding this to the 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 this does not work, try ASCII or latin1

Answer 4

This error "Exception Details: System.Collections.Generic.KeyNotFoundException:" may occur if you did not update your MySql client library after updating the connection character set to utf8mb4

Update the MySql.data.dll library version to the latest version 6.6.5.0

Hope this should solve the key exception not found.