Unix上のMYSQLで大文字でTableを作成後にlower_case_table_names=1とした場合に、大文字のTableが検索できない。

mysql

概要

OSがUnixかつ大文字でTableを作成後に大文字小文字を区別しないとした場合に、大文字のTableが検索できなくなります。（RedhatとCentOSで現象を確認、Windows7で発生しないことを確認）

詳細

MySQLでは大文字小文字の区別を行うかどうかは「lower_case_table_names」の設定を行うことで変更が可能です。

MySQLでのテーブルとデータベース名の保存方法はlower_case_table_namesシステム変数に影響されます。これはmysqld起動時に設定できます。 lower_case_table_namesは以下のテーブルに示された値をとり得ます。Unixでは、lower_case_table_namesのデフォルト値は 0で、Windowsでは1、Mac OS Xでは2となります。

http://dev.mysql.com/doc/refman/5.1/ja/identifier-case-sensitivity.html

上記の通り、Unixのデフォルト値は0で大文字と小文字を区別します。

mysql> show variables where variable_name='lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> create table HOGE(HUGA VARCHAR(10));
Query OK, 0 rows affected (0.01 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| HOGE           |
+----------------+
1 row in set (0.00 sec)

mysql> insert into HOGE(HUGA) value ('PIYO');
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from HOGE;
+------+
| HUGA |
+------+
| PIYO |
+------+
1 row in set (0.00 sec)
 
mysql> select * from hoge;
ERROR 1146 (42S02): Table 'test.hoge' doesn't exist

このように「HOGE」テーブルが存在する状態で、大文字小文字を区別しない設定に変更してみます。
変更するためにはmy.cnfに「lower_case_table_names=1」を加えます。
ちなみにmy.cnfの優先順位は↓の通り。

Linux系のMySQLでは、
/etc/my.cnf -> /etc/mysql/my.cnf -> SYSCONFDIR/my.cnf -> $MYSQL_HOME/my.cnf -> defaults-extra-fileで指定されたファイル -> $HOME/.my.cnf -> 直接渡したオプション
の順番で読み込まれる。

http://yoku0825.blogspot.jp/2012/11/mycnf.html

で、この状態で「HOGE」テーブルを検索してみます。

mysql> show variables where variable_name='lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.00 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| HOGE           |
+----------------+
1 row in set (0.00 sec)
 
mysql> select * from HOGE;
ERROR 1146 (42S02): Table 'test.hoge' doesn't exist
mysql> select * from hoge;
ERROR 1146 (42S02): Table 'test.hoge' doesn't exist

このように大文字でも小文字でも取得できなくなります。

「lower_case_table_names=1」の説明としては↓とありました。ルックアップ時ってどういうことですか？？

テーブル名はディスク上に小文字で記憶され、名前比較では大文字小文字は区別されません。MySQLでは、保管およびルックアップ時に全てのテーブル名が小文字に変換されます。このオプションはデータベース名やテーブルエイリアスにも適用されます。

http://dev.mysql.com/doc/refman/5.1/ja/identifier-case-sensitivity.html

尚、Windows7で同手順で実施しましたが、「HOGE」でも「hoge」でも取得可能でした。

回避策

回避策としては「lower_case_table_names=0」の状態でTable名を小文字に変更するという方法しかみつかりませんでした。
何か他に方法がありましたら教えて下さい。

mysql> show variables where variable_name='lower_case_table_names';             
+------------------------+-------+                                              
| Variable_name          | Value |                                              
+------------------------+-------+                                              
| lower_case_table_names | 0     |                                              
+------------------------+-------+
1 row in set (0.00 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| HOGE           |
+----------------+
1 row in set (0.00 sec)
 
mysql> rename table HOGE to hoge;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| hoge           |
+----------------+
1 row in set (0.00 sec)

ちなみに、小文字のviewを作成したらどうかと試してみましたが、やはり区別がつかないようでエラーが出ました。

mysql> show variables where variable_name='lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> create view hoge as select * from HOGE;                                  
Query OK, 0 rows affected (0.00 sec)                                            
                                                                                
mysql> select * from hoge;                                                      
+------+                                                                        
| HUGA |                                                                        
+------+                                                                        
| PIYO |                                                                        
+------+                                                                        
1 row in set (0.00 sec)                                                         

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| HOGE           |
| hoge           |
+----------------+
2 rows in set (0.00 sec)

my.cnfに「lower_case_table_names=1」を加えて再起動。

mysql> show variables where variable_name='lower_case_table_names';             
+------------------------+-------+                                              
| Variable_name          | Value |                                              
+------------------------+-------+                                              
| lower_case_table_names | 1     |                                              
+------------------------+-------+                                              
1 row in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| HOGE           |
| hoge           |
+----------------+
2 rows in set (0.01 sec)
 
mysql> select * from hoge;
ERROR 1462 (HY000): `test`.`hoge` contains view recursion
mysql> select * from HOGE;
ERROR 1462 (HY000): `test`.`hoge` contains view recursion