Spring boot + flyway + H2 in memory :Caused by: org.h2.jdbc.JdbcSQLException: Table "xxxx" not found; SQL statement:

Question

I am using H2 as in 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

By configuration is as below

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

Answer 1

In combination of spring-boot, flyway and H2 I have some simular problems to find tables in other schemas than default PUBLIC schema.

My fix for this problem is:

• 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 H2 default schema for test databases instead.

 flyway.schemas=PUBLIC

Answer 2

This is a pretty old question but I've just bumped into this very same issue in 2021. I managed to sort it out by just adding DB_CLOSE_DELAY=-1 to the connection string as documented here:

Here is my application.yaml:

spring:
  flyway:
    url: jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1
    baseline-on-migrate: true
    user: sa
    password: