MariaDB and MySQL table name is case sensitive if running on case-sensitive systems such as Linux and Unix. Windows, however, does not enforce case sensitivity for its folders and files. It is causing MySQL and MariaDB table names in Windows to be case-insensitive.
It means that a lowercase table named tablename is just the same as uppercase TABLENAME or TableName, and using any of these will not make any difference in your query. It is because MySQL and MariaDB store and query database tables based on the filesystem’s filename and folder.
You can use case-insensitive table names for MySQL and MariaDB in Linux and other Unix systems or use case sensitive table names in Windows by enabling lower_case_table_names option in the configuration file.
In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database, table, and trigger names. Such names are not case sensitive in Windows, but are case sensitive in most varieties of Unix.
The lower_case_table_names system variable also affects how the server handles identifier case sensitivity, as described later in this section.
To check what is the value of this variable in you installed MySql, run below command
mysqladmin -u <user name> -p variables
Check the lower_case_table_names in the list. Find more details about this variable here.
If lower_case_table_names is 0, it means your table, functions, triggers names are case sensitive. lower_case_table_names can take the values shown in the following table. This variable does not affect case sensitivity of trigger identifiers. On Unix, the default value of lower_case_table_names is 0. On Windows, the default value is 1. On OS X, the default value is 2.
| Value | Meaning |
|---|---|
0 | Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive. You should not set this variable to 0 if you are running MySQL on a system that has case-insensitive file names (such as Windows or OS X). If you force this variable to 0 with --lower-case-table-names=0 on a case-insensitive file system and access MyISAM tablenames using different lettercases, index corruption may result. |
1 | Table names are stored in lowercase on disk and name comparisons are not case sensitive. MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases. |
2 | Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement, but MySQL converts them to lowercase on lookup. Name comparisons are not case sensitive. This works only on file systems that are not case sensitive! InnoDB table names are stored in lowercase, as for lower_case_table_names=1. |
To change the value of ‘lower_case_table_names’, follow below steps.
- Go to my.conf file
- look up for: # The MySQL server [mysqld]
- add this right below it: lower_case_table_names = 2
- save the file and restart MySQL service
Now, fire below command.
mysqladmin -u <user name> -p variables
I have MySQL 8.0 installed on CentOS 7. I want to stop distinguishing between the case of table names and column names as in the Windows version. To do this, set my.cnf to lower_case_table_names = 1 and restart mysqld. That was fine before. However, for some time unknown, the procedure in MySQL 8.0 has not been easy. Lower_case_table_names can only be specified immediately after the initial installation of MySQL 8.0. To return to the initial installation state, you need to delete the datastore and recreate the datastore. Make a note of the procedure.
-(1) Take a dump of the existing DB -(2) Delete the data store in secret -(3) Set lower_case_table_names = 1 and recreate the datastore -(4) Set the root password -(5) Set GRANT by create database and create user -(6) Restore from dump
(1) Take a dump of the existing DB
Make a backup of the existing user-defined DB. Save all DB except mysql database. In the example below, it is a kankeri database and a xxx database.
$ mkdir -p /var/tmp/mysql
$ mysqldump -uroot -p kankeri > /var/tmp/dump-kankeri.sql
$ mysqldump -uroot -p xxx > /var/tmp/dump-xxx.sql
(2) Delete the data store in secret
Gently delete the MySQL datastore (/ var/lib/mysql). It is safe to mv and save. You should also clear mysqld.log. Easy to check for errors later.
$ mv /var/lib/mysql /var/tmp/mysql
$ rm -f /var/log/mysql/mysqld.log
$ touch /var/log/mysql/mysqld.log
$ chown mysql.mysql /var/log/mysql/mysqld.log
(3) Set lower_case_table_names = 1 and recreate the datastore
First, write lower_case_table_names = 1 in mysql-server.cnf. Add to cnf in the initial state. If extra settings remain, it will be difficult to isolate when an error occurs.
$ vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
lower_case_table_names=1
Then recreate the datastore and start the service. At this point, make sure that you can start without error.
$ mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql --console
$ ls -l /var/lib/mysql
$ more /var/log/mysql/mysqld.log
$ systemctl start mysqld
(4) Set the root password
Add skip-grant-tables to mysql-server.cnf to set the root password and restart the service. If necessary, set the encoding as well. Enter the MySQL console with a root account without password authentication.
$ vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
character-set-server=utf8
lower_case_table_names=1
skip-grant-tables
$ vi /etc/my.cnf.d/client.cnf
[client]
default-character-set=utf8
> use mysql
> select user,host,plugin,authentication_string from user;
+------------------+-----------+-----------------------+-----+
| user | host | plugin | authentication_string |
+------------------+-----------+-----------------------+-----+
| mysql.infoschema | localhost | caching_sha2_password | *** |
| mysql.session | localhost | caching_sha2_password | *** |
| mysql.sys | localhost | caching_sha2_password | *** |
| root | localhost | mysql_native_password | *** |
+------------------+-----------+-----------------------+-----+
> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name | Value |
+--------------------------------------+--------+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 8 |
| validate_password.mixed_case_count | 1 |
| validate_password.number_count | 1 |
| validate_password.policy | MEDIUM |
| validate_password.special_char_count | 1 |
+--------------------------------------+--------+
I weaken the password strength as follows. In the initial state, it is a mixture of uppercase letters, lowercase letters, numbers, and symbols. After that, set the password for the root account. A plain password that does not match the strength of the password will result in “ERROR 1819 (HY000): Your password does not satisfy the current policy requirements”.
> set global validate_password.length=6;
> set global validate_password.policy=LOW;
> set global validate_password.special_char_count=0;
> flush privileges;
> alter user 'root'@'localhost' identified by '***'; #With any password
> flush privileges;
> exit;
Remove skip-grant-tables, reboot and make sure you can log in with the root account.
$ vi /etc/my.cnf.d/mysql-server.cnf
[mysqld]
character-set-server=utf8
lower_case_table_names=1
#skip-grant-tables #Comment out
$ systemctl restart mysqld
$ mysql -uroot -p mysql Enter password: ***
(5) Set GRANT by create database and create user
Create the required user-defined database and account and set GRANT. In the example below, the kankeri and xxx databases. For the kankeri database, we have GRANTed accounts kadmin and kuser. Password strength is the same as root described above.
> use mysql;
> create database kankeri;
> create database xxx;
> CREATE USER kadmin@localhost IDENTIFIED BY '***';
> CREATE USER kuser@localhost IDENTIFIED BY '***';
> GRANT ALL PRIVILEGES ON kankeri.* TO kamin@localhost;
> GRANT DELETE,INSERT,SELECT,UPDATE ON kankeri.* TO kuser@localhost;
> flush privileges;
> select user,host,plugin,authentication_string from user;
+------------------+-----------+-----------------------+-----+
| user | host | plugin | authentication_string |
+------------------+-----------+-----------------------+-----+
| kadmin | localhost | mysql_native_password | *** |
| kuser | localhost | mysql_native_password | *** |
| mysql.infoschema | localhost | caching_sha2_password | *** |
| mysql.session | localhost | caching_sha2_password | *** |
| mysql.sys | localhost | caching_sha2_password | *** |
| root | localhost | mysql_native_password | *** |
+------------------+-----------+-----------------------+-----+
(6) Restore from the dump.
Restore the files backed up in the first step (1) and restore the data.
$ mysql -uroot -p kankeri < /var/tmp/dump-kankeri.sql
$ mysql -uroot -p xxx < /var/tmp/dump-xxx.sql
$ mysql -ukuser -p kankeri
Enter password: ***
> show tables;
that’s all
MySQL DataBase Administration 