Missing Relationship Cache Data fails to rebuild cache with DB error

Question

I'm getting a "Missing Relationship Cache Data" warning on the CiviCRM status page after updating from 5.67.3 to 5.72.2. Clicking the button to rebuild cache shows a spinner and fails to rebuild the cache. I tried running the command

cv api4 RelationshipCache.rebuild

and also

cv ev 'CRM_Contact_BAO_RelationshipCache::rebuild();'

but it just outputs "In Error.php line 971: DB Error: unknown error"

How do I figure out where the error is, and get the cache rebuild to work? The status warning says Expected 52684 records, and that it found 0 in cache. In addition, now the relationship tab in contacts doesn't show any relationships when previously there used to be.

Civi is running on Drupal 7.100 with php 8.1.28. cv version is 0.3.48.

I checked logs in the ConfigAndLog folder, Drupal logs, syslog, php-fpm, nginx logs and get no information regarding the error.

Answer 1

The issue was that a row in the end_date column of the civicrm_relationship table had an incorrect date value of '0000-00-00' when it should have been NULL.

I tracked this by watching the mysql log output where I found the sql query for rebuilding the relationship cache table. Running the query in phpmyadmin notified me of the error.