How to set persistent database with camunda

Hi

I am using camunda, as by default it is using h2 database which is not persistent, I want to use persistent database with camunda, in which data will not lost after reboot.

Thanks.

Hi @Muhammad_Asad,

I think you should go over steps described here https://docs.camunda.org/manual/7.6/installation/full/tomcat/manual/#create-the-database-schema-and-tables

Does that help?
Askar

I’ve also switched up to the TCP configuration of H2 - this way, when you’re using db tools (dbVisualizer, etc) you won’t get the ‘database is locked’ message.

see:

<connection-url>jdbc:h2:tcp://centosw02//opt/wildfly-1010.camunda.camel/camunda-h2-dbs/process-engine;MVCC=TRUE</connection-url>

Forgot to add that you’ll need to download and run H2 as a server - but, as everything else with H2… reasonably straightforward.

Are you trying to use a database other than H2, such as MySQL?

If you’re using MySQL, you’ll find scripts under the distribution’s root directory in subdirectory “sql/create”. You would need to create a database first, we call ours “camunda_db” in the MySQL server. Then execute the “mysql_engine_7.6.2-ee.sql” script, followed by the “mysql_identity_7.6.2-ee.sql” script.

You would then need to create a user for Camunda to log into that database and grant that user DELETE, INSERT, SELECT, and UPDATE privileges.

In your Java container (Tomcat, WildFly, etc.) database configuration, you would need to insert a datasource configuration string to allow the Process Engine to connect to the database. Following is an example from the WildFly standalone.xml file which assumes the MySQL server is running on the same server as Camunda:

    <datasources>
        <datasource jta="true" jndi-name="java:jboss/datasources/ProcessEngine" pool-name="ProcessEngine" enabled="true" use-java-context="true" use-ccm="true" statistics-enabled="true">
            <connection-url>jdbc:mysql://127.0.0.1:3306/camunda_db?useSSL=false&amp;autoReconnect=true</connection-url>
            <driver-class>com.mysql.jdbc.Driver</driver-class>
            <driver>mysql</driver>
            <pool>
                <min-pool-size>5</min-pool-size>
                <initial-pool-size>5</initial-pool-size>
                <max-pool-size>50</max-pool-size>
            </pool>
            <security>
                <user-name>camunda</user-name>
                <password>camundapass</password>
            </security>
            <validation>
                <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
                <background-validation>true</background-validation>
                <background-validation-millis>2000</background-validation-millis>
                <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.StaleConnectionChecker"/>
                <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
            </validation>
            <timeout>
                <set-tx-query-timeout>false</set-tx-query-timeout>
                <blocking-timeout-millis>0</blocking-timeout-millis>
                <idle-timeout-minutes>0</idle-timeout-minutes>
                <query-timeout>0</query-timeout>
                <use-try-lock>0</use-try-lock>
                <allocation-retry>0</allocation-retry>
                <allocation-retry-wait-millis>0</allocation-retry-wait-millis>
            </timeout>
            <statement>
                <share-prepared-statements>false</share-prepared-statements>
            </statement>
        </datasource>

This configuration example (which is for WildFly) would also require you to put the MySQL Connector/J driver library in the WildFly modules section (or Tomcat’s equivalent). The driver jar file goes here:

/usr/local/camunda-bpm-ee-wildfly10-7.6.2-ee/server/wildfly-10.1.0.Final/modules/system/layers/base/com/mysql/main/mysql-connector-java-commercial-5.1.39-bin.jar

You will also need to create a module.xml file at:

/usr/local/camunda-bpm-ee-wildfly10-7.6.2-ee/server/wildfly-10.1.0.Final/modules/system/layers/base/com/mysql/main/module.xml

This should contain the following:

<?xml version="1.0" encoding="UTF-8"?>
<module xmlns="urn:jboss:module:1.0" name="com.mysql">
    <resources>
        <resource-root path="mysql-connector-java-commercial-5.1.39-bin.jar"/>
    </resources>
    <dependencies>
        <module name="javax.api"/>
        <module name="javax.transaction.api"/>
    </dependencies>
</module>

The configuration would also require you to place the “ironjacamar” validation libraries in the modules directories as shown in this directory tree:

/usr/local/camunda-bpm-ee-wildfly10-7.6.2-ee/server/wildfly-10.1.0.Final/modules/system/layers/base/org/jboss/ironjacamar
├── api
│   └── main
│       ├── ironjacamar-common-api-1.3.4.Final.jar
│       ├── ironjacamar-common-spi-1.3.4.Final.jar
│       ├── ironjacamar-core-api-1.3.4.Final.jar
│       └── module.xml
├── impl
│   └── main
│       ├── ironjacamar-common-impl-1.3.4.Final.jar
│       ├── ironjacamar-core-impl-1.3.4.Final.jar
│       ├── ironjacamar-deployers-common-1.3.4.Final.jar
│       ├── ironjacamar-validator-1.3.4.Final.jar
│       └── module.xml
└── jdbcadapters
    └── main
        ├── ironjacamar-jdbc-1.3.4.Final.jar
        └── module.xml

The module file at “…/jboss/ironjacamar/api/main/module.xml” would contain the following:

<?xml version="1.0" encoding="UTF-8"?>

<!--
  ~ JBoss, Home of Professional Open Source.
  ~ Copyright 2010, Red Hat, Inc., and individual contributors
  ~ as indicated by the @author tags. See the copyright.txt file in the
  ~ distribution for a full listing of individual contributors.
  ~
  ~ This is free software; you can redistribute it and/or modify it
  ~ under the terms of the GNU Lesser General Public License as
  ~ published by the Free Software Foundation; either version 2.1 of
  ~ the License, or (at your option) any later version.
  ~
  ~ This software is distributed in the hope that it will be useful,
  ~ but WITHOUT ANY WARRANTY; without even the implied warranty of
  ~ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  ~ Lesser General Public License for more details.
  ~
  ~ You should have received a copy of the GNU Lesser General Public
  ~ License along with this software; if not, write to the Free
  ~ Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
  ~ 02110-1301 USA, or see the FSF site: http://www.fsf.org.
  -->

<module xmlns="urn:jboss:module:1.3" name="org.jboss.ironjacamar.api">
    <properties>
        <property name="jboss.api" value="private"/>
    </properties>

    <resources>
        <resource-root path="ironjacamar-common-api-1.3.4.Final.jar"/>
        <resource-root path="ironjacamar-common-spi-1.3.4.Final.jar"/>
        <resource-root path="ironjacamar-core-api-1.3.4.Final.jar"/>
    </resources>

    <dependencies>
        <module name="javax.resource.api"/>
        <module name="javax.api"/>
        <module name="org.jboss.logging"/>
        <module name="org.jboss.threads"/>
    </dependencies>
</module>

The “module.xml” file under “…/jboss/ironjacamar/impl/main” would contain the following:

<?xml version="1.0" encoding="UTF-8"?>

<!--
  ~ JBoss, Home of Professional Open Source.
  ~ Copyright 2010, Red Hat, Inc., and individual contributors
  ~ as indicated by the @author tags. See the copyright.txt file in the
  ~ distribution for a full listing of individual contributors.
  ~
  ~ This is free software; you can redistribute it and/or modify it
  ~ under the terms of the GNU Lesser General Public License as
  ~ published by the Free Software Foundation; either version 2.1 of
  ~ the License, or (at your option) any later version.
  ~
  ~ This software is distributed in the hope that it will be useful,
  ~ but WITHOUT ANY WARRANTY; without even the implied warranty of
  ~ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  ~ Lesser General Public License for more details.
  ~
  ~ You should have received a copy of the GNU Lesser General Public
  ~ License along with this software; if not, write to the Free
  ~ Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
  ~ 02110-1301 USA, or see the FSF site: http://www.fsf.org.
  -->

<module xmlns="urn:jboss:module:1.3" name="org.jboss.ironjacamar.impl">
    <properties>
        <property name="jboss.api" value="private"/>
    </properties>

    <resources>
        <resource-root path="ironjacamar-common-impl-1.3.4.Final.jar"/>
        <resource-root path="ironjacamar-core-impl-1.3.4.Final.jar"/>
        <resource-root path="ironjacamar-deployers-common-1.3.4.Final.jar"/>
        <resource-root path="ironjacamar-validator-1.3.4.Final.jar"/>
    </resources>

    <dependencies>
        <module name="sun.jdk"/>
        <!-- javax.security.auth.callback -->
        <module name="javax.api"/>
        <module name="javax.resource.api"/>
        <module name="javax.security.auth.message.api"/>
        <module name="javax.validation.api"/>
        <module name="org.hibernate.validator"/>
        <module name="org.jboss.as.naming"/>
        <module name="org.jboss.as.transactions"/>
        <module name="org.jboss.jboss-transaction-spi"/>
        <module name="org.jboss.ironjacamar.api"/>
        <module name="org.jboss.logging"/>
        <module name="org.jboss.threads"/>
        <!-- org.jboss.security -->
        <module name="org.picketbox"/>
        <module name="javax.xml.stream.api"/>
       <!--jgroups -->
        <module name="org.jgroups"/>
    </dependencies>
</module>

The module file at “jboss/ironjacamar/jdbcadapters/main/module.xml” would contain the following:

<?xml version="1.0" encoding="UTF-8"?>

<!--
  ~ JBoss, Home of Professional Open Source.
  ~ Copyright 2010, Red Hat, Inc., and individual contributors
  ~ as indicated by the @author tags. See the copyright.txt file in the
  ~ distribution for a full listing of individual contributors.
  ~
  ~ This is free software; you can redistribute it and/or modify it
  ~ under the terms of the GNU Lesser General Public License as
  ~ published by the Free Software Foundation; either version 2.1 of
  ~ the License, or (at your option) any later version.
  ~
  ~ This software is distributed in the hope that it will be useful,
  ~ but WITHOUT ANY WARRANTY; without even the implied warranty of
  ~ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  ~ Lesser General Public License for more details.
  ~
  ~ You should have received a copy of the GNU Lesser General Public
  ~ License along with this software; if not, write to the Free
  ~ Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
  ~ 02110-1301 USA, or see the FSF site: http://www.fsf.org.
  -->

<module xmlns="urn:jboss:module:1.3" name="org.jboss.ironjacamar.jdbcadapters">
    <properties>
        <property name="jboss.api" value="private"/>
    </properties>

    <resources>
        <resource-root path="ironjacamar-jdbc-1.3.4.Final.jar"/>
    </resources>

    <dependencies>
        <module name="javax.api"/>
        <module name="javax.resource.api"/>
        <module name="javax.transaction.api"/>
        <module name="javax.validation.api"/>
        <module name="org.hibernate.validator"/>
        <module name="org.jboss.as.naming"/>
        <module name="org.jboss.as.transactions"/>
        <module name="org.jboss.jboss-transaction-spi"/>
        <module name="org.jboss.ironjacamar.api"/>
        <module name="org.jboss.ironjacamar.impl"/>
        <module name="org.jboss.logging"/>
        <module name="org.jboss.threads"/>
        <module name="javax.xml.stream.api"/>

    </dependencies>
</module>

After all of that is set up and you restart the Java container, Camunda should connect to the database and all of your Camunda data will be persistent.

@mppfor_manu I am using Camunda-Tomcat on back end and apache2 on front and and using apache tomcat connector to run camunda on 8080 port. It will be very helpful if you will explain me process according to tomcat server with front end apach2, means how to create sql data base and use its connection in tomcat server in server.xml

@aakhmerov
How do I will do these processes:
Create a database schema for the Camunda BPM platform yourself.
Execute the SQL DDL scripts which create all required tables and default indices.
The SQL DDL scripts reside in the sql/create folder of the distribution:

$TOMCAT_DISTRIBUTION/sql/create/engine$PLATFORM_VERSION.sql $TOMCAT_DISTRIBUTION/sql/create/identity$PLATFORM_VERSION.sql

by only running these files using terminal or what?

By “apache2” front end, I assume you mean you’ll be using an Apache HTTPD Web Server. I’ve never done that. However, the database configuration is handled in the server.xml file. The details of manually setting up Tomcat can be found in the documentation at: https://docs.camunda.org/manual/7.6/installation/full/tomcat/manual/

The following was taken from a 7.5.3 distribution for Tomcat. I’ve modified it for use with MySQL.

    <Resource name="jdbc/ProcessEngine"
              auth="Container"
              type="javax.sql.DataSource" 
              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
              uniqueResourceName="process-engine"
              driverClassName="com.mysql.jdbc.Driver" 
              url="jdbc:mysql://127.0.0.1:3306/camunda_db?useSSL=false&amp;autoReconnect=true"
              username="camunda"  
              password="camunda"
              maxActive="20"
              minIdle="5" />

I assume that you will then need to put the MySQL Connector/J driver in the /lib directory of the Tomcat distribution. You can obtain the MySQL Connector/J driver here: https://dev.mysql.com/downloads/connector/j/

You will need to have the database up and running for this to work and you may need to tweak the configuration.

@mppfor_manu I understood, As I will install mysql server, which name of database I have to create in it and as I use Connector/J. When I will start camunda-start, is it will automcatically make tables in database in mysql server?

No, you must first run the scripts in the “sql/create” subdirectory of the distribution.

  1. Create a database in MySQL (e.g. camunda_db)

  2. You will need to put a “USE” statement in the first line of the mysql_engine_7.X.X-ee.sql and mysql-identity_7.X.X.sql scripts:

USE camunda_db;

  1. Run the mysql_engine_7.X.X-ee.sql script.

  2. Run the mysql_identity_7.X.X-ee.sql script.

These scripts will set up the database for Camunda so that when you start Camunda, it will be ready to use.

If you wish to use another database, you will find other scripts in that same directory.

Michael Peoples (mp4783)
Global Customer Service BizOps Orchestration
Office: +1 614-886-0923
Mobile: +1 614-886-0923

Principal Applications Developer

@mppfor_manu Thank yo so much, I have done all process you asked me,
now camunda is working with new resource but how will I find out that it is working with new mysql database

If you set up the datasource and removed the H2 datasource reference and Camunda is working, then it is using the new database.

You can log into the MySQL database and check some of the tables in the Camunda database to see if there are records.

Camunda itself wouldn’t start without a viable connection.

Michael Peoples (mp4783)
Global Customer Service BizOps Orchestration
Office: +1 614-886-0923
Mobile: +1 614-886-0923

Principal Applications Developer

Thank you so much @mppfor_manu for your excellent support, new mysql database is working now.