MySQL repository

This procedure assumes that you have installed MySQL on the local host. Follow the MySQL documentation that corresponds to your MySQL version. For supported MySQL versions, refer to Supported Repositories.

Configure IDM to use the new repository before you start IDM for the first time. This procedure assumes that a password has already been set for the MySQL root user:

Download MySQL Connector/J version 8.0 or later.

Do not use Connector/J versions 8.0.23 through 8.0.25. Why?
Unpack the downloaded file, and copy the JAR file to openidm/bundle/:
```
cp mysql-connector-java-version.jar /path/to/openidm/bundle/
```

Make sure that IDM is stopped:

/path/to/openidm/shutdown.sh
OpenIDM is not running, not stopping.

Remove the default DS repository configuration file (repo.ds.json ) from your project’s conf/ directory:
```
rm my-project/conf/repo.ds.json
```
Copy the MySQL database connection configuration file (datasource.jdbc-default.json) and the database table configuration file (repo.jdbc.json) to your project’s conf directory:
```
cp /path/to/openidm/db/mysql/conf/datasource.jdbc-default.json my-project/conf/
cp /path/to/openidm/db/mysql/conf/repo.jdbc.json my-project/conf/
```

If you have previously set up a MySQL repository for IDM, you must drop the openidm database and users before you continue:

mysql> drop database openidm;
Query OK, 21 rows affected (0.63 sec)mysql> drop user openidm;
Query OK, 0 rows affected (0.02 sec)mysql> drop user openidm@localhost;
Query OK, 0 rows affected (0.00 sec)

Import the IDM database and tables:

cd /path/to/mysql
mysql -u root -p < /path/to/openidm/db/mysql/scripts/openidm.sql
Enter password:

If errors like Access denied for user 'root'@'localhost' display, and you are deploying on a new installation of Ubuntu 16.04 or later, the UNIX_SOCKET plugin may be installed, which applies Linux root credentials to MySQL. In that case, substitute sudo mysql -u root for mysql -u root -p in the commands in this section.

Create the IDM database user.

For MySQL 8.0

Run the following script:

cd /path/to/mysql
mysql -u root -p < /path/to/openidm/db/mysql/scripts/createuser.sql
Enter password:

For MySQL 5.7

Run the following script:

cd /path/to/mysql
mysql -u root -p < /path/to/openidm/db/mysql/scripts/createuser.mysql57.sql
Enter password:

Run the script that creates the tables required by the workflow engine:

cd /path/to/mysql
mysql -D openidm -u root -p < /path/to/openidm/db/mysql/scripts/flowable.mysql.all.create.sql
Enter password:

If you are planning to direct audit logs to this repository, run the script that sets up the audit tables:
```
mysql -D openidm -u root -p < /path/to/openidm/db/mysql/scripts/audit.sql
Enter password:
```

Update the connection configuration to reflect your MySQL deployment. The default connection configuration (in the conf/datasource.jdbc-default.json file) is:

{
    "driverClass" : "com.mysql.cj.jdbc.Driver",
    "jdbcUrl" : "jdbc:mysql://&{openidm.repo.host}:&{openidm.repo.port}/openidm?allowMultiQueries=true&characterEncoding=utf8&serverTimezone=UTC",
    "databaseName" : "openidm",
    "username" : "openidm",
    "password" : "openidm",
    "connectionTimeout" : 30000,
    "connectionPool" : {
        "type" : "hikari",
        "minimumIdle" : 20,
        "maximumPoolSize" : 50
    }
}

In a production environment, set up SSL as described in the MySQL Connector Developer Guide. The default configuration expects SSL, which is strongly advised in a production environment. If you are running this in a test environment, you can bypass the SSL requirement:
- The default configuration expects SSL, unless you add &useSSL=false to the end of the url.
- If you are running MySQL 8.0.11+, add &allowPublicKeyRetrieval=true to the end of the url.

Specify the values for openidm.repo.host and openidm.repo.port in one of the following ways:
Set in an IDM Properties File
Set the values in resolver/boot.properties or your project’s conf/system.properties file. For example:
openidm.repo.host=localhost openidm.repo.port=3306
These lines are commented out by default in resolver/boot.properties.

The default MySQL port is 3306. You can use the netstat -tlnp command to check which port your MySQL instance is running on.
Set as an Environment Variable
Set the properties in the OPENIDM_OPTS environment variable and export that variable before startup. You must include the JVM memory options when you set this variable. For example:
export OPENIDM_OPTS="-Xmx2048m -Xms2048m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=3306" /path/to/openidm/startup.sh -p my-project Executing ./startup.sh... Using OPENIDM_HOME: /path/to/openidm Using PROJECT_HOME: /path/to/openidm Using OPENIDM_OPTS: -Xmx2048m -Xms2048m -Dopenidm.repo.host=localhost -Dopenidm.repo.port=3306 Using LOGGING_CONFIG: -Djava.util.logging.config.file=/path/to/openidm/conf/logging.properties Using boot properties at /path/to/openidm/resolver/boot.properties -> OpenIDM version "7.5.0" OpenIDM ready
Make sure that the server starts without errors.

PingIDM 7.5.0

MySQL repository