Upgrade to 5.57.1 Fails With Error executing task "Fix dangerous delete cascade"

Question

I am trying to upgrade from CiviCRM 5.31.1 to 5.57.1 with Drupal 9. I am running MySQL 5.7.41.

I downloaded the new version of CiviCRM with Composer. I then used CV to execute upgrade:db.

The upgrade started normally read out 46 dots under "Executing upgrade..." and then printed "Error executing task "Fix dangerous delete cascade." The remainder of the error is In Error.php line 955: DB Error: unknown error.

When looking at the log in ConfigAndLog I find:

(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => exceptionHandler
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => ALTER TABLE `civicrm_activity` ADD CONSTRAINT `FK_civicrm_activity_original_id` FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE SET NULL [nativecode=1292 ** Incorrect datetime value: '0000-00-00 00:00:00' for column 'activity_date_time' at row 2037]
    [type] => DB_Error
    [user_info] => ALTER TABLE `civicrm_activity` ADD CONSTRAINT `FK_civicrm_activity_original_id` FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE SET NULL [nativecode=1292 ** Incorrect datetime value: '0000-00-00 00:00:00' for column 'activity_date_time' at row 2037]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="ALTER TABLE `civicrm_activity` ADD CONSTRAINT `FK_civicrm_activity_original_id` FOREIGN KEY (`original_id`) REFERENCES `civicrm_activity` (`id`) ON DELETE SET NULL [nativecode=1292 ** Incorrect datetime value: '0000-00-00 00:00:00' for column 'activity_date_time' at row 2037]"]
)

I have Googled this and I see a few things for MySQL but I am not sure how to apply them. I cannot find anything close for CiviCRM and nothing directly related for Pear either.

I would appreciate help figuring this out.

Thanks!

Answer 1

That's kind of interesting since it seems like it doesn't have anything to do with the upgrade statement and is more about some weird data in the database. Either you used to have SQL_MODE set to allow zero datetimes and now you don't, or the database was moved from another system at some point. But either way 0000 doesn't make sense for activity date. You have a couple choices:

• Update SQL_MODE to remove these values from the config: NO_ZERO_IN_DATE, NO_ZERO_DATE, but then you still have weird dates, just they'll be allowed, OR
• Update the data so it doesn't have any zeros, but the trick will be what datetime to use since you won't know what it should be without knowing the context for the activity. You might be able to get something good enough by using the earliest modified date from civicrm_log which will at least be close some of the time: UPDATE civicrm_activity a LEFT JOIN (SELECT m.entity_table, m.entity_id, min(modified_date) AS mindate FROM civicrm_log m GROUP BY m.entity_table, m.entity_id) t ON (t.entity_table = 'civicrm_activity' AND t.entity_id = a.id) SET a.activity_date_time = t.mindate WHERE a.activity_date_time = '0000-00-00 00:00:00';. Make a backup first, which you probably already have from before the earlier upgrade.