lower_case_table_names=1 on Ubuntu 18.04 doesn't let mysql to start

Question

I have installed mysql community server 8.013 on ubuntu 18.04 and I have the following issue. I want to set lower_case_table_names=1 in order to have case insensitive table names in my db. I edited /etc/mysql/mysql.conf.d/mysqld.cnf and added the following line under [mysqld]

lower_case_table_names=1

mysqld.cnf now is as follows

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log
lower_case_table_names=1

I stopped the mysql server using

sudo service mysql stop

Then I tried to start it again using

sudo service mysql start

but I get the following error

Job for mysql.service failed because the control process exited with error code.
See "systemctl status mysql.service" and "journalctl -xe" for details.

I tried

systemctl status mysql.service

and I got the following info

christoph@christoph-Latitude-E6530:/etc/init.d$ systemctl status mysql.service
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: exit-code) since Thu 2018-11-01 16:38:14 EET; 24s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 6681 ExecStart=/usr/sbin/mysqld (code=exited, status=1/FAILURE)
  Process: 6642 ExecStartPre=/usr/share/mysql-8.0/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
 Main PID: 6681 (code=exited, status=1/FAILURE)
   Status: "SERVER_BOOTING"
    Error: 2 (No such file or directory)

Νοε 01 16:38:12 christoph-Latitude-E6530 systemd[1]: Starting MySQL Community Server...
Νοε 01 16:38:14 christoph-Latitude-E6530 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Νοε 01 16:38:14 christoph-Latitude-E6530 systemd[1]: mysql.service: Failed with result 'exit-code'.
Νοε 01 16:38:14 christoph-Latitude-E6530 systemd[1]: Failed to start MySQL Community Server.

using journalctl -xe I got the following info

-- The result is RESULT.
Νοε 01 17:32:00 christoph-Latitude-E6530 sudo[2526]: pam_unix(sudo:session): session closed for user root
Νοε 01 17:32:04 christoph-Latitude-E6530 wpa_supplicant[743]: wlp3s0: WPA: Group rekeying completed with 4c:5e:0c:7a:95:cf [GTK=CCMP]
Νοε 01 17:32:08 christoph-Latitude-E6530 gnome-shell[1565]: Some code accessed the property 'discreteGpuAvailable' on the module 'appDisplay'. That property w
Νοε 01 17:32:08 christoph-Latitude-E6530 gnome-shell[1565]: Some code accessed the property 'WindowPreviewMenuItem' on the module 'windowPreview'. That proper
lines 1349-1371/1371 (END)
Νοε 01 17:31:58 christoph-Latitude-E6530 systemd[1]: Starting MySQL Community Server...
-- Subject: Unit mysql.service has begun start-up
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has begun starting up.
Νοε 01 17:31:58 christoph-Latitude-E6530 audit[2593]: AVC apparmor="STATUS" operation="profile_replace" info="same as current profile, skipping" profile="unconfined" name="/usr/sbin/mysqld" pid=2593 comm=
Νοε 01 17:31:58 christoph-Latitude-E6530 kernel: kauditd_printk_skb: 28 callbacks suppressed
Νοε 01 17:31:58 christoph-Latitude-E6530 kernel: audit: type=1400 audit(1541086318.959:39): apparmor="STATUS" operation="profile_replace" info="same as current profile, skipping" profile="unconfined" name
Νοε 01 17:32:00 christoph-Latitude-E6530 systemd[1]: mysql.service: Main process exited, code=exited, status=1/FAILURE
Νοε 01 17:32:00 christoph-Latitude-E6530 systemd[1]: mysql.service: Failed with result 'exit-code'.
Νοε 01 17:32:00 christoph-Latitude-E6530 systemd[1]: Failed to start MySQL Community Server.
-- Subject: Unit mysql.service has failed
-- Defined-By: systemd
-- Support: http://www.ubuntu.com/support
-- 
-- Unit mysql.service has failed.

I also tried to start the server using

sudo service mysql start --initialize lower_case_table_names=1

as described here lower_case_table_names Settings in MySQL 8.0.12 but I still get the same error and mysql doesn't start at all

If I run the following

sudo vi /var/log/mysql/error.log

I get the following cause of the error

Different lower_case_table_names settings for server ('1') and data dictionary ('0').
Data Dictionary initialization failed.

Any ideas why this is happening and how to fix it?

Can you put spaces on either side of `=` in the config file, where you declare `lower_case_table_names` — Madhur Bhaiya, Nov 01 '18 at 16:28
No it didn't work. If I use lower_case_table_names = 2 it starts, but with lower_case_table_names = 1 , it never starts — Christoph Adamakis, Nov 02 '18 at 11:13
Maybe reading this doc link may help: https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html Basically, having this variable as `1` is not recommended for case-sensitive OS — Madhur Bhaiya, Nov 02 '18 at 11:31
It might be a bug on Mysql version 8.0 https://bugs.mysql.com/bug.php?id=90695 — Christoph Adamakis, Nov 05 '18 at 13:02
According to this https://bugs.mysql.com/bug.php?id=90695 , the guys in Mysql suggest to start the server like this sudo service mysql start --initialize lower_case_table_names=1 but again it fails!!!!!!!!!!!!!!!!!!!! — Christoph Adamakis, Nov 05 '18 at 14:18
Does this answer your question? [lower\_case\_table\_names Settings in MySQL 8.0.12](https://stackoverflow.com/questions/51803216/lower-case-table-names-settings-in-mysql-8-0-12) — Igor Mikushkin, Jan 23 '20 at 01:46

score 14 · Answer 1 · edited Apr 22 '21 at 14:47

In order to make this work in MySQL 8.0 and linux follow the steps bellow

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

move or remove /var/lib/mysql directory. This will delete all databases!!!!
```
mv /var/lib/mysql /tmp/mysql
```

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

edit /etc/mysql/mysql.conf.d/mysqld.cnf and add the following line after
```
[mysqld]

lower_case_table_names=1
```

Initialize mysql using the following

mysqld --defaults-file=/etc/mysql/my.cnf --initialize lower_case_table_names=1 --user=mysql --console

Change defaults-file with the actual location of your defaults file. more info on mysql initialization here: https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization-mysqld.html

(Optional) Repeat

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

if owner of the files in /var/lib/mysql is not mysql

Start mysql using
```
sudo service mysql start
```
If everything worked correctly, open mysql using
```
mysql -u root -p
```

and by running this query

SHOW VARIABLES where Variable_name like 'lower%';

you will get

'lower_case_table_names', '1'

Restore mysql schema using the dump created in step 0.
Execute mysql_upgrade to create the sys schema

I get this error when i try to initialize `mysqld`: "Too many arguments (first extra is 'lower_case_table_names=1')" (Server version: 8.0.17-0ubuntu2) — dvlcube, Nov 18 '19 at 21:10
Figured it out. Had to initialize this way: `mysqld --defaults-file=/etc/mysql/my.cnf --initialize --user=mysql --lower_case_table_names=1 --console`. Now it works! — dvlcube, Nov 18 '19 at 21:23

score 13 · Answer 2 · answered Jul 28 '20 at 19:55

This worked for me on a fresh Ubuntu Server 20.04 installation with MySQL 8.0.20 (no existing databases to care about - so if you have important data then you should backup/export it elsewhere before doing this):

So... I did everything with elevated permissions:

sudo su

Install MySQL (if not already installed):

apt-get install mysql-server

Backup configuration file, uninstall it, delete all databases and MySQL related data:

cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup
service mysql stop
apt-get --purge autoremove mysql-server
rm -R /var/lib/mysql

Restore saved configuration file, edit the file (add a line just under [mysqld] line):

cp /etc/mysql/mysql.conf.d/mysqld.cnf.backup /etc/mysql/mysql.conf.d/mysqld.cnf
vim /etc/mysql/mysql.conf.d/mysqld.cnf

...
lower_case_table_names=1
...

Reinstall MySQL (keeping the configuration file), configure additional settings:

apt-get install mysql-server
service mysql start
mysql_secure_installation
mysql

SHOW VARIABLES LIKE 'lower_case_%';
exit

Thank you so much, this worked to me after spending a day on this issue. — yibe, Jan 10 '21 at 18:13

score 2 · Answer 3 · answered Nov 21 '19 at 20:46

You need to reinit MySQL data directory. According to https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html lower_case_table_names can only be configured when initializing the server.

You can do it using this script https://github.com/igrmk/ubuntu-reinit-mysql.

Please note that your data will be DESTROYED! So create a backup before running it.

score 0 · Answer 4 · answered Apr 20 '20 at 12:41

I also had the same issue.

I edited the /etc/mysql/conf.d/mysql.cnf file. (NOT the /etc/mysql/mysql.conf.d/mysqld.cnf file.)

I inset the lower_case_table_names=1 just below [mysqld] line. (There were no lines below [mysqld] line below.

Then I restarted mysql using sudo service mysql restart

It solved my issue.

score 0 · Answer 5 · answered Oct 28 '20 at 14:47

0

Please go to below location and add the following code

location - cd /etc/mysql/mysql.conf.d/

then sudo vim mysqld.cnf

next, add below under the [mysqld]

lower_case_table_names=1

answered Oct 28 '20 at 14:47

Rajitha Bhanuka

714
10
11

score 0 · Answer 6 · answered Oct 11 '21 at 13:32

On Mysql Server 8.0 Windows os, if you change lower_case_table_names=2 in mysql.ini the server will not start so you have to follow this step 1: Backup all data / export to .sql or Dump all data step 2: Stop the server from service step 3: Delete the data folder from C:\Program Files\ Mysql Server 8.0\Data step 4: Open C:\Program Files\ Mysql Server 8.0\my.ini as an administrator using notepad step: 5 find set lower_case_table_names= 2 if you want camelCase table name step: 6 open mysql installer and open reconfigure then open ->show advanced and logging option -> Advanced Options -> use second option from the list step: 7 then everything will be ok

lower_case_table_names=1 on Ubuntu 18.04 doesn't let mysql to start

6 Answers6

Linked