Saturday, March 2, 2013

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 mysqlchanging 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:
which wasn't there in:

No comments: