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] MySQL Verification Team
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] MySQL Verification Team
On 8.0 is done by design and then !bug.
[9 May 2018 20:58] MySQL Verification Team
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.
[27 Mar 2020 14:37] Dalton Meira
After install mysql 8.0.19 I execute:

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

Obs: In the documentation is missing " in the end (https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_tab...)

But not changed. When I execute

mysql>  show variables like 'lower_case_%';

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)
[31 Jul 2020 13:42] Brian Sinicki
Have just tried to install version 8.0.21 on Ubuntu Server 20.04.3 and the debconf pre-installation configuration DOES NOT WORK.

Anyone else out there have news on this?
[31 Jul 2020 20:22] Lars Tangvald
Firstly, for Ubuntu 20.04, this functionality is not present in the packages in the Ubuntu archive (i.e. not from repo.mysql.com).

However, I see an oversight in the script as well; It will only check the debconf variable if the system does not see the installation as an upgrade, and that's only true if any old versions have been completely purged.
So if you e.g. installed an older version, removed it, deleted all files and reinstalled, it might still treat it as an upgrade and skip the logic.

A couple of workarounds for this:
Either
 - Delete all of /var/lib/mysql
 - Add the setting to /etc/mysql (we suggest putting custom config settings in a separate file in /etc/mysql/mysql.conf.d)
 - Restart the service. It should recreate a fresh database with the setting applies

Or:
 - Before installing mysql, create something like /etc/mysql/mysql.conf.d/lower.cnf and give it a [mysqld] section that just contains the lower-case-table-names value
 - Then install mysql
[5 Oct 2020 14:51] Donizete Gomes
SOLUÇÃO

consegui solucionar e setar o lower_case_table_names no linux mint, Mysql 8.0. Segue os passos abaixo:

Configurar a variável lower case table names
Após a instalação completa do mysql é necessário executar os comandos abaixo para configurar a variável lower_case_table_names = 1:

Passo 01
Fazer backup do mysql schema, caso possua

mysqldump -h localhost -u root -p mysql > /home/username/dumps/dump.sql
stop mysql

sudo service mysql stop

Passo 02
Mover ou remover /var/lib/mysql. Isto deletará todas as bases!

sudo mv /var/lib/mysql /tmp/mysql
ou
sudo rm -rf /var/lib/mysql

Passo 03
Criar um novo /var/lib/mysql e fazer um usuário mysql user owner

sudo mkdir /var/lib/mysql
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql

Passo 04
Alterar /etc/mysql/mysql.conf.d/mysqld.cnf e adicionar a linha após [mysqld]

lower_case_table_names=1

Passo 05
Inicializar o mysql usando o seguinte:

sudo systemctl start mysqld

Passo 06
Repetir os comandos:

sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 750 /var/lib/mysql
[5 Oct 2020 20:00] Donizete Gomes
In order to make this work in MySQL 8.0 and linux follow the steps bellow

0) Backup mysql schema before executing the following steps using

mysqldump  -h localhost -u root -p mysql > /home/username/dumps/mysqldump.sql
and then stop mysql using

sudo service mysql stop
1) move or remove /var/lib/mysql directory. This will delete all databases!!!!

mv /var/lib/mysql /tmp/mysql
2)Create a new /var/lib/mysql directory and make mysql user as owner

mkdir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql
3)edit /etc/mysql/mysql.conf.d/mysqld.cnf and add the following line after [mysqld]

lower_case_table_names=1
4) Initialize mysql using the following

sudo systemctl start mysqld

5) (Optional) Repeat

chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql
[7 Jan 2021 14:39] June sdfsd
This is a real showstopper, and has caused me a whole day of wasted time, and I still couldn't get MySql started. This is bad, real bad. The workaround suggested below to blow away the datastore, causes mroe problems, such as pipes being deleted, then not able to connect properly, with the service failing to start. It's a nightmare, and I feel like installing Postgres instead. You guys at oracle should've fixed this the day it was reported. Are you trying to saboutage your own product here?
[22 Jan 2021 9:06] Brian Sinicki
Just thought I'd come back in here and "poke" this bug again because I see that other folks are still having trouble with it. WFT Oracle? Why is the status on this bug "Closed"??? Fix it already!!
[6 Apr 2021 16:59] john danilson
This bug also affects rds creation using an xtrabackup for 8.x.  Like others have said we have no opportunity in rds land to do an initialization before creating the db from an s3 bucket.  Please fix this.
[6 Apr 2021 16:59] john danilson
This bug also affects rds creation using an xtrabackup for 8.x.  Like others have said we have no opportunity in rds land to do an initialization before creating the db from an s3 bucket.  Please fix this.
[23 Feb 2022 14:49] Jovan Sreckovic
Hello all,

Is this bug solved in any of the next releases Mysql 8.19-8.29?
[19 Apr 2022 18:48] dav wibli
I also had a problem when I wanted to harmonize the table names (for my site https://www.unecuisson.fr). But now I avoid, I put direct in lowercase.