How to connect MySQL / MariaDB + HikariCP with Liferay 7 / DXP

Procedure for connecting HikariCP (Liferay DXP standard) and MySQL to the Connection pool with the Tomcat bundle

Liferay 7 / DXP uses the connection pool library HikariCP as standard. The following is the setting method to apply the latest database connector and connection pool library to connect to MySQL.

For MySQL

JNDI name, connection pool library settings

Add the following to portal-ext.properties

portal-ext.properties

setup.database.jar.url[com.mysql.jdbc.Driver]=http://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.45/mysql-connector-java-5.1.45.jar
setup.liferay.pool.provider.jar.name[hikaricp]=hikaricp.jar
setup.liferay.pool.provider.jar.url[hikaricp]=http://central.maven.org/maven2/com/zaxxer/HikariCP/2.7.4/HikariCP-2.7.4.jar
jdbc.default.jndi.name=jdbc/LiferayPool
jdbc.default.liferay.pool.provider=hikaricp

ROOT.xml configuration sample

Describe the following resources between the <Context> tags.

Enter the address of the DB you are using in the [server-url-is-here] part. Enter the database name created in MySQL in the part of [your-DB-name-is-here]. Set dataSource.user and dataSource.password to those of your environment.

According to Liferay's Development Checklist, the maximum number of connections is 75 with the following settings, but please change it according to your environment. It is also necessary to change the number of thread pools according to the number of database connections. Generally, the database pool size is about 30% to 40% of the thread pool size, so it is assumed that the thread pool at this time is set to about 250.

xml:tomcat-8.0.32/conf/Catalina/localhost/ROOT.xml

<Resource
    auth="Container"
    type="javax.sql.DataSource"
    name="jdbc/LiferayPool"
    factory="com.zaxxer.hikari.HikariJNDIFactory"
    dataSourceClassName="com.mysql.jdbc.jdbc2.optional.MysqlDataSource"
    driverClass="com.mysql.jdbc.Driver"
    idleMaxAgeInMinutes="15"
    idleConnectionTestPeriodInMinutes="10"
    partitionCount="3"
    acquireIncrement="5"
    maxConnectionsPerPartition="25"
    minConnectionsPerPartition="4"
    statementsCacheSize="50"
    dataSource.url="jdbc:mysql://[server-url-is-here]:3306/[your-DB-name-is-here]?useUnicode=true&amp;characterEncoding=UTF-8&amp;useFastDateParsing=false&amp;useSSL=false"
    dataSource.user="root"
    dataSource.password="password"
    jdbcUrl="jdbc:mysql://[server-url-is-here]:3306/[your-DB-name-is-here]?useUnicode=true&amp;characterEncoding=UTF-8&amp;useFastDateParsing=false&amp;useSSL=false"/>

For MariaDB

JNDI name, connection pool library settings

Add the following to portal-ext.properties

portal-ext.properties

setup.database.jar.name[org.mariadb.jdbc.Driver]=mariadb.jar
setup.database.jar.url[org.mariadb.jdbc.Driver]=https://downloads.mariadb.com/Connectors/java/connector-java-2.2.0/mariadb-java-client-2.2.0.jar
setup.liferay.pool.provider.jar.name[hikaricp]=hikaricp.jar
setup.liferay.pool.provider.jar.url[hikaricp]=http://central.maven.org/maven2/com/zaxxer/HikariCP/2.7.4/HikariCP-2.7.4.jar
jdbc.default.jndi.name=jdbc/LiferayPool
jdbc.default.liferay.pool.provider=hikaricp

If mariadb.jar is not expanded to $ {tomcat_home} / lib / ext, download it manually and rename it to mariadb.jar and place it.

ROOT.xml configuration sample

As in MySQL, describe the following resources between the <Context> tags.

Enter the address of the DB you are using in the [server-url-is-here] part. Enter the database name created in MySQL in the part of [your-DB-name-is-here]. Set dataSource.user and dataSource.password to those of your environment.

xml:tomcat-8.0.32/conf/Catalina/localhost/ROOT.xml

<Resource
    auth="Container"
    type="javax.sql.DataSource"
    name="jdbc/LiferayPool"
    factory="com.zaxxer.hikari.HikariJNDIFactory"
    dataSourceClassName="org.mariadb.jdbc.MySQLDataSource"
    driverClass="org.mariadb.jdbc.Driver"
    idleMaxAgeInMinutes="15"
    idleConnectionTestPeriodInMinutes="10"
    partitionCount="3"
    acquireIncrement="5"
    maxConnectionsPerPartition="25"
    minConnectionsPerPartition="4"
    statementsCacheSize="50"
    jdbcUrl="jdbc:mariadb://[server-url-is-here]:3306/[your-DB-name-is-here]?useUnicode=true&amp;characterEncoding=UTF-8&amp;useFastDateParsing=false&amp;useSSL=false"
    dataSource.url="jdbc:mariadb://[server-url-is-here]:3306/[your-DB-name-is-here]?useUnicode=true&amp;characterEncoding=UTF-8&amp;useFastDateParsing=false&amp;useSSL=false"
    dataSource.user="root"
    dataSource.password="mariadb"
    />

Launch Liferay

Start Liferay with tomcat-8.0.32 / bin / startup.sh for Mac / * nix and tomcat-8.0.32 / bin / startup.bat for Windows.