Upgrading gives error "Incorrect datetime value: '0000-00-00 00:00:00'" in MySQL 5.7
When upgrading CiviCRM from 4.7.12 to 5.3.2 I got the following error ...
PEAR_Exception: "DB Error: unknown error"
- ERROR TYPE: DB_Error
- ERROR CODE: -1
- ERROR MESSAGE: DB Error: unknown error
- ERROR MODE: 16
- ERROR 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]
The error is raised by 2 lines of SQL in civicrm/CRM/Upgrade/Incremental/sql/4.7.19.mysql.tpl ...
UPDATE civicrm_financial_trxn SET trxn_date = NULL WHERE trxn_date = '0000-00-00 00:00:00';
UPDATE civicrm_contribution SET receive_date = NULL WHERE receive_date = '0000-00-00 00:00:00';I'm using MySQL 5.7.23. I found a solution which is to cast the DATETIME columns in the WHERE clause to CHAR(20) before comparing to '0000-00-00 00:00:00'.
To upload designs, you'll need to enable LFS. More information