lower_case_table_names Settings in MySQL 8.0.12

asuka picture asuka · Aug 11, 2018 · Viewed 28.3k times · Source

I've just compiled the version MySQL 8.0.12 in a Ubuntu 16.0.4.

After following the instructions in the website and making the following my.cnf file:

[mysqld]
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/usr/local/mysql/data/localhost.localdomain.err
user=mysql
secure_file_priv=/usr/local/mysql/mysql-files
local_infile=OFF

log_error = /var/log/mysql/error.log

# Remove case sensitive in table names
lower_case_table_names=1

I get the following error:

2018-08-11T19:45:06.461585Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').

What should I change so that data dictionary is aligned to server settings?

Answer

Thomas picture Thomas · Jul 24, 2020

So far, I can get it to work with a workaround (I originally posted on askubuntu): by re-initializing MySQL with the new value for lower_case_table_names after its installation. The following steps apply to a new installation. If you have already data in a database, export it first to import it back later:

  1. Install MySQL:
    sudo apt-get update    
    sudo apt-get install mysql-server -y
    
  2. Stop the MySQL service:
    sudo service mysql stop
    
  3. Delete the MySQL data directory:
    sudo rm -rf /var/lib/mysql
    
  4. Recreate the MySQL data directory (yes, it is not sufficient to just delete its content):
    sudo mkdir /var/lib/mysql    
    sudo chown mysql:mysql /var/lib/mysql
    sudo chmod 700 /var/lib/mysql
    
  5. Add lower_case_table_names = 1 to the [mysqld] section in /etc/mysql/mysql.conf.d/mysqld.cnf.
  6. Re-initialize MySQL with --lower_case_table_names=1:
    sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console
    
  7. Start the MySQL service:
    sudo service mysql start
    
  8. Retrieve the new generated password for MySQL user root:
    sudo grep 'temporary password' /var/log/mysql/error.log
    
  9. Change the password of MySQL user root either by:
    sudo mysql -u root -p
    
    and executing:
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPa$$w0rd';
    
    afterwards, OR by calling the "hardening" script anyway:
    sudo mysql_secure_installation
    

After that, you can verify the lower_case_table_names setting by entering the MySQL shell:

sudo mysql -u root -p

and executing:

SHOW VARIABLES LIKE 'lower_case_%';

Expected output:

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+