Planet MySQL :: Planet MySQL - Archives - lower_case_table

To lose your data or make it unavailable there is an excellent option in MySQL, rather than drop or delete Option name is lower_case_table_names. Default value of this setting is 0:

       mysql> select @@lower_case_table_names; 
       +--------------------------+ 
       | @@lower_case_table_names | 
       +--------------------------+
       | 0 | 
       +--------------------------+ 
       1 row in set (0.00 sec)

Due to documentation value=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. So related to documentation, tables T1 and t1 will be different, as well as database DB1 and db1. Let’s create sample table and databases:

      mysql> create database DB1; 
      Query OK, 
      1 row affected (0.06 sec) 
      mysql> create database db3; 
      Query OK, 
      1 row affected (0.03 sec) 
      mysql> use db3; 
      Database changed 
      mysql> create table TABLE1(id int not null); 
      Query OK, 
      0 rows affected (0.04 sec) 
      mysql> insert into TABLE1(id) values(1),(2),(3),(4),(5); 
      Query OK, 
      5 rows affected (0.01 sec) 
      Records: 5 Duplicates: 0 Warnings: 0

You are happy with your tables and databases, but then suddenly somebody with best practice brain says that, it is general rule to change this option equal to 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.
You read documentation , and there was no caution. Decided to change this option, edit my.cnf file and add following under [mysqld]: lower_case_table_names = 1 , then restarted MySQL. From now, you will not able to access to your UPPERCASE created database and tables.

      mysql> use DB1; 
      ERROR 1049 (42000): Unknown database 'db1' 
      mysql> drop database DB1; 
      ERROR 1008 (HY000): Can't drop database 'db1'; database doesn't exist 
      mysql> use db3;
      Database changed 
      mysql> show tables; 
      +---------------+ 
      | Tables_in_db3 | 
      +---------------+ 
      | TABLE1 | 
      +---------------+ 
      1 row in set (0.00 sec) 
      mysql> select * from TABLE1;
      ERROR 1146 (42S02): Table 'db3.table1' doesn't exist

There is no WARNING/Caution in documentation related to this issue. It maybe critical for many applications, because many developers create database and table names using CamelCase pattern or they just begin everything with Uppercase. So be careful while changing this option. In documentation 2 steps provided for this purpose: 1. For individual tables you can rename it as -> RENAME TABLE TABLE1 to table1; 2. Or you can take backup of all databases, then drop all databases, change option and restart MySQL and then import taken backup. The best way is everytime you have a fresh install of MySQL change this option to 1.

http://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html

The post lower_case_table_names option to lose databases and tables appeared first on Azerbaijan MySQL UG.

MySQL Links