1

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!

2
  • Can you look in ConfigAndLog for the full error?
    – Demerit
    Jan 19, 2023 at 5:35
  • @Demerit, I added information form ConfigAndLog. Thank you!
    – Josh
    Jan 22, 2023 at 18:45

1 Answer 1

3

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.
1
  • Had same issue. It was only one row. The row did have a create_date and modified date so it was easy to update. I couldn't get mysql to accept the activity_date_time = '0000-00-00 00:00:00' so I changed this to < '0001-01-01 00:00:00'
    – Paul-Tahoe
    Mar 26 at 18:48

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.