Spring Boot - multiple datasource - hbm2ddl issue

Question

I have multiple datasources defined in my Spring Boot (version 2.1.3.Final) app, and for one of these I am setting hbm2ddl to update. However, if I ran the application for the second time, it always tries to execute CREATE TABLE statement (instead of alter, or no statement in case of no change in entity)

The datasource definition look like this:

    // @formatter:off
@EnableJpaRepositories(entityManagerFactoryRef = "triggerEMF", transactionManagerRef = "triggerTM", basePackages = {
        "com.customer.trigger.repository" }, excludeFilters = @Filter(CDHRepository.class))
// @formatter:on
@Configuration
@EnableTransactionManagement
public class TriggerDSConfig {

    private static final Logger LOGGER = LoggerFactory.getLogger(TriggerDSConfig.class);

    @Autowired
    private Environment env;

    @Primary
    @Bean(name = "triggerDS")
    @ConfigurationProperties("trigger.datasource")
    public DataSource dataSource() {

        return DataSourceBuilder.create().build();

    }

    @Primary
    @Bean(name = "triggerTM")
    public PlatformTransactionManager psqlTransactionManager(@Qualifier("triggerEMF") EntityManagerFactory customerEntityManagerFactory) {
        return new JpaTransactionManager(customerEntityManagerFactory);

    }

    @Primary
    @Bean(name = "triggerEMF")
    public LocalContainerEntityManagerFactoryBean psqlEntityManager(EntityManagerFactoryBuilder builder) throws SQLException {
        Map<String, String> props = new HashMap<String, String>();
        props.put("hibernate.hbm2ddl.auto", env.getProperty("trigger.hbm2ddl"));
        props.put("hibernate.dialect", env.getProperty("trigger.dialect"));
        props.put("hibernate.default_schema", "public");

        DataSource ds = dataSource();
        ds.getConnection(); // eager connection pool init

        return builder.dataSource(ds) //
                .packages("com.customer.trigger.model.entity") //
                .persistenceUnit("trigger-pu") //
                .properties(props) //
                .build(); //

    }

    // Initialise & Populate DS in Local profile
    @Bean
    @Profile({ "local" })
    public DataSourceInitializer triggerEngineDBInit(@Qualifier("triggerDS") DataSource datasource) {

        ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
        resourceDatabasePopulator.addScript(new ClassPathResource("db/schema-triggerengine.sql"));
        resourceDatabasePopulator.addScript(new ClassPathResource("db/data-triggerengine.sql"));

        DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
        dataSourceInitializer.setDataSource(datasource);
        dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
        dataSourceInitializer.setEnabled(env.getProperty("trigger.datasource.initialize", Boolean.class, false));
        return dataSourceInitializer;
    }

}

The YAML definition of this datasource look like this:

    trigger:
  datasource:
    jdbcUrl: jdbc:h2:~/triggerdb2;DB_CLOSE_ON_EXIT=FALSE;MODE=PostgreSQL
    username: sa
    password: sa
  hbm2ddl: update
  dialect: org.hibernate.dialect.H2Dialect

I can confirm that DDL scripts are not executed. It's hbm2ddl which is failing. I was debugging it quite deep and I've got as far as this class: https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/tool/schema/internal/GroupedSchemaMigratorImpl.java

And this is the problematic piece of code: https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/tool/schema/internal/GroupedSchemaMigratorImpl.java#L69-L71

The table information is null - so hibernate generate create table statement (instead of alter or no statement). But of course the table exists, so this fails with following error:

            at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277)
        at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
        at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:183)
        at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
        at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:310)
        at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:467)
        at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:939)
        at org.springframework.orm.jpa.vendor.SpringHibernateJpaPersistenceProvider.createContainerEntityManagerFactory(SpringHibernateJpaPersistenceProvider.java:57)
        at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.createNativeEntityManagerFactory(LocalContainerEntityManagerFactoryBean.java:365)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.buildNativeEntityManagerFactory(AbstractEntityManagerFactoryBean.java:390)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.afterPropertiesSet(AbstractEntityManagerFactoryBean.java:377)
        at org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean.afterPropertiesSet(LocalContainerEntityManagerFactoryBean.java:341)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1821)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1758)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:593)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:515)
        at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:320)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:222)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:318)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:199)
        at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1105)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:867)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:549)
        at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:142)
        at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:775)
        at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:316)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1260)
        at org.springframework.boot.SpringApplication.run(SpringApplication.java:1248)
        at com.customer.trigger.Application.main(Application.java:11)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.boot.maven.AbstractRunMojo$LaunchRunner.run(AbstractRunMojo.java:558)
        at java.lang.Thread.run(Thread.java:748)
Caused by: org.h2.jdbc.JdbcSQLException: Table "SUGGESTION_VOD__C" already exists; SQL statement:

This issue occurs with PostgreSQL as well (not just H2)

Any ideas?

Reproducer:

https://github.com/agiertli/spring-boot-hbm-ddl-issue

Answer 1

After removing following yaml configuration:

       # temp:
        #  use_jdbc_metadata_defaults: "false"

The issue no longer occurs. Of course, now we are receiving strange CLOB related errors, but they do not seem to have a real impact so that's a separate issue.

Answer 2

I ran your project in my local. you are correct when I ran twice I got the table already exist error. and solution was

Map<String, String> props = new HashMap<String, String>();
props.put("hibernate.hbm2ddl.auto", "update");
props.put("hibernate.dialect", env.getProperty("trigger.dialect"));
props.put("hibernate.default_schema", env.getProperty("trigger.schema"));

you are maybe missing some property in your yml. Also use @Value for these properties.

Answer 3

i read your problem. the problem is not depend on database and I think your problem is that you don't use database version control for your project. the version control handle the all of these things for you and(it knows when to create or alter and very much more).

these are good database version control that you can use liquibase flyway

my opinion is to use liquibase because its easier with very good documentation