eXo Platform Database Guide with JBoss/Tomcat

By default, eXo Platform uses HSQL and you do not need any configuration to make it work. Follow this chapter only when you need to use another RDBMS.

You can use MySQL, PostgreSQL, MSSQL, Oracle, Sybase and DB2. In principle, any SQL database that provides a JDBC-compliant driver can be used.

In this chapter

• Creating databases: you need to create the databases. Tables are created automatically at the first startup of Platform.
• Configuring eXo Platform: a complete guide to configure eXo to use an RDBMS.
• Datasource JNDI name: follow this section if you want to change the JNDI names of the datasources.
• Frequently asked questions: solutions to some possible issues.

See also: Data directory configuration

4.1. Creating databases

eXo Platform uses two datasources: one for the Identity Management (IDM) service and one for the Java Content Repository (JCR) service.

1. Create 2 databases, one for IDM and one for JCR (or use the same database for both). Leave them empty; the tables are created automatically at the first startup.
   • If you do not need special characters, use the character set latin1 and the collation latin1_general_cs (eXo JCR is case sensitive). In MySQL:

     CREATE DATABASE plf_jcr DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_general_cs;

   • If you need special characters, use the character set utf8 and the collation utf8_bin. In MySQL:

     CREATE DATABASE plf_jcr DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_bin;

2. Grant a user access to the databases. The user must be allowed to connect remotely from the host where eXo Platform runs. In MySQL:

   grant all on ${dbname}.* to '$username'@'$IP' identified by '$password';

   • $IP is your app server host name; wildcards are accepted (e.g. 192.168.1.% = all IPs on the 192.168.1.x network).
   • $username and $password are the credentials eXo Platform uses to connect to the databases.

4.2. Configuring eXo Platform

eXo Platform relies on the application server (Tomcat/JBoss) to access databases. It uses two JNDI datasources:

• IDM uses exo-idm_portal.
• JCR uses exo-jcr_portal.

eXo provides ready-made, fine-tuned configurations, so you typically just pick a sample and modify the connection URL and credentials.

4.2.1. For Tomcat

1. Configure the datasources.
   1. Edit conf/server.xml and remove the default HSQL configuration:

      <!-- eXo IDM Datasource for portal -->
      <Resource name="exo-idm_portal" ...
      username="sa" password="" driverClassName="org.hsqldb.jdbcDriver" ... />
      
      <!-- eXo JCR Datasource for portal -->
      <Resource name="exo-jcr_portal" ...
      username="sa" password="" driverClassName="org.hsqldb.jdbcDriver" ... />

   2. Add a new one. For MySQL, copy the sample in conf/server-mysql.xml:

      <!-- eXo IDM Datasource for portal -->
      <Resource name="exo-idm_portal" auth="Container" type="javax.sql.DataSource"
      ...
      username="plf" password="plf" driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost:3306/plf?autoReconnect=true" />
      
      <!-- eXo JCR Datasource for portal -->
      <Resource name="exo-jcr_portal" auth="Container" type="javax.sql.DataSource"
      ...
      username="plf" password="plf" driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost:3306/plf?autoReconnect=true" />

   3. Edit the username, password and url (host, port and database name). Besides MySQL, if you use Enterprise Edition, you will find samples for other RDBMSs in conf/server-*.xml.
   4. If your database character set is utf8, append the character encoding to the url (this differs between RDBMSs). For MySQL:

      url="jdbc:mysql://localhost:3306/plf?autoReconnect=true&characterEncoding=utf8"

2. Set the SQL dialect if necessary. This step is not mandatory because the dialect is auto-detected in most cases. Only some RDBMSs need attention:
   1. JCR: only when using MySQL with the utf8 character set, edit gatein/conf/exo.properties:

      exo.jcr.datasource.dialect=MySQL-UTF8

   2. IDM: eXo Platform auto-detects the dialect for the RDBMSs listed here. Only when your RDBMS is not in the list (e.g. Postgres Plus Advanced Server 9.2), edit gatein/conf/exo.properties:

      hibernate.dialect=org.hibernate.dialect.PostgresPlusDialect

   If you have not created exo.properties yet, see Configuration overview.
3. Download the JDBC driver and install it to $PLATFORM_TOMCAT_HOME/lib.

   Tip: You can usually find a suitable driver for your JDK on the database vendor's website. For MySQL: Connector/J; for Oracle: Oracle JDBC.

4.2.2. For JBoss

1. Configure the datasources.
   1. Edit standalone/configuration/standalone-exo.xml and remove the default HSQL configuration:

      <!-- eXo IDM Datasource for PLF -->
      <datasource enabled="true" jndi-name="java:/comp/env/exo-idm_portal"
        jta="false" pool-name="exo-idm_portal" spy="false" use-ccm="true"
        use-java-context="true">
          <!-- HSQLDB -->
          <driver>hsqldb-driver.jar</driver>
          <driver-class>org.hsqldb.jdbcDriver</driver-class>
          <connection-url>jdbc:hsqldb:file:${exo.data.dir}/hsql/exo-plf;
      shutdown=true;hsqldb.write_delay=false;</connection-url>
      ...
      
      <!-- eXo JCR Datasource for PLF -->
      <datasource enabled="true" jndi-name="java:/comp/env/exo-jcr_portal"
        jta="false" pool-name="exo-jcr_portal" spy="false" use-ccm="true"
        use-java-context="true">
          <!-- HSQLDB -->
          ...

   2. For MySQL, uncomment some lines in the file and edit the driver, username, password and url:

      <!-- MySQL -->
      <driver>mysql-connector-java-5.1.6-bin.jar</driver>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <connection-url>jdbc:mysql://localhost:3306/plf?autoReconnect=true</connection-url>
      ...
      <security>
          <user-name>root</user-name>
          <password>exoplf</password>
      </security>
      <validation>
          ...
          <!-- MySQL -->
          <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter" />
          <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker" />

   3. If your database character set is utf8, append the character encoding to the url (different between RDBMSs). For MySQL:

      <connection-url>jdbc:mysql://localhost:3306/plf?autoReconnect=true&amp;characterEncoding=utf8</connection-url>

2. Set the SQL dialect if necessary. This step is not mandatory because the dialect is auto-detected in most cases:
   1. JCR: only when using MySQL with the utf8 character set, edit standalone/configuration/gatein/exo.properties:

      exo.jcr.datasource.dialect=MySQL-UTF8

   2. IDM: only when your RDBMS is not in the auto-detected list (e.g. Postgres Plus Advanced Server 9.2), edit standalone/configuration/gatein/exo.properties:

      hibernate.dialect=org.hibernate.dialect.PostgresPlusDialect

   If you have not created exo.properties yet, see Configuration overview.
3. Download the JDBC driver and install it to $PLATFORM_JBOSS_HOME/standalone/deployments.

   Tip: You can usually find a suitable driver for your JDK on the database vendor's website. For MySQL: Connector/J; for Oracle: Oracle JDBC.

   Note: For MySQL in particular, this fast install method might not work with some combinations of MySQL server and driver versions. If that happens, install the driver as a module (see below).

4.2.3. Installing the JDBC driver as a JBoss module

This alternative method solves a deployment problem with some combinations of MySQL server and driver versions. The exact versions are not documented, but the problem likely occurs with recent versions, such as MySQL server 5.6.19 and mysql-connector-java-5.1.35-bin.jar.

When it happens, you get a JBAS014775 message like the following and Platform JBoss does not start:

JBAS014775:    New missing/unsatisfied dependencies:
      service jboss.jdbc-driver.mysql-connector-java-5_1_35-bin_jar (missing)
dependents:
      [service jboss.data-source.java:/comp/env/exo-idm_portal,
      service jboss.driver-demander.java:/comp/env/exo-jcr_portal,
      service jboss.driver-demander.java:/comp/env/exo-idm_portal,
JBAS014799: ... and 3 more ]

Remove the jar from $PLATFORM_JBOSS_HOME/standalone/deployments/ and install it as a JBoss module:

1. Create a new folder: $PLATFORM_JBOSS_HOME/modules/com/mysql/main/.
2. Place the driver (.jar) in that folder.
3. Create a module.xml file in that folder with the following content:

   <?xml version="1.0" encoding="UTF-8"?>
   <module xmlns="urn:jboss:module:1.0" name="com.mysql">
       <resources>
           <resource-root path="mysql-connector-java-5.1.35-bin.jar"/> <!-- replace with your jar file -->
       </resources>
       <dependencies>
           <module name="javax.api"/>
       </dependencies>
   </module>

4. Modify the datasources configuration in standalone-exo.xml: declare a driver in the drivers tag and reference it in the datasource tag:

   <subsystem xmlns="urn:jboss:domain:datasources:1.1">
       <datasources>
           <!-- eXo IDM Datasource for PLF -->
           <datasource enabled="true" jndi-name="java:/comp/env/exo-idm_portal"
             jta="false" pool-name="exo-idm_portal" spy="false" use-ccm="true"
             use-java-context="true">
               <driver>com.mysql</driver>
               <connection-url>jdbc:mysql://localhost:3306/plf?autoReconnect=true</connection-url>
               <!-- note: don't put driver-class tag here -->
               ...
           </datasource>
   
           <!-- similar to other datasources, JCR (and Quartz in cluster) -->
           <drivers>
               <driver name="com.mysql" module="com.mysql">
                   <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                   <driver-class>com.mysql.jdbc.Driver</driver-class>
               </driver>
           </drivers>
       </datasources>
   </subsystem>

4.3. Datasource JNDI name

As said above, eXo Platform uses two datasources, exo-idm_portal and exo-jcr_portal. If for any reason you change those names in the datasource configuration (XML file), you need to match them in some other files.

Note: The properties file (exo.properties) does not take the "_portal" suffix, because eXo appends it automatically, as explained below.

The suffix of the datasource JNDI names must be "_portal". Take JCR as an example; it uses the following property to look up a datasource for the portal:

exo.jcr.datasource.name=java:/comp/env/exo-jcr

Because the core of eXo Platform is designed to support multiple portals, there are theoretically different datasources for different portals. Consequently, this property is treated as the datasource name's prefix, and the portal name (by default "portal") is appended to complete the name in the JNDI lookup.

So if you change the JNDI names exo-idm_portal and exo-jcr_portal, edit the following properties:

# JNDI Name of the IDM datasource
exo.idm.datasource.name=java:/comp/env/exo-idm
...
# name of the datasource that will be used by eXo JCR
exo.jcr.datasource.name=java:/comp/env/exo-jcr

in gatein/conf/exo.properties (Tomcat) or standalone/configuration/gatein/exo.properties (JBoss). If you have not created exo.properties yet, see Configuration overview.

On Tomcat only, also edit conf/Catalina/localhost/context.xml.default:

<ResourceLink name="exo-jcr_portal" global="exo-jcr_portal" type="javax.sql.DataSource"/>
<ResourceLink name="exo-idm_portal" global="exo-idm_portal" type="javax.sql.DataSource"/>

4.4. Frequently asked questions

• Q1. Any note for Microsoft SQL?
• Q2. How to remove the idle MySQL connections?
• Q3. How to enable managed connection?

Q1. Any note for Microsoft SQL?

For Microsoft SQL, add sendStringParametersAsUnicode=false to the connection url, as in the example below:

<Resource name="exo-idm_portal" auth="Container" type="javax.sql.DataSource"
initialSize="5" maxActive="20" minIdle="5" maxIdle="15" maxWait="10000"
validationQuery="SELECT 1" validationQueryTimeout="5"
testWhileIdle="true" testOnBorrow="true" testOnReturn="false"
timeBetweenEvictionRunsMillis="30000" minEvictableIdleTimeMillis="60000"
removeAbandoned="true" removeAbandonedTimeout="300" logAbandoned="false"
poolPreparedStatements="true"
username="plf" password="plf"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;databaseName=plf;sendStringParametersAsUnicode=false" />

This parameter is necessary to avoid possible performance problems. MS SQL Server differentiates Unicode data types (such as nchar) from ASCII data types (such as char). By default (without this parameter), all JDBC drivers send strings in Unicode format to SQL Server. When, for example, comparing against a non-Unicode column, SQL Server first tries to convert the data in the table into Unicode, which might cause a serious performance issue.

The parameter differs slightly between JDBC drivers. See more details here.

Q2. How to remove the idle MySQL connections?

Some RDBMSs, like MySQL, close idle connections after a period (8 hours by default on MySQL). A connection taken from the pool then becomes invalid and any SQL command fails with errors such as:

org.hibernate.SessionException: Session is closed!
at org.hibernate.impl.AbstractSessionImpl.errorIfClosed(AbstractSessionImpl.java:72)
at org.hibernate.impl.SessionImpl.getTransaction(SessionImpl.java:1342)

To avoid this, use DBCP to monitor idle connections and drop them when they are invalid, with the testWhileIdle, timeBetweenEvictionRunsMillis and validationQuery parameters. The validation query is specific to your RDBMS. For MySQL:

testWhileIdle="true" timeBetweenEvictionRunsMillis="300000" validationQuery="SELECT 1"

• testWhileIdle activates idle connection monitoring.
• timeBetweenEvictionRunsMillis defines the interval between two checks in milliseconds (5 minutes in the example).
• validationQuery provides a simple SQL command to validate the connection to the RDBMS.

For more details, refer to:

• http://markmail.org/message/a3bszoyqbvi5qer4
• http://stackoverflow.com/questions/15949/javatomcat-dying-database-connection
• https://confluence.atlassian.com/display/JIRA/Surviving+Connection+Closures

Q3. How to enable managed connection?

This question is specific to the JCR datasource in the Platform JBoss package.

To use managed connections, set gatein.jcr.datasource.managed to "true" in $PLATFORM_JBOSS_HOME/standalone/configuration/gatein/exo.properties. See Configuration overview if you have not created this file yet.

gatein.jcr.datasource.managed=true

This property needs to be "true" in two cases:

• You use a datasource with JTA enabled:

  <datasource jndi-name="java:/comp/env/exo-jcr_portal" jta="true" ... />

• You use an xa-datasource:

  <xa-datasource jndi-name="java:/comp/env/exo-jcr_portal" ... />

Using managed connections has pros and cons, so only do it if you know what you are doing. By default, eXo Platform JBoss uses datasource jta="false".

Source: http://docs.exoplatform.com/public/topic/PLF41/PLFAdminGuide.Database.html