Disappearing records in RelationToPrimarys table solved
: Stewart Arneil
Minutes: 600
Last week the third cycle of mysteriously disappearing records in the rtp table was reported. After another cycle of testing we (combining insights of mRtin Tracey and me) finally figured out the problem. The RTP table had four foreign key constraints. One of those was pointing at the primary_id_fk field in the mentions table and was set to ON DELETE CASCADE. That was causing the problem under circumstances detailed below. When we removed that constraint, we could no longer reproduce problem.
the relationToPrimarys.rtp_primary_id_fk had a constraint pointing to primarys.primary_id and a constraint pointing to mentions.primary_id_fk the latter of which had ON DELETE CASCADE
relationToPrimarys record id 31 has value 9 in field rtp_mention_id_fk
relationToPrimarys record id 31 has value 12 in field rtp_primary_id_fk
relationToPrimarys record id 34 has value 9 in field rtp_mention_id_fk
relationToPrimarys record id 34 has value 12 in field rtp_primary_id_fk
mentions record id 9 has value 12 in primary_id_fk
mentions record id 15 has value 12 in primary_id_fk
If user deleted mentions record id 15 because nothing pointed to it, then because of the ON DELETE CASCADE constraint any record in mentions which had value of mentions_primary_id_fk = 12 is deleted (i.e. record id 31 and record id 34) even though neither of them point to mentions record id 15. Thus the mysterious disappearing of records in the rtps table.