0

I'm trying to install the hrbank extension of CiviHR 1.3.0 on a CiviCRM 4.7.13/Ubuntu 16.04/Php7 stack. I get the error "DB Error: unknown error"

The stacktrace in the ConfigAndLog directory shows that a value 0 is used for the date_created field of the civicrm_custom_group, and apparently that is not allowed:

Oct 25 08:45:09  [info] Creating canary table

Oct 25 09:10:01  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , collapse_adv_display , created_date , is_reserved ) VALUES ('Bank_Details' , 'Bank Details' , 'Individual' , 'Tab' ,  0 , '' , '' ,  12 ,  1 , 'civicrm_value_bank_details_9' ,  0 ,  0 ,  0 ,  1 )  [nativecode=1292 ** Incorrect datetime value: '0' for column 'created_date 'at row 1]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , collapse_adv_display , created_date , is_reserved ) VALUES ('Bank_Details' , 'Bank Details' , 'Individual' , 'Tab' ,  0 , '' , '' ,  12 ,  1 , 'civicrm_value_bank_details_9' ,  0 ,  0 ,  0 ,  1 )  [nativecode=1292 ** Incorrect datetime value: '0' for column 'created_date' at row 1]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , collapse_adv_display , created_date , is_reserved ) VALUES ('Bank_Details' , 'Bank Details' , 'Individual' , 'Tab' ,  0 , '' , '' ,  12 ,  1 , 'civicrm_value_bank_details_9' ,  0 ,  0 ,  0 ,  1 )  [nativecode=1292 ** Incorrect datetime value: '0' for column 'created_date' at row 1]"]
)


Oct 25 09:10:01  [info] $backTrace = #0 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Error.php(229): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB.php(984): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_d...")
#3 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_d...")
#4 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_d...", "DB_Error", TRUE)
#5 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
#6 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_d...", "1292 ** Incorrect datetime value: '0' for column 'created_date' at row 1")
#7 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_d...")
#9 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(2438): DB_common->query("INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_d...")
#10 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/DB/DataObject.php(1060): DB_DataObject->_query("INSERT INTO civicrm_custom_group (name , title , extends , style , collapse_d...")
#11 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/DAO.php(482): DB_DataObject->insert()
#12 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Utils/Migrate/Import.php(130): CRM_Core_DAO->save()
#13 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Utils/Migrate/Import.php(207): CRM_Utils_Migrate_Import->copyData(Object(CRM_Core_DAO_CustomGroup), Object(SimpleXMLElement), TRUE, "name")
#14 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Utils/Migrate/Import.php(78): CRM_Utils_Migrate_Import->customGroups(Object(SimpleXMLElement), (Array:2))
#15 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Utils/Migrate/Import.php(56): CRM_Utils_Migrate_Import->runXmlElement(Object(SimpleXMLElement))
#16 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/templates/CRM/Admin/Page/Extensions/civihr/civihr-1.3.0/hrbank/CRM/HRBank/Upgrader/Base.php(95): CRM_Utils_Migrate_Import->run("/var/www/wordpress/wp-content/plugins/civicrm/civicrm/templates/CRM/Admin/Pag...")
#17 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/templates/CRM/Admin/Page/Extensions/civihr/civihr-1.3.0/hrbank/CRM/HRBank/Upgrader/Base.php(250): CRM_HRBank_Upgrader_Base::executeCustomDataFileByAbsPath("/var/www/wordpress/wp-content/plugins/civicrm/civicrm/templates/CRM/Admin/Pag...")
#18 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/templates/CRM/Admin/Page/Extensions/civihr/civihr-1.3.0/hrbank/hrbank.civix.php(45): CRM_HRBank_Upgrader_Base->onInstall()
#19 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/templates/CRM/Admin/Page/Extensions/civihr/civihr-1.3.0/hrbank/hrbank.php(50): _hrbank_civix_civicrm_install()
#20 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Extension/Manager/Module.php(76): hrbank_civicrm_install()
#21 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Extension/Manager/Module.php(48): CRM_Extension_Manager_Module->callHook(Object(CRM_Extension_Info), "install")
#22 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Extension/Manager.php(229): CRM_Extension_Manager_Module->onPreInstall(Object(CRM_Extension_Info))
#23 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Admin/Form/Extensions.php(181): CRM_Extension_Manager->install((Array:1))
#24 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Form.php(431): CRM_Admin_Form_Extensions->postProcess()
#25 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
#26 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Admin_Form_Extensions), "next", "Next")
#27 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Admin_Form_Extensions), "next")
#28 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_Extensions), "next")
#29 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("next")
#30 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(384): CRM_Core_Controller->run()
#31 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Page/Basic.php(168): CRM_Core_Page_Basic->edit(1, NULL)
#32 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Admin/Page/Extensions.php(121): CRM_Core_Page_Basic->run()
#33 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(312): CRM_Admin_Page_Extensions->run((Array:3), NULL)
#34 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(86): CRM_Core_Invoke::runItem((Array:13))
#35 /var/www/wordpress/wp-content/plugins/civicrm/civicrm/CRM/Core/Invoke.php(54): CRM_Core_Invoke::_invoke((Array:3))
#36 /var/www/wordpress/wp-content/plugins/civicrm/civicrm.php(1227): CRM_Core_Invoke::invoke((Array:3))
#37 /var/www/wordpress/wp-includes/plugin.php(524): CiviCRM_For_WordPress->invoke("")
#38 /var/www/wordpress/wp-admin/admin.php(222): do_action("toplevel_page_CiviCRM")
#39 {main}
2

I think this is more of an issue with CiviCRM/HR than MySQL

Passing 000 like dates is deprecated in newer versions of MySQL so approaches that disable NO_ZERO_DATE are a little short sighted (though they might get you out of an immediate pickle).

Hence the answer is probably to fix the CiviHR and/or CiviCRM. In most cases, I imagine the fix is no harder than changing the MySQL setting.

e.g. a fix like https://github.com/veda-consulting/uk.co.vedaconsulting.mailchimp/pull/279/commits/5edb7bf772d5bb89302a8970bd7fae37672b5beb solve the problem for the mailchimp extension.

A fix to somewhere like CRM/Core/BAO/CustomGroup.php might be good to solve the problem more globally.

1

I encountered the same problem when installing CiviVolunteer recently. I was using a similar setup: Ubuntu 16.04, CiviCRM 4.7.16 and MySQL 5.7.17. This is likely a MySQL issue: recent versions of MySQL have changed the default behavior with respect to the NO_ZERO_DATE setting. I just posted an issue with CiviVolunteer here which might be helpful: https://issues.civicrm.org/jira/browse/VOL-299

Here's my workaround, I'm guessing this will work for you too:

  • Login to the CiviCRM database and examine the current sql_mode setting: SELECT @@GLOBAL.sql_mode;
  • Note the current setting parameters, if NO_ZERO_DATE is set it should be disabled
  • Edit: /etc/mysql/mysql.conf.d/mysqld.cnf and add the settings from the previous step with NO_ZERO_DATE omitted. For example, add the following to the end of the config file: sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' Note: this workaround fix is fairly broad and affects all database instances, this could probably be fine tuned somewhat.
  • Restart MySQL and reinstall your CiviCRM extension
  • After successful installation, revert the sql_mode settings by removing the above line from mysqld.cnf.

Related links:

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.