Spring boot + flyway + H2 in memory: Thrown by: org.h2.jdbc.JdbcSQLException: Table "xxxx" not found; SQL statement:
I am using H2 as a memory database in a spring boot project for unit testing. However I am getting below error
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:196)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1885)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1839)
at org.hibernate.loader.Loader.doQuery(Loader.java:910)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:355)
at org.hibernate.loader.Loader.doList(Loader.java:2554)
at org.hibernate.loader.Loader.doList(Loader.java:2540)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2370)
at org.hibernate.loader.Loader.list(Loader.java:2365)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:497)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:236)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1300)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:495)
at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getSingleResult(CriteriaQueryTypeQueryAdapter.java:71)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$SingleEntityExecution.doExecute(JpaQueryExecution.java:202)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:97)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:88)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:395)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:373)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.data.repository.core.support.RepositoryFactorySupport$DefaultMethodInvokingMethodInterceptor.invoke(RepositoryFactorySupport.java:486)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
... 80 more
Caused by: org.h2.jdbc.JdbcSQLException: Table "cm_user" not found; SQL statement:
select user0_.id as id1_6_, user0_.created_by as created16_6_, user0_.created_date as created_2_6_, user0_.last_modified_by as last_mo17_6_, user0_.last_modified_date as last_mod3_6_, user0_.status as status4_6_, user0_.version as version5_6_, user0_.avatar_name as avatar_n6_6_, user0_.avatar_url as avatar_u7_6_, user0_.dob as dob8_6_, user0_.email as email9_6_, user0_.failed_login_attempt as failed_10_6_, user0_.first_name as first_n11_6_, user0_.gender as gender12_6_, user0_.last_name as last_na13_6_, user0_.last_password_update_date as last_pa14_6_, user0_.password as passwor15_6_ from cm_user user0_ where user0_.email=? [42102-187]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:179)
at org.h2.message.DbException.get(DbException.java:155)
at org.h2.command.Parser.readTableOrView(Parser.java:5261)
at org.h2.command.Parser.readTableFilter(Parser.java:1235)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1874)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1983)
at org.h2.command.Parser.parseSelectSub(Parser.java:1868)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1689)
at org.h2.command.Parser.parseSelect(Parser.java:1677)
at org.h2.command.Parser.parsePrepared(Parser.java:433)
at org.h2.command.Parser.parse(Parser.java:305)
at org.h2.command.Parser.parse(Parser.java:277)
at org.h2.command.Parser.prepareCommand(Parser.java:242)
at org.h2.engine.Session.prepareLocal(Session.java:461)
at org.h2.engine.Session.prepareCommand(Session.java:403)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1189)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:72)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:277)
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
... 112 more
The configuration looks like this:
spring.datasource.url =jdbc:h2:mem:test;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE;DATABASE_TO_UPPER=false;INIT=create schema if not exists test;
spring.datasource.username = sa
spring.datasource.password =
spring.datasource.driverClassName = org.h2.Driver
# Specify the DBMS
spring.jpa.database =
# Show or not log for each sql query
spring.jpa.show-sql = false
#flyway.check-location=true
flyway.locations=classpath:db/migration/mysql
flyway.schemas=test
+4
source to share
2 answers
Combined with spring-boot, flyway and H2, I have some problematic issues for looking up tables in schemas other than the standard PUBLIC schema.
My fix for this problem:
-
add as first script
V1.0.1__create_schema.sql
create schema if not exists test;
-
use allways full qualifier in procedure, functions and statements
create table test.my_table;
Or use the default H2 schema for test databases.
flyway.schemas=PUBLIC
+5
source to share