How do you migrate Mirthconnect to use MySQL/MariaDB

Question

When trying to connect mirthconnect to use mariaDB instead of Derby embedded DB, mirth connect fails when creating the database.

Using:

Ubuntu 18.04 LTS
MariaDB 10.1.29-MariaDB-6
Mirth Connect 3.6.0
Java openjdk 10.0.1

Steps to setup:

Install Maria DB
Install Mirth connect.
Create the database to use with Mirth
Create the mirth user in Maria DB
Configure mirth connect to connect to Maria DB
Start mirth service

http://www.hpctech.org/8-steps-to-install-mirthconnect-with-mysql-mariadb-on-linux/

When starting the mirthconnect service the following exception is thrown.

ERROR 2018-07-04 15:13:11,013 [Main Server Thread]                 com.mirth.connect.server.Mirth: Failed to migrate database schema
com.mirth.connect.model.util.MigrationException: Failed to execute script: /mysql/mysql-database.sql
    at com.mirth.connect.server.migration.Migrator.executeScript(Migrator.java:103)
    at com.mirth.connect.server.migration.Migrator.executeScript(Migrator.java:86)
    at com.mirth.connect.server.migration.ServerMigrator.initDatabase(ServerMigrator.java:234)
    at com.mirth.connect.server.migration.ServerMigrator.migrate(ServerMigrator.java:59)
    at com.mirth.connect.server.controllers.DefaultMigrationController.migrate(DefaultMigrationController.java:91)
    at com.mirth.connect.server.Mirth.startup(Mirth.java:226)
    at com.mirth.connect.server.Mirth.run(Mirth.java:153)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:488)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
    at com.mysql.jdbc.Util.getInstance(Util.java:383)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2769)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:907)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:791)
    at com.mirth.connect.server.migration.Migrator.executeScript(Migrator.java:100)
    ... 6 more

Half of the database tables have been created

show tables;
+------------------------+
| Tables_in_mirthconnect |
+------------------------+
| CHANNEL                |
| EVENT                  |
| PERSON                 |
| PERSON_PASSWORD        |
| PERSON_PREFERENCE      |
| SCHEMA_INFO            |
| SCRIPT                 |
+------------------------+

Can anyone tell me:

what the exception implies?
how to resolve the error?

score 2 · Accepted Answer · answered Jul 05 '18 at 09:51

The error in question

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes

seems to do with the key length - the database default character set was utf16 not utf8 therefore the keysize of one of the tables was actually greater than 767 bytes due to the size of characters in bytes.

When creating the database for mirth - make sure that the character set is set to UTF8

create database mirthconnect character set utf8 collate utf8_general_ci;

How do you migrate Mirthconnect to use MySQL/MariaDB

1 Answers1