From 2f144cc61fb5e97b2d90c565768875a3087cf297 Mon Sep 17 00:00:00 2001 From: Ken West Date: Sat, 6 Oct 2018 18:25:12 +1000 Subject: [PATCH] MySQL 5.7 may bork when comparing datetime columns to '0000-00-00 00:00:00' so cast the column to a CHAR(20) when comparing --- CRM/Upgrade/Incremental/sql/4.7.19.mysql.tpl | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/CRM/Upgrade/Incremental/sql/4.7.19.mysql.tpl b/CRM/Upgrade/Incremental/sql/4.7.19.mysql.tpl index ee87b96788..2e644c5155 100644 --- a/CRM/Upgrade/Incremental/sql/4.7.19.mysql.tpl +++ b/CRM/Upgrade/Incremental/sql/4.7.19.mysql.tpl @@ -44,8 +44,9 @@ VALUES -- Some legacy sites have `0000-00-00 00:00:00` values in -- `civicrm_financial_trxn.trxn_date` which correspond to the same value in -- `civicrm_contribution.receive_date` -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'; +-- MySQL 5.7 may bork when comparing datetime columns to '0000-00-00 00:00:00' so cast the column to a CHAR(20) when comparing +UPDATE civicrm_financial_trxn SET trxn_date = NULL WHERE CAST(trxn_date AS CHAR(20)) = '0000-00-00 00:00:00'; +UPDATE civicrm_contribution SET receive_date = NULL WHERE CAST(receive_date AS CHAR(20)) = '0000-00-00 00:00:00'; -- CRM-20439 rename card_type to card_type_id of civicrm_financial_trxn table (IIDA-126) ALTER TABLE `civicrm_financial_trxn` CHANGE `card_type` `card_type_id` INT(10) UNSIGNED NULL DEFAULT NULL COMMENT 'FK to accept_creditcard option group values'; -- 2.25.1