Answers.Body: <p>Yes that link you posted has the answer in #4. The easiest thing to do would be to remove those values from the sql_mode setting, i.e. in mysql run this command</p>
  
  <p>SELECT @@GLOBAL.sql_mode;</p>
  
  <p>Then it will output some string. Remove any reference to STRICT_TRANS_TABLES, NO_ZERO_DATE, and NO_ZERO_IN_DATE from that string. Then whatever is left you would assign that to sql_mode in your my.cnf file:</p>
  
  <p>sql_mode="whatever is left from the string"</p>
  
  <p>Then restart mysql.</p>
  
  <p>FYI this would change the behavior of other databases on your server too, if you have any.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-22T15:18:36.887Z
Answers.Text: Thank you @Demerit! It worked perfectly, and the update completed all the way. My web site is on a shared server, so I can't restart MySQL and will probably have to pull down the database, update and re-upload it ...
  
  There were a lot of warnings ("you may want to change these from "DATETIME" to "TIMESTAMP""), under /civicrm/a/#/status, so I ran [Doctor When: Temporal cleanup agent](https://github.com/civicrm/org.civicrm.doctorwhen) which seems to have fixed the issues.
Answers.UserDisplayName: regit55
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-22T19:23:00.200Z
Answers.Text: I ended up doing the update locally, and uploading the database, which worked well.
Answers.UserDisplayName: regit55
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-03-13T12:50:18.560Z
Answers.Text: Thanks, It works, it would be better if I could find this answer earlier.
Answers.UserDisplayName: buxizhizhoum
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-21T00:49:43.810Z
Answers.LastActivityDate: 2018-09-21T00:49:43.810Z
Answers.UserDisplayName: Demerit
Answers.Body: <p>I came across a similar issue during an upgrade from CiviCRM 5.29 to 5.51.</p>
  <p>I took the approach of fixing the incorrect datetime values rather than changing the MySQL configuration to ignore incorrect dates.</p>
  <p>The following SQL query should set the incorrect dates to <code>NULL</code>:</p>
  <pre><code>UPDATE civicrm_financial_trxn SET created_date=NULL WHERE created_date=0;
  </code></pre>
  <p>Note that you have to use <code>WHERE created_date=0</code> not <code>WHERE created_date='0000-00-00 00:00:00'</code>.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-08-04T15:52:42.177Z
Answers.LastActivityDate: 2022-08-04T15:52:42.177Z
Answers.UserDisplayName: William Mortada
Body: <p>I recently updated from a 2011 installation of CiviCRM 3.4.2 and Drupal 6, to Drupal 7 with CiviCRM 4.6.34.</p>
  
  <p>Today I tried to upgrade from CiviCRM 4.6.34 to 5.5.1, and everything went well until 4.7.19, when I get the error inserted below.</p>
  
  <p>My PHP and MySQL versions:</p>
  
  <pre><code>$ php --version
  PHP 7.0.18-0ubuntu0.16.04.1 (cli) ( NTS )
  $ mysql --version
  mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper
  </code></pre>
  
  <p>I found <a href="https://www.digitalocean.com/community/tutorials/how-to-prepare-for-your-mysql-5-7-upgrade" rel="nofollow noreferrer">this article</a> which might have some clues I am just not sure how to interpret it. Maybe someone else has experienced the same error?</p>
  
  <hr>
  
  <h2>Error message</h2>
  
  <pre><code>CiviCRM Upgrade Tasks
  [Error: Upgrade DB to 4.7.19: SQL]
  Error Field Error Value
  Type  DB_Error
  Code  -1
  Message DB Error: unknown error
  Mode  16
  UserInfo  UPDATE civicrm_financial_trxn SET trxn_date = NULL WHERE trxn_date = '0000-00-00 00:00:00' [nativecode=1292 ** Incorrect datetime value: '0000-00-00 00:00:00' for column 'trxn_date' at row 1]
  DebugInfo UPDATE civicrm_financial_trxn SET trxn_date = NULL WHERE trxn_date = '0000-00-00 00:00:00' [nativecode=1292 ** Incorrect datetime value: '0000-00-00 00:00:00' for column 'trxn_date' at row 1]
  
  PEAR_Exception: DB Error: unknown error in /var/www/public/drupal7/sites/all/modules/civicrm/packages/PEAR.php on line 921
  - DB_Error: DB Error: unknown error in unknown on line unknown
  Exception trace
  # Function  Location
  0 CRM_Core_Error::exceptionHandler(Object(DB_Error))  /var/www/public/drupal7/sites/all/modules/civicrm/packages/PEAR.php:921
  1 PEAR_Error-&gt;__construct('DB Error: unknow…', -1, 16, Array, 'UPDATE civicrm_f…')  /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB.php:984
  2 DB_Error-&gt;__construct(-1, 16, Array, 'UPDATE civicrm_f…') /var/www/public/drupal7/sites/all/modules/civicrm/packages/PEAR.php:575
  3 PEAR-&gt;_raiseError(Object(DB_mysqli), null, -1, 16, Array, 'UPDATE civicrm_f…', 'DB_Error', true)  /var/www/public/drupal7/sites/all/modules/civicrm/packages/PEAR.php:223
  4 PEAR-&gt;__call('raiseError', Array) /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB/common.php:1907
  5 DB_common-&gt;raiseError(-1, null, null, 'UPDATE civicrm_f…', '1292 ** Incorrec…') /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB/mysqli.php:933
  6 DB_mysqli-&gt;mysqliRaiseError() /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB/mysqli.php:403
  7 DB_mysqli-&gt;simpleQuery('UPDATE civicrm_f…') /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB/common.php:1216
  8 DB_common-&gt;query('UPDATE civicrm_f…') /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Utils/File.php:366
  9 CRM_Utils_File::runSqlQuery('mysql://root:roo…', '-- CRM-19715 SEL…', null) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Form.php:177
  10  CRM_Upgrade_Form-&gt;source('-- CRM-19715 SEL…', true) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Form.php:372
  11  CRM_Upgrade_Form-&gt;processLocales('/var/www/public/…', '4.7.19') /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Form.php:406
  12  CRM_Upgrade_Form-&gt;processSQL('4.7.19')  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Incremental/Base.php:80
  13  CRM_Upgrade_Incremental_Base::runSql(Object(CRM_Queue_TaskContext), '4.7.19') /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/Task.php:88
  14  CRM_Queue_Task-&gt;run(Object(CRM_Queue_TaskContext))  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/Runner.php:214
  15  CRM_Queue_Runner-&gt;runNext(true) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:52
  16  CRM_Queue_Page_AJAX::{closure}()  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/ErrorPolicy.php:106
  17  CRM_Queue_ErrorPolicy-&gt;call(Object(Closure))  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:54
  18  CRM_Queue_Page_AJAX::runNext()  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:275
  19  CRM_Core_Invoke::runItem(Array) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:84
  20  CRM_Core_Invoke::_invoke(Array) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:52
  21  CRM_Core_Invoke::invoke(Array)  /var/www/public/drupal7/sites/all/modules/civicrm/drupal/civicrm.module:445
  22  civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext') /var/www/public/drupal7/includes/menu.inc:527
  23  menu_execute_active_handler() /var/www/public/drupal7/index.php:21
  24  {main}   
  </code></pre>
  
Tags: <mysql-5.7>
Title: Error "Incorrect datetime value" during upgrade to version 5.5.1
ThreadText: # Error "Incorrect datetime value" during upgrade to version 5.5.1
  
  (Asked by: username_0 on 2018-09-18)
  
  I recently updated from a 2011 installation of CiviCRM 3.4.2 and Drupal 6, to Drupal 7 with CiviCRM 4.6.34.
  
  Today I tried to upgrade from CiviCRM 4.6.34 to 5.5.1, and everything went well until 4.7.19, when I get the error inserted below.
  
  My PHP and MySQL versions:
  
  ```
  $ php --version
  PHP 7.0.18-0ubuntu0.16.04.1 (cli) ( NTS )
  $ mysql --version
  mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper
  
  ```
  
  I found [this article](https://www.digitalocean.com/community/tutorials/how-to-prepare-for-your-mysql-5-7-upgrade) which might have some clues I am just not sure how to interpret it. Maybe someone else has experienced the same error?
  
  ---
  
  ## Error message
  
  ```
  CiviCRM Upgrade Tasks
  [Error: Upgrade DB to 4.7.19: SQL]
  Error Field Error Value
  Type  DB_Error
  Code  -1
  Message DB Error: unknown error
  Mode  16
  UserInfo  UPDATE civicrm_financial_trxn SET trxn_date = NULL WHERE trxn_date = '0000-00-00 00:00:00' [nativecode=1292 ** Incorrect datetime value: '0000-00-00 00:00:00' for column 'trxn_date' at row 1]
  DebugInfo UPDATE civicrm_financial_trxn SET trxn_date = NULL WHERE trxn_date = '0000-00-00 00:00:00' [nativecode=1292 ** Incorrect datetime value: '0000-00-00 00:00:00' for column 'trxn_date' at row 1]
  
  PEAR_Exception: DB Error: unknown error in /var/www/public/drupal7/sites/all/modules/civicrm/packages/PEAR.php on line 921
  - DB_Error: DB Error: unknown error in unknown on line unknown
  Exception trace
  # Function  Location
  0 CRM_Core_Error::exceptionHandler(Object(DB_Error))  /var/www/public/drupal7/sites/all/modules/civicrm/packages/PEAR.php:921
  1 PEAR_Error->__construct('DB Error: unknow…', -1, 16, Array, 'UPDATE civicrm_f…')  /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB.php:984
  2 DB_Error->__construct(-1, 16, Array, 'UPDATE civicrm_f…') /var/www/public/drupal7/sites/all/modules/civicrm/packages/PEAR.php:575
  3 PEAR->_raiseError(Object(DB_mysqli), null, -1, 16, Array, 'UPDATE civicrm_f…', 'DB_Error', true)  /var/www/public/drupal7/sites/all/modules/civicrm/packages/PEAR.php:223
  4 PEAR->__call('raiseError', Array) /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB/common.php:1907
  5 DB_common->raiseError(-1, null, null, 'UPDATE civicrm_f…', '1292 ** Incorrec…') /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB/mysqli.php:933
  6 DB_mysqli->mysqliRaiseError() /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB/mysqli.php:403
  7 DB_mysqli->simpleQuery('UPDATE civicrm_f…') /var/www/public/drupal7/sites/all/modules/civicrm/packages/DB/common.php:1216
  8 DB_common->query('UPDATE civicrm_f…') /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Utils/File.php:366
  9 CRM_Utils_File::runSqlQuery('mysql://root:roo…', '-- CRM-19715 SEL…', null) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Form.php:177
  10  CRM_Upgrade_Form->source('-- CRM-19715 SEL…', true) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Form.php:372
  11  CRM_Upgrade_Form->processLocales('/var/www/public/…', '4.7.19') /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Form.php:406
  12  CRM_Upgrade_Form->processSQL('4.7.19')  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Upgrade/Incremental/Base.php:80
  13  CRM_Upgrade_Incremental_Base::runSql(Object(CRM_Queue_TaskContext), '4.7.19') /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/Task.php:88
  14  CRM_Queue_Task->run(Object(CRM_Queue_TaskContext))  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/Runner.php:214
  15  CRM_Queue_Runner->runNext(true) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:52
  16  CRM_Queue_Page_AJAX::{closure}()  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/ErrorPolicy.php:106
  17  CRM_Queue_ErrorPolicy->call(Object(Closure))  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Queue/Page/AJAX.php:54
  18  CRM_Queue_Page_AJAX::runNext()  /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:275
  19  CRM_Core_Invoke::runItem(Array) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:84
  20  CRM_Core_Invoke::_invoke(Array) /var/www/public/drupal7/sites/all/modules/civicrm/CRM/Core/Invoke.php:52
  21  CRM_Core_Invoke::invoke(Array)  /var/www/public/drupal7/sites/all/modules/civicrm/drupal/civicrm.module:445
  22  civicrm_invoke('upgrade', 'queue', 'ajax', 'runNext') /var/www/public/drupal7/includes/menu.inc:527
  23  menu_execute_active_handler() /var/www/public/drupal7/index.php:21
  24  {main}   
  
  ```
  
  **Tags:** mysql-5.7
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2018-09-21. Score: 3
  
  Yes that link you posted has the answer in #4. The easiest thing to do would be to remove those values from the sql\_mode setting, i.e. in mysql run this command
  
  SELECT @@GLOBAL.sql\_mode;
  
  Then it will output some string. Remove any reference to STRICT\_TRANS\_TABLES, NO\_ZERO\_DATE, and NO\_ZERO\_IN\_DATE from that string. Then whatever is left you would assign that to sql\_mode in your my.cnf file:
  
  sql\_mode="whatever is left from the string"
  
  Then restart mysql.
  
  FYI this would change the behavior of other databases on your server too, if you have any.
  
  > **Comments:**
  > **username_0**: Thank you @username_1! It worked perfectly, and the update completed all the way. My web site is on a shared server, so I can't restart MySQL and will probably have to pull down the database, update and re-upload it ...  There were a lot of warnings ("you may want to change these from "DATETIME" to "TIMESTAMP""), under /civicrm/a/#/status, so I ran [Doctor When: Temporal cleanup agent](https://github.com/civicrm/org.civicrm.doctorwhen) which seems to have fixed the issues.  
  > **username_0**: I ended up doing the update locally, and uploading the database, which worked well.  
  > **username_2**: Thanks, It works, it would be better if I could find this answer earlier.  
  
  ---
  
  ### Answer by username_3 on 2022-08-04. Score: 1
  
  I came across a similar issue during an upgrade from CiviCRM 5.29 to 5.51.
  
  I took the approach of fixing the incorrect datetime values rather than changing the MySQL configuration to ignore incorrect dates.
  
  The following SQL query should set the incorrect dates to `NULL`:
  
  ```
  UPDATE civicrm_financial_trxn SET created_date=NULL WHERE created_date=0;
  
  ```
  
  Note that you have to use `WHERE created_date=0` not `WHERE created_date='0000-00-00 00:00:00'`.
  
  
  ---
  
  