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, see 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:

  1. Download MySQL Connector/J version 5.1 or later.

  2. Unpack the downloaded file, and copy the .jar file to openidm/bundle/:

    cp mysql-connector-java-version.jar /path/to/openidm/bundle/
  3. Make sure that IDM is stopped:

    /path/to/openidm/shutdown.sh
    OpenIDM is not running, not stopping.
  4. Remove the default DS repository configuration file (repo.ds.json) from your project's conf/ directory:

    rm my-project/conf/repo.ds.json
  5. 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/
  6. 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)
  7. Import the IDM database and tables:

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

    Note

    If you see errors like Access denied for user 'root'@'localhost', and 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.

  8. Create the IDM database user.

    Run the following script:

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

    Run the following script:

    cd /path/to/mysql
    mysql -u root -p < /path/to/openidm/db/mysql/scripts/createuser.mysql56.sql
    Enter password: 
  9. 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:

    Warning

    You must use MySQL version 5.6.4 or later. If you are using an older version, perform the MySQL upgrade before upgrading to IDM 7 or later. For additional information, see the Flowable Note for MySQL users.

  10. 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: 
  11. 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.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
        }
    }

    Specify the values for openidm.repo.host and openidm.repo.port in one of the following ways:

    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 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="-Xmx1024m -Xms1024m -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:   -Xmx1024m -Xms1024m -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.0.2"
    OpenIDM ready

Tip

In a production environment, set up SSL as described in the MySQL Connector Developer Guide. If you are using an older version of MySQL, add &useSSL=true to the end of the jdbcURL.

If you are running a relatively recent version of MySQL (5.5.45+, 5.6.26+, 5.7.6+), the default configuration expects you to set up SSL, unless you add &useSSL=false to the end of the jdbcUrl.

When you have set up MySQL for use as the internal repository, make sure that the server starts without errors.

Read a different version of :