About corrupted characters after changing collation and enabling MySQL Full Unicode Support (utf8mb4) in Control Panel >> Paths & Database
Example, Apostrophes becomes "’"
Is there a way to fix this? it seems all older posts then March 14th has "’" and new posts are correct.
I know there are more types of characters that are corrupted also but not how many kinds yet.
Two options are:
A) restore your the backup you made and retain your older database collation.
As written, the transition tool only converts the table collation. The data was not converted. This means that all characters going forward will be stored in the new character set. All prior data containing extended characters are from your previous character collation, and will remain as such.
or
B) find/replace and/or... continue forward with UTF8, knowing that all of your new posts from the day you choose to convert would be the first day of using modern database encoding standards with your character set.
Defaults in MySQL 8.0Old Default: latin1
New Default: utf8mb4
The default value of the character_set_server system variable and command line option –character-set-server changes from latin1 to utf8mb4. This is the server’s default character set. UTF8MB4 is the dominating character encoding for the web, and this change will make life easier for the vast majority of MySQL users. The upgrade from 5.7 to 8.0 will not change any character set for any existing database objects. But unless you specify character_set_server back to your previous default or explicitly set the character set then a new schema/table/column will by default be in utf8mb4. We recommend users to move to utf8mb4 whenever possible.
Source:
https://mysqlserverteam.com/new-defaults-in-mysql-8-0/Further reading:
Debugging Character Set Issues by Example
http://mysqlserverteam.com/debugging-character-set-issues-by-example/
