Unable to execute data.sql and schema.sql at Spring Boot Startup

Question

I'm trying to execute some DB initialization for a Spring Boot application against a MySQL Database that is running in a container. During the authentication process, I receive an error "Table not found". I've checked the DB and no tables have been created indeed. Is there something missing in DB properties?

spring.datasource.url = jdbc:mysql://172.17.0.2:3306/schema
spring.datasource.username = user
spring.datasource.password = password
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = create-drop
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect
spring.data.rest.basePath=/
spring.datasource.data = classpath:/data.sql
spring.datasource.schema = classpath:/schema.sql

All in all, the JDBC Settings work fine provided that I create the DDL from the mysql command line. So it's just not executing the data.sql and schema.sql at startup. Do I need some extra properties for mysql ?

Have you defined your spring.datasource.driverclassname? — user4148098
– user4148098, Commented Nov 28, 2018 at 15:36
I have just tried adding the driverclasname but it didn't change. Also I'm using 'spring-boot-starter-data-jpa'. Does it make difference? — Carla
– Carla, Commented Nov 28, 2018 at 16:18
Are you certain that you provided the correct path to your sql files? Maybe you can try adding your sql files into the resources folder and remove the spring.datasource.data = classpath:/data.sql spring.datasource.schema = classpath:/schema.sql — Merve Sahin
– Merve Sahin, Commented Nov 28, 2018 at 16:26

santosh · Accepted Answer · 2018-12-01 14:45:36Z

5

You can update the application.properties with following properties.

application.properties:

spring.jpa.hibernate.ddl-auto=none
spring.jpa.generate-ddl=false
spring.datasource.initialization-mode=always

By default value of spring.jpa.generate-ddl is false. If you set spring.jpa.generate-ddl=true or spring.jpa.hibernate.ddl-auto to any of value validate, update, create, create-drop. Spring boot generates schema scripts and creates the tables based on the entities available in your application.

Now you should create the schema.sql and data.sql files under resources folder and set the property spring.datasource.initialization-mode=always in application.properties.

You can also run the scripts based on the platform. Let's suppose you want to run the scripts for hsqldb database, then set spring.datasource.platform=hsqldb in application.properties file and created scripts file schema-hsqldb.sql and data-hsqldb.sql under resources folder. You can find more details about how to load schema.sql and data.sql on startup with spring boot.

answered Dec 1, 2018 at 14:45

santosh

1165 bronze badges

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

2 Comments

PAA Over a year ago

Does this process works for any DB ? I dont see its working with these configurations. Any pointers here ?

Woodchuck Over a year ago

fyi, spring.datasource.initialization-mode=always seems now to have been replaced by spring.sql.init.mode=always (docs.spring.io/spring-boot/docs/current/reference/html/…)

Pinocchio · Accepted Answer · 2020-10-02 10:36:39Z

1

Everything works as expected until a spring-boot update the pom file from

<version>1.5.8.RELEASE</version>

to

<version>2.3.3.RELEASE</version>

A switch back and update all test are passed without any exceptions. In Version 2.3.3 a test fault caused by missing data in database. I've spend hours to solve the ddl initialization for Spring Version 2.3.3.RELEASE without find regular solution.

edited Oct 2, 2020 at 10:36

answered Aug 21, 2020 at 13:23

Pinocchio

693 bronze badges

Comments

Ilman Iqbal · Accepted Answer · 2022-07-25 07:13:02Z

1

For spring boot version 2.7, ensure that you have configured spring.sql.init.mode property in the application.properties file

Example:

spring.sql.init.mode=always
spring.jpa.hibernate.ddl-auto=update
spring.jpa.generate-ddl=true

edited Jul 25, 2022 at 7:13

answered Jul 25, 2022 at 7:12

Ilman Iqbal

214 bronze badges

Comments

Wytrzymały Wiktor · Accepted Answer · 2019-07-16 15:36:15Z

0

As you have

spring.jpa.hibernate.ddl-auto = create-drop

Spring doesn't run the schema.sql and data.sql.

Try it with

spring.jpa.hibernate.ddl-auto = none

Check the docs

In a JPA-based app, you can choose to let Hibernate create the schema or use schema.sql, but you cannot do both. Make sure to disable spring.jpa.hibernate.ddl-auto if you use schema.sql.

edited Jul 16, 2019 at 15:36

Wytrzymały Wiktor

14.4k6 gold badges32 silver badges48 bronze badges

answered Nov 28, 2018 at 16:17

pcoates

2,3271 gold badge12 silver badges21 bronze badges

1 Comment

Carla Over a year ago

Thanks, I have tried changing to 'none' but it still doesn't execute the SQL files.

Collectives™ on Stack Overflow

Unable to execute data.sql and schema.sql at Spring Boot Startup

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related