Uploaded image for project: 'CiviVolunteer'
  1. CiviVolunteer
  2. VOL-299

Installation fails with "Incorrect datetime value: '0' for column 'created_date'"

    Details

    • Type: Bug
    • Status: Done/Fixed
    • Priority: Blocker
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.7.21-2.2.2
    • Fix Version/s: 4.7.21-2.2.3
    • Labels:
      None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Verified?:
      No

      Description

      Summary:
      Installation of CiviVolunteer 2.2.1 fails with following error: Incorrect datetime value: '0' for column 'created_date'

      Configuration

      • Ubuntu 16.04.1 (kernel 4.4.0-57)
      • MySQL 5.7.17
      • CiviCRM 4.7.16

      Full stack trace:

      #0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(187): CRM_Core_Error::backtrace()
      #1 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
      #2 /var/www/html/sites/all/modules/civicrm/packages/DB.php(984): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "INSERT INTO civicrm_custom_group (name , title , extends , extends_entity_col...")
      #3 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "INSERT INTO civicrm_custom_group (name , title , extends , extends_entity_col...")
      #4 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "INSERT INTO civicrm_custom_group (name , title , extends , extends_entity_col...", "DB_Error", TRUE)
      #5 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->__call("raiseError", (Array:7))
      #6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "INSERT INTO civicrm_custom_group (name , title , extends , extends_entity_col...", "1292 ** Incorrect datetime value: '0' for column 'created_date' at row 1")
      #7 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
      #8 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO civicrm_custom_group (name , title , extends , extends_entity_col...")
      #9 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(2438): DB_common->query("INSERT INTO civicrm_custom_group (name , title , extends , extends_entity_col...")
      #10 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(1060): DB_DataObject->_query("INSERT INTO civicrm_custom_group (name , title , extends , extends_entity_col...")
      #11 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(487): DB_DataObject->insert()
      #12 /var/www/html/sites/all/modules/civicrm/CRM/Utils/Migrate/Import.php(130): CRM_Core_DAO->save()
      #13 /var/www/html/sites/all/modules/civicrm/CRM/Utils/Migrate/Import.php(211): CRM_Utils_Migrate_Import->copyData(Object(CRM_Core_DAO_CustomGroup), Object(SimpleXMLElement), TRUE, "name")
      #14 /var/www/html/sites/all/modules/civicrm/CRM/Utils/Migrate/Import.php(78): CRM_Utils_Migrate_Import->customGroups(Object(SimpleXMLElement), (Array:2))
      #15 /var/www/html/sites/default/files/civicrm/ext/org.civicrm.volunteer/CRM/Volunteer/Upgrader.php(787): CRM_Utils_Migrate_Import->runXmlElement(Object(SimpleXMLElement))
      #16 /var/www/html/sites/default/files/civicrm/ext/org.civicrm.volunteer/CRM/Volunteer/Upgrader.php(48): CRM_Volunteer_Upgrader->executeCustomDataTemplateFile("volunteer-customdata.xml.tpl")
      #17 /var/www/html/sites/default/files/civicrm/ext/org.civicrm.volunteer/CRM/Volunteer/Upgrader/Base.php(306): CRM_Volunteer_Upgrader->install()
      #18 /var/www/html/sites/default/files/civicrm/ext/org.civicrm.volunteer/volunteer.civix.php(54): CRM_Volunteer_Upgrader_Base->onInstall()
      #19 /var/www/html/sites/default/files/civicrm/ext/org.civicrm.volunteer/volunteer.php(262): _volunteer_civix_civicrm_install()
      #20 /var/www/html/sites/all/modules/civicrm/CRM/Extension/Manager/Module.php(76): volunteer_civicrm_install()
      #21 /var/www/html/sites/all/modules/civicrm/CRM/Extension/Manager/Module.php(48): CRM_Extension_Manager_Module->callHook(Object(CRM_Extension_Info), "install")
      #22 /var/www/html/sites/all/modules/civicrm/CRM/Extension/Manager.php(229): CRM_Extension_Manager_Module->onPreInstall(Object(CRM_Extension_Info))
      #23 /var/www/html/sites/all/modules/civicrm/CRM/Admin/Form/Extensions.php(181): CRM_Extension_Manager->install((Array:1))
      #24 /var/www/html/sites/all/modules/civicrm/CRM/Core/Form.php(451): CRM_Admin_Form_Extensions->postProcess()
      #25 /var/www/html/sites/all/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
      #26 /var/www/html/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(61): CRM_Core_StateMachine->perform(Object(CRM_Admin_Form_Extensions), "next", "Next")
      #27 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Admin_Form_Extensions), "next")
      #28 /var/www/html/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Admin_Form_Extensions), "next")
      #29 /var/www/html/sites/all/modules/civicrm/CRM/Core/Controller.php(351): HTML_QuickForm_Page->handle("next")
      #30 /var/www/html/sites/all/modules/civicrm/CRM/Core/Page/Basic.php(384): CRM_Core_Controller->run()
      #31 /var/www/html/sites/all/modules/civicrm/CRM/Core/Page/Basic.php(168): CRM_Core_Page_Basic->edit(1, NULL)
      #32 /var/www/html/sites/all/modules/civicrm/CRM/Admin/Page/Extensions.php(121): CRM_Core_Page_Basic->run()
      #33 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(310): CRM_Admin_Page_Extensions->run((Array:3), NULL)
      #34 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(84): CRM_Core_Invoke::runItem((Array:13))
      #35 /var/www/html/sites/all/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:3))
      #36 /var/www/html/sites/all/modules/civicrm/drupal/civicrm.module(448): CRM_Core_Invoke::invoke((Array:3))
      #37 /var/www/html/includes/menu.inc(527): civicrm_invoke("admin", "extensions")
      #38 /var/www/html/index.php(21): menu_execute_active_handler()
      #39 {main}
      
      Database Error Code: Incorrect datetime value: '0' for column 'created_date' at row 1, 1292
      Additional 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 , extends_entity_column_value , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , collapse_adv_display , created_date , is_reserved ) VALUES ('CiviVolunteer' , 'CiviVolunteer' , 'Activity' , '56' , 'Inline' ,  1 , '' , '' ,  2 ,  1 , 'civicrm_value_civivolunteer_4' ,  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 , extends_entity_column_value , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , collapse_adv_display , created_date , is_reserved ) VALUES ('CiviVolunteer' , 'CiviVolunteer' , 'Activity' , '56' , 'Inline' ,  1 , '' , '' ,  2 ,  1 , 'civicrm_value_civivolunteer_4' ,  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 , extends_entity_column_value , style , collapse_display , help_pre , help_post , weight , is_active , table_name , is_multiple , collapse_adv_display , created_date , is_reserved ) VALUES ('CiviVolunteer' , 'CiviVolunteer' , 'Activity' , '56' , 'Inline' ,  1 , '' , '' ,  2 ,  1 , 'civicrm_value_civivolunteer_4' ,  0 ,  0 ,  0 ,  1 )  [nativecode=1292 ** Incorrect datetime value: '0' for column 'created_date' at row 1]"]
      )
      

      Probable Cause:
      This problem seems to be happening because of changes in MySQL default behavior with respect to the NO_ZERO_DATE sql_mode setting.
      See: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

      Workaround:

      • 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 CiviVolunteer
      • After successful installation, revert the sql_mode settings by removing the above line from mysqld.cnf.

      Related Issues:
      This seems to relate to the following issue:
      https://issues.civicrm.org/jira/browse/CRM-19546

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                pittstains Frank J. Gómez
                Reporter:
                mcharnoky Mike Charnoky
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: