design for quality

clients
resources

Use case found for querying multiple databases

200812Mar

Today I finally found a use case for querying multiple databases.

Within 1/2 hour of each other I had two different individuals affiliated with a Drupal site tell me that pages which were there yesterday weren't today. Investigation into the site didn't turn up anything but broken links.

I wanted to know how wide-spread the issue was. I mean, were the pages dropping like flies?

To find out, I installed a the previous day's backup as its own database and was then was able to use the following query to locate any instances of null entries in the goofy site where there should have been a match in the old site:

SELECT g.nid, g.title, b.nid, b.tile
FROM goofysite.node g RIGHT JOIN backupsite.node b
ON g.nid = b.nid
ORDER BY g.title;

It worked pretty well! And fortunately, we only had the two dropped pages so that seemed to indicate something non-systemic as the issue.

Ultimately I think what happened was that the pages were accidentally deleted with some spam messages by accident. The attachments associated with the nodes were deleted, and that would indicate that an actual delete action had occurred. I'd done a grep on the logs to check for 'node/[node numbers]/delete' and didn't find those, however if these items were deleted with spam content, those URLs wouldn't have been triggered.

tags

© 2008