So, I inherited a long-standing and complicated drupal site with a lot of records that reference each other. The site started out in Drupal 6 and these references used to be CCK Nodereferences, but they ported to Drupal 7, and I think things are now mostly entity-references, though there might be a CCK Nodereference here or there. I'm mentioning this because it's an area that I don't know much about and I think it might be relevant for what follows.
The problem is this: There are some nodes that are referenced by ID by other nodes that seem to have disappeared. Let's say, for example, we have a content type "Chicken coop", which contains references to 100 "chicken" nodes. If I edit "Chicken coop", I'll see 100 fields, each with a name and value, for example, "Hen 1[nid:1]", "Rooster 50[nid:62]" but one of them will be a null reference. "[nid:4]", and when I build a view to list all the "Chickens", there will be no chicken with nid:4. It's like that chicken got removed from the database and removal didn't propagate to all the things that referenced it like it should have. Obviously, I'd like to know how this happened, and I've noticed that we have had backups turned on for several years. I'd like to find out when this happened and what other things happened around this time. Did these null references appear at the same time?
I can download the backups and load them into a local mysql instance. How would I query to see if chicken nid:4 is still in them? This does not seem simple or intuitive from the drupal schema I've seen.
Does this problem seem familiar to anyone and do you have any suggestions on whether this was a one-time thing that's not likely to happen again?
Any other logs that you would recommend examining or troubleshooting steps I ought to take?