18

We all know that Drupal doesn't really support the concept of foreign keys in the database, and likely never will1.

We also know that it is possible for modules to not do the right thing, or that users will periodically do crazy things like

DELETE FROM node_revision
WHERE vid = 123

in an effort to "keep the database tidy". When this happens, hooks don't run, and there is orphaned data in the database. Mostly, this data is harmless, but occasionally it causes really weird behavior and can bring out bugs in code.

Are there any decent methods for detecting whether you have have dead and/or inconsistent data in your database?

1 The Drupal 7 Schema API supports them for documentation, but not enforcement.

|improve this question
  • I assume you mean "any decent methods from within Drupal"? Or are you open to an outside tool? – iconoclast Oct 5 '12 at 14:17
  • Ideally, within Drupal, but I'm open to anything. I started paper planning a Drupal tool, but don't know how far it will go. – mpdonadio♦ Oct 5 '12 at 14:59
  • Can the problem be reduced to a set of SQL queries to find such data? If you were not building any easy-to-use tool with a nice Drupal UI, but instead sitting at a MySQL client issuing queries manually, how would you find such inconsistencies? – iconoclast Oct 5 '12 at 15:13
  • The queries aren't really the problem. It is more knowing what tables to query and what keys to join on. My paper idea is to use the Schema API to get all of the tables, and then try to figure out from each schema whether we can build a query. This may end up being a holiday project for me. – mpdonadio♦ Oct 5 '12 at 16:03
  • Btw, looking like finally some FK support in D8: drupal.org/node/911352 – Nic Nov 12 '12 at 10:31
2

As I was writing up a description for a sandbox project, I ran across a sandbox project for doing this:

http://drupal.org/sandbox/anton_krechetov/1345066

It currently only works for Drupal 6, and there haven't been any commits since Nov 2011, but it looks promising.

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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