[FIXED] Liqubase multitenacy issue on tenant schema update based on change log

Issue

as a requirement I have a spring boot project that uses multi tenant based on schema, when I run the application the migration goes fine on master schema(public), but when it tries to apply changes to all tenants (other schemes) it returns an exception that the table from sql script already exists even if the schema is empty:

18:15:31.006 [main] TRACE o.s.c.i.s.SpringFactoriesLoader - Loaded [org.springframework.boot.diagnostics.FailureAnalysisReporter] names: [org.springframework.boot.diagnostics.LoggingFailureAnalysisReporter]
18:15:31.007 [main] ERROR o.s.boot.SpringApplication - Application run failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'tenantLiquibase' defined in class path resource [com/*/dao/multitenancy/TenantLiquibaseConfig.class]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set sql/changelog/tenant/db.changelog-tenant-1.0.yaml::v20_tenant_ddl::vsuruceanu:
     Reason: liquibase.exception.DatabaseException: ERROR: relation "property" already exists
  Location: File: heap.c, Routine: heap_create_with_catalog, Line: 1162
  Server SQLState: 42P07 [Failed SQL: (0) create table property
(
    property_id                         bigserial not null
        constraint property_pk
            primary key,
    name                                varchar(100),
    address                             varchar(300),
    sticky_note                         varchar,
    expected_roi                        numeric,
    profile                             varchar(200),
    nickname                            varchar(200),
    condo                               varchar(200),
    condo_yearly_fees                   numeric,
    mortage_ammount                     numeric,
    closing_cost_ammount                numeric,
    earthquake_supplies_inventory       varchar(1000),
    earthquake_supplies_good_until_date date,
    ownership_type                      public.property_ownership_type,
    purchase_date                       date,
    purchase_amount                     numeric,
    purchase_vat                        numeric,
    purchase_solicitors                 varchar(1000)
)]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1794)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:594)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:516)
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:324)
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:226)
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:322)
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202)
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:897)
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:879)
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:551)
    at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:143)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:758)
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:750)
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:397)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:315)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
    at com.*.api.ApiApplication.main(ApiApplication.java:21)
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set sql/changelog/tenant/db.changelog-tenant-1.0.yaml::v20_tenant_ddl::vsuruceanu:
     Reason: liquibase.exception.DatabaseException: ERROR: relation "property" already exists
  Location: File: heap.c, Routine: heap_create_with_catalog, Line: 1162
  Server SQLState: 42P07 [Failed SQL: (0) create table property
(
    property_id                         bigserial not null
        constraint property_pk
            primary key,
    name                                varchar(100),
    address                             varchar(300),
    sticky_note                         varchar,
    expected_roi                        numeric,
    profile                             varchar(200),
    nickname                            varchar(200),
    condo                               varchar(200),
    condo_yearly_fees                   numeric,
    mortage_ammount                     numeric,
    closing_cost_ammount                numeric,
    earthquake_supplies_inventory       varchar(1000),
    earthquake_supplies_good_until_date date,
    ownership_type                      public.property_ownership_type,
    purchase_date                       date,
    purchase_amount                     numeric,
    purchase_vat                        numeric,
    purchase_solicitors                 varchar(1000)
)]
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:646)
    at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:53)
    at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:83)
    at liquibase.Liquibase.update(Liquibase.java:202)
    at liquibase.Liquibase.update(Liquibase.java:179)
    at liquibase.integration.spring.SpringLiquibase.performUpdate(SpringLiquibase.java:366)
    at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:314)
    at com.*.dao.multitenancy.liquibase.DynamicSchemaBasedMultiTenantSpringLiquibase.runOnAllSchemas(DynamicSchemaBasedMultiTenantSpringLiquibase.java:59)
    at com.*.dao.multitenancy.liquibase.DynamicSchemaBasedMultiTenantSpringLiquibase.afterPropertiesSet(DynamicSchemaBasedMultiTenantSpringLiquibase.java:52)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1853)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1790)
    ... 17 common frames omitted
Caused by: liquibase.exception.DatabaseException: ERROR: relation "property" already exists
  Location: File: heap.c, Routine: heap_create_with_catalog, Line: 1162
  Server SQLState: 42P07 [Failed SQL: (0) create table property
(
    property_id                         bigserial not null
        constraint property_pk
            primary key,
    name                                varchar(100),
    address                             varchar(300),
    sticky_note                         varchar,
    expected_roi                        numeric,
    profile                             varchar(200),
    nickname                            varchar(200),
    condo                               varchar(200),
    condo_yearly_fees                   numeric,
    mortage_ammount                     numeric,
    closing_cost_ammount                numeric,
    earthquake_supplies_inventory       varchar(1000),
    earthquake_supplies_good_until_date date,
    ownership_type                      public.property_ownership_type,
    purchase_date                       date,
    purchase_amount                     numeric,
    purchase_vat                        numeric,
    purchase_solicitors                 varchar(1000)
)]
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:402)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:59)
    at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:131)
    at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1276)
    at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1258)
    at liquibase.changelog.ChangeSet.execute(ChangeSet.java:609)
    ... 27 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: relation "property" already exists
  Location: File: heap.c, Routine: heap_create_with_catalog, Line: 1162
  Server SQLState: 42P07
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:310)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:296)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:273)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:268)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:95)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:398)
    ... 32 common frames omitted
Disconnected from the target VM, address: '127.0.0.1:36551', transport: 'socket'

The database schema is empty. Here is the screenshot of the database structure

Here is my pom file:

<properties>
        <spring.datasource.driverClassName>org.postgresql.Driver</spring.datasource.driverClassName>
        <spring.datasource.url>jdbc:postgresql://localhost:5432/database1</spring.datasource.url>
        <spring.datasource.username>*</spring.datasource.username>
        <spring.datasource.password>*</spring.datasource.password>
        <spring.jpa.properties.hibernate.dialect>org.hibernate.dialect.PostgreSQL95Dialect</spring.jpa.properties.hibernate.dialect>
        <spring.jpa.properties.hibernate.multiTenancy>SCHEMA</spring.jpa.properties.hibernate.multiTenancy>
        <spring.jpa.properties.hibernate.multi_tenant_connection_provider>com.*.api.multitenancy.SchemaMultiTenantConnectionProvider</spring.jpa.properties.hibernate.multi_tenant_connection_provider>
        <spring.jpa.properties.hibernate.tenant_identifier_resolver>com.*.api.multitenancy.TenantIdentifierResolver</spring.jpa.properties.hibernate.tenant_identifier_resolver>
        <repoDirectory>repo</repoDirectory>
        <buildDirectory>${project.basedir}/target</buildDirectory>
    </properties>

    <repositories>
        <repository>
            <id>repo</id>
            <url>file://${project.basedir}/${repoDirectory}</url>
        </repository>
    </repositories>

    <dependencies>
        <dependency>
            <groupId>com.vladmihalcea</groupId>
            <artifactId>hibernate-types-52</artifactId>
            <version>2.9.7</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-commons</artifactId>
            <version>2.3.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.4.20.Final</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.2.8.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-orm</artifactId>
            <version>5.2.8.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
            <version>2.3.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-autoconfigure</artifactId>
            <version>2.3.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-annotations</artifactId>
            <version>2.11.2</version>
        </dependency>
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <version>1.15.1</version>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.14</version>
        </dependency>
        <dependency>
            <groupId>org.liquibase</groupId>
            <artifactId>liquibase-core</artifactId>
            <version>4.3.1</version>
        </dependency>
        <dependency>
            <groupId>org.snakeyaml</groupId>
            <artifactId>snakeyaml-engine</artifactId>
            <version>2.2.1</version>
        </dependency>
    </dependencies>

Here is application.properties:

## PostgreSQL

spring.datasource.driverClassName=@spring.datasource.driverClassName@
[email protected]@
[email protected]@
[email protected]@

spring.jpa.properties.hibernate.dialect=@spring.jpa.properties.hibernate.dialect@
spring.jpa.properties.hibernate.multiTenancy=@spring.jpa.properties.hibernate.multiTenancy@
spring.jpa.properties.hibernate.multi_tenant_connection_provider=@spring.jpa.properties.hibernate.multi_tenant_connection_provider@
spring.jpa.properties.hibernate.tenant_identifier_resolver=@spring.jpa.properties.hibernate.tenant_identifier_resolver@
logging.level.liquibase = DEBUG

multitenancy.schema-cache.maximumSize=100
multitenancy.schema-cache.expireAfterAccess=10
multitenancy.master.repository.packages=com.*.dao.repositories.shared
multitenancy.master.entityManager.packages=com.*.dao.config.SharedConfiguration
multitenancy.master.liquibase.enabled=true
multitenancy.master.liquibase.changeLog=classpath:/sql/changelog/public/db.changelog-public.yaml
multitenancy.tenant.repository.packages=com.*.dao.repositories.shared.SubscriptionRepository
multitenancy.tenant.entityManager.packages=com.*.dao.config.MultitenancyConfiguration
multitenancy.tenant.liquibase.changeLog=classpath:/sql/changelog/tenant/db.changelog-tenant.yaml

Liquibase config for public schema (LiquibaseConfig):

@Lazy(false)
@Configuration
@ConditionalOnProperty(name = "multitenancy.master.liquibase.enabled", havingValue = "true", matchIfMissing = true)
public class LiquibaseConfig {

    @Bean
    @ConfigurationProperties("multitenancy.master.liquibase")
    public LiquibaseProperties masterLiquibaseProperties() {
        return new LiquibaseProperties();
    }

    @Bean
    public SpringLiquibase masterLiquibase(ObjectProvider<DataSource> liquibaseDataSource) {
        LiquibaseProperties liquibaseProperties = masterLiquibaseProperties();
        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setDataSource(liquibaseDataSource.getIfAvailable());
        liquibase.setChangeLog(liquibaseProperties.getChangeLog());
        liquibase.setContexts(liquibaseProperties.getContexts());
        liquibase.setDefaultSchema(liquibaseProperties.getDefaultSchema());
        liquibase.setLiquibaseSchema(liquibaseProperties.getLiquibaseSchema());
        liquibase.setLiquibaseTablespace(liquibaseProperties.getLiquibaseTablespace());
        liquibase.setDatabaseChangeLogTable(liquibaseProperties.getDatabaseChangeLogTable());
        liquibase.setDatabaseChangeLogLockTable(liquibaseProperties.getDatabaseChangeLogLockTable());
        liquibase.setDropFirst(liquibaseProperties.isDropFirst());
        liquibase.setShouldRun(liquibaseProperties.isEnabled());
        liquibase.setLabels(liquibaseProperties.getLabels());
        liquibase.setChangeLogParameters(liquibaseProperties.getParameters());
        liquibase.setRollbackFile(liquibaseProperties.getRollbackFile());
        liquibase.setTestRollbackOnUpdate(liquibaseProperties.isTestRollbackOnUpdate());
        return liquibase;
    }

}

Here is the dynamic multitenant config:

@Lazy(false)
@Configuration
@ConditionalOnProperty(name = "multitenancy.tenant.liquibase.enabled", havingValue = "true", matchIfMissing = true)
public class TenantLiquibaseConfig {

    @Bean
    @ConfigurationProperties("multitenancy.tenant.liquibase")
    public LiquibaseProperties tenantLiquibaseProperties() {
        return new LiquibaseProperties();
    }

    @Bean
    public DynamicSchemaBasedMultiTenantSpringLiquibase tenantLiquibase() {
        return new DynamicSchemaBasedMultiTenantSpringLiquibase();
    }

}

DynamicSchemaBasedMultiTenantSpringLiquibase:

public class DynamicSchemaBasedMultiTenantSpringLiquibase implements InitializingBean, ResourceLoaderAware {

    @Autowired
    private SubscriptionRepository masterTenantRepository;

    @Autowired
    private DataSource dataSource;

    @Autowired
    @Qualifier("tenantLiquibaseProperties")
    private LiquibaseProperties liquibaseProperties;

    private ResourceLoader resourceLoader;

    public ResourceLoader getResourceLoader() {
        return resourceLoader;
    }

    @Override
    public void setResourceLoader(ResourceLoader resourceLoader) {
        this.resourceLoader = resourceLoader;
    }

    private static Logger logger = LoggerFactory.getLogger(TenantContext.class.getName());

    @Override
    public void afterPropertiesSet() throws Exception {
        logger.info("Schema based multitenancy enabled");
        this.runOnAllSchemas(dataSource, masterTenantRepository.findAll());
    }

    protected void runOnAllSchemas(DataSource dataSource, Collection<Subscription> tenants) throws LiquibaseException, SQLException {
        for(Subscription tenant : tenants) {
            logger.info("Initializing Liquibase for tenant {} schemaName: {}", tenant.getSubscriptionId(), tenant.getSchemaName());
            SpringLiquibase liquibase = this.getSpringLiquibase(dataSource, tenant.getSchemaName());
            liquibase.afterPropertiesSet();
            logger.info("Liquibase ran for tenant {}", tenant.getSchemaName());
        }
    }

    protected SpringLiquibase getSpringLiquibase(DataSource dataSource, String schema) throws SQLException {

        SpringLiquibase liquibase = new SpringLiquibase();
        liquibase.setResourceLoader(getResourceLoader());
        liquibase.setDataSource(dataSource);
        liquibase.setDefaultSchema(schema);
        liquibase.setChangeLog(liquibaseProperties.getChangeLog());
        liquibase.setContexts(liquibaseProperties.getContexts());
        liquibase.setLiquibaseSchema(liquibaseProperties.getLiquibaseSchema());
        liquibase.setLiquibaseTablespace(liquibaseProperties.getLiquibaseTablespace());
        liquibase.setDatabaseChangeLogTable(liquibaseProperties.getDatabaseChangeLogTable());
        liquibase.setDatabaseChangeLogLockTable(liquibaseProperties.getDatabaseChangeLogLockTable());
        liquibase.setDropFirst(liquibaseProperties.isDropFirst());
        liquibase.setShouldRun(liquibaseProperties.isEnabled());
        liquibase.setLabels(liquibaseProperties.getLabels());
        liquibase.setChangeLogParameters(liquibaseProperties.getParameters());
        liquibase.setRollbackFile(liquibaseProperties.getRollbackFile());
        liquibase.setTestRollbackOnUpdate(liquibaseProperties.isTestRollbackOnUpdate());
        return liquibase;
    }

}

Change set:

databaseChangeLog:

- changeSet:
    id: v20_tenant_ddl
    author: admin
    changes:
        sqlFile:
            encoding: utf8
            path: sql/snaphots/v20_tenant_ddl.sql

I use:

Spring Boot 2.3.2
Postgresql 12
Maven 3.6.3
Liquibase 4.3.1

Solution

After a few days of researching, I have concluded that Liquibase 4.3.1 does not fully support the sqlFile when using the multitenancy feature. As a fix I have rewrote the .sql scripts to .yaml change log format:

databaseChangeLog:

  - changeSet:
      id: init
      author: Valentin
      changes:
        - createSequence:
            sequenceName: property_property_id_seq

        - createTable:
            tableName: property
            columns:
              - column:
                  name: property_id
                  type: bigserial
                  defaultValueSequenceNext: property_property_id_seq
                  constraints:
                    nullable: false
                    primaryKey: true
                    primaryKeyName: property_pk
              - column:
                  name: name
                  type: character varying(100)
              - column:
                  name: address
                  type: character varying(300)
                  constraints:
                    nullable: false

              ...

              - column:
                  name: zip
                  type: character varying(100)
                  constraints:
                    nullable: false

       ...

After changing the format everything where working perfectly.

Answered By - Val S
Answer Checked By - Robin (JavaFixing Admin)