Body: <p>I am using MySQL Workbench on MacOSX and there are four SQL statements in the 4.7.9 upgrade script which give a 1064 error. The first of the statements in error is shown below. MySQL Workbench has the error underline on the first { character.</p>
  
  <pre><code>UPDATE civicrm_option_value SET
    {localize field="label"}label = '{ts escape="sql"}Print/Merge Document{/ts}'{/localize},
    {localize field="description"}description = '{ts escape="sql"}Export letters and other printable documents.{/ts}'{/localize}
    WHERE name = 'Print PDF Letter' AND option_group_id = @option_group_id_act;
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-12T01:50:00.117Z
Comments.Text: Can you please describe the steps taken to arrive at this error?
Comments.UserDisplayName: Chris Burgess
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-12T03:28:27.117Z
Comments.Text: I was upgrading from 4.6.20 to 4.7.9. with the following errors. civicrm_domain.version field was too small.
  I then encountered an error in 4.7.8 SQL as shown below.
  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]
  I removed that SQL and used the SQL Editor in MySQL Workbench to process the remaining 4.7.8 SQL statements.
Comments.UserDisplayName: Martin Fuggle
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-12T03:28:54.633Z
Comments.Text: I then used the SQL Editor in MySQL Workbench to process the 4.7.9 SQL statements. That showed that there were four statements in error. The first of those is shown in the error report above.
Comments.UserDisplayName: Martin Fuggle
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-12T03:52:22.667Z
Comments.Text: Aha. (That first `image_URL` key length issue is [CRM-18983](https://issues.civicrm.org/jira/browse/CRM-18983) mentioned in [this SE question](http://civicrm.stackexchange.com/questions/12489/when-upgrading-from-4-7-7-to-4-7-8-i-get-error-specified-key-was-too-long).) Does this mean you're using MySQL on OSX? It only seems to be reported on OSX, but that might just be because Linux distro MySQLs are mostly on 5.6.
Comments.UserDisplayName: Chris Burgess
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2016-08-12T03:54:47.120Z
Comments.Text: I'm guessing that SQL you're seeing is supposed to have been run through Smarty. (I ... just ... I can't even, but that's how it's done there, we generate SQL using Smarty templating. C'est la CiviCRM.) I'd double-check that the SQL as it was executed is *really* what CiviCRM would have executed during an upgrade, I suspect here it might not have been.
Comments.UserDisplayName: Chris Burgess
Tags: <upgrade>
Title: MySQL Error 1064 when trying to upgrade from 4.7.8. to 4.7.9
ThreadText: # MySQL Error 1064 when trying to upgrade from 4.7.8. to 4.7.9
  
  (Asked by: username_0 on 2016-08-11)
  
  I am using MySQL Workbench on MacOSX and there are four SQL statements in the 4.7.9 upgrade script which give a 1064 error. The first of the statements in error is shown below. MySQL Workbench has the error underline on the first { character.
  UPDATE civicrm_option_value SET
    {localize field="label"}label = '{ts escape="sql"}Print/Merge Document{/ts}'{/localize},
    {localize field="description"}description = '{ts escape="sql"}Export letters and other printable documents.{/ts}'{/localize}
    WHERE name = 'Print PDF Letter' AND option_group_id = @option_group_id_act;
  
  **Tags:** upgrade
  
  > **Comments:**
  > **username_1**: Can you please describe the steps taken to arrive at this error?  
  > **username_0**: I was upgrading from 4.6.20 to 4.7.9. with the following errors. civicrm_domain.version field was too small. I then encountered an error in 4.7.8 SQL as shown below. 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] I removed that SQL and used the SQL Editor in MySQL Workbench to process the remaining 4.7.8 SQL statements.  
  > **username_0**: I then used the SQL Editor in MySQL Workbench to process the 4.7.9 SQL statements. That showed that there were four statements in error. The first of those is shown in the error report above.  
  > **username_1**: Aha. (That first `image_URL` key length issue is [CRM-18983](https://issues.civicrm.org/jira/browse/CRM-18983) mentioned in [this SE question](http://civicrm.stackexchange.com/questions/12489/when-upgrading-from-4-7-7-to-4-7-8-i-get-error-specified-key-was-too-long).) Does this mean you're using MySQL on OSX? It only seems to be reported on OSX, but that might just be because Linux distro MySQLs are mostly on 5.6.  
  > **username_1**: I'm guessing that SQL you're seeing is supposed to have been run through Smarty. (I ... just ... I can't even, but that's how it's done there, we generate SQL using Smarty templating. C'est la CiviCRM.) I'd double-check that the SQL as it was executed is *really* what CiviCRM would have executed during an upgrade, I suspect here it might not have been.  
  
  ---
  
  ## 0 answers
  
  