Spring Boot 2 - H2 Database - @SpringBootTest - Failing on org.h2.jdbc.JdbcSQLException: Table already exists

Question

Unable to test Spring Boot & H2 with a script for creation of table using schema.sql.

So, what’s happening is that I have the following properties set:

spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.initialization-mode=always
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.platform=h2
spring.datasource.url=jdbc:h2:mem:city;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.generate-ddl=false
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true

and, I expect the tables to be created using the schema.sql. The application works fine when I run gradle bootRun. However, when I run tests using gradle test, my tests for Repository passes, but the one for my Service fails stating that it’s trying to create the table when the table already exists:

Exception raised:

Caused by: org.h2.jdbc.JdbcSQLException: Table "CITY" already exists;             
SQL statement:
CREATE TABLE city ( id BIGINT NOT NULL, country VARCHAR(255) NOT NULL, map VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, state VARCHAR(2555) NOT NULL, PRIMARY KEY (id) ) [42101-196]
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.ddl.CreateTable.update(CreateTable.java:117)
at org.h2.command.CommandContainer.update(CommandContainer.java:101)
at org.h2.command.Command.executeUpdate(Command.java:260)
at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:192)
at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:164)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:471)
... 105 more

The code is setup and ready to recreate the scenario. README has all the information -> https://github.com/tekpartner/learn-spring-boot-data-jpa-h2

Parlin

Answer 1

If the tests are run individually, they pass. I think the problem is due to schema.sql being executed twice against the same database. It fails the second time as the tables already exist.

As a workaround, you could set spring.datasource.continue-on-error=true in application.properties.

Another option is to add the @AutoConfigureTestDatabase annotation where appropriate so that a unique embedded database is used for each test.

Gentleman

Using @AutoConfigureTestDatabase solved it. Thanks for taking the time. – Parlin

Same, using @AutoConfigureTestDatabase solved it. I was strugling on it for 3hours, thank you – Farthingale

Thanks, these two properties solved my problem too. But, why are the SQLs executed twice within the same application context? – Fibrinolysin

Answer 2

There are 2 other possible solutions you could try:

1. Add a drop table if exists [tablename] in your schema.sql before you create the table.
2. Change the statement from CREATE TABLE to CREATE TABLE IF NOT EXISTS

Monocyte

Your solution worked best for me because it also resolves the problem that comes after that one, that has to do with data insertion causing unique keys conflicts. Thanks for that. – Carborundum

For data insert unique key conflicts I used https://mcmap.net/q/587491/-h2-sql-database-insert-if-the-record-does-not-exist – Wiper

Answer 3

When there is a name for the db, it remains in the memory while the JVM runs. Here the db name is "city":

spring.datasource.url=jdbc:h2:mem:city;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

So when you want to create a new db for every test class omit the db name:

spring.datasource.url=jdbc:h2:mem:;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

It is called "in memory private" connection mode: http://www.h2database.com/html/features.html#connection_modes

Damselfly