Server

JDBC Datasource

Updated: March 18, 2024

Accessing a Single Database (Default)

Nuxeo uses JDBC connections for a number of purposes, and therefore defines different datasource names for them. Examples are:

  • jdbc/nxsqldirectory
  • jdbc/nxaudit-logs
  • jdbc/repository-default

Additional datasources can be used, for instance when defining a Directory you have to specify which datasource it uses, and you may want to use something else than the standard jdbc/nxsqldirectory if you want to store data elsewhere.

These datasources are all defined in nxserver/config/datasources-config.xml (which is generated from templates/common-base/nxserver/config/datasources-config.xml.nxftl). The datasources are defined like this:

<link name="jdbc/NuxeoDS" global="jdbc/nuxeo" type="javax.sql.DataSource" />
<link name="jdbc/nxsqldirectory" global="jdbc/nuxeo" type="javax.sql.DataSource" />

So, by default they are actually linked to the common datasource ${nuxeo.db.commonds} (jdbc/nuxeo by default):

<datasource name="${nuxeo.db.commonds}"
  driverClassName="${nuxeo.db.driver}"
  url="${nuxeo.db.jdbc.url}"
  username="${nuxeo.db.user}"
  password="${nuxeo.db.password}"
  maxPoolSize="${nuxeo.db["max-pool-size"]}"
  minPoolSize="${nuxeo.db["min-pool-size"]}"
  blockingTimeoutMillis="${nuxeo.db["blocking-timeout-millis"]}"
  idleTimeoutMinutes="${nuxeo.db["idle-timeout-minutes"]}"
  sqlExceptionSorter="${nuxeo.db["sql-exception-sorter"]}"
  validationQuery="${nuxeo.db.validationQuery}"
  accessToUnderlyingConnectionAllowed="true" />

The properties used in this file come from the ones defined in bin/nuxeo.conf and the template chosen for your database.

Accessing Multiple Databases

You may need to access to multiple databases and then require to run the connection in XA mode. In such case, you should enable the XA mode by setting nuxeo.db.xaMode=true in bin/nuxeo.conf.

Sharing Connections with Other Applications

When you're deploying Nuxeo as a static WAR, you may want to share the database connections with other applications. You should then configure the datasource server side such as a global resource in Tomcat and instruct Nuxeo to acquire a connection from that pool instead of opening a new JDBC connection.

<datasource name="${nuxeo.db.commonds}"
  datasource="jdbc/your-global-datasource"
  maxPoolSize="${nuxeo.db["max-pool-size"]}"
  minPoolSize="${nuxeo.db["min-pool-size"]}"
  blockingTimeoutMillis="${nuxeo.db["blocking-timeout-millis"]}"
  idleTimeoutMinutes="${nuxeo.db["idle-timeout-minutes"]}"
  sqlExceptionSorter="${nuxeo.db["sql-exception-sorter"]}"
  validationQuery="${nuxeo.db.validationQuery}"
  accessToUnderlyingConnectionAllowed="true" />

Managing Idle Connections

By default idle (unused) connections are maintained 5 minutes in the pool. This can be configured by changing the parameter nuxeo.db.idle-timeout-minutes in nuxeo.conf:

nuxeo.db.idle-timeout-minutes=30

Recovering from Connection Errors

Nuxeo is able to recover from lost connections by using an exception sorter, whose role is to determine which errors are fatal:

  • if the connection is detected as not valid by the driver with a 10 seconds timeout,

  • if the returned error code is considered FATAL, the connection is destroyed from the pool,

  • otherwise the connection is kept in the pool and will be re-used later.

The datasource parameter to manage the connection error is  sqlExceptionSorter. Four implementations are available:

  • org.tranql.connector.AllExceptionsAreFatalSorter
  • org.tranql.connector.NoExceptionsAreFatalSorter
  • org.tranql.connector.jdbc.KnownSQLStateExceptionSorter
  • org.nuxeo.runtime.datasource.DatasourceExceptionSorter

By the default, the DatasourceExceptionSorter is configured. It enables you to contribute fatal SQL codes for a defined database. Each sorter contribution is identified uniquely by its id. You could override or extend a sorter configuration using the override attribute. Once a  SQL exception is thrown, a sorter configuration is selected by comparing the exception stack trace packages with the path attribute. If a package in the stack trace is starting by the path value, this sorter configuration is selected. If no sorter configuration is matching the trace, the default sorter configuration identified by the empty string is selected. The contributed SQL codes could be a SQL class code or a complete SQL code. For easier configuration, you could use the enumeration labels  for the standard SQL class code .

<extension target="org.nuxeo.runtime.datasource" point="sorter">
  <sorter id="adb" path="org.adb" override="false">
    <code>ConnectionException</code>
    <code>RemoteDatabaseAccess</code>
    <code>SystemError</code>
    <code>90001</code>
  </sorter>
</extension>

You can also define your own exception sorter by implementing [org.tranql.connector.ExceptionSorter](https://javadocs.com/docs/org.tranql/tranql-connector/1.6/org/tranql/connector/ExceptionSorter.java). In that case, redefine nuxeo.db.sql-exception-sorter in nuxeo.conf to change this:

nuxeo.db.sql-exception-sorter=org.tranql.connector.jdbc.KnownSQLStateExceptionSorter