5

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?

| improve this question | | | | |
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.

| improve this answer | | | | |
  • Would that be 'install the patch between installing the upgrade and running the database upgrade'? – thoni56 Jun 22 '16 at 7:39
  • Yes. The patch above requires that you recommence the DB upgrade from a backup. Because 4.7.8 has already been released I'm expecting it won't get merged in its current state (but it can still get the ball rolling, and help people like you who want to be able to upgrade a DB to that release). – Chris Burgess Jun 22 '16 at 10:51
  • You mean that since the database upgrade have failed on the "current" installation that is kinda smoked. So a restore from backup is the only option. But if I haven't started the upgrade (or just restored from backup) I should install the upgrade, then apply the patch and finally run the database upgrade? – thoni56 Jun 22 '16 at 10:58
  • Correct. Simplest process is (1) install CiviCRM 4.7.8, leave your DB unchanged (2) apply the patch from PR (3) run CiviCRM upgrader. You can also resurrect the failed DB upgrade, but it's probably easier to get a fresh copy of the DB and go from there. – Chris Burgess Jun 22 '16 at 11:26
  • Should you have data in the failed upgrade DB which needs to be preserved (ie, the upgrade was run on production site and now you may need to choose between losing today's data with a backup restore, or working through a failed upgrade), it may be worth working through getting the failed upgrade to complete. If that is your situation (or similar), I suggest asking a separate question on how to do that. There may already be a Q&A here for this? – Chris Burgess Jun 22 '16 at 11:31
0

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.

| improve this answer | | | | |
  • According to dev.mysql.com/doc/refman/5.6/en/… there are also various other requirements, like innodb_file_per_table and innodb_file_format. Just changing innodb_large_prefix and restarting did not work for me on an already installed site. – thoni56 Jun 22 '16 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.