CODEWITHSUNDEEP
springspring-boot
Carla
Carla

Reputation: 3390

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


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 ?

Upvotes: 4

Views: 13919

Answers (4)

Ilman Iqbal
Ilman Iqbal

Reputation: 21

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

Upvotes: 1

Pinocchio
Pinocchio

Reputation: 69

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.

Upvotes: 1

pcoates
pcoates

Reputation: 2307

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.

Upvotes: 0

santosh
santosh

Reputation: 116

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.

Upvotes: 5

Related Questions