MariaDB Table doesn't exist 에러를 해결해 봅니다.

작성일 | In

MySQL lower_case_table_names 설정 방법을 알아봅니다.

목표

  • 업무 중에 발생한 오류를 다시 정리합니다.
  • 이슈를 해결하는 접근 방식을 개선해봅니다.



앞서

업무 수행 중에 발생한 문제들을 자세히 살펴보면, 시간을 낭비하며 해결하려고 노력했던 상황들이 몇 번 있었습니다. 이러한 상황에서는 초기에 잘못된 접근 방식이나 잘못된 가정으로 인해 오히려 업무의 효율성을 떨어뜨리는 결과를 초래했습니다. 이러한 오류들을 다양한 관점에서 분석하고, 어떻게 효과적으로 회피할 수 있는지 고민해보았습니다.

먼저, 이러한 문제들은 대부분 처음 접하는 새로운 작업이나 기술을 다룰 때 발생합니다. 게시글에서 발생한 이슈는 기존에 얼핏 알고 있는 지식이 오히려 발목을 잡은 경험입니다. 기존의 지식과 경험에 의존하여 문제를 해결하려 했던 것도 원인 중 하나였으며 에러나 이슈를 바라보는 다양한 시각을 개선해야 한다는 생각을 가지게 되었습니다.



개요

업무와 관련된 민간함 모든 데이터들은 재구성 하였습니다.

사내에서 개발하는 애플리케이션을 인수인계 받아 새로운 서버에 구축하는 업무를 받았습니다.

새로운 서버 환경에 맞게 배포를 완료하고, 로그인 부터 기능 테스트까지 진행하기 위해 로그인을 시도했지만 실패했습니다.

발생한 로그는 아래와 같습니다.

log

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# tail -f /home/#####.out

[ERROR] 10:17:32.870 [http-nio-0.0.0.0-9001-exec-7][o.a.c.c.C.[.[.[.[dispatcherServlet]][?:?] Servlet.service() for servlet [dispatcherServlet] in context with path [/common/v1] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Table 'TABLE' doesn't exist
### The error may exist in class path resource [sql/tcore/UserMapper.xml]
### The error may involve com.~.UserMapper.userLogin-Inline
### The error occurred while setting parameters
### SQL: select    ~~    from Employees  where DELETE_YN = 'N' and LOGIN_ID = ?
### Cause: java.sql.SQLSyntaxErrorException: Table 'Employees' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'TABLE' doesn't exist] with root cause
java.sql.SQLSyntaxErrorException: Table 'Employees' doesn't exist
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at ...
  • 주어진 로그로부터 추론할 수 있는 사실은 다음과 같습니다:
    • org.springframework.jdbc.BadSqlGrammarException 에러가 발생했음을 알 수 있습니다.
    • SQL 문에서 테이블 ‘Employees’를 사용하려고 하는데 해당 테이블이 존재하지 않아서 발생한 것임을 알 수 있습니다. 이로 인해 java.sql.SQLSyntaxErrorException 예외가 발생하였습니다.
    • 실행된 SQL: select ~~ from Employees where DELETE_YN = 'N' and LOGIN_ID = ? SQL 문이 실행되었는데, ‘Employees’ 테이블이 없어서 에러가 발생했습니다.
    • 에러 발생 위치: sql/tcore/UserMapper.xml 파일 내에 있는 com.~.UserMapper.userLogin-Inline 메소드에서 문제가 발생하였음을 알 수 있습니다.


테이블 확인

해당 애플리케이션은 MariaDB를 사용하고 있습니다. 로그에 나온대로 해당 테이블이 존재하는지 확인해 봅니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ mysql -u root -p
Enter password:

MariaDB [CompanyDB]> show tables;
+-----------------------------+
| Tables~                     |
+-----------------------------+
| Employees                   |
| Departments                 |
| Projects                    |
| Customers                   |
| ...                         |
+-----------------------------+
10 rows in set (0.001 sec)

Employees 테이블이 존재하는걸 확인했으며 다음으로는 상세 정보를 확인해 DB Connection 을 맺는 유저가 Employees 테이블에 올바른 접근 권한이 있는지 확인합니다.


유저 접근 권한 확인

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# Employees view 상세 정보
MariaDB [CompanyDB]> show create Employees;
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View            | Create View                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      | character_set_client | collation_connection |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| Employees | CREATE ALGORITHM=UNDEFINED DEFINER=`Auser`@`%` SQL SECURITY DEFINER VIEW `Employees` AS select `a`.`USER_ID` AS `user_id`,`a`.`NAME` AS `name`,`a`.`LOGIN_ID` AS `login_id`,`a`.`PASSWORD` AS `password`,`a`.`PHONE` AS `phone`,`a`.`EMAIL` AS `email`,`a`.`DEPARTMENT` AS `department`,if(`a`.`USE_EXPIRE` = 'Y',1,0) AS `use_expire`,`a`.`EXPIRE_DATE` AS `expire_date`,if(`a`.`USE_EXPIRE` = 'Y',if(date_format(current_timestamp(),'%Y-%m-%d') > `a`.`EXPIRE_DATE`,'E',`a`.`STATUS`),`a`.`STATUS`) AS `status`,`a`.`GROUP_ID` AS `group_id`,date_format(`a`.`CREATE_DATE`,'%Y-%m-%d %H:%i:%s') AS `create_date`,date_format(`a`.`UPDATE_DATE`,'%Y-%m-%d %H:%i:%s') AS `update_date`,`a`.`CREATE_USER_ID` AS `create_user_id`,`a`.`UPDATE_USER_ID` AS `update_user_id`,`b`.`login_id` AS `update_user_name`,date_format(`a`.`LAST_LOGIN_DATE`,'%Y-%m-%d %H:%i:%s') AS `last_login_date`,if(`a`.`~` = 'Y',1,0) AS `~`,if(`a`.`~` = 'Y',1,0) AS `~`,if(`a`.`~` = 'Y',1,0) AS `~`,`a`.`AUTH_FLAG` AS `auth_flag`,`a`.`DELETE_YN` AS `delete_yn`,`a`.`HOME_URL` AS `HOME_URL` from (`-` `a` left join (select `~`.`USER_ID` AS `user_id`,`~`.`LOGIN_ID` AS `login_id` from `-` where `-`.`DELETE_YN` = 'N') `b` on(`a`.`UPDATE_USER_ID` = `b`.`user_id`)) | utf8                 | utf8_general_ci      |
+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.002 sec)

# 'Auser'@'abc' 권한 확인
MariaDB [CompanyDB]> show grants for 'Auser'@'abc';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for tcore@edt                                                                                            |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `Auser`@`abc` IDENTIFIED BY PASSWORD '*12345678' |
| GRANT ALL PRIVILEGES ON `CompanyDB`.* TO `Auser`@`abc`                                                       |
| GRANT SELECT ON `CompanyDB`.`Employees` TO `Auser`@`abc`                                               |
+-----------------------------------------------------------------------------------------------------------------+

Employees는 view 테이블이며 해당 테이블에 접근 권한을 확인하기 위해 Employees 테이블의 생성 정의 쿼리를 확인합니다. 생성 정의 쿼리에는 해당 테이블의 상세 옵션을 담고 있습니다.

  • CREATE ALGORITHM=UNDEFINED 란?

    CREATE ALGORITHM=UNDEFINED 옵션은 MySQL의 VIEW(뷰) 생성 시 사용되는 옵션 중 하나입니다.

    VIEW는 하나 이상의 테이블에서 데이터를 선택적으로 추출하여 논리적으로 가상의 테이블을 만들어주는 데이터베이스 객체입니다. 이때 CREATE ALGORITHM 옵션은 VIEW의 작동 방식을 지정합니다.

    알고리즘(Algorithm)은 VIEW의 작동 방식을 결정하는데, 주로 다음과 같이 사용됩니다:

    1. UNDEFINED: MySQL 엔진이 최적의 알고리즘을 자동으로 선택합니다.
    2. MERGE: 텍스트 기반 뷰를 생성하며, UNION을 사용해 다른 테이블의 결과를 결합합니다.
    3. TEMPTABLE: 임시 테이블에 결과를 저장하고 처리합니다.

    일반적으로 UNDEFINED를 사용하면 MySQL이 최적의 알고리즘을 선택하도록 하기 때문에, 개발자가 직접 지정할 필요 없이 자동으로 처리됩니다. 이는 효율적인 쿼리 실행을 위해 MySQL이 최적의 방법을 선택하도록 하는 것입니다.

눈 여겨 봐야할 옵션은 SQL 문의 DEFINER=Auser@% SQL SECURITY DEFINER` 부분 입니다.

DEFINER 및 SQL SECURITY 절은 MySQL 데이터베이스에서 VIEW(뷰)를 생성할 때 사용되는 옵션입니다. 이 옵션들은 VIEW의 접근 권한 및 보안 설정을 지정하는 데 사용됩니다.

  1. DEFINER: VIEW를 생성한 사용자 또는 권한을 지정합니다. DEFINER 절은 해당 VIEW가 생성된 사용자나 권한의 권한으로 VIEW를 사용하게 됩니다. 즉, 해당 사용자나 권한으로 VIEW를 조회하면, 그 사용자의 권한으로 데이터를 볼 수 있습니다. 예를 들어 DEFINER=user@%로 설정하면, 해당 뷰를 사용할 때 해당 사용자의 권한으로 데이터를 조회하게 됩니다.
  2. SQL SECURITY: VIEW의 실행 보안을 지정합니다. SQL SECURITY DEFINER로 설정하면, DEFINER에서 지정한 사용자 또는 권한의 권한으로 VIEW를 실행합니다. 반면에 SQL SECURITY INVOKER로 설정하면, VIEW를 호출한 사용자의 권한으로 VIEW를 실행합니다.

따라서 주어진 구문인 DEFINER=user@% SQL SECURITY DEFINER는 Employees라는 VIEW를 생성한 사용자(Auser)의 권한으로 VIEW를 실행하며, 접근 시 해당 사용자의 권한으로 데이터를 조회하도록 설정한 것을 나타냅니다. 이로써 현재 DB 커넥션을 맺는 유저는 올바른 접근 권한이 부여되어 있음을 확인할 수 있습니다.

두 번째로 ‘Auser’@’abc’ 유저의 권한을 확인했을때 CompanyDB 에 접근가능하며 해당 DB에 존재하는 모든 테이블에 접근 가능함을 확인할 수 있습니다.

Employees 테이블이 존재하는것을 확인했으며, 접근 유저 또한 올바른 권한이 부여된 걸 확인했습니다. 로그인 과정중 발생한 에러이기 때문에 ID,PW 데이터가 틀렸을 가능성을 생각해 다시한번 password를 확인하고 시도해봅니다.

1
2
3
4
5
6
7
8
# Employees table 확인
MariaDB [CompanyDB]> select * from Employees;
+---------+-------+----------+-------------------------------------------+---------------+-------------------+---------------+------------+-------------+--------+----------+---------------------+---------------------+----------------+----------------+------------------+---------------------+------------------------+---------------------+--------------------+-------------+-----------+--------------------------+
| user_id | name  | login_id | password                                  | phone         | email             | department    | use_expire | expire_date | status | group_id | create_date         | update_date         | create_user_id | update_user_id | update_user_name | last_login_date     |            ~           |            ~        |            ~       | auth_flag   | delete_yn | HOME_URL                 |
+---------+-------+----------+-------------------------------------------+---------------+-------------------+---------------+------------+-------------+--------+----------+---------------------+---------------------+----------------+----------------+------------------+---------------------+------------------------+---------------------+--------------------+-------------+-----------+--------------------------+
|       1 | admin | admin    | *1sqqwalkmawkmalwkmwlkdmwlkdmksmzxmckqEQZS| 010-XXXX-XXXX | admin@abc.co.kr   | ~~부서         |          0 | NULL        | Y      |       51 | 2019-02-27 21:38:05 | 2019-09-19 22:58:36 | 1              | 1              | admin            | 2023-07-20 11:05:17 |                      0 |                   0 |                  1 | SUPER_ADMIN | N         | /~                       |
+---------+-------+----------+-------------------------------------------+---------------+-------------------+---------------+------------+-------------+--------+----------+---------------------+---------------------+----------------+----------------+------------------+---------------------+------------------------+---------------------+--------------------+-------------+-----------+--------------------------+
1 row in set (0.002 sec)

login_id 와 password 모두 network level 과 code level 에서 logging을 걸어 확인해보며 큰 흐름을 다시 생각해 보았습니다.

해당 로그인 Flow 는 아래와 같습니다.

  1. UI 에서 로그인을 시도합니다.
  2. DB 커넥션을 맺고 해당 사용자 정보가 담겨있는 CompanyDB 데이터베이스로 접근합니다.
    1. 이때 접근하는 유저는 ‘Auser’@’abc’ 입니다.
  3. CompanyDB 의 테이블을 조회합니다.
  4. Employees 테이블을 찾습니다.
  5. 로그인 요청을 보낸 파라미터값을 확인하여 DB값과 일치 여부를 판단합니다.
  6. 데이터가 일치 하면 로그인이 성공 합니다.

log를 확인해본 결과 로그인 로직이 발생하는 5번 Flow 까지 도달하지 못하고 4번 Flow 에서 여전히 Employees 테이블을 찾지 못하는 에러 로그를 발생시키고 있었습니다.



도움

현재 시점에서 다른 각도에서 해당 이슈를 접근하기 어렵다고 판단하여 동료분에게 해당 이슈에 대해 공유하여 도움을 요청드렸습니다.

문제의 원인은 MySQL(MariaDB) 의 테이블명이 대소문자를 구분한다는 것 이었습니다.

사소한 원인이지만 해당 원인을 찾기 까지는 배경지식이 필요합니다.

각 Mysql 계열 DB의 버전별 특징과 대소문자를 구분하는 OS가 호환성을 인지하고 있어야 합니다.

(window OS는 테이블명을 구분하지 않지만 Linux 계열은 테이블명의 대소문자를 구분합니다.)

  • 리눅스에서 MySQL 데이터베이스 테이블명이 대소문자를 구분하는 이유

    리눅스에서 MySQL 데이터베이스 테이블명이 대소문자를 구분하는 이유는 주로 MySQL의 기본 파일 시스템과 데이터베이스 관리 방식과 관련이 있습니다. 이러한 특징은 MySQL이 다양한 운영체제에서 동작할 수 있도록 설계된 데이터베이스 관리 시스템의 특성에 기인합니다.

    1. 파일 시스템의 대소문자 구분: 리눅스는 대소문자를 엄격하게 구분하는 파일 시스템을 사용합니다. 이로 인해 파일이나 디렉토리의 이름이 대소문자에 민감하게 반응하며, MySQL 역시 파일 시스템을 이용하여 테이블과 데이터를 저장하기 때문에 테이블명도 대소문자를 엄격하게 구분하게 됩니다.
    2. 표준화와 일관성: MySQL은 ANSI SQL 표준을 따르기 위해 테이블과 열의 이름을 구분하기 위해 대소문자를 구분합니다. 이는 데이터베이스와 테이블을 구별하기 위한 표준화된 방식을 유지하기 위함이며, 데이터베이스 사용자가 일관성 있는 작업을 할 수 있도록 도와줍니다.
    3. 이식성과 호환성: MySQL은 여러 운영체제와 통합되는 데이터베이스 관리 시스템입니다. 이식성을 갖추기 위해 다양한 운영체제의 파일 시스템 특성을 반영하여 대소문자를 구분하게 됩니다.

    대소문자 구분은 데이터베이스 시스템과 데이터의 일관성과 표준을 유지하면서, 다양한 환경에서 데이터베이스를 안정적으로 동작시키기 위한 중요한 요소 중 하나입니다.



MySQL 테이블 및 데이터베이스 이름 대소문자 구분 설정 변경

1
2
3
4
5
6
7
8
9
10
# 현재 설정 값을 확인합니다.
MariaDB [(none)]> show variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.002 sec)
  • lower_case_table_names 0
    • CREATE TABLE이나 CREATE DATABASE 실행 시 디스크에 저장되는 테이블과 데이터베이스의 이름을 대소문자 구분하여 생성합니다.
    • SELECT 나 INSERT 사용 시에도 대소문자를 구분해서 사용해야 합니다.
  • lower_case_table_names 1
    • 테이블과 DB 이름을 소문자로 생성하며 참조시에는 소문자로 변경하여 처리합니다.
    • 대소문자 구분 X
    • 기존에 대문자가 포함되어 생성한 테이블과 DB는 문제가 될 수 있습니다.
  • lower_case_table_names 2
    • CREATE TABLE이나 CREATE DATABASE 실행 시 디스크에 저장되는 테이블과 데이터베이스의 이름을 대소문자를 구분해서 생성합니다.
    • 참조시에는 소문자로 변경한다 대소문자를 구분하지 않는 팡일 시스템을 가진 OS(Mac OS X)에서만 동작합니다.

설정 변경

1
2
3
4
5
6
7
8
9
10

vi /etc/my.cnf.d/mariadb-server.cnf

[mysqld]
...
lower_case_table_names = 1
...

sudo systemctl restart mariadb

Reference

  • https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html