When upgrading from 4.7.7 to 4.7.8 I get error "Specified key was too long"

Question

I tried to upgrade my Joomla CiviCRM 4.7.7 to 4.7.8 and got the following error in the database upgrade:

ALTER TABLE `civicrm_contact` CHANGE `image_URL` `image_URL` VARCHAR(512)
CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL
COMMENT 'optional URL for preferred image (photo, logo, etc.) to display for this contact.'
[nativecode=1071 ** Specified key was too long; max key length is 767 bytes]

This old MySQL bug tells part of the story, I think. I run MySQL 5.6.24 so the limit should be higher, but when I try to do the ALTER TABLE directly I get the same error.

The statement seems to lengthen the image_URL column from 255 to 512 VARCHAR in UTF8. Turns out I can not even lengthen it to 256...

Is there some setting on the database that limits the key length 'globally' that I could have inherited from earlier (site has been up and upgraded for 5 years) versions?

Fortunately I did this on a restored backup, as I do with all upgrades before applying them on the live site. But this is a MacOSX and the live site an Ubuntu 15.10 so there might be some differences there too...

UPDATE: Here's a blog post indicating that innodb_large_prefix can be used to fix this. And it might be default from MySQL 5.7.

Question is just how to do that. Can I change my existing MySQL to run with innodb_large_prefix and then do the upgrade? Will a change in the MySQL setting interfere with my existing data or other tables/databases on the same MySQL server?

Answer 1

This issue is CRM-18983: DB error on upgrade to 4.7.8. I ran into it on OSX also while testing an upgrade.

There is a PR at https://github.com/civicrm/civicrm-core/pull/8605 currently which addresses this by reducing the size of the key before increasing the column size. Not all sites will have the option of reconfiguring MySQL server settings to accommodate the DB change in 4.7.8, so it makes sense to me that we do this in the schema.

Applying the patch before running the upgrade works for me - please post feedback on the JIRA issue or PR if you find it helps.

Answer 2

You can modify the innodb_large_prefix value several ways (http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix). I'd recommend changing your my.cnf file that contains the startup configuration and parameters, then restarting mysql on your system. Exactly how to do that varies by your OS version, one typical approach is to do it through a control panel, one we use on Debian is

$ sudo /etc/init.d/mysql restart

Once you have the MySQL daemon running with this setting, it will be able to run the ALTER TABLE query successfully. Your CiviCRM system will run fine with your existing data.