8

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.

Tiago Martins Peres 李大仁
  • 9,694
  • 13
  • 50
  • 86
HerrimanCoder
  • 5,858
  • 20
  • 65
  • 111
  • I assume you have studied https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html ? – Rick James Oct 11 '18 at 23:50
  • 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 Oct 11 '18 at 23:52
  • 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 Oct 12 '18 at 17:27

2 Answers2

15

I figured it out. When installing MySQL Server 8.0.x, you have to select Advanced Options so that you can get to this screen:

enter image description here

Then you select Preserve Given Case, which is not the default. It's ludicrous that MySQL hides and buries this setting in obscurity, and then provides no possible way post-installation to change it. They need to fix this!!

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.

HerrimanCoder
  • 5,858
  • 20
  • 65
  • 111
  • File a bug report at bugs.mysql.com – Rick James Oct 20 '18 at 02:05
  • 2
    It looks like selecting "Advanced" does not always show these options on the page above. – Razor_alpha Dec 06 '18 at 19:18
  • 1
    @Razor_alpha the option to view "Advanced" happens when you check a box during the "Type and Networking" wizard. it's not obvious, but at the bottom of that wizard, there's a checkbox that states "Show Advanced Options and Logging". – Napoli Oct 22 '19 at 16:05
  • @Napoli Thank you. Yes, even with advanced option I had troubles if the previous installation was not properly removed. Luckily I managed to remove the folders and the registry entries manually. – Razor_alpha Oct 22 '19 at 21:51
6

8.0.11 changelog:

It is now prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared. (Bug #27309094, Bug #89035)

See also: https://bugs.mysql.com/bug.php?id=89035

By --initialized, it is referring the initial install of 8.0. How was that done?

Rick James
  • 106,233
  • 9
  • 103
  • 171
  • If I need to reinstall it, fine, but how should I install it differently this time? I mainly just allowed all the defaults and I don't remember it asking me about casing of objects. Also, I've never heard the term "initialized" as being equivalent to "software installed". Wouldn't initialized imply "on startup" rather than "on installation"? – HerrimanCoder Oct 12 '18 at 16:23
  • @HerrimanCoder - I don't know. Dig through the initialization script. – Rick James Oct 12 '18 at 18:33
  • 1
    What is the "initialization script", and where is it? I need a little more info, please. I have read the bug report referenced above, it didn't help much. – HerrimanCoder Oct 15 '18 at 15:23