Database Configuration documentation for the dotCMS Content Management System

While dotCMS does cache aggressively and will attempt to limit database traffic on the front end of your site, the database is an important part of your overall dotCMS system and your database performance is vital to dotCMS, especially in the authoring environment.

Supported Databases

The following table lists all of the databases supported by dotCMS, the versions of dotCMS each database is supported in, and official links for each database.

DatabasedotCMS VersionsOfficial Links
PostgreSQLAll versions (Community and Enterprise)Main site / Documentation / Download
MySQLAll versions (Community and Enterprise)Main site / Documentation / Download
Microsoft SQL ServerEnterprise Professional and Enterprise Prime onlyMain site / Documentation
OracleEnterprise Prime onlyMain site / Documentation

To see the section of this document specific to each database, click the link for that database in the first column. To view an official site for a database, click the appropriate link in the last column. Please see the dotCMS features list for more information on features supported in different dotCMS versions.

Supported Versions

New dotCMS installations should always use the most recent release of the selected database. Support for older database versions is provided only to customers with dotCMS support contracts in place before new database versions are released.

For a complete list of the current versions of each database supported, please see the Database portion of the dotCMS Technology Requirements documentation.

Configuration

The Data source can be initialized in multiple different ways. Any of the following methods can be used, and these sources will be checked in the order they are listed here:

Database Configuration File (db.properties)

Database configuration is done in the /dotserver/tomcat-X.x.xx/webapps/ROOT/META-INF/db.properties file via ROOT folder plugin.

The db.properties file contains parameters you can modify to change how dotCMS connects to your database.

Note: Previous versions of dotCMS allowed configuration of the database through the context.xml file in the WEB-INF folder. This file is still supported for backward compatibility, but is deprecated, and will stop being supported in a future release.

System Variables

The following system variables can be set to initialize a datasource using this option:

System EnvironmentDefault Value
connection_db_driverorg.postgresql.Driver
connection_db_base_urljdbc:postgresql://localhost/dotcms
connection_db_username 
connection_db_password 
connection_db_max_total60
connection_db_max_idle10
connection_db_max_wait60000
connection_db_validation_querySELECT 1
connection_db_leak_detection_threshold60000
connection_db_default_transaction_isolation 

Docker Secrets

Note: Docker Secrets are supported only in Swarm mode. For more information, please see the Docker documentation.

There are two different ways to configure your database using Docker Secrets:

  1. Using a custom file (stored in the Docker container)
    • The absolute path must of this file must be set in the dotmarketing-config.properties using the property DOCKER_SECRET_FILE_PATH.
    • The file must have the following format:
      key1=value1
      key2=value2
      
  2. Using Docker commands

For a list of the properties that can be set via either of these methods, please see System Variables, above.


Important:

Docker secrets currently are supported for database configuration only for the dotCMS 02-single-node implementation. Other implementations can not use Docker secrets, because they use the Docker stack, which doesn't support dependencies among docker services, so the order in which services are deployed is not guaranteed.


Docker Commands

To create docker secrets and run dotcms with secrets:

  1. Initialize the swarm
    docker swarm init
    
  2. Create the secrets
    printf dotcms | docker secret create connection_db_name -
    printf org.postgresql.Driver | docker secret create connection_db_driver -
    printf jdbc:postgresql://db:5432/dotcms | docker secret create connection_db_base_url -
    printf dotcmsdbuser | docker secret create connection_db_username -
    printf password | docker secret create connection_db_password -
    printf 60 | docker secret create connection_db_max_total -
    printf 10 | docker secret create connection_db_max_idle -
    printf 60000 | docker secret create connection_db_max_wait -
    printf SELECT 1 | docker secret create connection_db_validation_query -
    printf 60000 | docker secret create connection_db_leak_detection_threshold -
    printf 2 | docker secret create connection_db_default_transaction_isolation -
    
  3. Deploy the stack and create the containers
    docker stack deploy -c docker-compose.yml dotcms_stack
    

For more information about how to use Docker Secrets, please see the following Docker documentation:

Database Specific Requirements and Known Issues

The following sections cover the specific requirements and known issues for each specific database supported by dotCMS.

PostgreSQL

Important First Steps

Postgres does not support procedural languages “out-of-the-box”. To enable procedural languages, run the following command in postgres before creating your dotCMS database:

createlang plpgsql template1

For more information, please see the PostgreSQL createlang documentation.

MySQL

Important First Steps

  • When creating the dotCMS table, make sure to create it with the UTF-8 char set and the DEFAULT UTF-8 collation. e.g.
    create database dotcms_zip default character set = utf8 default collate = utf8_general_ci;
    
  • If MySQL is running with log-bin enabled set:
    1. Place the following in your my.cnf file (for Unix/Mac) or my.ini file (for Windows):
      log-bin = /path/to/log-bin/file
      binlog-format=row
      lower_case_table_names=1
      
    2. Restart the MySQL Service and dotCMS Server

Backup (mysqldump)

Since MySQL version 5, by default mysqldump will back up all the triggers but NOT the stored procedures/functions. There are 2 mysqldump parameters that control this:

  • routines (FALSE by default, backs up the stored procedures)
  • triggers (TRUE by default, backs up the triggers)

To ensure that dotCMS stored procedures are included in the dump/backup, you must pass the -routines argument to the mysqldump command:

mysqldump  --routines > outputfile.sql

For more information, please see the mysqldump documentation.

Known Issues

When upgrading mySQL to a new version, the following permissions issue may appear:

ERROR reindex.ReindexThread: Unable to index record
com.dotmarketing.exception.DotDataException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate parameters that represent INOUT strings irregardless of actual parameter types.
at com.dotmarketing.common.db.DotConnect.loadResult(DotConnect.java:197)
at com.dotmarketing.common.db.DotConnect.loadObjectResults(DotConnect.java:805)
at com.dotmarketing.common.business.journal.DistributedJournalFactoryImpl.findContentReindexEntriesToReindex(DistributedJournalFactoryImpl.java:329)
at com.dotmarketing.common.business.journal.DistributedJournalAPIImpl.findContentReindexEntriesToReindex(DistributedJournalAPIImpl.java:65)
at com.dotmarketing.common.reindex.ReindexThread.fillRemoteQ(ReindexThread.java:211)
at com.dotmarketing.common.reindex.ReindexThread.getNextDocToIndex(ReindexThread.java:221)
at com.dotmarketing.common.reindex.ReindexThread.run(ReindexThread.java:86)
Caused by: java.sql.SQLException: User does not have access to metadata required to determine stored procedure parameter types. If rights can not be granted, configure connection with "noAccessToProcedureBodies=true" to have driver generate param 
Solution

In MySQL, the user running the DB needs special permissions to use the metadata stored procedure in the mysql.procedure table. Therefore, one of the following steps needs to be taken:

  • Make sure the MySQL db user configured in the dotCMS context.xml file is a MySQL super user.
  • In MySQL, extend the db users permissions configured in dotCMS context.xml (that is not a super user), to have access to the mysql.procedure table.
    grant select on mysql.proc to 'dotcms'@'localhost';
    flush privileges; 
    
  • Set the noAccessToProcedureBodies property in the dotCMS context.xml file to true.
    jdbc:mysql://xxx.xxx.xxx.xxx:3306/db_user?useUnicode=true&noAccessToProcedureBodies=true</connection-url> 
    
    • Setting this property works around the inability of a limited db user to access the mysql.procedure table.

For more information on these commands, please see the MySQL Users and Privileges, GRANT command, and JDBC connectors documentation.

MS SQL Server

Note: Use of the MS SQL Server database is only supported in dotCMS Enterprise Professional and Enterprise Prime editions. Please see the list of dotCMS versions for more information on features supported in different version of dotCMS.

Important First Steps

Authentication

Important: You must use SQL user authentication and not Windows User (integrated) authentication. There are no longer any supported open source tools to provide authentication of SQL Server databases using integrated Windows authentication.

The database user which is used to create the dotCMS database should have the db_owner role on the dotcms database

Transaction Locking

Microsoft SQL Server databases can experience issues with transaction locking. Make sure to run the following command on your db BEFORE running dotCMS or establishing any connections to the dotCMS database.

ALTER DATABASE dotcms SET READ_COMMITTED_SNAPSHOT ON;
GO

ALTER DATABASE dotcms SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

Note:

  • Make sure to replace “dotcms” in the above commands with the name of your own SQL server database.

For more information on these commands, please see the MS SQL ALTER DATABASE documentation.

Checking Existing SQL Server Settings

If you wish to find out what your current settings are, run the following query:

SELECT sd.is_read_committed_snapshot_on,sd.snapshot_isolation_state_desc
FROM sys.databases AS sd
WHERE sd.[name] = 'dotcms';

Notes:

  • If you need more information on SQLServer regarding row locking and isolation levels see the Microsoft SQL Server 2005 Row Versioning-Based Transaction Isolation document.
  • If you need to execute SQL queries which might lock a table or row while others are connected to the database, make sure you run SET TRANSACTION ISOLATION LEVEL SNAPSHOT first.
    • If you are writing a dotCMS plugin and you get your connection from the DBConnection factory this will already be done for you.

Oracle

Note: Use of the Oracle database is only supported in dotCMS Enterprise Prime edition. Please see the dotCMS features table for more information on features supported in dotCMS versions.

Important First Steps

Character Set

The character set for your dotCMS Oracle database must be AL32UTF8. Please reference the following statement from the Oracle web site:

Oracle recommends AL32UTF8 as the database character set. AL32UTF8 is Oracle's name for the UTF-8 encoding of the Unicode standard. The Unicode standard is the universal character set that supports most of the currently spoken languages of the world. The use of the Unicode standard is indispensable for any multilingual technology, including database processing.
Database Permissions

The following Oracle commands grant the permissions that are needed to start dotCMS in Oracle.

Note:

  • These are the permissions dotCMS uses to test with Oracle.
  • These might be more permissive than necessary, but you can be sure that Oracle will start if your user has these permissions.
CREATE USER "DOTCMS_SENDER" PROFILE "DEFAULT" IDENTIFIED BY "XXXXXXXX" ACCOUNT UNLOCK;

GRANT "CONNECT" TO "DOTCMS_SENDER";
GRANT "EXP_FULL_DATABASE" TO "DOTCMS_SENDER";
GRANT "GATHER_SYSTEM_STATISTICS" TO "DOTCMS_SENDER";
GRANT "IMP_FULL_DATABASE" TO "DOTCMS_SENDER";
GRANT "OEM_ADVISOR" TO "DOTCMS_SENDER";
GRANT "OEM_MONITOR" TO "DOTCMS_SENDER";
GRANT "RESOURCE" TO "DOTCMS_SENDER";

ALTER USER "DOTCMS_SENDER" DEFAULT ROLE ALL;
EXIT

Known Issues

Oracle 11G R2.0 and most Oracle Express versions have a known issue that may cause errors similar to the following when trying to pull templates:

ORA-00600: internal error code, arguments: [rwoirw: check ret val], [], [], [], [], [], [], [], [], [], [], [] 
00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"

Solution

If you see these types of errors, you may workaround this issue by adding a database trigger to the Role and Policy Modeler Oracle database to set the Oracle parameter _replace_virtual_columns=false.

To do this, run the following from a SQL*Plus session connected to the Role and Policy Modeler Oracle database with administrative privilege (that is, as SYSTEM or SYS):

-- BEGIN
CREATE OR REPLACE TRIGGER WORKAROUNDORA9965278 AFTER
LOGON ON DATABASE BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET "_replace_virtual_columns"=false';
END;
/
-- END

For more information on Oracle database configuration, please see the Oracle documentation.