Thursday, September 1, 2016

Running lh-toolkit 1.x on MySQL 5.7

MySQL 5.7 by default uses sql_mode where no zero in dates or zero dates are allowed. Where as by default the timestamp uses 00:00:00. MySQL 5.7 by default uses ANSI standard GROUP_BY clauses, that were not required for previous versions of MySQL.
The best way to fix this across sessions is to add the following to my.ini or my.cnf depending on your platform. For Ubuntu 16.04 this is located at /etc/mysql/mysql.conf.d/mysqld.cnf
sql_mode=""
In the setup wizard for the URL use:
jdbc:mysql://localhost:3306/@DBNAME@?autoReconnect=true&InnoDB=InnoDB
Basically, the connection needs to have the text InnoDB in it, so that openmrs doesn't try to set the storage_engine variable, which has been changed to default_storage_engine. But that itself is not required because it should be left upto the database implementer to choose the engine. It could be XtraDB (Percona's fork of InnoDB) or Aria (used in MariaDB).
Once these are done, you should be able to use MySQL 5.7 (and its excellent performance improvements) with lh-toolkit or OpenMRS 1.11.x and higher