Spring Boot - multiple datasource - hbm2ddl issue

1
votes

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

3
javahibernatespring-bootdatasource
Can you create a test case in github? – Ekrem
Hi, I have added a link to reproducer in the initial post. README is included. – Anton Giertli
This might be a long shot, but can you verify that the value coming out of env.getProperty("trigger.hbm2ddl") is update? I am sure it is, but it would be nice to verify. – hooknc
Hi, I can confirm the value is update. This is visible when I print it to console and also, when I was debugging I was able to see hibernate was trying to execute update, but for some reason it couldn't see our tables, so it generated create-table statements. – Anton Giertli

3 Answers

0
votes

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.

0
votes

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.

-2
votes

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