Possible Bug? Foreign key constraint error on upgrade to 5.31.0 (populating civicrm_relationship_cache)

Question

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
    (
        [0] => CRM_Core_Error
        [1] => 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.

1. 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)
2. Export that data to, eg. a CSV file and reformat from CSV data as SQL insert statements using INSERT IGNORE instead of INSERT to 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.
3. 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)
4. Repeat step 2 above with the results of step 3.
5. 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'.
6. Run the civicrm database upgrade script. It should now complete successfully.
7. 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.

Answer 1

Your system have orphan data in civicrm_relationship table. There are few rows in civicrm_relationship table that have contact id in column civicrm_relationship.contact_id_a or civicrm_relationship_contact_id_b are missing in civicrm_contact.

SELECT cr.*
FROM civicrm_relationship cr
LEFT JOIN civicrm_contact cc 
  ON cc.id = cr.contact_id_a
WHERE cc.id IS NULL

SELECT cr.*
FROM civicrm_relationship cr
LEFT JOIN civicrm_contact cc 
  ON cc.id = cr.contact_id_b
WHERE cc.id IS NULL;

Above query should give you list of orphan records.

Fix:

1. Delete all orphan data from civicrm_relationship table before running upgrade
2. Check if FK is defined for civicrm_relationship table

HTH

Pradeep

Answer 2

Further to Pradeep Nayak's reply, the ultimate cause of the problems I was having were missing foreign key constraints on the civicrm_relationship table leaving orphaned data when contacts were deleted.

Therefore, a solution which fixes the problem rather than working around it would be:

1. Check that all foreign key constraints are missing on civicrm_relationship table.

2. Run DELETE FROM civicrm_relationship WHERE contact_id_a NOT IN (SELECT DISTINCT id FROM civicrm_contact).

3. Run DELETE FROM civicrm_relationship WHERE contact_id_b NOT IN (SELECT DISTINCT id FROM civicrm_contact).

4. Add the missing foreign key constraints:

   ALTER TABLE civicrm_relationship ADD CONSTRAINT FK_civicrm_relationship_contact_id_a FOREIGN KEY (contact_id_a) REFERENCES civicrm_contact(id) ON DELETE CASCADE;

   ALTER TABLE civicrm_relationship ADD CONSTRAINT FK_civicrm_relationship_contact_id_b FOREIGN KEY (contact_id_b) REFERENCES civicrm_contact(id) ON DELETE CASCADE;

   ALTER TABLE civicrm_relationship ADD CONSTRAINT FK_civicrm_relationship_relationship_type_id FOREIGN KEY (relationship_type_id) REFERENCES civicrm_relationship_type(id) ON DELETE CASCADE;

   ALTER TABLE civicrm_relationship ADD CONSTRAINT FK_civicrm_relationship_case_id FOREIGN KEY (case_id) REFERENCES civicrm_case(id) ON DELETE CASCADE;

5. Run the CiviCRM upgrade script.

I still think an upgrade silently failing is a bug that probably deserves to be fixed.