Exception "org.h2.jdbc.JdbcSQLSyntaxErrorException:Table "NAME" not found " during initialization H2 database for testing Spring Boot application

Question

I write integration tests annotated with @SpringBootTest. To run tests I insert data to the H2 database from the data.sql in test resources.

I have a situation when the firstly tests run successfully, and after a number of times, I have an error, which tells me, that H2 cannot insert data in a table because that table is not found. The error comes with different raws every next launch of tests. In the same log, I see that this table was created and other insertions performed successfully before the exception happend. I see that 4 of my 5 tests are green and one is red. It can be a different test at different times.

A reboot of an IDE makes tests run successfully again 3-4 times. After that, the error returns.

I tried to use @DirtiesContext() with my test class and with test methods, but it didn't solve the problem.

I have a guess that the source of the problem may be in the way I initialize databases. For both databases I use a reference for one data.sql file. I didn't manage to find a way to separate them into different *.sql files.

The second guess is that insertions into databases begin before tables are created. I'm checking this theory now by moving all insertions into the test code. But I'm not sure that it'll help as I sow log that tables was created before insertion started.

I use "mvn clean" before every test run.

I would be very grateful for your recommendations in solving this problem.

My entities:

@Entity
@Table(name = "entity1", schema = "schema1")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class SomeEntityClass extends GenericEntity<Long> { ...}


@MappedSuperclass
public abstract class GenericEntity<ID extends Serializable> implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column

    protected ID id;

//getter, setter
}

In my data.sql file initialization looks like that:

create schema if not exists schema1;
create schema if not exists schema2; 

drop table if exists schema1.table1;
create table schema1.table1
(....structure of the table...)
 
drop table if exists schema2.table2;
create table schema2.table2
(....structure of the table...)
 
INSERT INTO schema1.table1
(...)
VALUES (...)
 
... many insertions
 
 
INSERT INTO schema2.table2
(...)
VALUES (...)
 
... many insertions

This is H2 configuration in application.properties in test/resources:

# The first database
spring.datasource.url=jdbc:h2:mem:database01;INIT=RUNSCRIPT FROM 'src/test/resources/data.sql'
spring.datasource.username=user1
spring.datasource.password=abc
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.generate-ddl=true

# The second database
spring.datasource.security.url=\
  jdbc:h2:mem:dbo_security_db:database02;INIT=RUNSCRIPT FROM 'src/test/resources/data.sql'
spring.datasource.security.username=user1
spring.datasource.security.password=abc
spring.datasource.security.driver-class-name=org.h2.Driver

spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.format_sql=true

Log with error:

08-09-2021 09:00:08.729 DEBUG [o.s.jdbc.datasource.init.ScriptUtils] - 0 returned as update count for SQL: create table schema1.table1( ….)
08-09-2021 09:00:08.730 DEBUG [o.s.jdbc.datasource.init.ScriptUtils] - 1 returned as update count for SQL: INSERT INTO schema1.table1(...) VALUES ()
///...other insertions into this table performed well
08-09-2021 09:00:08.735 WARN  [o.s.b.w.s.c.AnnotationConfigServletWebServerApplicationContext] - Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #36 of URL [file:/home/nikiforov-java/Documents/.../target/test-classes/data.sql]: INSERT INTO schema1.table1(...) VALUES (...); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "TABLE1" not found; SQL statement: ...

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
08-09-2021 09:00:08.782 ERROR [o.s.boot.SpringApplication] - Application run failed

Answer 1

After adding:

spring.jpa.defer-datasource-initialization=true

to your application.properties file, if you still have the same:

"Exception "org.h2.jdbc.JdbcSQLSyntaxErrorException:Table "NAME" not found exception, double check all your column names in your entity. Be sure you are not using reserved keywords, and if you are, add an alias using: @Column(name = "<YOUR NOT RESERVED KEYWORD COLUMN NAME>")

Answer 2

Add this property to application.properties:

spring.jpa.defer-datasource-initialization=true

According to Spring Boot 2.5 RELEASE notes:

By default, data.sql scripts are now run before Hibernate is initialized. This aligns the behavior of basic script-based initialization with that of Flyway and Liquibase. If you want to use data.sql to populate a schema created by Hibernate, set spring.jpa.defer-datasource-initialization to true.