Can't set lower_case_table_names in MySQL 8.x on Windows 10

Question

In MySQL 8.0.12 running on Windows 10, it seems impossible to set lower_case_table_names to 2, so as to achieve the appearance of mixed case DB and table names in Workbench. I realize that under the hood these objects may remain lower case, which is fine. But I want it to look right in Workbench, and I could always achieve this in previous versions of MySQL. When I attempt to do that and restart the service so it takes effect, the service crashes and stops. In the mysql logs I see this:

Different lower_case_table_names settings for server ('2') and data dictionary ('1').

Data Dictionary initialization failed.

This seems to be a common problem for a lot of people.

I read here that the solution is:

So lower_case_table_names needs to be set together with --initialize.

But I have no idea what that means, or how to set it at startup. I have googled all over and read several forum articles but I can't find clear instructions on how to resolve this.

I assume you have studied dev.mysql.com/doc/refman/8.0/en/… ? — Rick James
What exact version of 8.0 are you using? Are you migrating data from an older version? Are you accessing the server only through Workbench? — Rick James
Rick: Yes, I'm familiar with that article, and I need option "2". When I entered that in the ini file and started the service, it crashed with the errors shown. I updated the version # in the original post. It's a fresh install, not an upgrade. — HerrimanCoder

HerrimanCoder HerrimanCoder · Accepted Answer · 2018-10-16T23:35:24

I figured it out. When installing MySQL Server 8.0.x, you have to check Show Advanced and Logging Options ...

...so that you can get to this screen:

Then you select Preserve Given Case, which is not the default. Hey MySQL developers: This setting should be on the main path, not buried in the optional advanced path.

Be aware that you might have to add lower_case_table_names=2 to my.ini, in case mysql removes it. And you may get a buggy, nonsensical warning as discussed here. I do wish mysql would fix their big obvious bugs that everyone runs in to.

===============================

Notes added on 9/25/2021:

This Preserve Given Case option can only be selected on first-time installation. You cannot go back and choose it later. Editing the config file will only cause errors. Attempting to upgrade or modify the install results in readonly options for this item.

If you already have MySQL installed without this desired option, you'll need to completely uninstall it and reinstall it.

As of MySQL 8.0.26, these steps are still valid.

===============================

Can't set lower_case_table_names in MySQL 8.x on Windows 10

2 Answers