Cannot Auto Create Activiti Tables With Auto Commit Falseon Postgresql Database.

Introduction

Activiti is a popular open-source workflow and Business Process Management (BPM) platform that provides a robust and scalable solution for automating business processes. However, when using Activiti with a PostgreSQL database, users may encounter issues with auto-creating tables when the auto-commit setting is set to false. In this article, we will explore the issues with PostgreSQL setup and provide a solution to auto-create Activiti tables with auto-commit set to false.

Background

We were using Hikari Data Source to connect to a PostgreSQL database with Activiti 8.7.0. The application.properties file had the following settings:

spring.datasource.hikari.auto-commit=false
spring.activiti.database-schema-update=true
spring.jpa.hibernate.ddl-auto=update
spring.activiti.dbHistoryUsed=true
spring.sql.init.mode=always
spring.datasource.hikari.jdbcUrl=jdbc:postgresql://localhost:5432/apps_db?currentSchema=activiti
spring.activiti.database-schema: activiti

Issue 1: Schema Validation

The first issue we encountered was that the Activiti tables did not exist in the database, even though the DbSqlSession was returning true for the isEngineTablePresent() method. This was because the spring.activiti.database-schema setting was not being used to validate the existence of the tables.

if (isEngineTablePresent()) {
    // ...
}

We would have expected the currentSchema from the spring.datasource.hikari.jdbcUrl setting to be used for schema validation. However, a quick fix was to add the spring.activiti.database-schema setting, which was set to the activiti schema.

Issue 2: Setup Script Not Committing

The second issue we encountered was that the setup script did not finish with a commit, which meant that the tables were not created in the database. Even though we could step through the code and see that the code was being executed, the tables did not exist at the end.

Solution

To resolve these issues, we changed the spring.datasource.hikari.auto-commit setting to true. However, this meant that our whole application would need to use auto-commit=true, which we would prefer not to.

spring.datasource.hikari.auto-commit=true

For now, in production, we don't use schema auto-create, so it's only our localhost development that is affected by the auto-commit=true setting. Once the schema is created, we set it back to false and restart the application, and it works okay.

Expected Fix

We would expect that it should be a simple fix to add a commit at the end of the db creations script to ensure all the changes have been applied.

Conclusion

In conclusion, we encountered two issues with PostgreSQL setup when using Activiti with auto-commit set to false. The first issue was with schema validation, and the second issue was with the setup script not committing. We resolved these issues by changing the spring.datasource.hikari.auto-commit setting to true, but this is not an ideal solution. We would expect that a simple fix to add a commit at the end of the db creations script would resolve these issues.

Future Work

In the future, we would like to explore ways to resolve these issues without having to set auto-commit to true. This may involve modifying the Activiti code to add a commit at the end of the db creations script or using a different database setup script.

Activiti Configuration

Here is an example of the Activiti configuration that we used:

spring.datasource.hikari.auto-commit=false
spring.activiti.database-schema-update=true
spring.jpa.hibernate.ddl-auto=update
spring.activiti.dbHistoryUsed=true
spring.sql.init.mode=always
spring.datasource.hikari.jdbcUrl=jdbc:postgresql://localhost:5432/apps_db?currentSchema=activiti
spring.activiti.database-schema: activiti

Hikari Data Source Configuration

Here is an example of the Hikari Data Source configuration that we used:

spring.datasource.hikari.jdbcUrl=jdbc:postgresql://localhost:5432/apps_db?currentSchema=activiti
spring.datasource.hikari.username=postgres
spring.datasource.hikari.password=password
spring.datasource.hikari.driver-class-name=org.postgresql.Driver
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.connection-timeout=30000

PostgreSQL Database Configuration

Here is an example of the PostgreSQL database configuration that we used:

spring.datasource.url=jdbc:postgresql://localhost:5432/apps_db
spring.datasource.username=postgres
spring.datasource.password=password
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.maximum-pool-size=10
spring.datasource.idle-timeout=30000
spring.datasource.connection-timeout=30000

Activiti Tables

Here is an example of the Activiti tables that we created:

CREATE TABLE ACT_GE_PROPERTY (
    NAME_ VARCHAR(64) NOT NULL,
    VALUE_ VARCHAR(300) NOT NULL,
    REV_ INTEGER NOT NULL,
    PRIMARY KEY (NAME_)
);

CREATE TABLE ACT_GE_BYTEARRAY (
    ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    DEPLOYMENT_ID_ VARCHAR(64) NOT NULL,
    BYTEARRAY_ BYTEA NOT NULL,
    GENERATED_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL (
    ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL_VERSION (
    ID_ VARCHAR(64) NOT NULL,
    MODEL_ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL_VERSION_HISTORY (
    ID_ VARCHAR(64) NOT NULL,
    MODEL_VERSION_ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_PROPERTY (
    NAME_ VARCHAR(64) NOT NULL,
    VALUE_ VARCHAR(300) NOT NULL,
    REV_ INTEGER NOT NULL,
    PRIMARY KEY (NAME_)
);

CREATE TABLE ACT_GE_BYTEARRAY (
    ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    DEPLOYMENT_ID_ VARCHAR(64) NOT NULL,
    BYTEARRAY_ BYTEA NOT NULL,
    GENERATED_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL (
    ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL_VERSION (
    ID_ VARCHAR(64) NOT NULL,
    MODEL_ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL_VERSION_HISTORY (
    ID_ VARCHAR(64) NOT NULL,
    MODEL_VERSION_ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

Q: What is Activiti?

A: Activiti is a popular open-source workflow and Business Process Management (BPM) platform that provides a robust and scalable solution for automating business processes.

Q: What is PostgreSQL?

A: PostgreSQL is a powerful, open-source relational database management system that provides a robust and scalable solution for storing and managing data.

Q: How do I configure Activiti to use PostgreSQL?

A: To configure Activiti to use PostgreSQL, you need to add the following properties to your application.properties file:

spring.datasource.hikari.jdbcUrl=jdbc:postgresql://localhost:5432/apps_db?currentSchema=activiti
spring.datasource.hikari.username=postgres
spring.datasource.hikari.password=password
spring.datasource.hikari.driver-class-name=org.postgresql.Driver
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.connection-timeout=30000

Q: What is the spring.datasource.hikari.auto-commit property?

A: The spring.datasource.hikari.auto-commit property determines whether the database connection should automatically commit changes after each operation. By default, this property is set to true, but you can set it to false to manually commit changes.

Q: Why do I need to set spring.datasource.hikari.auto-commit to true to use Activiti with PostgreSQL?

A: You need to set spring.datasource.hikari.auto-commit to true to use Activiti with PostgreSQL because the Activiti setup script does not finish with a commit, which means that the tables are not created in the database. By setting auto-commit to true, you ensure that the changes are committed to the database.

Q: Can I use Activiti with PostgreSQL without setting spring.datasource.hikari.auto-commit to true?

A: Yes, you can use Activiti with PostgreSQL without setting spring.datasource.hikari.auto-commit to true, but you need to modify the Activiti code to add a commit at the end of the db creations script.

Q: How do I create the Activiti tables in PostgreSQL?

A: To create the Activiti tables in PostgreSQL, you need to run the following SQL script:

CREATE TABLE ACT_GE_PROPERTY (
    NAME_ VARCHAR(64) NOT NULL,
    VALUE_ VARCHAR(300) NOT NULL,
    REV_ INTEGER NOT NULL,
    PRIMARY KEY (NAME_)
);

CREATE TABLE ACT_GE_BYTEARRAY (
    ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    DEPLOYMENT_ID_ VARCHAR(64) NOT NULL,
    BYTEARRAY_ BYTEA NOT NULL,
    GENERATED_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL (
    ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL_VERSION (
    ID_ VARCHAR(64) NOT NULL,
    MODEL_ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

CREATE TABLE ACT_GE_MODEL_VERSION_HISTORY (
    ID_ VARCHAR(64) NOT NULL,
    MODEL_VERSION_ID_ VARCHAR(64) NOT NULL,
    NAME_ VARCHAR(255) NOT NULL,
    KEY_ VARCHAR(255) NOT NULL,
    CATEGORY_ VARCHAR(255) NOT NULL,
    VERSION_ INTEGER NOT NULL,
    PRIMARY KEY (ID_)
);

Q: How do I configure the Activiti database schema?

A: To configure the Activiti database schema, you need to add the following properties to your application.properties file:

spring.activiti.database-schema-update=true
spring.jpa.hibernate.ddl-auto=update
spring.activiti.dbHistoryUsed=true
spring.sql.init.mode=always
spring.datasource.hikari.jdbcUrl=jdbc:postgresql://localhost:5432/apps_db?currentSchema=activiti
spring.activiti.database-schema: activiti

Q: What is the spring.activiti.database-schema property?

A: The spring.activiti.database-schema property determines the name of the database schema to use for Activiti.

Q: Can I use a different database schema for Activiti?

A: Yes, you can use a different database schema for Activiti by setting the spring.activiti.database-schema property to the name of the schema you want to use.

Q: How do I troubleshoot issues with Activiti and PostgreSQL?

A: To troubleshoot issues with Activiti and PostgreSQL, you can check the Activiti logs for errors, check the PostgreSQL logs for errors, and use a database client to inspect the database schema and data.