Problem / Bug in DB Creation?

criew · August 27, 2020, 6:26am

I have an issue when using Camunda and auto schema update with two users but different default schemas on the same postgresql database.
Pretty much the same as described here:

I think the reason is, that DbSqlSession::isTablePresent is not strict enough and will find the tables of user1.

1st application starts with user1 and creates camunda tables in schema1
2nd application starts with user2, DbSqlSession::isTablePresent finds table of user1 in schema1 even though the default schema for user2 is schema2. Then later a call to schema2.act_ge_property fails.

The linked solution in the stack overflow topic uses two separate postgres - instances which is not an option for us.

=> is there an easy solution / workaround for this, or is this a bug in Camunda?

rob2universe · September 2, 2020, 8:40am

I updated https://github.com/rob2universe/two-camunda-instances and documented it better.

The issue you observed can be avoided by setting the target schema explicitly.

camunda.bpm.database.schema-name
camunda.bpm.database.table-prefix

(documentation)

When it is not set Camunda relies on the default schema returned for the user in the database meta information.

github.com

camunda/camunda-bpm-platform/blob/adae508ca17ed55ae62c8aeeca879ef369d0dc17/engine/src/main/java/org/camunda/bpm/engine/impl/cfg/ProcessEngineConfigurationImpl.java#L632


protected RejectedJobsHandler customRejectedJobsHandler;

protected Map<String, EventHandler> eventHandlers;
protected List<EventHandler> customEventHandlers;

protected FailedJobCommandFactory failedJobCommandFactory;

protected String databaseTablePrefix = "";

/**
 * In some situations you want to set the schema to use for table checks / generation if the database metadata
 * doesn't return that correctly, see https://jira.codehaus.org/browse/ACT-1220,
 * https://jira.codehaus.org/browse/ACT-1062
 */
protected String databaseSchema = null;

protected boolean isCreateDiagramOnDeploy = false;

protected ProcessApplicationManager processApplicationManager;

protected CorrelationHandler correlationHandler;

github.com

camunda/camunda-bpm-platform/blob/adae508ca17ed55ae62c8aeeca879ef369d0dc17/engine/src/main/java/org/camunda/bpm/engine/impl/db/sql/DbSqlSessionFactory.java#L585


  constants.put("constant.null.startTime", "CAST(NULL AS datetime2) AS START_TIME_");
  constants.put("constant.varchar.cast", "'${key}'");
  constants.put("constant.integer.cast", "NULL");
  constants.put("constant.null.reporter", "NULL AS REPORTER_");
  dbSpecificConstants.put(MSSQL, constants);
}

protected String databaseType;
protected String databaseTablePrefix = "";
/**
 * In some situations you want to set the schema to use for table checks /
 * generation if the database metadata doesn't return that correctly, see
 * https://jira.codehaus.org/browse/ACT-1220,
 * https://jira.codehaus.org/browse/ACT-1062
 */
protected String databaseSchema;
protected SqlSessionFactory sqlSessionFactory;
protected IdGenerator idGenerator;
protected Map<String, String> statementMappings;
protected Map<Class<?>,String>  insertStatements = new ConcurrentHashMap<>();
protected Map<Class<?>,String>  updateStatements = new ConcurrentHashMap<>();