[AWS, JPA, MySQL] Table doesn't exist

환경

- Spring Boot 3.3.1

- OS : Ubuntu 24.04 LTS (GNU/Linux 6.8.0-1008-aws x86_64) (프리티어)

- DB : MySQL 8.X

 

문제 상황

ubuntu:~/spring-ml-practice/build/libs$ sudo java -jar spring-ml-practice-0.0.1-SNAPSHOT.jar

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _ | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/

 :: Spring Boot ::                (v3.3.1)

2024-06-29T21:08:19.056Z  INFO 5768 --- [spring-ml-practice] [           main] C.s.SpringMlPracticeApplication          : Starting SpringMlPracticeApplication v0.0.1-SNAPSHOT using Java 17.0.11 with PID 5768 (/home/ubuntu/spring-ml-practice/build/libs/spring-ml-practice-0.0.1-SNAPSHOT.jar started by root in /home/ubuntu/spring-ml-practice/build/libs)
2024-06-29T21:08:19.071Z  INFO 5768 --- [spring-ml-practice] [           main] C.s.SpringMlPracticeApplication          : No active profile set, falling back to 1 default profile: "default"
2024-06-29T21:08:21.205Z  INFO 5768 --- [spring-ml-practice] [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2024-06-29T21:08:21.413Z  INFO 5768 --- [spring-ml-practice] [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 179 ms. Found 6 JPA repository interfaces.
2024-06-29T21:08:23.227Z  INFO 5768 --- [spring-ml-practice] [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 80 (http)
2024-06-29T21:08:23.264Z  INFO 5768 --- [spring-ml-practice] [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2024-06-29T21:08:23.266Z  INFO 5768 --- [spring-ml-practice] [           main] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.25]
2024-06-29T21:08:23.629Z  INFO 5768 --- [spring-ml-practice] [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2024-06-29T21:08:23.634Z  INFO 5768 --- [spring-ml-practice] [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 4422 ms
2024-06-29T21:08:24.834Z  INFO 5768 --- [spring-ml-practice] [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2024-06-29T21:08:25.044Z  INFO 5768 --- [spring-ml-practice] [           main] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 6.5.2.Final
2024-06-29T21:08:25.145Z  INFO 5768 --- [spring-ml-practice] [           main] o.h.c.internal.RegionFactoryInitiator    : HHH000026: Second-level cache disabled
2024-06-29T21:08:26.039Z  INFO 5768 --- [spring-ml-practice] [           main] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2024-06-29T21:08:26.126Z  INFO 5768 --- [spring-ml-practice] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2024-06-29T21:08:27.060Z  INFO 5768 --- [spring-ml-practice] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection com.mysql.cj.jdbc.ConnectionImpl@56299b0e
2024-06-29T21:08:27.063Z  INFO 5768 --- [spring-ml-practice] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2024-06-29T21:08:27.289Z  WARN 5768 --- [spring-ml-practice] [           main] org.hibernate.orm.deprecation            : HHH90000025: MySQLDialect does not need to be specified explicitly using 'hibernate.dialect' (remove the property setting and it will be selected by default)
2024-06-29T21:08:29.423Z  INFO 5768 --- [spring-ml-practice] [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000489: No JTA platform available (set 'hibernate.transaction.jta.platform' to enable JTA platform integration)
2024-06-29T21:08:29.433Z  INFO 5768 --- [spring-ml-practice] [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2024-06-29T21:08:30.333Z  INFO 5768 --- [spring-ml-practice] [           main] o.s.d.j.r.query.QueryEnhancerFactory     : Hibernate is in classpath; If applicable, HQL parser will be used.
2024-06-29T21:08:31.922Z  WARN 5768 --- [spring-ml-practice] [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2024-06-29T21:08:33.046Z  INFO 5768 --- [spring-ml-practice] [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 80 (http) with context path '/'
2024-06-29T21:08:33.110Z  INFO 5768 --- [spring-ml-practice] [           main] C.s.SpringMlPracticeApplication          : Started SpringMlPracticeApplication in 15.326 seconds (process running for 16.976)
2024-06-29T21:08:33.120Z  INFO 5768 --- [spring-ml-practice] [           main] C.s.config.DatabaseConnectionCheck       : Database connected successfully.
2024-06-29T21:08:44.795Z  INFO 5768 --- [spring-ml-practice] [p-nio-80-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring DispatcherServlet 'dispatcherServlet'
2024-06-29T21:08:44.796Z  INFO 5768 --- [spring-ml-practice] [p-nio-80-exec-1] o.s.web.servlet.DispatcherServlet        : Initializing Servlet 'dispatcherServlet'
2024-06-29T21:08:44.800Z  INFO 5768 --- [spring-ml-practice] [p-nio-80-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 3 ms
Hibernate: 
    select
        m1_0.member_email,
        m1_0.activity_level,
        m1_0.height,
        m1_0.name,
        m1_0.password,
        m1_0.sex,
        m1_0.weight 
    from
        members m1_0 
    where
        m1_0.member_email=?
2024-06-29T21:08:45.363Z  WARN 5768 --- [spring-ml-practice] [p-nio-80-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1146, SQLState: 42S02
2024-06-29T21:08:45.363Z ERROR 5768 --- [spring-ml-practice] [p-nio-80-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Table 'test.members' doesn't exist

 

주요한 부분은 아래와 같다.

2024-06-29T21:08:44.800Z  INFO 5768 --- [spring-ml-practice] [p-nio-80-exec-1] o.s.web.servlet.DispatcherServlet        : Completed initialization in 3 ms
Hibernate: 
    select
        m1_0.member_email,
        m1_0.activity_level,
        m1_0.height,
        m1_0.name,
        m1_0.password,
        m1_0.sex,
        m1_0.weight 
    from
        members m1_0 
    where
        m1_0.member_email=?
2024-06-29T21:08:45.363Z  WARN 5768 --- [spring-ml-practice] [p-nio-80-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1146, SQLState: 42S02
2024-06-29T21:08:45.363Z ERROR 5768 --- [spring-ml-practice] [p-nio-80-exec-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : Table 'test.members' doesn't exist

 

test.member 테이블을 찾을 수 없다는 상황이다.

 

로컬 개발 환경에서는 h2 DB를 사용하였고, 실제 배포할 때는 MySQL을 사용했다.

h2에서는 아무 문제가 없었는데, 리눅스 환경과 MySQL을 사용하고나니 이런 상황이 발생하여 매우 당황했다.

 

members 테이블의 생성 쿼리는 아래와 같다.

CREATE TABLE Members (
    member_email VARCHAR(255) PRIMARY KEY,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    sex INT NOT NULL,
    activity_level INT NOT NULL,
    weight INT NOT NULL,
    height INT NOT NULL
);

 

JPA 엔티티 클래스 코드는 아래와 같다.

@Entity
@Table(name = "Members")
@Getter @Setter
public class Member {
    @Id
    private String memberEmail;
    private String password;
    private String name;
    private int sex;
    private int activityLevel;
    private int weight;
    private int height;
}

맨 처음에 이 문제를 맞닥뜨렸을 때는 도대체 뭐가 문제인지 파악하기 힘들었다.

 

원인

주요 원인은 내 배포 환경인 우분투에서는 MySQL이 대소문자를 확실히 구분한다는 것이다.

mysql> SHOW VARIABLES LIKE 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.01 sec)

MySQL의 lower_case_table_names 설정이 0이므로, 대소문자를 구분하게 되어 있다.

엔티티 클래스의 테이블 이름 매핑은 Members 로 되어있는데, 실제 DB에 저장되어있는 테이블 명은 members이기 때문이다.

하지만, 엔티티 클래스의 테이블 이름 매핑은 members 로 소문자로 바꿔 봤지만 해결되지 않았다. 

 

해결 방법

현재 테이블 이름은 Members이고, MySQL에서는 테이블 이름을 대소문자를 구분하여 처리하고 있다.

MySQL의 lower_case_table_names 설정이 0이므로, 대소문자를 구분하게 되어 있다.

따라서 엔티티 클래스에서 테이블 이름을 소문자로 변경하는 것이 필요하다.

이 뿐만이 아니라 DB에 DDL 쿼리를 날릴 때, 테이블 명 또한 소문자로 변경이 필요하다.

-- 테이블 명 소문자로 변경
CREATE TABLE members (
    member_email VARCHAR(255) PRIMARY KEY,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    sex INT NOT NULL,
    activity_level INT NOT NULL,
    weight INT NOT NULL,
    height INT NOT NULL
);

 

@Entity
@Table(name = "members") //매핑 테이블명 소문자로 변경
@Getter @Setter
public class Member {
    @Id
    private String memberEmail;
    private String password;
    private String name;
    private int sex;
    private int activityLevel;
    private int weight;
    private int height;
}

 

테이블 명과 컬럼명 모두 소문자로 변경하고나서 정상적으로 테이블을 찾았다.

 

정확한 원인은 조금 더 파악해야할 것 같다.