In MySQL UTF-8 does not actually mean UTF-8. If you’re curious to see why, there is an excellent post by Mathias explaining in detail why you should choose utf8mb4 over utf8.

While Mathias nicely covers the upgrade and configuration for MySQL 5.5.3+ on a Unix-Like system, I needed to configure MySQL 8.0.15 on Windows. Now, I won’t tell the whole story of how I finally ended up with this configuration, leaving this just here for my future self.

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

To check the server settings after changes are applied and the MySQL service restarted, I executed…

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

…with the result being…

Variable_name Value
character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8mb4
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8mb4
character_set_system utf8
collation_connection utf8mb4_0900_ai_ci
collation_database utf8mb4_unicode_ci
collation_server utf8mb4_unicode_ci

…alright except for the collation_connection parameter. Now, I am stil unsure, whether that can be set as server variable or not. Trying to set in in my.ini.

[mysqld]
collation-connection=utf8mb4_unicode_ci

The service no longer started. ☹

2019-08-06T15:02:14.009170Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.15)  MySQL Community Server - GPL.
2019-08-06T15:02:40.403776Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2019-08-06T15:02:40.406958Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.15) starting as process 26140
2019-08-06T15:02:47.154232Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2019-08-06T15:02:47.165349Z 0 [ERROR] [MY-000067] [Server] unknown variable 'collation-connection=utf8mb4_unicode_ci'.
2019-08-06T15:02:47.165375Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2019-08-06T15:02:47.181314Z 0 [ERROR] [MY-010119] [Server] Aborting

For right now, I am happy with the setup and will leave it there. If you have any clue about the collation-connection, I am more than happy to hear about it.

Resources

Options