Saturday, May 18, 2024
 Popular · Latest · Hot · Upcoming
rated 0 times [  7] [ 0]  / answers: 1 / hits: 11272  / 3 Years ago, thu, june 3, 2021, 11:46:53

It seems to be a lot of trouble to get MySQL running with lower_case_table_names=1 as can be seen in:

I'm trying to install MySQL 8.0.19 on Ubuntu Server 20.04 LTS. lower_case_table_names=1 cannot be set after mysql has already been initialized, which happens automatically when installing from the package repositories.

According to the documentation Server System Variables | sysvar_lower_case_table_names:

For APT installations on Debian and Ubuntu, however, the server is
initialized for you, and there is no opportunity to configure the
setting in an option file beforehand. You must therefore use the
debconf-set-selection utility prior to installing MySQL using APT to
enable lower_case_table_names. To do so, run this command before
installing MySQL using APT:

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

I did that on a freshly installed Ubuntu Server 20.04 and ran sudo apt-get install mysql-server afterwards, but it did not change lower_case_table_names to 1.

If at all, what am I doing wrong? How can I successfully install MySQL 8.0 with lower_case_table_names set to 1?

More From » server


So far, I can get it to work with a workaround: by re-initializing MySQL with the new value for lower_case_table_names after its installation. These are the requiered steps:

  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 |

[#2965] Friday, June 4, 2021, 3 Years  [reply] [flag answer]
Only authorized users can answer the question. Please sign in first, or register a free account.

Total Points: 218
Total Questions: 113
Total Answers: 124

Location: British Indian Ocean Territory
Member since Tue, Feb 22, 2022
2 Years ago
otatorm questions
Wed, May 26, 21, 17:20, 3 Years ago
Fri, Jan 21, 22, 12:38, 2 Years ago
Sat, Apr 9, 22, 08:34, 2 Years ago
Sat, Nov 13, 21, 09:50, 3 Years ago