When an application contains multiple DataSource beans, EntityManagerFactoryBuilder will default ddl-auto to a value that may only be appropriate for the primary DataSource

[randomComitter]

After updating Spring Boot from 3.3.5 to 3.4.3, the default ddl-auto create-drop will be applied to the non-embedded database if project has two databases, one of them is an embedded db and no explicit value is set for spring.jpa.hibernate.ddl-auto in application properties.
Is this a bug or an intended behavior?

The current documentation for Database Initialization mentions the following:

If an embedded database is identified and no schema manager (Flyway or Liquibase) has been detected, ddl-auto defaults to create-drop. In all other cases, it defaults to none.

which gives impression that create-drop will be applied by default only to the embedded database.
However, sometime between Spring Boot 3.3.5 and 3.4.3 a change was made so that default value create-drop for spring.jpa.hibernate.ddl-auto was applied to all data sources whether they were embedded or not (if at least one of them was embedded), which is dangerous behaviour and can lead to unexpected data loss in production databases.

[wilkinsona]

Please provide a minimal sample that reproduces the behavior you have described. There are too many unknowns at the moment in terms of how the two databases and the entity managers that depend on them are configured.

[randomComitter]

@wilkinsona thanks for your reply.

Sure, here's a minimal project that reproduces described behavior: https://github.com/randomComitter/dropcreate

[wilkinsona]

Thanks for the sample.

The change in behavior is due to this change. The ddl-auto setting (determined using the primary DataSource) is now applied to the auto-configured EntityManagerFactoryBuilder rather than only being applied to the auto-configured LocalContainerEntityManagerFactoryBean.

You're injecting the EntityManagerFactoryBuilder and using it to create both LocalContainerEntityManagerFactoryBeans in your app. As a result, they now have the same ddl-auto configuration as determined from the primary DataSource. You won't see the problem if you flip to ExternalDataSourceConfiguration defining the primary beans and qualify those defined by EmbeddedDataSourceConfiguration as the default ddl-auto will now by for an external DataSource.

I think the ideal here would be that configuring the DataSource on the builder results in the ddl-auto being updated if it hasn't already been explicitly configured. However, I'm not yet sure if that's possible.

[randomComitter]

Thank you very much for the explanations and the proposed solution.

Indeed, if I move @Primary annotation from EmbeddedDataSourceConfiguration to ExternalDataSourceConfiguration, the databases are no longer initialized.
However, it isn't immediately clear that a minor version update of Spring Boot can lead to data loss by simply altering the order of database configuration.
Given the statement in release notes: The auto-configured EntityManagerFactoryBuilder defines the native (e.g. Hibernate) properties as well - I wonder how many users would fully grasp the potential implications without deep knowledge of Spring Boot’s internals.

At least we didn’t expect that a minor version update would damage our database (luckily it was just a test database), which has made us more cautious about similar changes in the future.

My current understanding of the issue is as follows. If the primary data source is an embedded database and the property ddl-auto is not explicitly set, it defaults to create-drop. These properties are now passed on to the auto-configured EntityManagerFactoryBuilder. As a result, when configuring additional data sources using this builder, they inherit the primary data source’s properties, which might not always be desired or appropriate.

If my understanding is correct, this raises two important questions:

1. Is create-drop a sensible default? Since it can potentially lead to unintended data loss, it seems like a high-risk default.
2. Is it really a desired behavior, that additional data sources inherit defaults of the primary data source?

As an additional note which might be worth mentioning: when ExternalDataSourceConfiguration is Primary, then connecting to embedded H2 database isn't possible via the local console (http://localhost:8080/application/h2-console), even if these properties are still set: spring.h2.console.enabled=true and spring.h2.console.path=/application/h2-console.

[snicoll]

Thank you very much for the explanations and the proposed solution.

This is merely a workaround for the time being. As for the rest, this issue is opened and flagged for resolution in the next maintenance release of Spring Boot. This is a regression and we're going to fix it.

Please give it a try (in particular with your note about the H2 console) once a snapshot is available with the fix. I tried to run your sample but it requires to setup a MySQL instance. It would have been preferable to provide one with the sample (via a docker command or a docker compose file).