오류내역) org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT COUNT(*) CNT FROM ANIME] Caused by: java.sql.SQLSyntaxErrorException: Table 'teemo.anime' doesn't exist

[ BadSqlGrammarException ]

기존에 만들어준 DB를 MYSQL로 바꾼 후

부트에서 설정값을 세팅하고 실행하였으나

아래와 같은 오류가 발생하였다.

원인 후보(의심 지점) 포착

오류 메시지 첫 줄은 다음과 같았다.

org.springframework.jdbc.BadSqlGrammarException: 
StatementCallback; bad SQL grammar [SELECT COUNT(*) CNT FROM ANIME]

처음에는 SQL 문법이 잘못되었거나,

테이블명이 틀렸다고 생각할 수 있다.

하지만 이 에러는 스프링 JDBC에서 

자주 등장하는 포장 예외라서 진짜 원인은

보통 아래쪽 Caused by 구문에 숨어있다.

그래서 에러 로그를 끝까지 내려가며 원인을 확인했다.

---

[ 가설 수립: 'teemo에 문제가 있다' ]

로그 하단에서 다음 구문이 확인되었다.

Caused by: java.sql.SQLSyntaxErrorException: Table 'teemo.anime' doesn't exist

여기서 의심이 갔다.

• 내가 만들었던 스키마명은 animale_db인데
• 에러는 teemo.anime를 찾고 있다

즉, 애플리케이션이 animale_db가 아니라

teemo 스키마에 붙어버려서 해당 스키마에

ANIME 테이블이 없기 때문에

오류가 발생한 것으로 보였다.

 

가설 설정

→ datasource 설정이 teemo로 잡혀있어서,

→ teemo.ANIME를 조회하고 있다.

---

[ 확인: 현재 연결된 DB가 어디인지 검증 ]

가설을 확인하기 위해 application.properties를 확인했다.

그리고 datasource 설정에서 다음 내용을 발견했다.

spring.datasource.url=jdbc:mysql://localhost:3306/teemo

즉 스프링 부트는 실행 시점에

teemo 스키마로 연결되고 있었고,

DAO 쿼리(FROM ANIME)는 결국

teemo.ANIME를 조회하려고 했던 것이다.

 

그래서 teemo에 ANIME 테이블이 없으면

아래와 같은 에러가 나는 게 정상이다.

Table 'teemo.anime' doesn't exist

 

오류가 발생한 properties 설정 전체 코드

spring.application.name=animale
server.port=8088

spring.mvc.view.prefix=/WEB-INF/views/
spring.mvc.view.suffix=.jsp

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/teemo → 이 부분 때문에 에러 발생
spring.datasource.username=root
spring.datasource.password=1234

spring.servlet.encoding.charset=UTF-8
spring.servlet.encoding.enabled=true
spring.servlet.encoding.force=true

---

[ 해결: datasource url을 실제 스키마로 변경 ]

내가 실제로 사용하는 스키마는 animale_db이므로,
datasource url의 스키마명을 다음과 같이 변경했다.

변경 전
spring.datasource.url=jdbc:mysql://localhost:3306/teemo
↓
변경 후
spring.datasource.url=jdbc:mysql://localhost:3306/animale_db

설정 변경 후 부트를 재시작하면,

조회 대상이 teemo.ANIME가 아니라

animale_db.ANIME로 바뀌면서 오류가 해결된다.

---

[ 결론 ]

이번 BadSqlGrammarException은

SQL 문법 자체 문제가 아니라,

DB 연결 스키마가 잘못 잡혀서

존재하지 않는 테이블을 조회하면서 발생한 오류였다.

 

에러를 파악한 핵심은 아래 문장이다.

Table 'teemo.anime' doesn't exist

 

이 문구가 보이면, 쿼리보다 먼저

‘내가 지금 어느 스키마로 접속 중인지’를

가장 먼저 의심해야 한다.

 

※ 전체 오류 내역 더보기

[애니리스트 이동 로그] condition : [null]
[애니리스트 이동 로그] keyword : [null]
[애니리스트 이동 로그] isSearch : [false]
[애니리스트 이동 로그] 최종 condition : [ANIME_LIST_RECENT]
[애니리스트 이동 로그] 최종 keyword : [null]

[애니리스트 데이터 액션 로그] page : [1]
[애니리스트 데이터 액션 로그] condition : [ANIME_LIST_RECENT]
[애니리스트 데이터 액션 로그] keyword : [null]
[애니리스트 데이터 액션 로그] sort : [RECENT]
[애니리스트 데이터 액션 로그] sort 보정값 : [RECENT]

org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT COUNT(*) CNT FROM ANIME]
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:112)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:102)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1549)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:427)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:491)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:501)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:533)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:539)
at fourcheetah.animale.web.biz.impl.AnimeDAO.selectOne(AnimeDAO.java:284)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:158)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:135)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:719)
at fourcheetah.animale.web.biz.impl.AnimeDAO$$SpringCGLIB$$0.selectOne(<generated>)
at fourcheetah.animale.web.biz.impl.AnimeServiceImpl.getAnime(AnimeServiceImpl.java:24)
at fourcheetah.animale.web.controller.anime.AnimeListDataAction.execute(AnimeListDataAction.java:131)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:258)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:190)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:934)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:853)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:86)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:866)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1003)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:892)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:622)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:874)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:710)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:128)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:107)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:107)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:107)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:199)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:107)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:165)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:77)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:113)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:83)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:72)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:341)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:903)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1778)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:946)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:480)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:57)
at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.sql.SQLSyntaxErrorException: Table 'teemo.anime' doesn't exist
at cohttp://m.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112)
at cohttp://m.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114)
at cohttp://m.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1311)
at cohttp://m.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:111)
at cohttp://m.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:478)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:412)
... 59 more

[애니리스트 데이터 액션 로그] animeCount : [0]
[애니리스트 데이터 액션 로그] totalPage : [0]

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT
  anime_id            AS ANIME_ID,
  anime_title         AS ANIME_TITLE,
  anime_year          AS ANIME_YEAR,
  anime_quarter       AS ANIME_QUARTER,
  anime_thumbnail_url AS ANIME_THUMBNAIL_URL
FROM ANIME
ORDER BY anime_id DESC
LIMIT ?, ?]
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:112)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:102)
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1549)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:689)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:732)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:825)
at fourcheetah.animale.web.biz.impl.AnimeDAO.selectAll(AnimeDAO.java:249)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:359)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:158)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:135)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:719)
at fourcheetah.animale.web.biz.impl.AnimeDAO$$SpringCGLIB$$0.selectAll(<generated>)
at fourcheetah.animale.web.biz.impl.AnimeServiceImpl.getAnimeList(AnimeServiceImpl.java:19)
at fourcheetah.animale.web.controller.anime.AnimeListDataAction.execute(AnimeListDataAction.java:164)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:258)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:190)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:934)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:853)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:86)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:963)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:866)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1003)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:892)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:622)
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:874)
at jakarta.servlet.http.HttpServlet.service(HttpServlet.java:710)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:128)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:107)
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:107)
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:107)
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:199)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:116)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:107)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:165)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:77)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:113)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:83)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:72)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:341)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:397)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:903)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1778)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:946)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:480)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:57)
at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.sql.SQLSyntaxErrorException: Table 'teemo.anime' doesn't exist
at cohttp://m.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:112)
at cohttp://m.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:114)
at cohttp://m.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:988)
at cohttp://m.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1056)
at cohttp://m.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at cohttp://m.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.springframework.jdbc.core.JdbcTemplate.lambda$query$0(JdbcTemplate.java:738)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:670)
... 58 more