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

the hand of NOD · Accepted Answer · 2018-12-19 16:42:57Z

26

There are 2 other possible solutions you could try:

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

answered Dec 19, 2018 at 16:42

the hand of NOD

1,8192 gold badges21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cristianoms Over a year ago

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.

salerokada Over a year ago

For data insert unique key conflicts I used stackoverflow.com/a/19768315/4082981

Madhura Bhave · Accepted Answer · 2017-12-20 01:09:30Z

23

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.

edited Dec 20, 2017 at 1:09

answered Dec 20, 2017 at 0:53

Madhura Bhave

5014 silver badges6 bronze badges

3 Comments

Tora Tora Tora Over a year ago

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

HMarteau Over a year ago

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

Umesh Over a year ago

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

PeterB · Accepted Answer · 2023-04-12 14:36:55Z

4

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

edited Apr 12, 2023 at 14:36

answered Apr 12, 2023 at 14:20

PeterB

1,1318 silver badges11 bronze badges

Comments

Mohammed Masood Ansari · Accepted Answer · 2025-05-04 17:53:23Z

0

Go to schema.sql file and write query like as below queries

CREATE TABLE IF NOT EXISTS users(username varchar_ignorecase(50) not null primary key,password varchar_ignorecase(500) not null,enabled boolean not null);

CREATE TABLE IF NOT EXISTS authorities (username varchar_ignorecase(50) not null,authority varchar_ignorecase(50) not null,constraint fk_authorities_users foreign key(username) references users(username));

CREATE unique index IF NOT EXISTS ix_auth_username on authorities (username,authority);

answered May 4 at 17:53

Mohammed Masood Ansari

731 silver badge1 bronze badge

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related