[mysql] table name case sensitive or insensitive configuration | low_case_table_names

Troubleshooting

SELECT t2.col
FROM (   
          SELECT 1 col
     ) AS T1

/*
FROM 절의 subquery 인라인 뷰를 T1으로 alias하고, 
SELECT 절에서 t1.col 하며 t1 '테이블이 존재하지 않다'는 SQL 구문 오류가 발생한다.
테이블 명 대소문자를 구분하여 t1과 T1은 다른 것으로 해석하기 때문이다.
*/

 

Solution

1. Check configruation

show variables like 'lower_case_table_names';

show variables like 'lower_case_table_names' result example

lower_case_table_names variable values

0 = case-sensitive. Table name are stored as specified. camparisons are case-sensitive. 

1 = not case-sensitive. Table name are stored in lowcase on disk. camparisons are not case-sensitive. ( On windows default value.  )

2 = not case-sensitive. Table name are stored as given. campare in lowcase. ( On macOS default value. On Linux, not supported )

 

2. Change configuration

(ex) /etc/my.cnf 

On Operating Systems like Windows where filesystem is case-insensitive MySQL would be case-insensitive by default. But on operating systems like Linux MySQL is case-sensitive. To disable case-sensitivity in Linux we can add following line in '/etc/my.cnf' and restart mysqld service.

lower_case_table_names=0

There are other MySQL variables other then this which can help in changing the behavior of MySQL. Use 'show variables' command to see values of variables. We can also use 'SET GLOBAL <variable_name>=<value>;'syntax for changing values of variables on running MySQL.

 

3. Restart mysql service 

ex) On Lunux command

service mysqld restart

 

 

Reference : 

* MySQL - Server System Variables - dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names