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@
spring.datasource.url=@spring.datasource.url@
spring.datasource.username=@spring.datasource.username@
spring.datasource.password=@spring.datasource.password@
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
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.