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 126.96.36.19995) 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!