Migrating the Database

You can migrate the database from the default database (H2 or Apache Derby) to Oracle, Microsoft SQL Server, MySQL, or H2. You cannot migrate to Apache Derby, and H2 can be selected only with Java 11 or later. The following example migrates to MySQL.

1

Set up the MySQL database.

Prepare the MySQL database to migrate to. MySQL 8.0 and later are supported.

The tables in the target database must be empty. If any table contains data, the migration does not start and tells you which tables to empty.

2

Stop ManualWorks.

Stop ManualWorks. You cannot use ManualWorks while the database is being migrated.

3

Copy the MySQL JDBC driver.

3RABBITZ_HOME refers to the directory where ManualWorks is installed.

Add the JDBC driver for your MySQL version to the 3RABBITZ_HOME/web/WEB-INF/lib directory.

Download the MySQL JDBC driver from the MySQL website. For MySQL 8.0.32, use the mysql-connector-j-8.0.32.jar driver.

4

Create the migrate file.

In the 3RABBITZ_HOME directory, create an empty text file named 1migrate, with no extension.

5

Start ManualWorks.

Starting ManualWorks. Then go to the following address in a web browser.

/r/database_migration

You must log in as a user with the “Administrator” role.

6

Test the JDBC connection.

Enter the required information, and click the 2Test button.

The JDBC URL must match the database type you selected. URLs that contain options for running scripts, accessing files, or loading classes are not accepted.

7

Create the schema.

Click the 3Create Schema button.

8

Migrate the database.

Click the 4Start button to migrate the database.

9

Stop ManualWorks.

Stop ManualWorks.

10

Delete the migrate file.

Delete the migrate file.

If you do not delete this file, ManualWorks starts in database migration mode, and you cannot use any other features.

11

Change the database to MySQL in the server.xml file, and restart.

The first start can take a long time because database indexes are created.

Configure ManualWorks to use MySQL as follows.

  1. Open the 3RABBITZ_HOME/tomcat/conf/server.xml file.

<Context docBase="${3rabbitz.home}/web" path=""
  reloadable="false" backgroundProcessorDelay="3">
  <CookieProcessor className="org.apache.tomcat.util.http.LegacyCookieProcessor" />
  <!-- H2 -->
  <Resource name="jdbc/Base" auth="Container" type="javax.sql.DataSource"
    driverClassName="org.h2.Driver" url="jdbc:h2:h2/database;"
    username="database" password="database"
    maxTotal="100" maxIdle="30" maxWaitMillis="10000"/>
  <!-- for MySQL -->
  <!--
  <Resource name="jdbc/Base" auth="Container" type="javax.sql.DataSource"
    maxTotal="100" maxIdle="30" maxWaitMillis="10000"
    username="username" password="password"
    driverClassName="com.mysql.cj.jdbc.Driver"
    url="jdbc:mysql://localhost:3306/rabbitz" />
  -->
</Context>

  1. Comment out the H2 settings.

  2. Uncomment the MySQL settings.

  3. Change username, password, url, and other values.

  4. Start ManualWorks.

ManualWorks creates its tables automatically during installation and software updates. The MySQL account set in username must have privileges to create tables, alter columns, and create and drop indexes.