Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Is utf8_general_ci not available anymore? Desperately need help!

birdAndStar's profile image birdAndStar posted 8 months ago in General Permalink

I have developed a Winforms application (with C#) using MariaDB 10.5.5 which having been running pretty well. I recently needed to duplicate this application on another computer. I installed the most recent MariaDB (version 10.6.3 for Windows 64 bit, which came with HeidiSQL version 11.3.0.6295) on the new computer. I checked the box of using UTF8 as the character set during the MariaDB installation process. I then successfully used HeidiSQL to duplicate the existing database onto the new machine.

However, when I started my Winforms application on the new machine, I got an exception when it tried to read something from the database. The exception happened at MySql.Data.MySqlClient.MySqlField.SetFieldEncoding() when it was getting the column data, and it complained that the given key was not present in the dictionary.

Googling shows that encoding has something to do with character set. I realized that all my database columns are using 'utf8mb3_general_ci' for collation in the new machine. My existing working system uses 'utf8_general_ci'. I tried to change the collation directly using HeidiSQL, but 'utf8_general_ci' is not even present in the dropdown of choices.

I tried to run SQL scripts using MariaDB's guide on how to change collations at different levels, database, table, column, and none of them had any effect.

Do you know what maybe the problem? I've spent days trying to figure this out but still have no clue.

Thank you so much!

ansgar's profile image ansgar posted 8 months ago Permalink

I remember utf8_general_ci was buggy, and replaced by utf8mb4_general_ci (not utf8mb3_general_ci by the way). Probably they removed the buggy one from the newer releases, not sure.

birdAndStar's profile image birdAndStar posted 8 months ago Permalink

Thank you so much. You gave me some clues. It seems utf8 is just an alias for utf8mb3, and utf8mb3 is deprecated in the newer MariaDB versions. So in HeidiSQL, I dropped my database, edited my localDBexport.sql file and changed everywhere it used to be utf8 to utf8mb4. Ran the sql file and got a new database. I checked and every table now has utf8mb4_general_ci as its collation. Strangely, when I ran my Winforms application, I got a new exception now that says "Character set 'utf8mb3' is not supported by .NET Framework". So something in my database is still holding on to the utf8mb3 stuff. The only level I haven't changed is the server. So I dropped the database again and stopped MariaDB service, edited C:\Program Files\MariaDB 10.6\data\my.ini file and used this character-set-server=utf8mb4 instead. Restart the MariaDB service and re-imported the database. Then everything worked! Wow! I'm so happy! Thank you so much for helping me out!!!

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.