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 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}   

2 Answers 2


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.

  • 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 which seems to have fixed the issues.
    – regit55
    Sep 22, 2018 at 15:18
  • 1
    I ended up doing the update locally, and uploading the database, which worked well.
    – regit55
    Sep 22, 2018 at 19:23
  • Thanks, It works, it would be better if I could find this answer earlier. Mar 13, 2019 at 12:50

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'.

Your Answer

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

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