I've actually already diagnosed and worked around this problem for myself in a fairly large CiviCRM database within a Wordpress install, but thought I'd refer to those more expert here as to whether this error is deserving of a bug report.
On updating CiviCRM 5.28.0 to 5.31.0 I encountered a fatal foreign key error during the database upgrade process. From a user perspective the upgrade appeared to simply hang permanently. No error messages were thrown.
The error related to the population of the new civicrm_relationship_cache table introduced in version 5.29.alpha1.
Expected behaviour: upgrade DB from 5.28.0 to 5.31.0. User warnings or graceful failure in event of a failed upgrade process.
Actual behaviour: upgrade failed at 5.29.0 migrations without showing any errors or explanation to user. Not possible to resolve problems short of some fairly significant code exploration and SQL gymnastics.
The CiviCRM log did record the details:
Nov 21 22:45:53 [info] Running task: Upgrade DB to 5.29.alpha1: Fill civicrm_relationship_cache (3 => 5002) Nov 21 22:45:53 [error] $Fatal Error Details = Array ( [callback] => Array (  => CRM_Core_Error  => exceptionHandler ) [code] => -3 [message] => DB Error: constraint violation [mode] => 16 [debug_info] => INSERT INTO civicrm_relationship_cache (relationship_id, relationship_type_id, orientation, near_contact_id, near_relation, far_contact_id, far_relation, start_date, end_date, is_active) SELECT rel.id, rel.relationship_type_id, "a_b", rel.contact_id_a, reltype.name_a_b, rel.contact_id_b, reltype.name_b_a, rel.start_date, rel.end_date, rel.is_active FROM civicrm_relationship rel INNER JOIN civicrm_relationship_type reltype ON rel.relationship_type_id = reltype.id WHERE (rel.id >= 3 AND rel.id <= 5002) ON DUPLICATE KEY UPDATE relationship_type_id = rel.relationship_type_id, near_contact_id = rel.contact_id_a, near_relation = reltype.name_a_b, far_contact_id = rel.contact_id_b, far_relation = reltype.name_b_a, start_date = rel.start_date, end_date = rel.end_date, is_active = rel.is_active [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`cdhalton-network-civi`.`civicrm_relationship_cache`, CONSTRAINT `FK_civicrm_relationship_cache_near_contact_id` FOREIGN KEY (`near_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE C)]
To get around the problem, I had to do the following, which probably has a similar effect as running the SQL with foreign key checks turned off, but won't enter duplicate rows.
- In MySQL client (eg PHPMyAdmin) run the 'inner' select mentioned in the error during the failing upgrade query to get the data that needs to be populated to the new civicrm_relationship_cache table.
SELECT rel.id, rel.relationship_type_id, "a_b", rel.contact_id_a, reltype.name_a_b, rel.contact_id_b, reltype.name_b_a, rel.start_date, rel.end_date, rel.is_active FROM civicrm_relationship rel INNER JOIN civicrm_relationship_type reltype ON rel.relationship_type_id = reltype.id WHERE (rel.id >= NNNN AND rel.id <= NNNN)where 'NNNN' are the numbers found in the error log message above (in my case 3 and 5002)
- Export that data to, eg. a CSV file and reformat from CSV data as SQL insert statements using
INSERT IGNOREinstead of
INSERTto avoid any foreign key errors:
INSERT IGNORE INTO civicrm_relationship_cache (relationship_id, relationship_type_id, orientation, near_contact_id, near_relation, far_contact_id, far_relation, start_date, end_date, is_active) VALUES (39,2,"a_b",2222,"Spouse of",712,"Spouse of",NULL,NULL,1);(Change the sample data here to match each row of the results obtained in step one). I deliberately created each row as a separate insert in case of unexpected errors, so the entire insert process didn't fail. Save as an sql file for later.
- Then run the inverse of the query:
SELECT rel.id, rel.relationship_type_id, "b_a", rel.contact_id_b, reltype.name_b_a, rel.contact_id_a, reltype.name_a_b, rel.start_date, rel.end_date, rel.is_active FROM civicrm_relationship rel INNER JOIN civicrm_relationship_type reltype ON rel.relationship_type_id = reltype.id WHERE (rel.id >= NNNNN AND rel.id <= NNNNN)
- Repeat step 2 above with the results of step 3.
- Edit the upgrade file path/to/civicrm/civicrm/CRM/Upgrade/Incremental/php/FiveNineteen.php and comment out everything inside the populateRelationshipCache method except 'return TRUE'.
- Run the civicrm database upgrade script. It should now complete successfully.
- Run/import the SQL statements you generated in step 2 and 4 on the CiviCRM database to complete the migration process from civicrm_relationship to civicrm_relationship_cache.
If there's a vastly simpler way of achieving this workaround that I've missed, happy to hear it!
To reiterate, my primary question is: "is this a bug I should report?"
It's obviously fair to make the point that the problem is ultimately mine created by some data inconsistency that caused the foreign key error.
All I can say is this is a database that's been around almost a decade, been converted from Joomla to Wordpress along the way and 'foreign key errors happen', especially in databases of some age which may have had some less than perfect extensions installed and removed from time to time. It would be helpful if upgrade errors failed more gracefully, or even provided the option to continue ignoring the foreign key problem. Admittedly we're also dealing with MySQL's own completely unhelpful way of handling foreign key problems which make it cumbersome to identify the rows that are causing problems so as to resolve them rather than ignore them.