Databases

MySQL/MariaDB

Connecting over SSL

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

Connecting from the command line:

mysql.exe --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/MariaDB have a limit of 64Kb per records (except LOBs columns)

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.

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

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:

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

Post-installation

Create environment file:

sudo vi /etc/profile.d/oraclexe.sh
sudo chmod +x /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

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

Limitations

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