H2 database is included and configured by default in the RM development/evaluation configuration. It is highly recommended NOT to use H2 on production deployments.

Below you will find guides on how you can setup MySQL, MariaDB, Oracle or PostgreSQL database for the RM.

MySQL DB Setup Guide

Prerequisites

The following properties need to be set or added to the my.cfn/my.ini file of the MySQL as in the example below:

Setup

To properly create a MySQL database for the RM please follow the steps below:

1. Prepare a MySQL database and user for RM: Log into the MySQL console as admin user (e.g. `mysql -u root -p`) and execute:

   mysql> CREATE DATABASE mprm_data;

   mysql> CREATE USER 'mprm_user'@'<IP_Address>' IDENTIFIED BY 'mprm_secret';

   mysql> GRANT ALL PRIVILEGES ON mprm_data.* TO 'mprm_user'@'<IP_Address>';

   mysql> FLUSH PRIVILEGES;

   where:

   • mprm_data - is the name of the RM database

   • mprm_user - is the name of the MySQL user that has full rights on the RM database

   • mprm_secret - is the password assigned to the RM's database user

2. Turn off the built-in H2 database support in RM: Comment the following lines inside the file bin/mserver/db.sys:

   #url=jdbc\:h2\:h2db/mprm;DEFAULT_LOCK_TIMEOUT\=30000

   #user=sa

   #password=

   #driver=org.h2.Driver

3. Configure RM with the MySQL database settings: Append the following lines to the file bin/mserver/db.sys according to the values set in step 1 (Skip this step if you are going to use scm.xml cluster configuration!):

   url=jdbc\:mysql\://<host>:<port>/mprm_data?autoReconnect=false

   user=mprm_user

   password=mprm_secret

   driver=com.mysql.jdbc.Driver

4. Download and Install MySQL JDBC Driver: Download the latest MySQL Connector/J package.

5. Add the MySQL JDBC Driver to the RM Classpath: Edit the EXT_CLASSPATH= line inside the RM's startup script to point to the connector driver.

   For example: EXT_CLASSPATH=/etc/mysql/mysql-connector-java.jar

In case of using MySQL database and UTF8 encoding, to setup TR-069 device management correctly, set the property mprm.tr069.db.varCharColumnMaxLen=2000 into the default.prs file.

MariaDB Setup Guide

Prerequisites

The following properties need to be set or added to the my.cfn/mariadb.cnf file of the MySQL as in the example below:

Setup

To properly create a database for the RM please follow the steps below:

1. Prepare a MariaDB database and user for RM: Log into the MariaDB console as admin user (e.g. `mariadb -u root -p`) and execute:

   MariaDB [(none)]> CREATE DATABASE mprm_data;

   MariaDB [(none)]> CREATE USER 'mprm_user'@'<IP Address>' IDENTIFIED BY 'mprm_secret';

   MariaDB [(none)]> GRANT ALL PRIVILEGES ON mprm_data.* TO 'mprm_user'@'<IP Address>';

   MariaDB [(none)]> FLUSH PRIVILEGES;

    

   Note: To allow access to mprm_user from any IP address, use the '%' wildcard. For example: CREATE USER 'mprm_user'@'%' ...

   where:

   • mprm_data - is the name of the RM database

   • mprm_user - is the name of the DB user that has full rights on the RM database

   • mprm_secret - is the password assigned to the database user
     
     

2. Turn off the built-in H2 database support in RM: Comment out the relevant lines in the file bin/mserver/db.sys, following the same approach described for the other databases in this guide.

3. Configure RM with the MariaDB database settings: Append the following lines to the file bin/mserver/db.sys according to the values set in step 1 (Skip this step if you are going to use scm.xml cluster configuration!):

   url=jdbc\:mariadb\://<host>:<port>/mprm_data?autoReconnect=false

   user=mprm_user

   password=mprm_secret

   driver=org.mariadb.jdbc.Driver

4. Download and Install latest MariaDB JDBC Driver

5. Add the MariaDB JDBC Driver to the RM Classpath: Edit the EXT_CLASSPATH= line inside the RM's startup script to point to the connector driver.

   For example: EXT_CLASSPATH=/etc/mariadb/mariadb-java-client.jar

Similar to MySQL database, if you are using UTF8 encoding, set the property mprm.tr069.db.varCharColumnMaxLen=2000 into the default.prs file in order to setup TR-069 device management correctly

Oracle DB Setup Guide

Setup

To properly create an Oracle database for the RM please follow the steps below:

1. Prepare an Oracle database and user for RM: Log into the Oracle console as system user and execute:

   create user mprm_data identified by mprm_secret default tablespace users
   grant connect, resource to mprm_data

   where:

   • mprm_data - is the name of the RM database and the name of the Oracle user that has full rights on the RM database.

   • mprm_secret - is the password assigned to the RM's database user.

2. Turn off the built-in H2 database support in RM: Comment the following lines inside the file bin/mserver/db.sys:

   #url=jdbc\:h2\:h2db/mprm;DEFAULT_LOCK_TIMEOUT\=30000

   #user=sa

   #password=

   #driver=org.h2.Driver

3. Configure RM with the Oracle database settings: Append the following lines to the file bin/mserver/db.sys according to the values set in step 1 (Skip this step if you are going to use scm.xml cluster configuration!):

   url= jdbc\:oracle\:thin\host\:port\:oracle_instalation

   user=mprm_data

   password=mprm_secret

   driver=oracle.jdbc.OracleDriver

4. Download Oracle Driver: Download the latest Oracle driver in folder.

5. Add the Oracle Driver to the RM Classpath: Edit the EXT_CLASSPATH= line inside the RM's startup script to point to the connector driver.

   For example: EXT_CLASSPATH=/oracle_drivers/ojdbc6.jar

PostgreSQL

To accurately start the RM with the PostgreSQL database management system please take in consideration the steps below.

1. The PostgreSQL Server can be found here while the PostgreSQL JDBC Drivers are available here.

2. Managing users and databases is possible with the usage of the PostgreSQL Tools (pgAdmin). Simple SQL queries may be also used e.g. CREATE USER ttt WITH PASSWORD 'ttt'; CREATE DATABASE ttt OWNER ttt

3. The URL property in the db.sys file must be changed to: url=jdbc:postgresql://<ip>:<port>/<database_name>?prepareThreshold=0

4. The default port is 5432

5. The name of the database is <database_name> and should be created previously.

6. The property prepareThreshold=0 is used for better performance of batch queries e.g. jdbc:postgresql://127.0.0.1:5432/ttt?prepareThreshold=0

7. The driver class in the db.sys file should be changed to : org.postgresql.Driver

H2 Database Test Usage

The H2 database can be used for testing purposes but some matters must be considered first:

• The H2 database creates its files in the following folder : general\bin\mserver\h2db. If this folder does not exist the H2 will create it. When it is needed to commence work with an empty database, the following script that deletes the files under this folder must be executed: general\bin\h2_resetdb.bat (Windows) or general/bin/h2_resetdb (Linux)

• The Embedded mode is the default configuration.

• Starting the H2 database in Server mode requires the execution of one of the following scripts depending on the OS:

  For Windows:

  cd mserver

   

  java -mx256M -cp ../../h2/h2.jar org.h2.tools.Server -tcp -baseDir h2db -tcpAllowOthers

   

  cd ..

  For Linux:

  #!/bin/sh

   

  cd mserver

   

  java -mx256M -cp ../../h2/h2.jar org.h2.tools.Server -tcp -baseDir h2db -tcpAllowOthers

   

  cd ..

• The url property in the db.sys file has to be changed to url=jdbc:h2:tcp://localhost/mprm

• Connecting to the database in Server mode is possible with DbVisualizer, SQuirrel SQL Client or other programs that can view the database records created by the RM. The H2 driver is located in the general/h2/h2.jar file and the driver class is org.h2.Driver

System Properties

The following database-related system properties may come in handy in some problematic scenarios: