spring spring-data h2 spring-jdbc spring-test

Setting up Spring test env using H2 script initialization fails when inserting simple SQL data: "Unique index or primary key violation"

I have some older MySQL scripts which I migrated to H2 and now I want to initialize the test DB with that data.

First my src/test/resources/application.yaml:

spring:
    datasource:
        url: jdbc:h2:mem:bbstatstest
        username: sa
        password: sa
        driverClassName: org.h2.Driver
    jpa:
        hibernate:
            ddl-auto: none
            naming:
                physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
        open-in-view: false
        properties:
            hibernate:
                format_sql: true
        show-sql: true
    sql:
        init:
            mode: always

Spring config bean:

@SpringBootTest
@Sql({"/schema.sql", "/data.sql"}) // put files into src/test/resources 
class BasketballStatsSpringApplicationTests {

    @Test
    void contextLoads() {
    }
}

As you can see I use schema.sql and data.sql to get the job done (location: src/test/resources).

Create schema + tables (schema.sql):

CREATE SCHEMA IF NOT EXISTS bbstatstest;

CREATE TABLE IF NOT EXISTS bbstatstest.GeoContexts (
  id INT NOT NULL,
  parent_id INT NULL DEFAULT NULL,
  name VARCHAR(50) NOT NULL,
  type ENUM('CONTINENT', 'COUNTRY', 'REGION', 'STATE', 'DISTRICT') NULL DEFAULT NULL,
  PRIMARY KEY (id),
  CONSTRAINT geocontexts_self_fk
    FOREIGN KEY (parent_id)
    REFERENCES bbstatstest.GeoContexts (id)
    ON DELETE NO ACTION
    ON UPDATE CASCADE);

Insert some continents (data.sql):

INSERT INTO bbstatstest.GeoContexts (id, parent_id, name) VALUES
(1, NULL, 'Africa'),
(2, NULL, 'Antarctica'),
(3, NULL, 'Asia'),
(4, NULL, 'Europe'),
(5, NULL, 'North America'),
(6, NULL, 'Oceania'),
(7, NULL, 'South America');

Results in:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [data.sql]: INSERT INTO bbstatstest.GeoContexts (id, parent_id, name) VALUES (1, NULL, 'Africa'), (2, NULL, 'Antarctica'), (3, NULL, 'Asia'), (4, NULL, 'Europe'), (5, NULL, 'North America'), (6, NULL, 'Oceania'), (7, NULL, 'South America')

    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:282)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254)
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:54)
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.execute(ResourceDatabasePopulator.java:269)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.lambda$executeSqlScripts$9(SqlScriptsTestExecutionListener.java:362)
    at org.springframework.transaction.support.TransactionOperations.lambda$executeWithoutResult$0(TransactionOperations.java:68)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
    at org.springframework.transaction.support.TransactionOperations.executeWithoutResult(TransactionOperations.java:67)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:362)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.lambda$executeSqlScripts$4(SqlScriptsTestExecutionListener.java:275)
    at java.base/java.lang.Iterable.forEach(Iterable.java:75)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:275)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:222)
    at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.beforeTestMethod(SqlScriptsTestExecutionListener.java:165)
    at org.springframework.test.context.TestContextManager.beforeTestMethod(TestContextManager.java:320)
    at org.springframework.test.context.junit.jupiter.SpringExtension.beforeEach(SpringExtension.java:240)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: Unique index or primary key violation: "PRIMARY KEY ON BBSTATSTEST.GEOCONTEXTS(ID) ( /* key:1 */ 1, NULL, 'Africa', NULL)"; SQL statement:
INSERT INTO bbstatstest.GeoContexts (id, parent_id, name) VALUES (1, NULL, 'Africa'), (2, NULL, 'Antarctica'), (3, NULL, 'Asia'), (4, NULL, 'Europe'), (5, NULL, 'North America'), (6, NULL, 'Oceania'), (7, NULL, 'South America') [23505-224]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:520)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
    at org.h2.message.DbException.get(DbException.java:223)
    at org.h2.message.DbException.get(DbException.java:199)
    at org.h2.mvstore.db.MVPrimaryIndex.add(MVPrimaryIndex.java:120)
    at org.h2.mvstore.db.MVTable.addRow(MVTable.java:519)
    at org.h2.command.dml.Insert.insertRows(Insert.java:174)
    at org.h2.command.dml.Insert.update(Insert.java:135)
    at org.h2.command.dml.DataChangeStatement.update(DataChangeStatement.java:74)
    at org.h2.command.CommandContainer.update(CommandContainer.java:169)
    at org.h2.command.Command.executeUpdate(Command.java:256)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:262)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:231)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:261)
    ... 17 more

The scripts are obviously picked up and executed by ScriptUtils.executeSqlScript.

I keep staring at this for more than two hours now, but I don't get why this fairly simple stuff is causing me problems.

What's wrong? How do you fix this?

I'm using Spring 6.1.5: spring-jdbc-6.1.5 and H2: h2-2.2.224

Solution

A Spring Boot Application, as documented will automatically use the schema.sql and data.sql to populate the database.

Adding an @Sql as you did will now execute the schema.sql and data.sql again. As your schema.sql only creates tables if they don't exist (and no drop etc. in there) the tables are thus already populated upon start of the Spring Boot Application (that is what @SpringBootTest does it bootstraps a full application). Now it tries to insert the data again and thus yields an error.