Blog

We recently upgraded to MySQL 5.6, and one of our web applications started failing our automated tests. We have a test that drops all our tables and recreates them again, all done using Maven and Liquibase. The error we were getting was something like this.

SEVERE 1/1/13 12:00 PM:liquibase: Change Set /liquibase/tableName.sql::6::teamextension failed. Error:
Error executing SQL alter table tableName 
    add index indexName (columnName): Specified key was too long; max key length is 767 bytes

After we few Google searches, the found the issue to be described in the
Upgrading from Previous to Current Unicode Support page on the MySQL website. This boils down to the Unicode changes in MySQL 5.6.

So what’s the fix? We basically were indexing a very long column, and earlier MySQL versions were not complaining. The quickest fix is to tell MySQL to index only the first parts of the column.

    add index indexName (query(255));

But we have another problem. We were using Liquibase, with formatted SQL changelogs, and we cannot just change old changelogs. That will give errors when Liquibase runs. We started looking around the Liquibase documentation and saw the validCheckSum element, which could allow us to update old changelogs. Unfortunately, this only works with the XML changelogs. A little more digging and we came across the runOnChange attribute. Now we changed our changelog to:

--changeset teamextension:6 runOnChange
	alter table tableName
		add index indexName (columName(255));

Now things work again! If anyone has a better solution, please let us know. Thanks.