Spring Boot datasource initialization error with data.sql script after 2.5.0 upgrade

Question

I have a project configured with Spring Boot and Spring Data JPA.

My project contains the following data.sql initialization script (I'm just starting developing my application that's why I use an embedded H2 database):

INSERT INTO Project(id, name) VALUES (1, 'Project 1');

And I'm defining the following entity:

@Entity
public class Project {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    protected Project() {
    }

    public Project(String name) {
        this.name = name;
    }
    // ...
}

I don't have any other configuration/setup in place.

ERROR 5520 ---[  restartedMain] o.s.boot.SpringApplication               :Application run failed

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 #1 of URL [file:/C:/path/project/target/classes/data.sql]: INSERT INTO Project(id, name) VALUES (1, 'Project 1'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "PROJECT" not found; SQL statement:
INSERT INTO Project(id, name) VALUES (1, 'Project 1') [42102-200]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1786)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602)
...
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of URL [file:/C:/path/project/target/classes/data.sql]: INSERT INTO Project(id, name) VALUES (1, 'Project 1'); nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "PROJECT" not found; SQL statement:
INSERT INTO Project(id, name) VALUES (1, 'Project 1') [42102-200]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:622)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254)
...
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "PROJECT" not found; SQL statement:
INSERT INTO Project(id, name) VALUES (1, 'Project 1') [42102-200]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)

Of course, before the upgrade, the application was starting just fine.

Answer 1

TL;DR

It seems Spring Boot has modified how it initializes the DataSource with SQL scripts with 2.5.0.

This can be fixed by including the following property in the project:

spring:
  jpa:
    defer-datasource-initialization: true

The explanation:

Among the changes introduced in 2.5.0, it seems now the data.sql script is executed before Hibernate is initialized:

https://github.com/spring-projects/spring-boot/wiki/Spring-Boot-2.5-Release-Notes#hibernate-and-datasql

And since I'm relying on the ORM mechanism (i.e. Hibernate) to create the schema from the entity definition, the database table does not exist at the moment the initialization SQL script is executed.

Answer 2

Add "spring.jpa.defer-datasource-initialization = true" to application.properties file.

Reason: 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. While mixing database initialization technologies is not recommended, this will also allow you to use a schema.sql script to build upon a Hibernate-created schema before it’s populated via data.sql.

Source: https://github.com/spring-projects/spring-boot/wiki/Spring-Boot-2.5-Release-Notes#hibernate-and-datasql

Answer 3

It seems that you can make ORM vendor create the schema for you, and after that import the data by data.sql

To achieve that you also need another property: spring.sql.init.mode: always

My total set of relevant properties are the following:

spring:
  sql.init.mode: always
  datasource:
    url: jdbc:postgresql://localhost:5432/products
    username: 
    password: 
  jpa:
    defer-datasource-initialization: true
    hibernate:
      ddl-auto: create-drop
    database-platform: org.hibernate.dialect.PostgreSQLDialect

Answer 4

Hope it help somebody. I was also having the same issue, my data.sql file was not picked so only blank tables were generating. I used below mentioned properties and it worked for me:

    spring.sql.init.mode=always
    spring.datasource.url= jdbc:mysql://${MYSQL_CONTAINER_NAME:localhost}:3306/${MYSQL_DATABASE:uber}
    spring.datasource.username=${MYSQL_USER:boca-user}
    spring.datasource.password=${MYSQL_PASSWORD:boca-password}
    spring.datasource.initialization-mode=always
    spring.jpa.show-sql=true
    spring.jpa.defer-datasource-initialization = true
    spring.jpa.hibernate.ddl-auto=update