MySQL, MariaDB 내 lower_case_table_names 사용 시 주의 사항

이 페이지에서는 MySQL, MariaDB 내 table 대소문자 구분 기능을 사용하기 위해 다루는 파라미터 중 하나인 lower_case_table_names 를 사용 시 주의 해야 할 사항에 대해서 다룬다.

 

이 페이지는 아래의 MySQL 공식 문서와 관련되어 있다.

 
[1] https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names

 

 

lower_case_table_names 의 기능

 

이 파라미터는 단순히 사용자의 쿼리 내에 있는 table 명에 대해서만 조절을 해 주는 게 아닌 실제 table 의 character 조합 (collation) 에도 영향을 주기 때문에 아래 Test 예시와 같이 실제 table 생성 시점 및 파라미터 조합에 따라 우리가 원하는 결과가 나오지 않을 수 있다.

 

[Test]

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

mysql> SELECT * FROM test.test1;
ERROR 1146 (42S02): Table 'test.test1' doesn't exist

mysql> SELECT * FROM test.TEST1;
Empty set (0.00 sec)

/* lower_case_table_names 를 1로 바꾸고 reboot 수행 */

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.06 sec)

mysql> use test;
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> CREATE TABLE test.TEST2 (age INT);
Query OK, 0 rows affected (0.05 sec)

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

mysql> SELECT * FROM test.test1;
ERROR 1146 (42S02): Table 'test.test1' doesn't exist

mysql> SELECT * FROM test.TEST1;
ERROR 1146 (42S02): Table 'test.test1' doesn't exist

mysql> SELECT * FROM test.test2;
Empty set (0.00 sec)

mysql> SELECT * FROM test.TEST2;
Empty set (0.00 sec)

/* lower_case_table_names 값을 바꾸기 전, 기존 table 이었던 test1 이 보이지 않음 */

 

그렇기에, 이러한 lower_case_table_names 변경 문제를 예방하기 위해 아래와 같이 테이블명을 소문자로 바꿔보는 것을 고려 할 수 있다. 그러나 이는 application 단에서 사용하는 query 내 대소문자 존재 여부에 따라 영향도가 있기에 이를 먼저 고려 할 것을 권장한다.

 

[Test2]
	
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.07 sec)

mysql> SELECT * FROM test.TEST1;
Empty set (0.00 sec)

/* RENAME TABLE 을 통하여 소문자료 테이블명 변경 */

mysql> RENAME TABLE TEST1 to test1;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM test.test1;
Empty set (0.00 sec)

/* lower_case_table_names 을 1으로 변경 및 재기동 후 다시 테이블 조회 확인 */

+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.08 sec)

mysql> SELECT * FROM test.test1;
Empty set (0.00 sec)

 

또한, 이 문제는 AWS RDS 및 Aurora Cluster 를 업그레이드시에도 문제를 일으키는 주요 요인 중 하나이기에 이에 대한 주의가 필요하다.

 

그리고 MySQL 에서는 5.7 까지는 이 파라미터를 운영 중에 바꿀 수 있으나 8.0 부터는 서버 (혹은 instance) 첫 기동시에만 설정할 수 있도록 변경되었다. 즉, 운영 중에는 이를 바꿀 수가 없다. 그렇기에 5.7 에서 해당 파라미터를 자유롭게 사용하던 사용자라면 8.0 에서는 이에 대한 제한사항을 인지 하는 것이 좋다.