Why Spring Boot 2.0 application does not run schema.sql?

Question

While I was using Spring Boot 1.5, on application startup Hibernate executed schema.sql file located in /resources folder when appropriate configuration is set. After Spring Boot 2.0 release this feature does not work any more. I couldn't find anything about this change in documentation. Here is my application.properties file content:

spring.datasource.url=...
spring.datasource.username=...
spring.datasource.password=...

spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

Is there some change in Spring Boot 2.0 or is this an bug/issue?

Answer 1

Check the documents here.

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.

You have spring.jpa.hibernate.ddl-auto=create-drop that's why schema.sql is not executed. Looks like this is the way Spring Boot works.

Edit

I think that the problem(not really a problem) is that your application points to a mysql instance.

See the current Spring Boot properties:

spring.datasource.initialization-mode=embedded # Initialize the datasource with available DDL and DML scripts.

The default value is embedded - e.g. initialize only if you're running and embedded database, like H2.

Also see the answer of Stephan here. He said:

Adding spring.datasource.initialization-mode=always to your project is enough.

So try to set:

spring.datasource.initialization-mode=always

Answer 2

Not embedded (e.g. MySQL)

If you load a database that is not embedded, in Spring Boot 2 you need to add:

spring.datasource.initialization-mode=always

Check the Migration Guide:

Database Initialization

Basic DataSource initialization is now only enabled for embedded data sources and will switch off as soon as you’re using a production database. The new spring.datasource.initialization-mode (replacing spring.datasource.initialize) offers more control.

---

Embedded (e.g. h2)

I once had a similar problem, even though it was an h2 (so it was an embedded DB), my h2 configuration was activated by a my-test profile.

My test class was like:

@RunWith(SpringRunner.class)
@SpringBootTest                     // does not work alone
@ActiveProfiles("my-test")
public class MyEntityRepositoryTest {

The problem is @SpringBootTest alone did not initialize the test database. I had to either use @DataJpaTest or @SpringBootTest+@AutoConfigureTestDatabase. Examples

@RunWith(SpringRunner.class)
@DataJpaTest                       // works
@ActiveProfiles("sep-test")
public class MyEntityRepositoryTest {

or

@RunWith(SpringRunner.class)
@SpringBootTest                     // these two
@AutoConfigureTestDatabase          // together work
@ActiveProfiles("sep-test")
public class MyEntityRepositoryTest {

Answer 3

Recent Update

As of Spring Boot Version 2.7

the property spring.datasource.initialization-mode has been removed.

You should from this version and onwards use the replacement property spring.sql.init.mode

Example: spring.sql.init.mode:always

Spring Boot 2.7 changelog

Answer 4

It works fine for me, you can try it. Set datasource type to what you like instead of HikariCP.

spring.datasource.initialization-mode=always
spring.datasource.type=com.mysql.jdbc.jdbc2.optional.MysqlDataSource
spring.jpa.hibernate.ddl-auto=none

Answer 5

There might be another problem causing data.sql not to be executed, If you don't config the spring.jpa.hibernate.ddl-auto=none, the data.sql will not be executed.

Answer 6

I was able to make application run only after excluding Hikary CP like that:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
    <exclusions>
        <exclusion>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
        </exclusion>
    </exclusions>
</dependency>

Please, see the issue here

Answer 7

For me, for some reason, Spring didn't find the schema.sql file in the classpath. I had to specify it explicitly for it to work:

spring.sql.init.schema-locations=classpath:/sql/schema.sql