MySQL Parameterized Query C# - Incorrect string value: '\xF0' for column ''

Question

I'm using MySql.Data MySqlClient to run an UPDATE query on a MySQL 8 server in C#. I'm using a patameterised query, and trying to add a non BMP (supplemental) unicode emoji to my table value (this issue also occurs with some Chinese characters).

MySql.Data.MySqlClient.MySqlCommand query = _connection.CreateCommand();
query.CommandText = "UPDATE favorites SET fruit = @favFruit WHERE id = 1";                

MySql.Data.MySqlClient.MySqlParameter parameter = query.CreateParameter();

parameter.ParameterName = "favFruit";
parameter.Value = "";

query.Parameters.Add(parameter);

query.ExecuteNonQuery();

My query fails with Incorrect string value: '\xF0' for column 'fruit' at row 1

I have spent the whole day searching for the solution to my issue. I have tried:

• Adding CHARSET=utf8mb4; to mt connection string which is important and required
• Made sure my Database, Table and Column are using utf8mb4 charset and utf8mb4_0900_ai_ci collation, which is important and required - as utf8 is actually shorthand for "Part of Unicode" in MySQL
• Tried calling SET NAMES utf8mb4, which didn't do anything, because the server collations were all correctly set to utf8mb4 already, as evidenced by SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; - See MySQL utf8mb4, Errors when saving Emojis
• Set all the recommended defaults in my.ini as recommended around the web.

But it still doesn't work! I don't know what else to do!

Answer 1

After wasting a whole day, it turned out that the default parameter type in MySql.Data MySqlClient is Blob. When converting this value during query execution, something along the way doesn't convert the Unicode properly.

Adding parameter.MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString; fixed everything.

i.e.

MySql.Data.MySqlClient.MySqlParameter parameter = query.CreateParameter();
parameter.MySqlDbType = MySql.Data.MySqlClient.MySqlDbType.VarString;
parameter.ParameterName = "favFruit";
parameter.Value = ""