Spring-boot: create-drop does not work with Apache Derby

[king-h]

Using Spring Boot 1.4.2 in combination with auto-generated data sources for a Derby EmbeddedDatabase provokes "Schema '' does not exist" exceptions when auto-creating and executing the DDL towards Derby.

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
Caused by: java.sql.SQLSyntaxErrorException: Schema 'SA' does not exist
Caused by: org.apache.derby.iapi.error.StandardException: Schema 'SA' does not exist

How to recreate:

Setup a Maven project with the following dependencies:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>org.apache.derby</groupId>
        <artifactId>derby</artifactId>
    </dependency>
</dependencies>

using a Spring Boot application with the following annotations:

@SpringBootApplication
public class StudyDayApplication {
    /**
     * This main is for using Spring Boot in case of a JAR file packaging.
     * 
     * @param args
     */
    public static void main(String[] args) {
        SpringApplication.run(StudyDayApplication.class, args);
    }
}

Configure an "application.properties" file containing:

# PROFILES
spring.profiles.active=dev

and an "application-dev.properties" file containing:

# JPA (JpaBaseConfiguration, HibernateJpaAutoConfiguration)
spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.database=default
spring.jpa.show-sql=true
spring.jpa.hibernate.naming.strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl

# DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)
spring.datasource.continue-on-error=false
spring.datasource.generate-unique-name=false

Having at least one Entity mapping available will provoke the above error with the following auto-generated parts:

• Derby URL: url=jdbc:derby:memory:testdb;create=true
• Derby username: username=sa

Adding:
spring.datasource.username=<username>

leads to another Derby username. When configuring:
spring.datasource.generate-unique-name=true

we get cryptic schema names within the Derby URL replacing the above 'testdb' default value as expected. Both it does not help to get rid of the error.

Important: When switching the default "spring.jpa.hibernate.ddl-auto" from "create-drop" to "update" the DDL execution works. But: according to the Spring Boot documentation, "create-drop" is the default setting for embedded databases (like DERBY) - even when not having configured "spring.jpa.hibernate.ddl-auto" explicitly.

Once the DDL creation passed using "update" setting a subsequent run with "create-drop" works successfully then.

Is it a bug?

[snicoll]

Rather than pasting a whole project in a comment would you please share a project (repo url or zip file) that we can run to reproduce the issue?

[king-h]

@snicoll: here it is, the complete export of the minimum working example (MWE) located in Google Drive. An export of the STS Eclipse project. Please, find it here:
https://drive.google.com/file/d/0B6btT4vDRIz_RWoyVndkOVk3MVk/view?usp=sharing

It's configured with the "create-drop" setting explicitly so when executing, the error directly occurs. I'm really looking forward to reading from you again as I'm curious whether the bug will be accepted or not.

[snicoll]

Thanks. Here's a MWE that's a bit more reasonable: https://github.com/spring-projects/spring-boot-issues/tree/master/gh-7706

This is weird, I'll have a look

[snicoll]

Well ok, it is complicated. The FAQ of Apache Derby is very explicit about this:

So you will see the schema not exists error if your application tries to access the current schema before any objects have been created in it. Possibilities are you try to perform a DROP TABLE before creating it

That's exactly what happens with Hibernate. Our defaults to go create-drop here is problematic with Derby. Derby also has this special "APP" schema that is always here (the FAQ states that you should not rely on it).

If you add spring.datasource.username=app in your project, it will work. We're currently hard-coding "sa" for every embedded database.

I also wonder why we rely on create-drop for embedded database. After all the data should go away when the process completes...

[king-h]

@snicoll: thanks for that fast reply :-) Yesterday, I read the same FAQ but I did not recognize consiously the username change. Does it mean there will be a change handling the hard-coded "sa" user for every embedded database in a more dynamic way - or is it just about a Spring Boot documentation enhancement for embedded databases? Maybe, a default switch to "update" instead of "create-drop" might help, too.

Referring the Derby FAQ:

However even though the current schema is set to the user name, that schema may not exist. A schema is only created by CREATE SCHEMA or creating an object (table etc.) in that schema (this is implicit schema creation)

With:
spring.datasource.username=app

I get:
Caused by: org.apache.derby.iapi.error.StandardException: 'DROP TABLE' cannot be performed on 'STUDY' because it does not exist.
.

[snicoll]

I don't know yet.

Switching to "app" is not an option

[king-h]

Interesting: when switching to H2 it works flawlessly even in "create-drop" mode

[snicoll]

@king-h it's perfectly normal. The limitation we're discussing here is specific to Derby.

[umaj2ee]

Thank you...this post helped me .I was having create-drop showed ERROR . but resolved by updating the application.properties with

PROFILES

spring.profiles.active=dev

JPA (JpaBaseConfiguration, HibernateJpaAutoConfiguration)

spring.jpa.generate-ddl=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database=default
spring.jpa.show-sql=true
spring.jpa.hibernate.naming.strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl

DATASOURCE (DataSourceAutoConfiguration & DataSourceProperties)

spring.datasource.continue-on-error=false
spring.datasource.generate-unique-name=false
spring.datasource.username=app

[snicoll]

We're going to close this one in favour of the Apache Derby FAQ that has been referenced above already.