百度智能云

All Product Document

          Relational Database Service

          Detailed Interpretations for the Use of lower_case_table_names Parameters

          Background

          Baidu AI Cloud supports modification to the database table's uppercase/lowercase setting parameter. After being modified, this parameter does not take effect until the instance is restarted.

          • "lower_case_table_names=0" indicates case sensitive, and the created database is stored on the disk in original uppercase and lowercase letters.
          • "lower_case_table_names=1" indicates case insensitive. In the creation of a database table, MySQL converts all database table names into lowercase letters for storage on disk. Moreover, if the SQL statement is related to the database table, the table names are converted into lowercase letters for query and write-in, be they written in uppercase letters or lowercase letters at first.

          For parameter settings, see Baidu Cloud RDS Parameter Setting Guide

          Failure Phenomenon

          After one Baidu AI Cloud RDS user initiates "Delete Database" action on the console, the console page always shows "Deleting" state. Then, the user sends the ticket, and authorizes Baidu AI Cloud after-sales engineers for troubleshooting help, finding that the database is still there.

          Cause of analysis

          • The user sends the "drop database" action to the database via the RDS console and views the "binlog" blog. However, the user fails to see "drop database" statement, but there exists "drop table" statement, as detailed below:
          DROP TABLE IF EXISTS `tb_02`,`tb_03`,`tb_01`/*!*/;

          Therefore, we judge that the database fails to execute "drop database" command, and turns to generate "drop table" action. Since the table names are all written in lowercase letters, the action of deleting tables containing uppercase letters fails accordingly.

          • Through query we know that current database's "lower_case_table_names" value is 1 (case insensitive), but the table file names in operating system contain the uppercase letters. Thus, we judge that the user ever set the database as "case-sensitive" upon table creation, as well as created the table containing uppercase letters. Later, the user modified the "lower_case_table_names" parameter as "case-insensitive", leading to MySQL's failure in locating corresponding tables upon "dropping database".

          Solutions

          After being authorized by the user, the Baidu AI Cloud engineer logs in to the user's database server to delete remaining tablespace files containing uppercase letters and execute the "drop database" command anew to delete the database.

          Case review

          1. Current parameter environment is "lower_case_table_names=0" (case sensitive). In the "baidu_dba" database, create three tables, of which one table is named in uppercase letters, and two tables are named in lowercase letters. The SQL statement is as follows:
          CREATE TABLE TB_01 (
             id int(11) NOT NULL AUTO_INCREMENT,
             name varchar(64) DEFAULT NULL,
             PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
          
          CREATE TABLE tb_02 (
             id int(11) NOT NULL AUTO_INCREMENT,
             name varchar(64) DEFAULT NULL,
             PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
          
          CREATE TABLE tb_03 (
             id int(11) NOT NULL AUTO_INCREMENT,
             name varchar(64) DEFAULT NULL,
             PRIMARY KEY (id)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          After the creation, the system displays:

          image.png

          1. On the console, modify "lower_case_table_names=1" (case insensitive), and then restart RDS instance.
          2. On the console, delete the database "baidu_dba", finding that the console shows the "Deleting" state
          1. Log in to the master instance, finding that the "baidu_dba" database doe exist. However, in the database, there is only one "TB_01" table left over, and the other two tables "tb_02\tb_03" are deleted.

          image.png

          Conclusions & suggestions

          • The parameter "lower_case_table_names" must be definitely set as "case-sensitive" or "case-insensitive" upon database initialization. You are not advised to modify this parameter's value at will and frequently in the online environment.
          • When the parameter "lower_case_table_names" is set as "case-insensitive", the database table names are converted into lowercase letters for storage in the operating system, no matter whether or not the database tables created in the database contain uppercase letters. Therefore, those database tables in uppercase letters, which are set as case-sensitive beforehand, cease to be effective in the case-insensitive parameter environment, leading to failure to identify the table.
          • Upon database migration, you need to keep a close eye on the parameter "lower_case_table_names", and ensure the value consistency between the source database and target database.
          • If you want to modify the value of the parameter "lower_case_table_names" in the existing database, you should get through the following actions:
          The first case: for the case where only uppercase letters exist in the table name:
          Step 1: When lower_case_tables_name = 0, execute the rename table statement to change the table name with uppercase letters to lowercase
          Step 2: Set lower_case_tables_name = 1, restart the instance to take effect
          
          The second case: for the case of uppercase letters in the library name:
          Step 1: When lower_case_tables_name = 0, use mysqldump to export all the data of the current database, and then delete the old database
          Step 2: Set lower_case_tables_name = 1, restart the instance to take effect
          Step 3: Import the data to the instance. At this time, the library name containing uppercase letters has been automatically converted to lowercase.
          Previous
          SDK
          Next
          Detailed Interpretations for the Use of innodb_large_prefix Parameters