Bug #90695 lower_case_table_names option not working
Submitted: 30 Apr 2018 17:28 Modified: 11 Jul 2019 17:35
Reporter: Suresh Kumar Garlapati Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Installing Severity:S2 (Serious)
Version:8.0 OS:Ubuntu
Assigned to: Lars Tangvald CPU Architecture:Any
Tags: lower_case_table_names

[30 Apr 2018 17:28] Suresh Kumar Garlapati
Description:
Hi..

I am dealing with fresh database which I have installed.

To get the case insensitivity queries, I did the following:

Edited - /etc/mysql/my.cnf 

[mysqld]
lower_case_table_names=1

And then I have restarted the server, getting the following error:

sudo /etc/init.d/mysql restart

[....] Restarting mysql (via systemctl): mysql.serviceJob for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.
failed!

Any help appreciated. Thanks.

How to repeat:
1. Install MySQL 8.0
2. Add the following to /etc/mysql/my.cnf
[mysqld]
lower_case_table_names=1
3.Restart mysql
sudo /etc/init.d/mysql restart
4.Will get the error printed.
[30 Apr 2018 23:05] Miguel Solorzano
Thank you for the bug report. The issue you could check with error.log:

2018-04-30T22:39:59.404822Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.11) starting as process 7237
2018-04-30T22:39:59.709423Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2018-04-30T22:39:59.709630Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-04-30T22:39:59.709670Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-04-30T22:40:01.081186Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.11)  MySQL Community Server - GPL.
[1 May 2018 0:55] Suresh Kumar Garlapati
Yes, I saw the given log while I am debugging. Same approach I did for 5.7, which is working over there. This needs to be fixed right? Or Some other way I have to deal with it in 8.0?
[2 May 2018 7:29] Ståle Deraas
Posted by developer:
 
After initialization, is is not allowed to change this setting. https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_tab...
So "lower_case_table_names" needs to be set together with --initialize .
[2 May 2018 14:43] Suresh Kumar Garlapati
Thanks for quick reply.

But its not the same way which is working in 5.7.

We dont need to do like that.
[3 May 2018 11:32] Miguel Solorzano
On 8.0 is done by design and then !bug.
[9 May 2018 20:58] Miguel Solorzano
https://bugs.mysql.com/bug.php?id=90814 marked as duplicate of this one.
[2 Nov 2018 17:35] Donald Langhorne
While I can understand the concern for screwing things up by changing this mid-stream, simply preventing someone from changing it on an "out-of-the-box" install using a linux package manager(IE Apt) is really going too far.  You've essentially forced anyone who wants to install mysql 8 and use lower_case_table_names=1 to download source, compile and initialize the system themselves. 

In my opinion, if someone is stupid enough to change this setting on a running server with active databases, well... they get what they deserve, you shouldn't punish those of us who set it RIGHT after install before anything else has been done to the server.  

I tried re-running mysqld --initialize --lower-case-table-names=1 with no success.

I for one find this "protection" that has been added to be unecessary and annoying, PLEASE REMOVE IT!!!
[19 Dec 2018 10:21] Andrew Furniss
I agree with Donald here. Not being able to change this after initialization is such a pain. I'm using a MySQL AWS instance and have tried setting lower_case_table_names to 1 in the Parameter Group but it fails every time with the error "Different lower_case_table_names settings for server ('1') and data dictionary ('0').".

In AWS I don't have access to --initialize or and initialisation file so currently I am FORCED to have case-sensitive table names which means going through my whole code base and changing table names. Such fun.
[20 Dec 2018 13:18] Brian Sinicki
This really, *REALLY* sucks. Like. A. LOT. Is there a plan to fix this? How about some rock solid instructions to get this damn thing working on a Ubuntu 18 box? Does nobody else out there connect to a linux-based MySQL server from Windows machines?!!?!?
[20 Dec 2018 14:54] chris adamakis
I agree with Brian. This really, *REALLY* sucks. Like. A. LOT. Till the guys in Oracle fix this stupidity (if ever) my workaround  that works is the following
https://stackoverflow.com/questions/53103588/lower-case-table-names-1-on-ubuntu-18-04-does...
[12 Apr 2019 18:55] D B
I want to reiterate the importance of this problem. I can't migrate my AWS RDS instances because they all require this parameter setting. By design or not, it's a problem and should be fixed.
[13 Apr 2019 10:28] Brian Sinicki
I'm coming back here to bump this (again). The idiotic status of "Not of Bug" is just plain infuriating, so let's call this instead an "infinitely unhelpful feature in 8.0 that is designed specifically to make developers scream profanity into the dark and indifferent void." 

More precisely, this is becoming more and more of a pain in our collective arses. Anyone out there got some news about fixing this? Can Oracle at least send us the address of the person responsible so we can put burning bags of dog poo on their front porch as a tribute to their helpfulness?
[14 Apr 2019 4:47] Jesper wisborg Krogh
The key is to add lower_case_table_names = 1 to my.cnf before starting MySQL for the first time (or explicitly use --initialize).

I have written a blog with an example of doing this on Oracle Linux 7, RHEL 7, and CentOS 7 at https://mysql.wisborg.dk/2019/04/14/install-mysql-8-on-linux-with-lower_case_table_names-1... .

Hope that helps.
[14 Apr 2019 7:42] Brian Sinicki
Jesper wisborg Krogh, you are a gentleman and a scholar! Your blog post is pure gold. Many thanks for taking the time to do that, and for replying to us. This issue has been killing me for a while now!
[14 Apr 2019 17:46] Donald Langhorne
Jesper,

Your blog post is useful to help get people out of this mess but it doesn't answr the real problem.  I'll reitterate.  If you use Ubuntu and install mysql using th package manager (apt) it initializes the database as part of the install, so right out of the gate your locked out of being able to change this without going through a lot of hoops.  I was able to get mine changed back when I posted my original comment, but my complaint which I still have is that it is overly burdensome.  I am perfectly comfortable downloading from source and compiling on my own, but for a live server, i really don't like doing that as package managers are great at updates and managing dependancies.  I didn't look through your post in detail but I'm guessing that those package installs don't initialize the DB and leave that upto the user (I know some linux distros do that as I have encountered).  Ubuntu does init the DB for the user and while Ubuntu might not be for everyone you cannot ignore it, it's too popular to dismiss it imo.

There has to be a way to allow this.  Perhaps with a --force option or a --i-understand-this-risks option or even --im-a-dumbass-doing-this-to-a-server-with-data option.

I question why mysql would take this approach.  It would be akin to eliminating rm -r to allow deleting sub directories because someone might trash their system.  When you drop to the command line it comes with the responsibility that you can do damage if your not careful.  That has been the credo for linux from day one.

Thanks,

Don
[24 Apr 2019 7:06] Terje Røsten
Hi!

Will we try to add a flag that can be set *before* install of mysql-server deb package, when used lower_case_table_names will be added to default config.mysql
[24 Apr 2019 7:29] Lars Tangvald
The idea is to let the user use debconf-set-selections to enable the option
before running apt install.

This would be similar to how you can set the root password for a
non-interactive installation. This isn't really mentioned in our docs, which
we also need to fix.
[11 Jul 2019 17:35] Daniel Price
Posted by developer:
 
Fixed as of the upcoming 8.0.17 release, and here's the changelog entry:

In MySQL 8.0, the lower_case_table_names variable can only be configured
when the MySQL server is initialized. Because a MySQL server installation
on Debian and Ubuntu performed using APT initializes the MySQL server for
you, there was no opportunity to enable lower_case_table_names. To work
around this issue, you can now use the debconf-set-selection utility to
enable lower_case_table_names (set lower_case_table_names=1) prior to
installing MySQL using APT. 

To enable lower_case_table_names prior to installing MySQL using APT, execute the
following command: 

shell> sudo debconf-set-selections <<< "mysql-server mysql-server/lowercase-table-names select Enabled

Also updated (updated docs will appear with the 8.0.17 release)
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_tab...

Thank you for the bug report.
[18 Dec 2019 1:09] Shane Chambers
My vote is to fire the team who put this lower_case_table_names=1 by default into the mix.  You can't fix stupid.  These guys would put a toilet in the middle of a living room floor in front of the TV.