Databases

MySQL 5.x / MariaDB

JDBC driver

With MySQL version 5.x / MariaDB, you must use a 5.x JDBC Driver.

Connecting over SSL

Some MySQL 5.x / MariaDB servers must be accessed over SSL, in such a case some additional arguments are needed:

Connecting from the command line:

mysql --protocol=tcp --ssl-mode=REQUIRED --host=<host> --port=<port> --database=<database> --user=<username> --password=<password> [--default-character-set=utf8]

JDBC URL:

jdbc:mysql://<username>:<password>@<host>:<port>/<database>?autoReconnect=true&verifyServerCertificate=false&useSSL=true&requireSSL=true[&useUnicode=yes&characterEncoding=utf8&characterResultSets=utf8]

Maximum packet size

You need to check and increase if needed the max_allowed_packet settings to be compliant with your requirement, a good minmal value is 16M. If you are using BLOBs for files you should set it accordingly to the maximum size of files you are supposed to handle.

Limitations

MySQL 5.x / MariaDB have a limit of 64Kb per records (except LOBs columns)

MySQL 8.x

JDBC driver

With MySQL version 8.x, you must use a 8.x JDBC Driver.

Datasource configuration

You will have to alter your META-INF/context.xml manually to use the new driver class com.mysql.cj.jdbc.Driver (instead of the deprecated com.mysql.jdbc.Driver)

You will also need to add an explicit time zone to your database JDBC URL, e.g. &amp;serverTimezone=Europe/Paris

PostgreSQL

Backslashes

Beware backslashes treatment in PostgreSQL :

Before PostgreSQL 9.1, the configuration variable standard_conforming_strings was turned off by default. That's why PostgreSQL did not treat backslashes literally but interpreted them.

But according to SQL standard, backslashes should be treated literally. So, from PostgreSQL 9.1, the standard_conforming_strings config variable has been turned on.

If you want your code be portable between different database engines, you may want to have this config variable turned on. So if you're on PostgreSQL 9.0 or lower :

alter database YOUR_DB set standard_conforming_strings=on;

An other means is to use the E PostgreSQL specific prefix to construct a literal query regardless server or connection configuration like standard_conforming_strings.

Example :

set standard_conforming_strings to true;

select 'hop\'';
hop\'

select E'hop\'';
hop'
set standard_conforming_strings to false;

select 'hop\'';
hop'

select E'hop\'';
hop'

Case sensitive search

By default PostgreSQL does not provide case-insensitive search match when using standard LIKE comparator.

Active maintenance releases of Simplicité now use by default the non standard ILIKE comporator.

See this document for details.

Database linking

PostgreSQL 9.6+'s extension module postgres_fdw allows you to link a remote database into a local database.

To do so, connect to the local database as the postgres super administrator:

sudo su - postgres -c "psql -d <local database name>"

Then issue the following commands:

CREATE EXTENSION postgres_fdw;
CREATE SERVER <arbitrary remote server name> FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '<remote host name or IP>', port '<remote port>', dbname '<remote database name>');
CREATE USER MAPPING FOR CURRENT_USER SERVER <arbitrary remote server name> OPTIONS (user '<remote username>', password '<remove password>');
CREATE USER MAPPING FOR <local username> SERVER <arbitrary remote server name> OPTIONS (user '<remote username>', password '<remove password>');
CREATE SCHEMA <arbitrary local schema name>;
IMPORT FOREIGN SCHEMA <remote schema name, e.g. public> FROM SERVER <arbitrary local server name> INTO <arbitrary local schema name>;
GRANT USAGE ON SCHEMA <arbitrary local schema name> TO <local username>;
GRANT <ALL PRIVILEGES|SELECT> ON ALL TABLES IN SCHEMA <arbitrary local schema name> TO <local username>;

Use ALL PRIVILEGES for read+write access, SELECT for read-ony access.

Then after connecting as local user to local database:

PGPASSWORD=<local password> psql -h <local host> -p <local port> -U <local username> -d <local database>

you can access remote tables like this select * from <arbitrary local schema name>.<remote table name>;

Note: you can only import some of the remote schema's tables by appending LIMIT (<remote table name 1>, <remote table name 2>, ...) to the above IMPORT FOREIGN SCHEMA statement. Of course you can use other linking strategies depending on your requirement.

Oracle

Limitations

Oracle Express (XE) 11g client and server

Pre-installation

Check that you have at least 2Gb of swap

If it is not the case add a swapfile like this:

sudo su -
dd if=/dev/zero of=/swapfile bs=2048 count=2097152
chmod 600 /swapfile
mkswap /swapfile
echo "/swapfile swap swap defaults 0 0" >> /etc/fstab
swapon -a
swapon -s
exit

You may also need the bc package:

sudo yum install bc

Instalation and configuration

Download the package from Oracle website

Unzip it and install the RPM file:

unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
rpm -ivh Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm
rm -fr Disk1

Then configure the server (this includes setting the <system password> used bellow, e.g. manager):

sudo /etc/init.d/oracle-xe configure

Post-installation

Create environment file:

sudo vi /etc/profile.d/oraclexe.sh

With following content:

ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
export ORACLE_HOME
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH
SQLPATH=${ORACLE_HOME}/lib:${SQLPATH}
export SQLPATH
PATH=${ORACLE_HOME}/bin:${PATH}
export PATH
NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG
ORACLE_SID=XE
export ORACLE_SID
sudo chmod +x /etc/profile.d/oraclexe.sh

Then you can connect as SYSDBA either using the oracle account (no password needed in this case):

sudo su - oracle
sqlplus / as sysdba

or from any other account:

sqlplus sys/<system password>@XE as sysdba

Optimization

APEX is a management web UI that is managed by the Oracle server. As it requires lots of resources, it is better to disable it if not needed.

To do so, set APEX port to zero:

EXEC DBMS_XDB.SETHTTPPORT(0);
COMMIT;

To reactivate it if needed, reset APEX port to any non zero value (e.g. 8080):

EXEC DBMS_XDB.SETHTTPPORT(8080);
COMMIT;

Warning about express JDBC driver

Some of the versions of the Oracle express JDBC driver may cause some performance issues in high data volume contexts.

You should use an up-to-date regular version of the Oracle JDBC driver instead.

Oracle 12c instant client

This applies only when you only need an Oracle client on your machine.

Installation

Download the 3 instant client PRM packages from Oracle website:

And install them:

rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm oracle-instantclient12.2-tools-12.2.0.1.0-1.x86_64.rpm

Post-installation

Create environment file:

sudo vi /etc/profile.d/oracleclient.sh
sudo chmod +x /etc/profile.d/oracleclient.sh

With following content:

ORACLE_HOME=/usr/lib/oracle/12.2/client64
export ORACLE_HOME
LD_LIBRARY_PATH=${ORACLE_HOME}/lib:${LD_LIBRARY_PATH}
export LD_LIBRARY_PATH
PATH=${ORACLE_HOME}/bin:${PATH}
export PATH
NLS_LANG=AMERICAN_AMERICA.UTF8
export NLS_LANG

You can improve sqlplus behavior by appending options in $ORACLE_HOME/sqlplus/admin/glogin.sql like:

set pagesize 999
set linesize 999

Microsoft SQLServer

Installation

The up-to-date instructions are in this document:

Install the YUM repository:

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-preview.repo

Install server:

sudo yum install mssql-server

Enable and start server:

sudo systemctl enable mssql-server
sudo systemctl start mssql-server

Limitations

Cumulated length of indexed columns cannot exceed 900 bytes (this makes any long text field non indexable)