4

MySQL 8.0 has been released. Will CiviCRM work with it? I tried migrating a test database to it, and CiviCRM under Joomla failed.

Oct 31 12:36:35  [info] $Fatal Error Details = Array (
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] =>  SELECT  v.label as label ,v.name as value, v.grouping as grouping FROM   civicrm_option_value_en_GB v,
       civicrm_option_group_en_GB g WHERE  v.option_group_id = g.id   AND  g.name            = 'languages'   AND  g.is_active       = 1  AND v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grouping FROM   civicrm_option_value_en_GB v,
       civicrm_option_group_en_G' at line 1]
    [type] => DB_Error
    [user_info] =>  SELECT  v.label as label ,v.name as value, v.grouping as grouping FROM   civicrm_option_value_en_GB v,
       civicrm_option_group_en_GB g WHERE  v.option_group_id = g.id   AND  g.name            = 'languages'   AND  g.is_active       = 1  AND v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grouping FROM   civicrm_option_value_en_GB v,
       civicrm_option_group_en_G' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info=" SELECT  v.label as label ,v.name as value, v.grouping as grouping FROM   civicrm_option_value_en_GB v,
       civicrm_option_group_en_GB g WHERE  v.option_group_id = g.id   AND  g.name            = 'languages'   AND  g.is_active       = 1  AND v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grouping FROM   civicrm_option_value_en_GB v,
       civicrm_option_group_en_G' at line 1]"] )

The SQL below runs happily against MySQL 5.7 whilst it fails against MySQL 8.0 with

"SELECT" is not valid at this position for this server version, expecting '(', WITH

SQL:

SELECT v.label as label ,v.name as value, v.grouping as grouping FROM civicrm_option_value_en_GB v, civicrm_option_group_en_GB g WHERE v.option_group_id = g.id AND g.name = 'languages' AND g.is_active = 1 AND v.is_active = 1 AND ( v.component_id IS NULL OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) ) ORDER BY v.weight

Replacing the first line with:

SELECT v.label as label ,v.name as value, v.grouping as `grouping`

solves the problem.

Clearly some work is needed before CiviCRM will work with MySQL 8.0

Improve this question
1
  • 1
    When answering your own question, there's a checkbox indicating that you want to answer it yourself. This lets others chime in separately later, and your answer can be up/downvoted separately from the question. You may want to edit this to put your answer separately.
    – Jon G - Megaphone Tech
    Commented Oct 31, 2018 at 19:05

1 Answer 1

Reset to default
1

Clearly CiviCRM won't work with MySQL 8.9 yet; at least not in my environment.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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