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?

2 Answers 2


You can copy the database to your local version of MySQL. That is generic database exporting and importing that you can find instructions for elsewhere.

To query specific nodes you can use this SQL:

SELECT * FROM node WHERE nid = 4;

If you get a result the node exists, if not then it doesn't.

It might be possible that you have an entry in the node table but other related node data is missing, in which case your data is corrupted somehow. Hopefully that's not it.

There are so many options as to how it may have happened that we can't really give a definite answer, but the first 2 most likely possibilities that come to mind are:

  1. The nodes are gone, for whatever reason
  2. During the migration from Drupal 6 to Drupal 7, nodes somehow ended up with different node IDs and the referencing fields were not updated to match.

If it is the second one you can search on the node title, for example:

SELECT * FROM node WHERE title = 'Chicken';

If you get a result and the nid isn't what you expect based on the reference field then that is probably your problem. In that case you will need to somehow map the old node IDs to the new node IDs and then update the reference field values to match the new IDs.

Depending on how complex the problem ends up being you may have to further familiarise yourself with the Drupal schema.

I would assume that there probably aren't any logs that would help you out here. There may be logs stating when a node was updated but not the values that were updated. Or you might be able to see PHP errors at a similar time to things updating and maybe they are related but I probably wouldn't waste my time down that path.

After a few queries to determine whether the data is missing or if it is still there but has changed, I would probably start by comparing different database versions around points of interest in time (eg, before and after any serious development or migrations) to try to narrow down when the data disappeared/changed. Then work out what was happening at that time (was it a migration? etc.) then investigate based on that.

  • Thank you for this comprehensive answer. I'll give that query a go--I suspect things might have gotten changed in the migration but would really like to ensure it doesn't happen again.
    – Rokujolady
    Aug 10, 2016 at 3:39
  • Interesting topic, about going after Chickens using Drupal ... But, maybe you would want to transform your last SQL stmnt to something using LIKE ... If you do, who knows what else, apart from Chickens, would start to appear ... Aug 10, 2016 at 9:00
  • You could do that, however my answer is more relating to Drupal node schema. SQL instruction can be found elsewhere.
    – rooby
    Aug 10, 2016 at 21:52

Drupal 7 does not delete references to deleted nodes by default. See the Field reference delete module. Some details about it, from its project page (check that page for even more details):

This module removes references to a deleted entity from fields stored in an SQL database. It exists to prevent stale references to non-existent content from causing unexpected problems (for example, when the referencing content is being displayed).

I'm not sure if it will clean up existing "dead" references, but at least moving forward it does.

  • I did not realize that it didn't propagate deletions. I'll check out that module.
    – Rokujolady
    Aug 10, 2016 at 3:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.