Intalio BPMS : replacing the Derby in-memory DB by MySQL

By default, Intalio BPMS Enterprise Edition is configured to run against a Derby in-memory DB. This DB configuration is pretty handy and make Intalio an out of the box tool for deploying and executing business processes. Nevertheless, as soon as you'll be considering integrating Intalio to a production environment, you'll may also consider having Intalio run against a more robust RDBMS for different reasons : crash recovery facilities, backup & restore procedures, and so on. Most of the most popular RDMBS are supported by Intalio BPMS but the Community Edition only supports MySQL. 

This post describes the procedure to follow to have Intalio BPMS run against a MySQL DB.

1° Tool versions

Here are the tools and their respective version the following procedure is intended to :

• intalio-bpms-6.0.3.022
• mysql-5.5.15-win32 (Zip archive)
• Windows Vista 32bits

2° Setting up MySQL and creating a database for Intalio

• First, unzip the MySQL archive and let's refer to this directory as MYSQL_HOME
• Open a CMD console and go to the %MYSQL_HOME%/bin directory. Then execute : mysqld --console to startup the DB server
• By default, the MySQL root user does not have any password associated. You can defined its password by executing : mysqladmin -u root password PASSWORD (PASSWORD's value is up to you, of course)
• Let's create a DB user for the Intalio server. First, connect to MySQL as user "root" : mysql -u root -p (the -p option will make the console ask you the password). Then execute the following command : mysql > CREATE USER 'intalio'@'localhost' IDENTIFIED BY PASSWORD; (PASSWORD's value corresponds to the password assigned to the user 'intalio' and is up to you ) (don't forget the semicolon to have the MySQL prompt execute your command)
• Let's create a database for Intalio (I called it "intaliodb" but you can choose another name for yours). For this command too, you'll have to connect to the MySQL server as user "root" : mysql > create database intaliodb; (after having executed this command, you can check the "intaliodb" database does exist by executing this command : show databases;)
• Once the "intaliodb" has been created, you'll have to grant some permissions to the user "intalio" so that it will be able to create new tables, rows, and so on. Here's the command to execute, once you're connected as user "root" : mysql > grant CREATE,INSERT,DELETE,UPDATE,SELECT on intaliodb.* to intalio@localhost;

3° Configuring Intalio server

• At %INTALIO_HOME%/databases/MySQL, you'll find a SQL script named BPMS.sql. Executing this script on the "intaliodb" database will create the tables and indexes required by Intalio BPMS. Beware !!! This script contains the "type" keyword that's deprecated since MySQL 4.x. To execute this script on our MySQL 5.x server, we'll have to manually replace the "type" keyword by "engine" (basically, you only need to replace all the "type=innodb" and "type = innodb" in the BPMS.sql script by "engine=innodb"). Then, simply execute the script : (from a CMD console) mysql -u root -p intaliodb < PATH_TO_SCRIPT_FILE
• The last required step to configure the Intalio server is telling the Intalio server which JDBC driver it shoud use, and which database it should connect to. To achieve this, just replace the content of the %INTALIO_HOME%/conf/resources.properties file by the content of the %INTALIO_HOME%/databases/MySQL/tomcat-5-resources.properties file. Then, you'll need to customize some parameters to your own configuration. If you followed this procedure, here's how you properties file should look like : 

4° Redeploying existing processes 

• Now that we've replaced the default Derby database server by MySQL, all we need to do is redeploying the existing processes so that their associated info are stored in the MySQL server. To do that, start your Intalio server and then delete all the files with the .deployed extension in the %INTALIO_HOME%/var/deploy directory. The deleted file should be recreated after a few seconds, which is the time the Intalio server needs to hot-redeploy the processes.

5° Test

In order to check that your new Intalio configuration runs smoothly, you could run some of your existing processes (preferably, some with tasks or notifications) and then, check what is actually stored in the database. 

I'm personally using either SqlWave or HeidiSQL which are both great MySQL GUI clients when I need to perform some sort of monitoring work on the database used by my Intalio processes. Feel free to take a look at the tables whose name begins with "tempo_", they hold all the info related to your processes (tasks, notification, task status, assigned user, and so on) :