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?