In Managed Services, we have many customers, and as each has a different kind of config and environment, working on their environment is always fun and interesting. In this blog post, I will showcase an issue we faced when dropping a table and how it was resolved.
There was a ticket to drop a table in a client’s production environment (MySQL 5.7). The table had a # symbol at the beginning of the table’s name. I thought it was easy that we can use quotes or backtick symbols to specify the table to drop. But it did not work as I expected and I came to know why the customer created the ticket to drop the table.
The following example recreates the problem. It shows the table, but you are unable to see the structure and cannot drop it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql> show tables; +--------------------------+ | Tables_in_percona | +--------------------------+ | #Tableau_01_bw_F2DD_test | +--------------------------+ 1 row in set (0.00 sec) mysql> show create table `#Tableau_01_bw_F2DD_test`G ERROR 1146 (42S02): Table 'percona.#tableau_01_bw_f2dd_test' doesn't exist mysql> mysql> drop table `#Tableau_01_bw_F2DD_test`; ERROR 1051 (42S02): Unknown table 'percona.#tableau_01_bw_f2dd_test' mysql> mysql> drop table '#Tableau_01_bw_F2DD_test'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''#Tableau_01_bw_F2DD_test'' at line 1 mysql> mysql> drop table "#Tableau_01_bw_F2DD_test"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"#Tableau_01_bw_F2DD_test"' at line 1 mysql> |
When checking the .ibd and .frm files, those are present and nothing wrong with those physical files.
1 2 3 4 5 6 7 8 | [root@centos12 percona]# ls -lrth total 112K -rw-r-----. 1 mysql mysql 65 Dec 20 02:44 db.opt -rw-r-----. 1 mysql mysql 8.4K Dec 20 02:51 @0023Tableau_01_bw_F2DD_test.frm -rw-r-----. 1 mysql mysql 96K Dec 20 02:51 @0023Tableau_01_bw_F2DD_test.ibd [root@centos12 percona]# pwd /var/lib/mysql/percona [root@centos12 percona]# |
I thought the problem was due to the # symbol and wanted to try creating a new table with the # symbol and drop it. The result was a surprise that we were able to create the table and drop it. But again, it failed to drop the table which was given by the client.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | mysql> show tables; +--------------------------+ | Tables_in_percona | +--------------------------+ | #Tableau_01_bw_F2DD_test | | #tableau_01__test | +--------------------------+ 2 rows in set (0.00 sec) mysql> Drop table `#Tableau_01__Test`; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +--------------------------+ | Tables_in_percona | +--------------------------+ | #Tableau_01_bw_F2DD_test | +--------------------------+ 1 row in set (0.00 sec) mysql> Drop table `#Tableau_01_bw_F2DD_test`; ERROR 1051 (42S02): Unknown table 'percona.#tableau_01_bw_f2dd_test' mysql> show create table `#Tableau_01_bw_F2DD_test`G ERROR 1146 (42S02): Table 'percona.#tableau_01_bw_f2dd_test' doesn't exist mysql> mysql> show global variables like '%lower_case_table%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 1 | +------------------------+-------+ 1 row in set (0.00 sec) |
Here we noticed one thing — we created the table in uppercase and when showing the table it showed in lowercase. This gives us a clue to check lower_case_table_names and it was set to 1 (by default 0 in Unix). So we wanted to try to create a table in one value and drop it when it is set to another.
Lower_case_table_names values and their behavior:
| 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 macOS). 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 and view names are stored in lowercase, as for lower_case_table_names=1. |
Scenario One: Create table when lower_case_table_names=0 and drop when lower_case_table_names=1
Set lower_case_table_names=0 and created tables and one database with uppercase.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | mysql> use percona; Database changed mysql> create table `#Table1_test2`(ct INT primary key); Query OK, 0 rows affected (0.02 sec) mysql> create table `#table1_test2`(ct INT primary key); Query OK, 0 rows affected (0.01 sec) mysql> create table `Table1_test3`(ct INT primary key); Query OK, 0 rows affected (0.02 sec) mysql> create table `table1_test2`(ct INT primary key); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_percona | +-------------------+ | #Table1_test2 | | #table1_test2 | | Table1_test3 | | table1_test2 | +-------------------+ 4 rows in set (0.00 sec) mysql> show global variables like '%lower_case_table%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 0 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> create database Test_database; Query OK, 1 row affected (0.00 sec) mysql> show global variables like '%lower_case_table%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 0 | +------------------------+-------+ 1 row in set (0.00 sec) |
To change the value of lower_case_table_names to 1, I just changed the value in config and restarted the MySQL service. When lower_case_table_names is 1, you can see the first drop table of #Table1_test2 is successful and it shows, but the drop is failed for #table1_test2 and not showing in the table list. This is due to case-insensitive, as wherever we use uppercase it will lookup in lowercase only. This is the reason dropping the table of #Table1_test2 dropped the table #table1_test2.
We are unable to use Test_database since it was created in uppercase. So whatever the tables are inside the database will not be accessible. In short, when lower_case_table_names=1 uppercase letters are on tables and databases they will be treated as lowercase letters.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 | mysql> use percona; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------+ | Tables_in_percona | +-------------------+ | #Table1_test2 | | #table1_test2 | | Table1_test3 | | table1_test2 | +-------------------+ 4 rows in set (0.00 sec) mysql> Drop table `#Table1_test2`; Query OK, 0 rows affected (0.01 sec) mysql> drop table `#table1_test2`; ERROR 1051 (42S02): Unknown table 'percona.#table1_test2' mysql> drop table Table1_test3; ERROR 1051 (42S02): Unknown table 'percona.table1_test3' mysql> drop table table1_test2; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_percona | +-------------------+ | #Table1_test2 | | Table1_test3 | +-------------------+ 2 rows in set (0.00 sec) mysql> drop table `#Table1_test2`; ERROR 1051 (42S02): Unknown table 'percona.#table1_test2' mysql> show global variables like '%lower_case_table%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 1 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | Test_database | | mysql | | percona | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use Test_database; ERROR 1049 (42000): Unknown database 'test_database' mysql> use test_database; ERROR 1049 (42000): Unknown database 'test_database' mysql> use `Test_database`; ERROR 1049 (42000): Unknown database 'test_database' |
Scenario Two: Create table when lower_case_table_names=1 and drop when lower_case_table_names=0
When we tried to create databases and tables in uppercase, it created those in lowercase only. Creation of #table1_test2 failed with error already exists, since the first created statement of #Table1_test2 was converted into lowercase and created table #table1_test2. The same happened when creating the table Table1_test3 was successful, it was created as table1_test3 and failed when creating again the table table1_test3.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | mysql> create database lower1_to_Lower0; Query OK, 1 row affected (0.00 sec) mysql> show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | Test_database | | lower1_to_lower0 | | mysql | | percona | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec) mysql> use lower1_to_lower0; Database changed mysql> create table `#Table1_test2`(ct INT primary key); Query OK, 0 rows affected (0.02 sec) mysql> create table `#table1_test2`(ct INT primary key); ERROR 1050 (42S01): Table '#table1_test2' already exists mysql> create table `Table1_test3`(ct INT primary key); Query OK, 0 rows affected (0.02 sec) mysql> create table `table1_test3`(ct INT primary key); ERROR 1050 (42S01): Table 'table1_test3' already exists mysql> show tables; +----------------------------+ | Tables_in_lower1_to_lower0 | +----------------------------+ | #table1_test2 | | table1_test3 | +----------------------------+ 2 rows in set (0.00 sec) |
To change the value of lower_case_table_names from 1 to 0, I just changed the value in config and restarted the MySQL service. We were able to drop the tables and database when lowercase_table_name=0, since the database and table were not created with uppercase.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | mysql> show global variables like '%lower_case_table%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | lower_case_table_names | 0 | +------------------------+-------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | Test_database | | lower1_to_lower0 | | mysql | | percona | | performance_schema | | sys | +--------------------+ 7 rows in set (0.00 sec) mysql> use lower1_to_lower0; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +----------------------------+ | Tables_in_lower1_to_lower0 | +----------------------------+ | #table1_test2 | | table1_test3 | +----------------------------+ 2 rows in set (0.00 sec) mysql> drop table `#table1_test2`; Query OK, 0 rows affected (0.01 sec) mysql> drop table `table1_test3`; Query OK, 0 rows affected (0.01 sec) mysql> show tables; Empty set (0.01 sec) mysql> drop database lower1_to_lower0; Query OK, 0 rows affected (0.00 sec) mysql> show schemas; +--------------------+ | Database | +--------------------+ | information_schema | | Test_database | | mysql | | percona | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) |
Scenario one was the one that we faced in our client environment. The client long ago created the table when lower_case_table_names=0 and they changed it after some time to lower_case_table_names=1. So we got approval to set lower_case_table_names=0 and dropped the table and reverted it back to 1. Since this change was not dynamic, we needed a server restart.
Conclusion
I am not suggesting using 1 or 0 in lower_case_table_names, since this is based on the application requirement. But before changing the value from 0 to 1, please check if there are any tables or databases with uppercase. If any, those need to be converted into lowercase, as otherwise those uppercase tables and databases will not be accessible. When changing the value from 1 to 0, we did not face a problem, since the tables and databases are not allowed to be created in uppercase and those are created in lowercase only due to case-insensitivity. In MySQL 8.0 you won’t be able to change lower_case_table_names value after database instance creation, as the value of this variable affects definitions of data-dictionary tables and can’t be changed after the server is initialized.
Would this issue exist on mysql 8
Since the 8.0 version prohibits the change in the value of lower_case_table_names after the server is initialized. so will not face the same issue in 8 versions.