Skip to main content

Database configuration

Introduction​

Before you look into the Encap server's DBMS configuration, you must ensure that the DBMS you have chosen to use is installed and ready for use.

This involves getting the system up and running, making sure that its access is restricted, such as by firewall rules and secured by strong passwords, and that a user has been created within the system for use by the Encap server.

This user requires full control over a single separate database. Provided the database is created and the user has necessary privileges, the data structure such as tables and columns will be created.

With a DBMS configured for use by the Encap server up and running, you can now learn how to configure the Encap server.

Database Configuration​

To configure the database connection, edit the file named encap-db.properties located in the <ENCAP_HOME>/core/config directory of your unpacked Encap server distribution. Examples exists for MySQL and Oracle in the below chapters.

To change the configured DBMS, modify the encap.db.jdbcUrl and encap.db.driverClass properties to activate a different DBMS driver and point to your DBMS server.

If you need to change the authentication credentials towards the DBMS, modify the encap.db.user and encap.db.password properties to provide the credentials of the user that you have created in your DBMS for use by the Encap server. In the same file, you will also find properties for tuning the database connection pool.

Supported databases​

Database drivers​

Database drivers for MySQL and Oracle has to be downloaded because their licences doesn't allow the database drivers to be included in the Encap server distribution.

Encap server has been tested with Oracle JDBC driver and MySQL Connector of the latest version at time of release. Download the appropriate version from the links below.

The database driver must be placed under both <ENCAP_HOME>/core/lib and <ENCAP_HOME>/core/scripts/lib, where <ENCAP_HOME> refers to your unpacked Encap server distribution package.

MySQL​

Encap server requires the binary log format to be set to row. In the mysql configuration file my.cnf (default to /etc/mysql/my.cnf), you have to change or insert:

binlog_format = row

Identifier Case Sensitivity in mysql​

Encap server requires that the lower_case_table_names MySQL system variable is set to 0 which means that table and database names are stored on disk using the casing specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case-sensitive.

Example: MySQL - Create user for Encap server

$ mysql -u root -p
create user 'encap'@'localhost' identified by 'encap';
create database if not exists encap;
grant all privileges on encap.* to 'encap'@'localhost' with grant option;

Database configuration for MySQL pointing to localhost with the default port (3306) of an MySQL installation.

Example: Encap DB configuration file ./core/config/encap-db.properties for MySQL

# DB address and user
encap.db.driverClass=com.mysql.cj.jdbc.MysqlDataSource
encap.db.jdbcUrl=jdbc:mysql://localhost:3306/encap
encap.db.user=encap
encap.db.password=encap

# Pool tuning
encap.db.maxPoolSize=50
encap.db.minimumIdle=10
encap.db.idleTimeout=360000
# Pool size for integrated logging db pool
# encap.db.maxPoolSize * encap.db.log.mainPoolFactor => logging connection pool size
encap.db.log.mainPoolFactor=0.25
# encap.db.maxPoolSize * encap.db.supplementary.mainPoolFactor => manual transaction connection pool size
encap.db.supplementary.mainPoolFactor=0.20

MySQL database connection pool parameters​

The MySQL maximum number of database connections is 151 by default MySQL :: MySQL 8.0 Reference Manual :: B.3.2.5 Too many connections.

Maximum amount of database connections for the Encap server can be calculated by:

encap.db.maxPoolSize + (encap.db.maxPoolSize * encap.db.log.mainPoolFactor) + (encap.db.maxPoolSize * encap.db.supplementary.mainPoolFactor)
50 + (50 * 0.25) + (50 * 0.20) ≈ 75

To set the maximum number of database connections according to the configuration in encap-db.properties, calculate the total connections which are maxPoolSize +the following MySQL constructs can be used:

mysql> SET GLOBAL max_connections = 75;

To make this setting persistent, edit the MySQL configuration file my.cnf, under [mysqld] section:

[mysqld]
max_connections = 75

The configuration file my.cnf can be found by help of

mysqld --help --verbose  2>/dev/null | grep my.cnf

MySQL SSL driver options​

If you experience MySQL SSL warnings in the logs, looking like this (There are some variants it does not have to be verbatim):

Establishing SSL connection without server's identity verification is not recommended. According to MySQL requirements, SSL connection must be established by default if the explicit option isn't set.

For compliance with existing applications not using SSL the verifyServerCertificate property is set to false. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

This is probably caused by that you either don't have built-in support for SSL on your MySQL server, or it is disabled.

You can prevent this warning to appear in the logs by enabling SSL on the server or modifying the JDBC URL and adding the query parameters: useSSL=false, in the config file encap-db.properties.

Example: Explicit disabled ssl on jdbc client/driver

encap.db.jdbcUrl=jdbc:mysql://localhost:3306/encap?useSSL=false

How to enable SSL on MySQL servers: How To Configure SSL/TLS for MySQL on Ubuntu 16.04 | DigitalOcean.

Oracle​

Example setup for Encap server user in Oracle.

Example: Oracle - Create tablespace and user for Encap server

$ sqlplus "/ as sysdba"

/* oracle-create-tablespace.sql */
prompt create tablespace ENCAP_DATA
create tablespace ENCAP_DATA
   logging
   datafile '/opt/oracle/oradata/dbconfig/ORCL/ORCL_encap_data.dbf'
   size 5M autoextend on next 5M maxsize 2048M
   extent management local uniform size 1M
   segment space management auto
  ;

/* oracle-create-user.sql */
create user encap identified by encap
default tablespace ENCAP_DATA
temporary tablespace temp
quota unlimited on ENCAP_DATA
;

grant connect, create sequence, create view, alter session, create table, create procedure to encap;

Database configuration for Oracle pointing to localhost with the default port (1521) of an Oracle installation.

Example: Encap DB configuration file ./core/config/encap-db.properties for Oracle

# DB address and user
encap.db.driverClass=oracle.jdbc.pool.OracleDataSource
encap.db.jdbcUrl=jdbc:oracle:thin:@localhost:1521:xe
encap.db.user=encap
encap.db.password=encap

# Pool tuning
encap.db.maxPoolSize=50
encap.db.minimumIdle=10
encap.db.idleTimeout=360000
# Pool size for integrated logging db pool
# encap.db.maxPoolSize * encap.db.log.mainPoolFactor => logging connection pool size
encap.db.log.mainPoolFactor=0.25
# encap.db.maxPoolSize * encap.db.supplementary.mainPoolFactor => manual transaction connection pool size
encap.db.supplementary.mainPoolFactor=0.20

Oracle database connection pool parameters​

Here are some parameters typically used for tuning. Note that the links may not be to your Oracle version, so you should look at the documentation for these flags for your specific version.

Maximum amount of database connections for the Encap server can be calculated by:

encap.db.maxPoolSize + (encap.db.maxPoolSize * encap.db.log.mainPoolFactor) + (encap.db.maxPoolSize * encap.db.supplementary.mainPoolFactor)
50 + (50 * 0.25) + (50 * 0.20) ≈ 75

To set the number of database connections according to maxPoolSize in encap-db.properties, the following Oracle constructs can be used, assuming Oracle startup is done with an Oracle server parameter binary file (SPFILE):

alter system set processes = 75 scope = spfile;
alter system set sessions = 100 scope = spfile;
alter system set transactions = 125 scope = spfile;
shutdown immediate;
startup;

For Oracle startup with a text based PFILE as argument to the startup command, the processes, sessions and transaction parameters can be set in the PFILE, and effectuated by an Oracle instance restart.

On this page