Answers.Body: <p>This issue is <a href="https://issues.civicrm.org/jira/browse/CRM-18983" rel="nofollow">CRM-18983: DB error on upgrade to 4.7.8</a>. I ran into it on OSX also while testing an upgrade.</p>
  
  <p>There is a PR at <a href="https://github.com/civicrm/civicrm-core/pull/8605" rel="nofollow">https://github.com/civicrm/civicrm-core/pull/8605</a> 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.</p>
  
  <p>Applying the patch before running the upgrade works for me - please post feedback on the JIRA issue or PR if you find it helps.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-22T07:39:41.243Z
Answers.Text: Would that be 'install the patch between installing the upgrade and running the database upgrade'?
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-22T10:51:25.390Z
Answers.Text: 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).
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-22T10:58:33.053Z
Answers.Text: 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?
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-22T11:26:53.673Z
Answers.Text: Correct. Simplest process is (1) install CiviCRM 4.7.8, leave your DB unchanged (2) apply [the patch from PR](https://github.com/civicrm/civicrm-core/pull/8605.patch) (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.
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-22T11:31:38.103Z
Answers.Text: **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?
Answers.UserDisplayName: Chris Burgess
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-22T13:19:46.560Z
Answers.Text: This solved it for me on the backup machine on MacOSX and MySQL 5.6.31. On to do it on the live site...
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-21T19:04:43.580Z
Answers.LastActivityDate: 2016-06-21T19:04:43.580Z
Answers.UserDisplayName: Chris Burgess
Answers.Body: <p>You can modify the innodb_large_prefix value several ways (<a href="http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix" rel="nofollow">http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_large_prefix</a>). 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</p>
  
  <pre><code>$ sudo /etc/init.d/mysql restart
  </code></pre>
  
  <p>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. </p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-22T07:38:29.613Z
Answers.Text: According to http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix 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.
Answers.UserDisplayName: thoni56
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2016-06-21T17:38:16.030Z
Answers.LastActivityDate: 2016-06-21T17:38:16.030Z
Answers.UserDisplayName: Joe Murray
Body: <p>I tried to upgrade my Joomla CiviCRM 4.7.7 to 4.7.8 and got the following error in the database upgrade:</p>
  
  <pre><code>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]
  </code></pre>
  
  <p><a href="https://bugs.mysql.com/bug.php?id=6604" rel="nofollow">This old MySQL bug</a> 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.</p>
  
  <p>The statement seems to lengthen the <code>image_URL</code> column from 255 to 512 VARCHAR in UTF8. Turns out I can not even lengthen it to 256...</p>
  
  <p>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?</p>
  
  <p>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...</p>
  
  <p>UPDATE: <a href="http://mechanics.flite.com/blog/2014/07/29/using-innodb-large-prefix-to-avoid-error-1071/" rel="nofollow">Here's a blog post</a> indicating that <code>innodb_large_prefix</code> can be used to fix this. And it might be default from MySQL 5.7.</p>
  
  <p>Question is just how to do that. Can I change my existing MySQL to run with <code>innodb_large_prefix</code> 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?</p>
  
Tags: <upgrade><database>
Title: When upgrading from 4.7.7 to 4.7.8 I get error "Specified key was too long"
ThreadText: # When upgrading from 4.7.7 to 4.7.8 I get error "Specified key was too long"
  
  (Asked by: username_0 on 2016-06-17)
  
  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?
  
  **Tags:** upgrade, database
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2016-06-21. Score: 1 | Accepted answer
  
  This issue is [CRM-18983: DB error on upgrade to 4.7.8](https://issues.civicrm.org/jira/browse/CRM-18983). 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.
  
  > **Comments:**
  > **username_0**: Would that be 'install the patch between installing the upgrade and running the database upgrade'?  
  > **username_1**: 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).  
  > **username_0**: 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?  
  > **username_1**: Correct. Simplest process is (1) install CiviCRM 4.7.8, leave your DB unchanged (2) apply [the patch from PR](https://github.com/civicrm/civicrm-core/pull/8605.patch) (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.  
  > **username_1**: **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?  
  > **username_0**: This solved it for me on the backup machine on MacOSX and MySQL 5.6.31. On to do it on the live site...  
  
  ---
  
  ### Answer by username_2 on 2016-06-21. Score: 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.
  
  > **Comments:**
  > **username_0**: According to http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefix 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.  
  
  ---
  
  