Multi-Tenancy with MultiSchemaSharedEngine with Flowable and Postgres DB

Hi,

We are trying to implement multi-tenancy with flowable (Multitenant with singleengine/multischema) using Postgres DB.
But when we try to register different tenants we get the below error :

lowable.common.engine.impl.db.CommonDbSchemaManager - SQL: drop table if exists ACT_GE_BYTEARRAY cascade
14:17:00.488 [main] DEBUG org.flowable.common.engine.impl.db.CommonDbSchemaManager - SQL: drop table if exists ACT_GE_PROPERTY cascade
14:17:00.490 [main] DEBUG org.flowable.common.engine.impl.db.CommonDbSchemaManager - flowable db schema drop for component common successful
14:17:00.517 [main] INFO org.flowable.common.engine.impl.db.CommonDbSchemaManager - performing create on common with resource org/flowable/common/db/create/flowable.postgres.create.common.sql
14:17:00.517 [main] DEBUG org.flowable.common.engine.impl.db.CommonDbSchemaManager - SQL: create table ACT_GE_PROPERTY (
NAME_ varchar(64),
VALUE_ varchar(300),
REV_ integer,
primary key (NAME_)
)
14:17:00.524 [main] ERROR org.flowable.common.engine.impl.db.CommonDbSchemaManager - problem during schema create, statement create table ACT_GE_PROPERTY (
NAME_ varchar(64),
VALUE_ varchar(300),
REV_ integer,
primary key (NAME_)
)
org.postgresql.util.PSQLException: ERROR: no schema has been selected to create in
Position: 14
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)
at org.flowable.common.engine.impl.db.AbstractSqlScriptBasedDbSchemaManager.executeSchemaResource(AbstractSqlScriptBasedDbSchemaManager.java:314)
at org.flowable.common.engine.impl.db.AbstractSqlScriptBasedDbSchemaManager.executeSchemaResource(AbstractSqlScriptBasedDbSchemaManager.java:227)
at org.flowable.common.engine.impl.db.AbstractSqlScriptBasedDbSchemaManager.executeMandatorySchemaResource(AbstractSqlScriptBasedDbSchemaManager.java:215)
at org.flowable.common.engine.impl.db.ServiceSqlScriptBasedDbSchemaManager.internalDbSchemaCreate(ServiceSqlScriptBasedDbSchemaManager.java:49)
at org.flowable.common.engine.impl.db.ServiceSqlScriptBasedDbSchemaManager.schemaCreate(ServiceSqlScriptBasedDbSchemaManager.java:44)
at org.flowable.engine.impl.db.ProcessDbSchemaManager.schemaCreate(ProcessDbSchemaManager.java:91)
at org.flowable.engine.impl.cfg.multitenant.ExecuteSchemaOperationCommand.execute(ExecuteSchemaOperationCommand.java:51)
at org.flowable.engine.impl.cfg.multitenant.ExecuteSchemaOperationCommand.execute(ExecuteSchemaOperationCommand.java:28)
at org.flowable.engine.impl.interceptor.CommandInvoker$1.run(CommandInvoker.java:67)
at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperation(CommandInvoker.java:140)
at org.flowable.engine.impl.interceptor.CommandInvoker.executeOperations(CommandInvoker.java:114)
at org.flowable.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:72)
at org.flowable.engine.impl.interceptor.BpmnOverrideContextInterceptor.execute(BpmnOverrideContextInterceptor.java:26)
at org.flowable.common.engine.impl.interceptor.TransactionContextInterceptor.execute(TransactionContextInterceptor.java:53)
at org.flowable.common.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:98)
at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:35)
at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
at org.flowable.engine.impl.cfg.multitenant.MultiSchemaMultiTenantProcessEngineConfiguration.createTenantSchema(MultiSchemaMultiTenantProcessEngineConfiguration.java:149)
at org.flowable.engine.impl.cfg.multitenant.MultiSchemaMultiTenantProcessEngineConfiguration.buildProcessEngine(MultiSchemaMultiTenantProcessEngineConfiguration.java:134)
at com.example.demo.MultiTenantProcessEngineConfiguration.buildProcessEngine(MultiTenantProcessEngineConfiguration.java:66)
at com.example.demo.MultiTenantProcessEngineConfiguration.(MultiTenantProcessEngineConfiguration.java:17)
at com.example.demo.TestFlowable.main(TestFlowable.java:6)

**Our sample code**

public ProcessEngine buildProcessEngine() {
MultiSchemaMultiTenantProcessEngineConfiguration config = new MultiSchemaMultiTenantProcessEngineConfiguration(tenantInfoHolder);
config.setDatabaseType(MultiSchemaMultiTenantProcessEngineConfiguration.DATABASE_TYPE_POSTGRES);
config.setDatabaseSchemaUpdate(MultiSchemaMultiTenantProcessEngineConfiguration.DB_SCHEMA_UPDATE_DROP_CREATE);

config.setAsyncExecutorActivate(true);
config.setDisableIdmEngine(true);
config.setDisableEventRegistry(true);

/* config.registerTenant("flowable", createDataSource("jdbc:h2:mem:flowable-flowable;DB_CLOSE_DELAY=1000", "sa", ""));
config.registerTenant("acme", createDataSource("jdbc:h2:mem:flowable-acme;DB_CLOSE_DELAY=1000", "sa", ""));
config.registerTenant("starkindustries", createDataSource("jdbc:h2:mem:flowable-stark;DB_CLOSE_DELAY=1000", "sa", ""));
return config.buildProcessEngine();
}

private DataSource createDataSource(String jdbcUrl, String jdbcUsername, String jdbcPassword) {
JdbcDataSource ds = new JdbcDataSource();
ds.setURL(jdbcUrl);
ds.setUser(jdbcUsername);
ds.setPassword(jdbcPassword);
return ds;
}
*/

config.registerTenant("flowable", createDataSource("flowable", "postgres", "postgrespassword"));
config.registerTenant("acme", createDataSource("acme", "postgres", "postgres"));
config.registerTenant("starkindustries", createDataSource("starkindustries", "postgres", "postgres"));
return config.buildProcessEngine();
}


private DataSource createDataSource(String jdbcUrl, String jdbcUsername,
String jdbcPassword) {
int port = 5444;
PGSimpleDataSource ds = new PGSimpleDataSource();
ds.setServerNames(new String[]{"localhost"});
ds.setPortNumbers(new int[]{port});
ds.setUser("postgres");
ds.setPassword("postgrespassword");
ds.setDatabaseName("postgres");
ds.setCurrentSchema(jdbcUrl);

// ResultSet resultSet = ds.getConnection().createStatement().executeQuery("SELECT * FROM product");
// resultSet.next();

return ds; }

Start with MultiSchemaMultiTenantProcessEngineConfiguration.createTenantSchema and ExecuteSchemaOperationCommand, then review the PGSimpleDataSource setup in the sample. Reproduce tenant registration with the shown PostgreSQL configuration and trace schema creation. Done means all registered tenant schemas can be created without the reported “no schema has been selected” error.

Vom Indexierungsmodell aus dem Issue-Text verfasst.