Database creation and configuration for Bonita engine and BDM
This page describes the steps to create and customize the databases for Bonita Engine and Business Data Model in the Platform installation.
Bonita uses an RDBMS (Relational DataBase Management System) for the following purposes:
One database schema is required by Bonita Engine to store information about deployed process definitions, process configurations, history of process execution, users, as well as Bonita Platform configuration information.
We recommend that you configure a different database schema if your project uses business data.
Bonita supports MySQL, PostgreSQL, Microsoft SQL Server, and Oracle RDBMSs. You can find the list of RDBMSs versions supported on the supported database list page.
Bonita Studio, the WildFly bundle, the Tomcat bundle, and the deploy bundle come by default with an embedded H2 RDBMS. The database is automatically created at first startup, and can be used for all purposes described earlier.
However, H2 is only suitable for testing purposes. When building a production environment, you must switch to another RDBMS.
Here are the steps to follow. They are the same for the Bonita Engine database and the business data database:
Create the database
Customize RDBMS to make it work with Bonita
Add the JDBC driver to the bundle if the database is proprietary
Configure the bundle with database information
All RDBMSs require specific customization, which must be done before you complete your installation.
Bonita provides out of the box the Jdbc drivers for H2, PostgreSQL, Microsoft SQL Server and MySQL. For Oracle, you have to retrieve the related Jdbc drivers as follows.
Bonita 7.9.+ is compatible with Oracle 12.2.0.x only. Make sure you use the driver that works with your Oracle database version, make sure you check your driver’s Checksum.
Download the driver compatible with Oracle 126.96.36.199: ojdbc8.jar ( make sure it is the offical driver by checking the SHA1 Checksum:
The first step in configuring Bonita with your RDBMS is to create the new databases (i.e. schemas): one for the Bonita Engine data, and another one, optionally, if you use business data.
|If you use different RDBMS users for the Bonita Engine database and for the business data database, then you must grant the privileges to both users.|
To create each Bonita Engine and business data database, you need a RDBMS user account that has sufficient privileges (i.e. privileges to create each new schemas).
Next, the RDBMS user owner of each new schema (i.e. Bonita Engine data and business data) must own the following privileges:
SELECT, INSERT, UPDATE, DELETE on created TABLE
To create the database(s), we recommend that you refer to your RDBMS documentation:
Your database(s) must be configured to use the UTF-8 character set. Also, you are recommended to configure the database(s) to be case-insensitive so that searches in Bonita Portal, Bonita REST API and Bonita Engine API are case-insensitive.
|Bonita is not attached to a specific RDBMS user. Therefore, the RDBMS user that bonita uses to connect to the database can be changed after the platform creation, provided the new user has the required access rights. The same goes for the RDBMS user used to connect to the business data database, if it is different.|
Configure the database to use UTF-8.
postgresql.conf and set a non-zero value for
max_prepared_transactions. The default value, 0, disables prepared transactions, which is not compatible with Bonita Engine.
The value should be at least as large as the value set for
max_connections (default is typically 100).
See the PostgreSQL documentation for details.
Make sure your database is configured to use the AL32UTF8 character set. If your database already exists, see the Oracle documentation for details of how to migrate the character set.
Bonita Engine uses datasources that handle global transactions that span resources (XADataSource), so the Oracle user used by Bonita Engine, requires some specific privileges, and there are also specific settings for XA activation.
The procedure below is used to create the settings to enable Bonita Engine to connect to the Oracle database.
It is assumed in the procedure that:
Oracle product is already installed and running
An 'Oracle' OS user is already created
A database already exists
The environment is already set:
Connect to the database as the System Administrator.
oracle@ubuntu:~$ sqlplus / as sysdba
Check that the following components exist and are valid:
SQL query >
select comp_name, version, status from dba_registry;
Comp_name Version Status
Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle Database Java Packages
Add XA elements:
SQL query >
This only needs to be done once, after the installation of Oracle.
Create the database user to be used by the Bonita Engine and grant the required rights:
SQL query >
The following queries must be done for each new RDBMS user: i.e. one user = one database schema.
The queries must be done for both the Bonita Engine and the business data RDBMS users.
For example, if the users for the Bonita Engine and business data databases are
business_datarespectively (the actual values correspond to the ones set in the properties
bdm.db.userdefined in the
<TOMCAT_HOME>/setup/database.properties), you need to run the queries for both users.
For the Bonita Engine database RDBMS user (e.g. bonita):
SQL query >
CREATE USER bonita IDENTIFIED BY bonita; GRANT connect, resource TO bonita IDENTIFIED BY bonita; GRANT select ON sys.dba_pending_transactions TO bonita; GRANT select ON sys.pending_trans$ TO bonita; GRANT select ON sys.dba_2pc_pending TO bonita; GRANT execute ON sys.dbms_system TO bonita; GRANT execute ON sys.dbms_xa TO bonita; GRANT FORCE ANY TRANSACTION TO bonita; ALTER USER bonita quota unlimited on users;
For the business data database RDBMS user (e.g. business_data):
Note that the business data database RDBMS user (the one defined in
bdm.db.user) requires one additional privilege: CREATE SEQUENCE
SQL query >
CREATE USER business_data IDENTIFIED BY business_data; GRANT connect, resource TO business_data IDENTIFIED BY business_data; GRANT create sequence TO business_data; GRANT select ON sys.dba_pending_transactions TO business_data; GRANT select ON sys.pending_trans$ TO business_data; GRANT select ON sys.dba_2pc_pending TO bonitbusiness_dataa; GRANT execute ON sys.dbms_system TO business_data; GRANT execute ON sys.dbms_xa TO business_data; GRANT FORCE ANY TRANSACTION TO business_data; ALTER USER business_data quota unlimited on users;
To support XA transactions, SQL Server requires a specific configuration. You can refer to MSDN for more information. Here is the list of steps to perform (as an example, the database name BONITA is used):
Download the zip package of Microsoft SQL Server JDBC Driver 6.0 and unzip it.
%JDBC_DRIVER_INSTALL_ROOT%\sqljdbc_6.0\enu\xa\x64\(x64 for 64 bit version of Windows, x86 for 32 bit version of Windows) to
Copy/paste the content of
install.sqlfile (located in
%JDBC_DRIVER_INSTALL_ROOT%\sqljdbc\6.0\enu\xa) to SQL Server Management Studio’s Query Editor.
Execute the query in the Query Editor.
To confirm successful execution of the script, open the "Object Explorer" and go to: Master > Programmability > Extended Stored Procedures.
You should have 12 new procedures, each with a name starting with
Assign the new role 'SqlJDBCXAUser' to the user who owns the Bonita Engine database (
bonitadevin our example). To do so, execute the following commands in SQL editor:
USE master; GO CREATE LOGIN bonitadev WITH PASSWORD = 'secret_password'; GO CREATE USER bonitadev FOR LOGIN bonitadev; GO EXEC sp_addrolemember [SqlJDBCXAUser], 'bonitadev'; GO
In the Windows "Start" menu, select Administrative Tools-> Services.
In the "Services" window, make sure that the Distributed Transaction Coordinator service is set to start automatically. If it’s not yet started, start it.
Make sure that the other services it depends on, namely "Remote Procedure Call" and "Security Accounts Manager", are also set to start automatically.
dcomcnfgcommand, or go to the "Start" menu, then Administrative Tools > Component Services.
In the left navigation pane, navigate to Component Services > Computers > My Computer > Distributed Transaction Coordinator.
Select and right-click on Local DTC and then Properties.
Click on Security tab. Ensure that the checkbox for Enable XA Transactions is checked.
Click Apply, then click OK
Then stop and restart SQLServer to ensure it syncs up with the MS DTC changes.
Create the BONITA database:
CREATE DATABASE BONITA GO.
bonitadevas owner of BONITA database (use, for example, 'Microsoft SQL Management Studio')
Run the script below to avoid deadlocks:
ALTER DATABASE BONITA SET SINGLE_USER WITH ROLLBACK IMMEDIATE ALTER DATABASE BONITA SET ALLOW_SNAPSHOT_ISOLATION ON ALTER DATABASE BONITA SET READ_COMMITTED_SNAPSHOT ON ALTER DATABASE BONITA SET MULTI_USER
Run the script below to avoid that the SQL Server changes the status of databases to SUSPECT during database server startup when in-doubt XA transactions are found.
The value 2 in the block below means presume abort.
To minimize the possibility of extended down time, an administrator might choose to configure this option to presume abort, as shown in the following example
sp_configure 'show advanced options', 1 GO RECONFIGURE GO sp_configure 'in-doubt xact resolution', 2 GO RECONFIGURE GO sp_configure 'show advanced options', 0 GO RECONFIGURE GO
To support XA transactions, starting from MySQL 8.0, special XA rights must be granted to the RDBMS user. For example, if the RDBMS users for the Bonita Engine and business data databases are
business_data respectively, you should run the following command:
GRANT XA_RECOVER_ADMIN ON *.* to bonita, business_data; FLUSH PRIVILEGES;
Please note that the above command, by specifying
Refer to the Privileges section in the official MySQL documentation for further information.
MySQL defines a maximum packet size on the server side. The default value for this setting is appropriate for most standard use cases.
However, you need to increase the packet size if you see the following error:
Error: 1153 SQLSTATE: 08S01 (ER_NET_PACKET_TOO_LARGE) Message: Got a packet bigger than 'max_allowed_packet' bytes
You need to update the file
my.ini (for Windows) or
my.cnf (for Linux) to avoid the
max_allowed_packet setting and increase the value.
For more information, see the MySQL website.
MySQL does not support surrogate characters. If you want to use surrogate characters in your processes, you need to use another type of database.