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?

Answer 1

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

   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

5. (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

6. Start mysql using

   sudo service mysql start
   

7. 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'

8. Restore mysql schema using the dump created in step 0.

9. Execute mysql_upgrade to create the sys schema

Answer 2

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