mariadbmirthmirth-connect

How do you migrate Mirthconnect to use MySQL/MariaDB

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

Using:

Steps to setup:

  1. Install Maria DB
  2. Install Mirth connect.
  3. Create the database to use with Mirth
  4. Create the mirth user in Maria DB
  5. Configure mirth connect to connect to Maria DB
  6. 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:

Solution

  • 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;