Spring Boot schema.sql - drop db schema on restart

Question

Hi I'm using Spring Boot version 1.5.9.

When using Spring Boot to initialize schema.sql for mysql database, it works all fine and the database schema is getting created successfully. But on restart of the application this schema.sql script is executing again and the application fails to start because the tables already exist.

I tried spring.jpa.hibernate.ddl-auto=create-drop option in application.properties but it does not have any effect (probably because it only works for Hibernate entities which I'm not using)

Is there a way to have Spring Boot to re-create schema from schema.sql every time on restart if the database is not in-memory one?

GitHub: https://github.com/itisha/spring-batch-demo/tree/database-input

Answer 1

According to the documentation you can simply ignore exceptions by setting spring.datasource.continue-on-error property to true

Spring Boot enables the fail-fast feature of the Spring JDBC initializer by default, so if the scripts cause exceptions the application will fail to start. You can tune that using spring.datasource.continue-on-error.

or even turn it off with spring.datasource.initialize set to false

You can also disable initialization by setting spring.datasource.initialize to false.

Answer 2

A workaround could be, to change the create statements in your schema.sql from

 CREATE TABLE test .....

to

 CREATE TABLE IF NOT EXISTS test ...

use the IF NOT EXISTS statements