Sunday, August 21, 2011

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-
  • 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/ file by the content of the %INTALIO_HOME%/databases/MySQL/ 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) : 


    1. Hi
      Could you provide post on Intalio process using JSP/JSF/PHP/Flex/.NET etc as UI interface.

    2. Hi,

      What do you mean exactly, by using these technologies as UI? Integrating the tasks/notifications in your own UI layer? Or integrating the UI-FW console?

    3. Integrating the tasks/notifications in own UI layer.

    4. To integrate the tasks/notifications in your own UI layer, I guess you'll have to either invoke the web services that Intalio provides (token management service , task management service, ...), or you can have implement a business layer that would query the DB in which the tasks/notifications related info are stored.

      One last possible solution would be including the existing tasks/notifications jsp file provided by Intalio, in your own UI in case you've choosen a technology that support this kind of thing. For instance, if you've developped your own UI using JSP, you would be able to include whole or part of existing JSP files.

    5. Hi,thanks for this !

      my company ask me to devloppe an application using the the BPM ( intalio BPMS ) if you have a complete documentation or project or exemple please send it to me

      this application is a web site !

      or please give us your mail for more detail !


    6. Hi Tunisiebpms,

      I'm posting every example or tip that I consider relevant. In my company, we are currently integrating Intalio with an RCP client. If you have further question about Intalio or some topic that we could explore, feel free to leave a comment.

      Kind regards

    7. Hello, I appreciate your post, I followed step by step post, process keeps store on "store_process" but when I run an instance in the task fails

    8. Hi marlon,

      Maybe you could provide more details about your instance failure (error message, logs, ...)?

    9. me sirvió mucho el tuto, es muy eficaz, ahora tengo una duda, como creo nuevos usuario y roles, y que se registren en la base de datos

    10. The tutorial helped me a lot, is very effective, now I have a question, as I think new users and roles, and to register in the database

      1. I stopped working with Intalio almost 2 years ago so I might be unable to answer your question, but feel free to ask anyway, I'll try my best...

    11. Hi,
      first of all, i am very thank full to you for providing grate information regarding Intalio bpm, currently
      i need to implement sample poc on intalio bpms that is used for android mobiles by consuming intali web services.
      could you please guide me how to write web services for android apps by using latest intalio version intalio-bpms-6.5.1

    12. Hi there,

      I'm glad to hear you got interested in my posts. Unfortunately, I haven't worked with Intalio for almost 3 years. Also, I have no experience with Android programming so far, so I guess I won't be able to help you at this moment.