0

Upgrading a CiviCRM DB from 4.7.17 to 4.7.29 after moving from MySQL 5.1 (CentOS 6 install) to CentOS 7 with MariaDB 10.2. After the progress bar completes on the wp-admin/admin.php?page=CiviCRM&q=civicrm/upgrade&reset=1 page, then I get an error:

backTrace

#0 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(187): CRM_Core_Error::backtrace()
#1 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/DB.php(984): PEAR_Error->__construct("DB Error: syntax error", -2, 16, (Array:2), "ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...")
#3 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-2, 16, (Array:2), "ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...")
#4 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -2, 16, (Array:2), "ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...", "DB_Error", TRUE)
#5 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-2, NULL, NULL, "ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...", "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...")
#9 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2446): DB_common->query("ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...")
#10 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1635): DB_DataObject->_query("ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...")
#11 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(369): DB_DataObject->query("ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...")
#12 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1318): CRM_Core_DAO->query("ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...", FALSE)
#13 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Logging/Schema.php(399): CRM_Core_DAO::executeQuery("ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NU...", (Array:0), TRUE, NULL, FALSE, FALSE)
#14 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Logging/Schema.php(468): CRM_Logging_Schema->fixSchemaDifferencesFor("civicrm_activity", (Array:3), FALSE)
#15 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Logging/Schema.php(286): CRM_Logging_Schema->fixSchemaDifferencesForAll()
#16 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Form.php(770): CRM_Logging_Schema->fixSchemaDifferences()
#17 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Page/Upgrade.php(182): CRM_Upgrade_Form::doFinish()
#18 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Upgrade/Page/Upgrade.php(81): CRM_Upgrade_Page_Upgrade->runFinish()
#19 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(309): CRM_Upgrade_Page_Upgrade->run((Array:2), NULL)
#20 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
#21 /var/www/html/wp/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:2))
#22 /var/www/html/wp/wp-content/plugins/civicrm/civicrm.php(1216): CRM_Core_Invoke::invoke((Array:2))
#23 /var/www/html/wp/wp-includes/class-wp-hook.php(286): CiviCRM_For_WordPress->invoke("")
#24 /var/www/html/wp/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters("", (Array:1))
#25 /var/www/html/wp/wp-includes/plugin.php(453): WP_Hook->do_action((Array:1))
#26 /var/www/html/wp/wp-admin/admin.php(224): do_action("toplevel_page_CiviCRM")
#27 {main}




Database Error Code: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '() ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely rela' at line 1, 1064
Additional Details:

Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NULL () ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely related entity) was created or modified or deleted.' [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '() ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely rela' at line 1]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NULL () ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely related entity) was created or modified or deleted.' [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '() ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely rela' at line 1]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="ALTER TABLE `civicrm`.log_civicrm_activity ADD   `modified_date` timestamp NULL () ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely related entity) was created or modified or deleted.' [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '() ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely rela' at line 1]"]
)

If I remove the () from the middle of the SQL statement: ALTER TABLE civicrm.log_civicrm_activity ADD modified_date timestamp NULL () ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely related entity) was created or modified or deleted.' And do: ALTER TABLE civicrm.log_civicrm_activity ADD modified_date timestamp NULL ON UPDATE current_timestamp() COMMENT 'When was the activity (or closely related entity) was created or modified or deleted.' It adds to the DB. However, a subsequent run of the upgrade process then tries to Modify the column and ends in essentially the same error at the same spot. Anyone else run into this? Is this a bug or problem with my install?

CentOS 7, CiviCRM 4.7.17->4.7.29, WordPress 4.9.2, MariaDB 10.2 SCL, PHP 7.0 SCL

Improve this question

1 Answer 1

Reset to default
1

I have ran into this issue as well. Initially I wasn't sure if it was because I use multi-lingual (which adds some complexity) or because of MariaDB 10.2.

I had initially patched my version of CiviCRM to remove those empty ():

diff --git a/sites/all/modules/civicrm/CRM/Logging/Schema.php b/sites/all/modules/civicrm/CRM/Logging/Schema.php
index b3480601..e3a5d285 100644
--- a/sites/all/modules/civicrm/CRM/Logging/Schema.php
+++ b/sites/all/modules/civicrm/CRM/Logging/Schema.php
@@ -489,6 +489,7 @@ AND    (TABLE_NAME LIKE 'log_civicrm_%' $nonStandardTableNameString )
     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP ON UPDATE  CURRENT_TIMESTAMP", '', $query);
     $query = str_ireplace("DEFAULT CURRENT_TIMESTAMP", '', $query);
     $query = str_ireplace("NOT NULL", '', $query);
+    $query = str_ireplace('()', ' ', $query);
     return $query;
   }

However, looking at civicrm_contact, for example, I see default current_timestamp(), so the above replacements are causing the empty ().

It should probably something like this?

 $query = preg_replace("/DEFAULT CURRENT_TIMESTAMP(\(\))/", '', $query);
Improve this answer
5
  • This is also somewhat relevant: stackoverflow.com/questions/44722541/… "All versions of MariaDB from 5.5 through 10.2 and all MySQL from 5.5 to 5.7 accept current_timestamp() syntax as default value for a timestamp column."
    – bgm
    Jan 19, 2018 at 19:57
  • I'm also utilizing mulit-lingual. I don't think current_timestamp() is the problem. According to the docs current_timestamp and current_timestamp() are "symlinked" to Now(). However, your added line also changes NULL () ON UPDATE to NULL ON UPDATE. That's where the problem seems to lie. So from what I see it seems that your solution works, but not because of what you expected. Why is there () after the NULL?
    – G H
    Jan 19, 2018 at 20:07
  • CiviCRM asks the database to output a table schema, in order to build the log table. If MariaDB outputs modified_date timestamp NULL DEFAULT CURRENT_TIMESTAMP() or modified_date timestamp NULL DEFAULT DEFAULT CURRENT_TIMESTAMP() ON UPDATE CURRENT_TIMESTAMP, the str_ireplace from CiviCRM will convert it to: modified_date timestamp NULL (). The code just didn't expect those parenthesis, so they are not removed. My initial patch focuses on the parenthesis, but a better patch would probably be to have a regexp conditionally looking for those parenthesis?
    – bgm
    Jan 19, 2018 at 20:20
  • Ah! Now I see. So, it's simply leaving the () from the initial call. I think what you suggest should work, or perhaps above the first shown line adding: $query = str_ireplace("CURRENT_TIMESTAMP()", 'CURRENT_TIMESTAMP', $query);
    – G H
    Jan 19, 2018 at 20:31
  • Both edits seems to work fine. (After fixing the trailing slash.) Adding another str_ireplace command keeps the code uniform in that section, but utilizing preg_replace decreases code length.
    – G H
    Jan 19, 2018 at 21:17

Your Answer

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

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