[5.x]: SQL error with m230617_070415_entrify_matrix_blocks migration

[tyssen]

What happened?

Description

While attempting a Craft 5 upgrade I got the following

1. Ran the composer upgrade successfully
2. When running craft up it got to m230617_070415_entrify_matrix_blocks and then stopped with:

Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'postDate' at row 18
The SQL being executed was: ALTER TABLE entries ADD CONSTRAINT fk_uideqcoxvflgzpuixztokolwgbortxmtbrjs FOREIGN KEY (fieldId) REFERENCES fields (id) ON DELETE CASCADE (/var/www/html/craft/vendor/yiisoft/yii2/db/Schema.php:676)

Craft CMS version

5.2.8

PHP version

8.1

Operating system and version

Mac OSX / DDEV

Database type and version

MySQL 8.0

Image driver and version

No response

Installed plugins and versions

[brandonkelly]

Sounds like you somehow have an entry with a postDate set to 0000-00-00 00:00:00. That should not have been possible: according to https://dev.mysql.com/doc/refman/8.4/en/datetime.html, the minimum allowed datetime value is 1000-01-01.

Restore your database to a backup from before you attempted the upgrade, and then execute the following SQL query:

UPDATE `entries`
SET `postDate` = '1000-01-01 00:00:00'
WHERE `postDate` = '0000-00-00 00:00:00'

Then try the upgrade again.

If you’re still having trouble, please send your database backup and Composer files into support@craftcms.com.

[tyssen]

Yes, there was a single entry that had a 0000-00-00 00:00:00 post date. Everything went through fine after I fixed that one up. 👍

[brandonkelly]

Glad to hear!