Persisting Transaction Data

Overview

By default, Mule stores transaction data using Business Events. In this configuration, the data is stored on a local H2 database. For business-class deployments, however, it is advisable to store transaction data on an external database, both for improved performance and to aid in disaster recovery.

Do not confuse Business Events with persistent MMC state information. They are two independent systems, not related to each other.

It is also possible to use an external database to store persistent MMC state information. Click here for details.

Storing information about Mule’s current state of operations on an external database can facilitate disaster recovery in two different types of scenarios:

Mule transaction-level failures: Failure to complete the obligatory sequence of message processing events for a single message.
Mule system-level failures: Failure of the entire Mule system.

This document covers basic approaches to configuring the database Mule ESB can use to persist transactions and corresponding events. Two database servers are supported: Oracle and PostgreSQL.

Oracle

The following examples use Oracle Database Express Edition 11.*.. The installation outline is basic: the database server is installed on the local machine, listing on the default port with security deactivated (unsecured connection to the database).

Tools

SQL Developer 3.0.04:
- Download the latest version here
- Consult the installation guide for Linux and Windows here
Oracle Database Expression Edition 11.x:
- Download the latest version here
- Consult the Installation guides for Linux and Windows here
- The full documentation library is here

Important

Do not attempt to run SQL Developer with JDK 1.7.. Even though the SQL installation guide lists “JDK 1.6.11 or later” as suitable for installation, attempting to run SQL Developer with JDK 1.7. results in a warning message and malformed SQL code from SQL Developer.

Installing SQL Developer

Follow the simple instructions provided by the documentation listed above to decompress the installer and run the executable for SQL Developer.

Installing Oracle Express

Oracle Express provides a straightforward graphical installer for Windows or an .rpm package for Linux. Simply follow the instructions provided by the documentation listed above.

Both Windows and Linux require you to have administrator privileges to install Oracle Express.

Configuration Files

The files listener.ora and tnsnames.ora are automatically generated during installation.

For Linux, these files typically reside at: /u01/app/oracle/product/11.2.0/xe/network/admin

On Windows, listener.ora and tnsnames.ora typically reside at: C:\oraclexe\app\oracle\product\11.2.0\server\network\admin

listener.ora listing

# listener.ora Network Configuration File:
SID_LIST_LISTENER =
    (SID_LIST =
        (SID_DESC =
             (SID_NAME = PLSExtProc)
             (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
             (PROGRAM = extproc)
         )
    (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
        (PROGRAM = extproc)
    )
    (SID_DESC =
        (SID_NAME = XE)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
    )
)

LISTENER =
    (DESCRIPTION_LIST =
        (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
            (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
        )
    )

DEFAULT_SERVICE_LISTENER = (XE)

listing or tnsnames.ora

# tnsnames.ora Network Configuration File:

XE =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = XE)
        )
    )

EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
        (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        )
    (CONNECT_DATA =
        (SID = PLSExtProc)
        (PRESENTATION = RO)
    )
)

ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
        (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     )
    (CONNECT_DATA =
        (SID = CLRExtProc)
        (PRESENTATION = RO)
    )
)

For the HOST value, MuleSoft recommends a static IP address (for example, 192.168.1.10), or the 0.0.0.0 address. MuleSoft further recommends against using localhost or a hostname for HOST.

Please be aware that, if the file slqnet.ora exists (usually in /app/oracle/product/11.2.0/xe/network/admin), it may contain some options which have been known to cause problems. For example, unless you are fine-tuning the database, you should disable NFS security integration with SQLNET.AUTHENTICATION_SERVICES = (NONE).

Creating the Database User

To use the SQL Developer GUI to create the database user, complete the following tasks:

Launch the SQL Developer

Navigate to <sqldeveloper install>/sqldeveloper
- On Linux and Mac OS X, run the sqldeveloper.sh shell script
- On Windows, launch sqldeveloper.exe
  
  If SQL developer asks for the full Java path, enter the full path (or navigate to it if you are using a GUI). Typical Java paths are:
  - Linux: /usr/java/jdk1.6.0_31/bin/java
  - Windows: ` C:\Program Files\Java\jdk1.6.0_06\bin\java.exe`

Creating a New Database Connection

Click the Connections tab in the left-hand pane, right-click Connections, then selecting New Connection to display the New / Select Database connection dialog box.
In the connection Name field, enter: mmc_persistency_tracking
In the Username field, enter: SYSTEM
In the Password field, enter the SYSTEM password that you issued during the Oracle Express installation process.
In the Hostname field, ensure that the hostname is correct. (It will be localhost, if Oracle is installed on the local machine.)
In the SID field, enter xe

Verify and Save the Connection

Click Test to verify the connection. After testing, verify that Status : Success appears near the bottom of the dialog box.
Click Save to save the connection settings you have specified.

Configuring the Connection

Click Connect, then click + next to the name of your connection on the Connections tab to expand the connection elements menu.
Right-click Other Users, then select Create User to display the New/Edit User dialog box.
Complete the filed with the required information. The following provide sample guidance:
- User Name: TRACKER
- New Password: tracker
- Default Tablespace: USERS
- Temporary Tablespace: TEMP
- Roles tab: RESOURCE, CONNECT
- System Privileges tab: CREATE ANY CONTEXT, CREATE ANY TABLE, CREATE ANY SEQUENCE
Click Apply, then click Close.

Determining Database Quota

Database size will, of course, vary greatly depending on usage, and the database quota should be determined while taking into account the actual usage on the environment. One way to do this is by performing load tests and extrapolating the results to actual usage over a period of time.

To determine the actual size of the database, launch Oracle’s sqlplus utility (see below) and run the following command:

select sum(bytes) from user_segments;

details on using the sqlplus utility to run commands

Access the Oracle Express menu by completing one of the following steps, depending on your operating system:
- On Windows: From the Windows Start menu: To open the sqlplus command prompt, navigate to Programs (or All Programs) > Oracle Database Express 11g Edition > , Run SQL Command Line.
- On Linux: Open the appropriate menu (A*pplications* in Gnome, or the K menu in KDE), and select Oracle Database 11g Express Edition, then select Run SQL Command Line.
After the sqlplus command prompt opens, type: connect TRACKER/tracker@XE (in this example,TRACKER is the user and tracker is password)
Run the command select sum(bytes) from user segments`;
To exit sqlplus, type: exit

For detailed information about the sqlplus command, consult the SQL*Plus Reference Guide.

Output should be similar to the following:

SQL> connect TRACKER/tracker@XE
Connected.
SQL> select sum(bytes) from user_segments;

SUM(BYTES)
----------
   5832704

SQL>

This indicates that the current database size is 5.83 MB.

Sample Test Results for Database Quota

Test configuration: Agent running on Mule ESB 3.3, on Red Hat Enterprise Linux 6.1 64 bits. For this test, the Echo app in Mule was deployed on the same server.

Database tests were performed using JMeter, an Open Source Java desktop application to perform server load testing, developed by the Apache Software Foundation. JMeter can be downloaded here. Be sure to check the User Manual here.

On these tests, JMeter was used to generate calls to Mule’s Echo app, which is provided as part of Mule’s example applications bundle. Each call to the Echo app generates eight events: two fixed events, two custom events, and two message processors with two events each.

JMeter was configured to simulate 20 connections with a ramp-up of five seconds in an infinite loop, running for a specific time.

After a single call, the total storage added to the database was 1.625 MB (including database metadata). The following table lists number of transactions and database size at different time marks:

Load testing time	Cumulative Total Size	JMeter transactions	Cumalitive JMeter transactions
1 h	701.25MB	2,302,691	2,302,692
2 hs	2351.25 MB	4,710,680	7,013,372
3 hs	4577.25 MB	5,960,692	12,974,064

Configuring the Console

Install the Database Driver

The driver is ojdbc5.jar. You can download it here or from our internal Drivers page.

Copy ojdbc5.jar to the folder <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/lib/

Database Specific Configuration

Go to one of the following directories:

If running MMC on Tomcat: webapps/mmc/WEB-INF/classes/META-INF/

If running MMC on Mule ESB: <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/classes/META-INF/

Locate the file persistence.xml. Open it with a text editor, and find the following line:

<property name="openjpa.jdbc.DBDictionary" value="batchLimit=-1" />

Replace the line with the following:

<property name="openjpa.jdbc.DBDictionary" value="oracle(DriverVendor=oracle)}" />

Locate the file applicationContext-tracking.xml and open it with a text editor. Redefine the beans dataSource, jpavendorAdapter and entityManagerFactory, as listed below:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
    <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe" />
    <property name="username" value="TRACKER" />
    <property name="password" value="tracker" />
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="persistenceUnitName" value="persistence-unit" />

    <property name="jpaVendorAdapter">
        <bean id="jpaAdapter" class="org.springframework.orm.jpa.vendor.OpenJpaVendorAdapter" >
            <property name="database" value="ORACLE"/>
            <property name="showSql" value="true"/>
        </bean>
    </property>

    <property name="loadTimeWeaver">
        <bean class="com.mulesoft.mmc.tracking.repository.jpa.OpenJPALoadTimeWeaver" />
    </property>
</bean>

In the output above, notice that the configuration specifies a database listening on localhost (IP 127.0.0.1), on the default port for Oracle (1521). This is defined in the line <property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe" />

Also, the specified SID (TRACKER) and password (tracker) are the same as those specified on the first part of this document.

Known Limitations

On persistence.xml the following property is set:

<property name="openjpa.jdbc.SynchronizeMappings" value="buildSchema(SchemaAction=add)" />

This specifies that new tables will be added as needed, but if an essential change is done, the older tables will not be removed, which may be cause for conflicts.

Using SchemaAction=refresh instead of SchemaAction=add in the above line will solve this problem, but will produce ORA-01031 Not enough privileges exceptions. This is caused by the system trying to drop everything that it does not directly use, including sequences on the XDB and SYS schemas, which are reserved by Oracle.

Troubleshooting Tips

Error message: ORA-12519, TNS:no appropriate service handler found

If you get this error message, you will need to run the SQL command provided below, then restart the TNS listener.

As user SYS, run:

ALTER SYSTEM SET PROCESSES=150 SCOPE=SPFILE;

To run the SQL command, you can use the sqlplus utility, as explained above.

To restart your TNS listener:

On Windows:

Log in as the user who installed Oracle Database Express and open a DOS terminal.
To check the status of the TNS Listener, run the following command: LSNRCTL STATUS
To stop the TNS Listener, run LSNRCTL STOP
To start the TNS Listener, run LSNRCTL START

On Unix/Linux:

Log in to the oracle system user, for example by running the command su - oracle.
Set the appropriate environment variables:
1. Navigate to the bin directory of the Oracle installation (typically, /u01/app/oracle/product/11.2.0/xe/bin
2. Run the command source oracle_env.sh
After setting environment variables, check the TNS listener status by running lsnrctl status
To stop the TNS listener, run lsnrctl stop
To start the TNS listener, run lsnrctl start

PostgreSQL

The following tutorial covers a simple setup for MMC to work with a Postgres database. The Postgres installation outlined here is basic: the database is installed on the local machine on the default port, with security deactivated (unsecured connection to the database, user with full admin rights).

Tools

PostgreSQL 9.1.3 (download it here)
pgAdmin III (select it when installing Postgres)
Postgres JDBC driver: postgresql-9.1-901.jdbc4.jar. Select it at installation time or download it from our internal Drivers page

Installing the Database

The easiest way to install the database is by using the Enterprise One-Click installer, which you can download here.

You need administrator privileges on your machine for the installation to proceed.

Quick Postgres tutorials for Linux, Mac OS X and Windows can be found here.

Version 9.1 installs with the following default values:

Default port: 5432
Default admin user: postgres

Mule recommends installing one of Postgres’s GUI administration utilities, such as phpPdAdmin (Web-based) or pgAdmin III. These can be selected for installation during the Postgres installation process. In this document, we will use pgAdmin III.

Setting Up the Database Listener

Locate the file postgresql.conf, which is in the data directory of the Postgres installation, for example: /opt/PostgreSQL/9.1/data/postgresql.conf

In the postgresql.conf file, go to the "Connections and Authentication" section. Locate the line that begins with: Listen_addresses =. If the line reads: listen_addresses = 'localhost', then modify it to read as follows:

listen_addresses = '*'

This ill make PostgreSQL listen on all of the available interfaces.

Locate the file pg_hba.conf, which is also in the data directory of the Postgres installation. In this file, find the line that begins with: host all. It will probably look similar to the following:

host    all             all             127.0.0.1/32            md5

Change it to:

host    all             all             0.0.0.0/0       trust

This ensures that Postgres accepts all attempts to connect from any host, without asking for an encrypted password.

For a detailed description of the pg_hba.conf file, click here.

After setting up the listener, the database must be restarted. To do this, open a terminal window (Unix terminal or DOS prompt) and use the pg_ctl command.

To verify the state of the PostgreSQL server, run pg_ctl status

To restart the PostgreSQL server, run pg_ctl restart

Click for instructions on running pg_ctl on Unix or Linux systems

When running pg_ctl status, it is possible that you may get the following output:

pg_ctl: no database directory specified and environment variable PGDATA unset
Try "pg_ctl --help" for more information.

In that case, issue the command with the following options pg_ctl status -D <Postgres data directory>

For example:

pg_ctl status -D /opt/PostgreSQL/9.1/data

The same is valid for the restart option of the pg_ctl command. For example, to restart the PostgreSQL server:

pg_ctl restart -D /opt/PostgreSQL/9.1/data

Creating the Database User

We will create user with the the following parameters:

Role name: TRACKER
Password: tracker
Permissions:
- Can login
- Inherit rights from parent roles
- Can create database objects

To create the user with pgAdmin III, complete the following steps:

Launch pgAdmin III and login to the database server as user postgres by using pgAdmin III’s object browser (located on the right-hand pane) to right-click server PostgreSQL on localhost, then select Connect.
On the Object Browser, right-click Login Roles, then select New Login Role.
At the New Login Role dialog box, type TRACKER in the Role name field.
Go to the Definition tab in the dialog box, and type the password tracker in both Password fields.
In the Role Privileges tab, select all the checkboxes.
Click OK to close the New Login Role dialog box.

Creating a New Database

We will create a database called mmc_persistency_tracking, owned by user TRACKER

Using pgAdmin III:

Complete the following steps:

Using the Object Browser, navigate to Databases > New Database.
In the dialog box, type mmc_persistency_tracking in the Name field.
In the Owner field, select TRACKER
In the Definition tab, ensure that Encoding is set to UTF8
Click OK to close the New Database dialog box.

Verifying the New Database

Use PostgreSQL’s psql command-line utility to log in to database mmc_persistency_tracking as user TRACKER. To do this, open a terminal and run:

psql postgres –UTRACKER

Click to view how to run the psql command

When you run this command, psql should prompt for the user’s password. After typing it, you should get a prompt similar to the following:

mmc_persistency_tracking=#

This indicates that you have successfully connected to the mmc_persistency_tracking database as user TRACKER.

An example of the full login command and output:

mitra:/opt/PostgreSQL/9.1/bin$ ./psql mmc_persistency_tracking -UTRACKER
Password for user TRACKER:
psql.bin (9.1.3)
Type "help" for help.
mmc_persistency_tracking=#

To exit psql, type \q the press Enter

Configure the Console

Installing the Database Driver

Mule Driver

Copy the Postgres jdbc driver, postgresql-9.1-901.jdbc3.jar, to the following directory: <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/lib

Or make it generally available by copying it to: <Mule install path>/lib/user

Web Container Driver

Copy the Postgres jdbc driver, postgresql-9.1-901.jdbc3.jar, to the following directory: <Mule install path>/mmc/webapps/mmc/WEB-INF/lib

Database-specific Configuration

Go to one of the following directories:

If running MMC on Tomcat: webapps/mmc/WEB-INF/classes/META-INF/

If running MMC on Mule ESB: <Mule install path>/apps/mmc/webapps/mmc/WEB-INF/classes/META-INF/

Locate the file persistence.xml. Open it with a text editor, and find the following line:

<property name="openjpa.jdbc.DBDictionary" value="batchLimit=-1" />

Replace the line with the following:

<property name="openjpa.jdbc.DBDictionary" value="*postgres*" />

Locate the file applicationContext-tracking.xml and open it with a text editor. Redefine the beans dataSource and jpaAdapter as listed below:

<bean id="dataSource"  class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="org.postgresql.Driver" />
    <property name="url" value="jdbc:postgresql://172.16.20.78:5432/persistency" />
    <property name="username" value="TRACKER"/>
    <property name="password" value="tracker"/>
</bean>

[...]

<bean id="jpaAdapter" >
    <property name="database" value="POSTGRESQL"/>
    <property name="showSql" value="true"/>
</bean>

Troubleshooting Tips

If you have installed the database on a remote host and experience problems, ensure that network connectivity to the database is working. Open a terminal (Unix or DOS) and run: telnet <host> <port>

Output should be similar to the following:

mitra:~$ telnet dbserver 5432
Trying ::1...
Connected to dbserver.
Escape character is '^]'.

The above output indicates a successful connection to host dbserver on port 5432. A “connection refused” error indicates that nothing is listening on the specified host and port. Any other output often indicates a connectivity problem, such as a firewall blocking requests to the specified port.