Skip to main content

Initialize database on your server

The purpose of this tutorial is to show how to initialize a database required for Soffid IAM installation.

Prerequisites

First of all, you sould install a database required in the Soffid IAM installation.

The supported databases are:

  • MySQL
  • MariaDB
  • PostgreSQL
  • Oracle 
  • Microsoft SqlServer

MySQL/MariaDB

In order to configure MySQL database you need access to the database administration tool (mysql) with superuser permissions using a TCP/IP connection. If needed, please create a user for the Soffid installation. If you don't have such a user, or don't know its password, please access MySQL as root, execute the mysql tool and create the user with grant command (where ADMIN_USER is the user to be used during the installation process to create the soffid repository database and ADMIN_PASSWORD is the required password).

create database soffid;
use soffid;
grant all privileges on *.* to ADMIN_USER@localhost identified by 'ADMIN_PASSWORD' with grant option;

In addition, in order to be able to manage big files, like process definitions or software add-ons, we have to modify the max_allowed_packet parameter on MySQL. This parameter is commonly located on the /etc/mysql/my.cnf file.

You can find the default option file locations on Linux, Unix, Mac or Windows following this link

[mysqld]
max_allowed_packet=128M

If the version of MariaDB is 10.1.38, or newer, the recommended value for max_allowed_packet  is 512M

Note: in the case, we will obtain the next 'The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.' error when trying to upload an addon, we may update the default value of this mysql/mariadb parameter. This parameter is commonly allocated on the /etc/mysql/my.cnf file.

[mysqld]
innodb_log_file_size=256M

If you are installing on a Ubuntu 18.04 server, the default character set is set to utf8mb4. Using this character set can cause problems, as many index sizes will exceed the maximum key size of 767 bytes. To prevent this problem, change the following settings:

[mysqld]
character-set-server  = Latin1
collation-server      = Latin1_general_ci

Alternatively, if UTF character set is required, write the following  settings:

[mysqld]
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
innodb_large_prefix   = 1
innodb_file_format    = Barracuda
innodb_file_per_table = 1

Following this link you will find the steps to set up a two nodes database cluster.

Oracle

A new database instance should be created. Optionally two tablespaces should be created (SOFFID_DATA and SOFFID_INDEX) to separate soffid tables and indexes.

CREATE TABLESPACE SOFFID_DATA DATAFILE '/app/oracle/oradata/project/soffid_data.dbfdbf' SIZE 200M EXTENT MANAGEMENT LOCAL AUTOALLOCATE

To create the tablespace is necessary to provide the full path name, its size and MANAGEMENT AUTOALLOCATE option. The autoallocate option is needed because the tables are not sized by database creation scripts. Also, the Oracle Listener must have a TCP/IP port accepting connections.

Microsoft SQLServer

You must enable the SQL Server Browser Service at startup and the authentication method have to be set to “SQL Server and Windows Authentication mode”.

In addition, you must ensure that 'READ_COMMITTED_SNAPSHOT" parameter is enabled, you can do so with the following command: 

ALTER DATABASE [database_name] SET READ_COMMITTED_SNAPSHOT ON