MySQL stores several files on disk. Even in MySQL 8 where the data dictionary is stored in InnoDB tables, there are still all the tablespace files. Different file system behave differently, and one particular challenge is case sensitivity. On Microsoft Windows, the case does not matter, on Linux the case is important, and on macOS the case of the file names is preserved but the operating system by default makes it look like it is case insensitive.
Which convention that is the correct depends on your personal preference and use case. Between case sensitivity and case insensitivity, it basically boils down to whether mydb, MyDB, and MYDB should be the same identifier or three different ones. Since MySQL originally relied on the file system for its data dictionary, the default was to rely on the case sensitivity of the file system. The option lower_case_table_names was introduced to override the behaviour. The most common use is to set lower_case_table_names to 1 on Linux to introduce case insensitive schema and table names.
This blog will first discuss how lower_case_table_names work in MySQL 8 – it is not the same as in earlier versions. Then it will be shown how MySQL 8 can be initialized on Linux to use case insensitive identifiers.
MySQL 8 and lower_case_table_names
In MySQL 8, it is no longer allowed to change the value of the lower_case_table_names option after the data directory has been initialized. This is a safety feature – as described in the reference manual:
It is prohibited to start the server with a
https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_lower_case_table_nameslower_case_table_namessetting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.
If you try to start MySQL 8 with a different value of lower_case_table_names than MySQL was initialized, you will get an error like (from the MySQL error log):
2019-04-14T03:57:19.095459Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2019-04-14T03:57:19.097773Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-04-14T03:57:19.098425Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-04-14T03:57:20.784893Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15) MySQL Community Server - GPL.
So what are the steps to initialize MySQL 8 with lower_case_table_names = 1? Let’s go through them.
Installing MySQL 8 with Case Insensitive Identifier Names
There are several ways to install MySQL 8 on Linux. The steps that will be shown here are:
- Install the MySQL repository.
- Remove previous installations of MySQL or one of its forks.
- Clean the data directory.
- Install MySQL 8.
- Initialize with
lower_case_table_names = 1.
The example commands are from Oracle Linux 7 and also works on Red Hat Enterprise Linux (RHEL) 7 and CentOS 7. The MySQL YUM repository will be used. On other Linux distributions the steps will in general be different, but related.
1. Install the MySQL Repository
MySQL provides repositories for several Linux distributions for the Community Edition. You can download the repository definition files from MySQL Community Downloads. The files can also be accessed directly. For this example the YUM repository definition will be downloaded using wget and then installed using yum:
shell$ wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm --2019-04-14 12:28:31-- https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm ... HTTP request sent, awaiting response... 200 OK Length: 25892 (25K) [application/x-redhat-package-manager] Saving to: ‘mysql80-community-release-el7-2.noarch.rpm’ 100%[===========================================================>] 25,892 --.-K/s in 0.01s 2019-04-14 12:28:33 (1.76 MB/s) - ‘mysql80-community-release-el7-2.noarch.rpm’ saved [25892/25892] shell$ yum install mysql80-community-release-el7-2.noarch.rpm ... Dependencies Resolved ===================================================================================================== Package Arch Version Repository Size ===================================================================================================== Installing: mysql80-community-release noarch el7-2 /mysql80-community-release-el7-2.noarch 31 k Transaction Summary ===================================================================================================== Install 1 Package Total size: 31 k Installed size: 31 k Is this ok [y/d/N]: y Downloading packages: ... Installing : mysql80-community-release-el7-2.noarch 1/1 Verifying : mysql80-community-release-el7-2.noarch 1/1 Installed: mysql80-community-release.noarch 0:el7-2 Complete!
You can now remove the previous installation (if present) and its files.
2. Remove Previous Installations
MySQL or one of its forks may have been installed beforehand. This may even happen as a dependency of another package. You should never have more than one MySQL or fork installed using the package system (yum or rpm on Oracle Linux, RHEL, and CentOS).
You want to uninstall the existing packages in such a way that you do not remove the programs that depend on it – otherwise you will have to re-install those later. One option is to use the rpm command with the --nodeps option. On Oracle Linux 7, RHEL 7, and CentOS 7 this may look like:
shell$ rpm -e --nodeps mariadb-server-5.5.56-2.el7.x86_64 mariadb-5.5.56-2.el7.x86_64 mariadb-libs-5.5.56-2.el7.x86_64
You can find out which packages are installed using rpm -qa and pass the output through grep to search for the packages of interest.
The next step is to clean out any existing files left behind.
3. Clean the Data Directory
In order to be able to initialize MySQL in step 5., the data directory must be empty. You can choose to use a non-default location for the data directory, or you can re-use the default location which use /var/lib/mysql. If you want to preserve your old data directory, make sure you back it up first!
The data directory may have been removed in step 2., but if it has not, you can remove it using the following command:
shell$ rm -rf /var/lib/mysql
Optionally, you can also remove the error log, and if you store files outside the data directory (for example the binary log files or InnoDB log files), you should also remove those. The error log is located in /var/log/; for other files, you will need to check your configuration file (usually /etc/my.cnf).
You are now ready to install the MySQL 8.
4. Install MySQL 8
You can choose between several packages and patch releases (maintenance releases). It is recommended to install the latest patch release. You can see from the release notes which release is the latest. By default, yum will also install the latest release. Which packages you want to install depends on your requirements. The MySQL reference manual includes a list of the available packages with a description of what they include.
In this example, the following packages will be installed:
- mysql-community-client: Client applications such as the
mysqlcommand-line client. - mysql-community-common: Some common files for MySQL programs.
- mysql-community-libs: Shared libraries using the latest version of the API.
- mysql-community-libs-compat: Shared libraries using the version of the API corresponding to what RPM packages from the Oracle Linux/RHEL/CentOS repositories that depend on MySQL uses. For Oracle Linux 7, RHEL 7, and CentOS 7 this means version 18 (e.g.
libmysqlclient.so.18). - mysql-community-server: The actual MySQL Server.
- mysql-shell: MySQL Shell – the second generation command-line client with devops support. This RPM is not listed in the above reference as it is not part of the MySQL Server RPM bundle, however when using the MySQL YUM repository, it can be installed in the same way as the other RPMs.
The yum command thus becomes:
shell$ yum install mysql-community-{client,common,libs,libs-compat,server} mysql-shell
...
Dependencies Resolved
=====================================================================================================
Package Arch Version Repository Size
=====================================================================================================
Installing:
mysql-community-client x86_64 8.0.15-1.el7 mysql80-community 25 M
mysql-community-common x86_64 8.0.15-1.el7 mysql80-community 566 k
mysql-community-libs x86_64 8.0.15-1.el7 mysql80-community 2.2 M
mysql-community-libs-compat x86_64 8.0.15-1.el7 mysql80-community 2.1 M
mysql-community-server x86_64 8.0.15-1.el7 mysql80-community 360 M
mysql-shell x86_64 8.0.15-1.el7 mysql-tools-community 9.0 M
Transaction Summary
=====================================================================================================
Install 6 Packages
Total download size: 400 M
Installed size: 1.8 G
Is this ok [y/d/N]: y
Downloading packages:
...
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
Userid : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
Package : mysql80-community-release-el7-2.noarch (installed)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mysql-community-common-8.0.15-1.el7.x86_64 1/6
Installing : mysql-community-libs-8.0.15-1.el7.x86_64 2/6
Installing : mysql-community-client-8.0.15-1.el7.x86_64 3/6
Installing : mysql-community-server-8.0.15-1.el7.x86_64 4/6
Installing : mysql-community-libs-compat-8.0.15-1.el7.x86_64 5/6
Installing : mysql-shell-8.0.15-1.el7.x86_64 6/6
Verifying : mysql-community-libs-compat-8.0.15-1.el7.x86_64 1/6
Verifying : mysql-community-common-8.0.15-1.el7.x86_64 2/6
Verifying : mysql-community-server-8.0.15-1.el7.x86_64 3/6
Verifying : mysql-shell-8.0.15-1.el7.x86_64 4/6
Verifying : mysql-community-client-8.0.15-1.el7.x86_64 5/6
Verifying : mysql-community-libs-8.0.15-1.el7.x86_64 6/6
Installed:
mysql-community-client.x86_64 0:8.0.15-1.el7 mysql-community-common.x86_64 0:8.0.15-1.el7
mysql-community-libs.x86_64 0:8.0.15-1.el7 mysql-community-libs-compat.x86_64 0:8.0.15-1.el7
mysql-community-server.x86_64 0:8.0.15-1.el7 mysql-shell.x86_64 0:8.0.15-1.el7
Complete!
Notice how the GPG key for the MySQL YUM repository is downloaded, and you are requested to verify it is the correct key. This happens, because it is the first time the repository is used. You can also manually add the GPG key using the instructions in Signature Checking Using GnuPG.
You are now ready to the final step: configuring and starting MySQL Server for the first time.
5. Initialize with lower_case_table_names = 1
As mentioned in the introduction to this blog, you need to ensure that lower_case_table_names is configured when MySQL initializes its data directory. When you use systemd to start MySQL, it will happen automatically when you start MySQL with an empty data directory. This means, you should update the MySQL configuration file with the desired value of lower_case_table_names before the first start.
The default location for the MySQL configuration file is /etc/my.cnf. Open this file with your favourite editor and ensure the line lower_case_table_names = 1 is listed in the [mysqld] group:
[mysqld] lower_case_table_names = 1
Optionally, you can make other changes to the configuration as needed.
Now, you can start MySQL:
shell$ systemctl start mysqld
This will take a little time as it includes initializing the data directory. Once MySQL has started, you can retrieve the temporary password for the root account from the MySQL error log:
shell$ grep 'temporary password' /var/log/mysqld.log 2019-04-14T03:29:00.122862Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: aLxwMUQr%7C,
The temporary password is randomly generated during the initialization to avoid MySQL being left with a known default password. Use this temporary password to log in and set your permanent root password:
shell$ mysql --user=root --host=localhost --password Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.15 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER USER root@localhost IDENTIFIED BY 'n3w_$tr0ng_P@s$word'; Query OK, 0 rows affected (0.12 sec)
By default for RPM installations, MySQL has the password validation component installed using the MEDIUM strength policy. This means you will need to use a relatively strong password.
You can now verify that MySQL uses case insensitive schema and table identifiers:
mysql> SELECT @@global.lower_case_table_names;
+---------------------------------+
| @@global.lower_case_table_names |
+---------------------------------+
| 1 |
+---------------------------------+
1 row in set (0.00 sec)
mysql> SELECT VARIABLE_SOURCE, VARIABLE_PATH
FROM performance_schema.variables_info
WHERE VARIABLE_NAME = 'lower_case_table_names';
+-----------------+---------------+
| VARIABLE_SOURCE | VARIABLE_PATH |
+-----------------+---------------+
| GLOBAL | /etc/my.cnf |
+-----------------+---------------+
1 row in set (0.01 sec)
mysql> CREATE SCHEMA db1;
Query OK, 1 row affected (0.03 sec)
mysql> use DB1;
Database changed
mysql> CREATE TABLE t1 (id int unsigned NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.47 sec)
mysql> INSERT INTO T1 VALUES (1);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM t1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.01 sec)
The query in lines 9-11 queries what the source of the value of the lower_case_table_names option is. This shows that the value of 1 (from the previous query) is picked up from the /etc/my.cnf file. The rest of the queries show how the db1 schema and the db1.t1 table can be accessed both using lower and upper case.
That is it. Now you can use MySQL Server without having to remember which case was used when a schema object was created.