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

Alter Table for column with Foreign key in MySQL 5.6 Fails

Oracle released the much awaited MySQL 5.6 GA on 5th Feb, 2013. Much to everyone’s surprise and changing direction in some sense, lots of improvements were made available in the Community release of MySQL, which were expected to be only part of the Enterprise Edition only.

Eager to try out the new NoSQL and performance improvements in 5.6, I downloaded the new installer. It is a packaged installer than unpacks and installs connectors, workbench and few other things along with the MySQL 5.6 Server. A surprising place where I got stuck was trying to install OpenMRS. The liquibase changeset uses <modifyType> tag and attempts to change the varchar column size. This works well under MySQL 5.5, but fails in 5.6.

While I’ve tried searching for this change in the release notes, what’s new and few other places, I haven’t found this mentioned clearly for the MySQL 5.6 release. The problem is that earlier you could disable the foreign key constraints check, modify the columns that have the constraints and re-enable the foreign key checks. If you changed the columns on both ends fine, things would just work well. But in 5.6 it seems there has been a change to this and the only mention I’ve found is new error messages that the server can throw. There is probably some tighten of things around the constraints management, but I couldn’t find much.

Here are the server error messages from MySQL 5.6 and MySQL 5.5:

http://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html#error_er_fk_column_cannot_change
which wasn't there in:
http://dev.mysql.com/doc/refman/5.5/en/error-messages-server.html

Register for MySQL Roadshow India 2008

If you are one of the developers who use MySQL, then this is your chance to meet fellow devs. MySQL is organizing a Roadshow across 4 Indian cities in Chennai, Bangalore, Hyderabad and Mumbai.

During week 8, MySQL AB will conduct free MySQL Seminars in four cities in India:
February 18, Chennai
February 19, Bangalore
February 20, Hyderabad
February 21, Mumbai

Learn more about these events and register:
http://www.mysql.com/news-and-events/events/mysql-in-feb08.html

I'll be attending the roadshow in Mumbai. Hopefully, we can meet up and discuss some tech!!