0

System:

WordPress 5.2.5, CiviCRM 5.21.1, PHP 7.2.18

Background:

At some point in a previous upgrade - I suspect from 5.19.x - my database tables haven't updated properly. This resulted in my cronjob failing after I attempted to update any extensions: all a bit weird. I rolled back to my earliest backup (5.17.x) and then. following a suggestion from another stackexchange question, turned off verbose logging, then stepped the upgrades from 5.17 -> 5.18 -> 5.19 -> 5.20 -> 5.21. I also disabled all the extensions before upgrading. I have a feeling what might have happened - looking at the release notes - is that the APIv4 extension was installed, then I disabled it and when the upgrade from 5.19 happened one or more tables weren't altered properly. Looks like maybe APIv4.4 needed to be active when moving from 5.19 to 5.20. But I'm really not sure.

Problem:

The new upgrade process seems to have worked and I've tested some stuff with no problems. However, I now can't turn verbose logging back on. It seems like this is what was causing the whole original problem: It seems maybe I'm missing the is_active column in the log_civicrm_status_pref table. Whenever I try to turn verbose logging on, Civi throws an error and won't change it.

I'm now concerned there are more hidden problems with the database, but I have no idea how to go about working out what's wrong or how to fix it. Any advice gratefully received. NOTE: rolling back the whole DB to a backup is not really an option at this point, or at least it's a nuclear option I really don't want to have to do as there are active event pages.

Error log:

Jan 22 07:33:04  [error] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) DEFAULT '1' COMMENT 'Is this status check active' [nativecode=1265 ** Data truncated for column 'check_info' at row 43891]
    [type] => DB_Error
    [user_info] => ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) DEFAULT '1' COMMENT 'Is this status check active' [nativecode=1265 ** Data truncated for column 'check_info' at row 43891]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) DEFAULT '1' COMMENT 'Is this status check active' [nativecode=1265 ** Data truncated for column 'check_info' at row 43891]"]
)


Jan 22 07:33:04  [debug] $backTrace = #0 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(905): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(922): CRM_Core_Error::exceptionHandler(Object(DB_Error))
#2 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/DB.php(987): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...")
#3 /var/www/html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...")
#4 /var/www/html/wp-content/plugins/civicrm/civicrm/vendor/pear/pear-core-minimal/src/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...", "DB_Error", TRUE)
#5 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1920): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...", "1265 ** Data truncated for column 'check_info' at row 43891")
#7 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1229): DB_mysqli->simpleQuery("ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...")
#9 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2416): DB_common->query("ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...")
#10 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1607): DB_DataObject->_query("ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...")
#11 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(420): DB_DataObject->query("ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...")
#12 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(1419): CRM_Core_DAO->query("ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...", FALSE)
#13 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Logging/Schema.php(450): CRM_Core_DAO::executeQuery("ALTER TABLE `wordpress`.log_civicrm_status_pref ADD   `is_active` tinyint(4) ...", (Array:0), TRUE, NULL, FALSE, FALSE)
#14 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Logging/Schema.php(529): CRM_Logging_Schema->fixSchemaDifferencesFor("civicrm_status_pref", (Array:3))
#15 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Logging/Schema.php(284): CRM_Logging_Schema->fixSchemaDifferencesForAll()
#16 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Logging/Schema.php(269): CRM_Logging_Schema->fixSchemaDifferences(TRUE)
#17 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Logging/Schema.php(103): CRM_Logging_Schema->enableLogging()
#18 [internal function](): CRM_Logging_Schema::onToggle("0", "1", (Array:15), 1)
#19 /var/www/html/wp-content/plugins/civicrm/civicrm/Civi/Core/SettingsBag.php(345): call_user_func((Array:2), "0", "1", (Array:15), 1)
#20 /var/www/html/wp-content/plugins/civicrm/civicrm/Civi/Core/SettingsBag.php(256): Civi\Core\SettingsBag->setDb("logging", "1")
#21 /var/www/html/wp-content/plugins/civicrm/civicrm/Civi/Core/SettingsBag.php(153): Civi\Core\SettingsBag->set("logging", "1")
#22 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/BAO/Setting.php(194): Civi\Core\SettingsBag->add((Array:21))
#23 /var/www/html/wp-content/plugins/civicrm/civicrm/api/v3/Setting.php(254): CRM_Core_BAO_Setting::setItems((Array:23), (Array:1))
#24 /var/www/html/wp-content/plugins/civicrm/civicrm/Civi/API/Provider/MagicFunctionProvider.php(85): civicrm_api3_setting_create((Array:23))
#25 /var/www/html/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(152): Civi\API\Provider\MagicFunctionProvider->invoke((Array:9))
#26 /var/www/html/wp-content/plugins/civicrm/civicrm/Civi/API/Kernel.php(83): Civi\API\Kernel->runRequest((Array:9))
#27 /var/www/html/wp-content/plugins/civicrm/civicrm/api/api.php(86): Civi\API\Kernel->runSafe("setting", "create", (Array:22))
#28 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Admin/Form/SettingTrait.php(339): civicrm_api3("setting", "create", (Array:22))
#29 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Admin/Form/Setting.php(106): CRM_Admin_Form_Setting->saveMetadataDefinedSettings((Array:23))
#30 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Admin/Form/Setting.php(86): CRM_Admin_Form_Setting->commonProcess((Array:23))
#31 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(479): CRM_Admin_Form_Setting->postProcess()
#32 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/StateMachine.php(144): CRM_Core_Form->mainProcess()
#33 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Next.php(45): CRM_Core_StateMachine->perform(Object(CRM_Admin_Form_Setting_Miscellaneous), "next", "Next")
#34 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Admin_Form_Setting_Miscellaneous), "next")
#35 /var/www/html/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_Setting_Miscellaneous), "next")
#36 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(335): HTML_QuickForm_Page->handle("next")
#37 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Utils/Wrapper.php(98): CRM_Core_Controller->run()
#38 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(241): CRM_Utils_Wrapper->run("CRM_Admin_Form_Setting_Miscellaneous", "Misc (Undelete, PDFs, Limits, Logging, Captcha, etc.)", (Array:0))
#39 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(68): CRM_Core_Invoke::runItem((Array:16))
#40 /var/www/html/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(36): CRM_Core_Invoke::_invoke((Array:4))
#41 /var/www/html/wp-content/plugins/civicrm/civicrm.php(1449): CRM_Core_Invoke::invoke((Array:4))
#42 /var/www/html/wp-includes/class-wp-hook.php(286): CiviCRM_For_WordPress->invoke("")
#43 /var/www/html/wp-includes/class-wp-hook.php(310): WP_Hook->apply_filters("", (Array:1))
#44 /var/www/html/wp-includes/plugin.php(465): WP_Hook->do_action((Array:1))
#45 /var/www/html/wp-admin/admin.php(253): do_action("toplevel_page_CiviCRM")
#46 {main}
2

It seems you already have is_active column added to civicrm_status_pref table. This was added in civi 5.19 version.

From the backtrace, it seems the error is shown after civi tries to add the same column in the existing log table of civicrm_status_pref which is log_civicrm_status_pref.

Try renaming this table to eg log_civicrm_status_pref_backup and then enable logging to see if it is enabled without any errors.

If the above is successful, you'll notice a new log table is created with the name log_civicrm_status_pref.

If you want to retrieve the existing entries from log_civicrm_status_pref_backup. I think you can take a dump of this table without CREATE statement and then re-import it in the database.

| improve this answer | |
  • Thanks Jitendra, I'll try this tonight and see if it works. – Hugh Jan 22 at 19:54
  • 1
    It worked! Thanks a lot. – Hugh Jan 23 at 8:44

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.