Bug #96843 MySQL Server failed to restart after set default_collation_for_utf8mb4
Submitted: 12 Sep 2019 3:02 Modified: 12 Sep 2019 8:18
Reporter: Efrey Kong Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Workbench Severity:S1 (Critical)
Version:8.0.17.0 OS:Windows (10 Enterprise 1803)
Assigned to: CPU Architecture:Any
Tags: default_collation_for_utf8mb4

[12 Sep 2019 3:02] Efrey Kong 
Description:
I've installed MySQL 5.5 previously, and recently I've uninstalled MySQL 5.5 and Installed MySQL Community 8.0.17.0 (Custom mode, MySQL server & Workbench selected).

After that, I've enabled default_collation_for_utf8mb4changed for MySQL (via Workbench > Options File > General > International > default_collation_for_utf8mb4), then I've restarted MySQL server service via Services management windows. but MySQL failed to start.

I've checked C:\ProgramData\MySQL\MySQL Server 8.0\Data folder, and there is a error file "EKSTUDIO.err" (My PC's name is EKSTUDIO), and there are some error message in it: "[ERROR] [MY-000067] [Server] unknown variable 'default_collation_for_utf8mb4=utf8mb4_0900_ai_ci'."

Temporary fix:
Open my.ini file, find and remove `default_collation_for_utf8mb4=utf8mb4_0900_ai_ci`, and then save the file.
restart MySQL service successfully.

How to repeat:
This issue has also been repeated on another my computer. Here is the step to reproduce:

1. Open MySQL Workbench (8.0.17)
2. Swicth to `Administration` tab, and click `Options File`
3. Scroll down to the International group, check on the `default_collation_for_utf8mb4` option, keep the default value `utf8mb4_0900_ai_ci`. then click `Apply...` button to take effective.

4. Open Windows Services management windows, go to MySQL service, and choose to restart. then an error message will be popped up.
[12 Sep 2019 8:18] MySQL Verification Team 
Hello Efrey Kong,

Thank you for the report and feedback.
Observed this on Win10 with installer 1.4.31.

regards,
Umesh
[12 May 2022 1:56] Jose Ramirez Ruiz 
Posted by developer:
 
Setting the default_collation_for_utf8mb4 variable is not allowed using a configuration file (E.g. my.ini),
hence the error when restarting the service is expected. Optionally you can set it by running a SET command such as "SET default_collation_for_utf8mb4 = utf8mb4_0900_ai_ci". Note that you will get a warning since the variable is intended for internal use only as indicated in the official documentation.

MySQL Workbench should not allow to add the value into the configuration file because it is not supported. Transferring bug to Workbench team.
[19 May 2022 22:19] Jose Ramirez Ruiz 
Sending to correct product.
Workbench should not be allowing to add the mentioned variable into the my.ini file since said variable is not meant to be updated there.