Deleted pages breaking PAGESINCATEGORY (1 cat)

Pages whose rows in categorylinks weren't deleted when the pages were, for a single given category.

This causes a miscount in {{PAGESINCATEGORY}} for the categories they're in, in such a way that purging the category page - the usual solution - doesn't fix it.  (Speedy deletion-related categories are particularly prone to such errors.) The problem is that rows in categorylinks tying the old page id to the category remain, and the recount triggered by purging doesn't check to see if those pages still exist. Restoring then re-deleting the page usually does fix it, though only if no other revisions have been placed at that page title in the meantime (which assigns a new page_id).

(See query 69117 for a version that checks all immediate subcategories of a given category.)

SQL

SET @cat = REPLACE('Candidates for speedy deletion by user', ' ', '_');
SELECT DISTINCT log_namespace, log_title
FROM logging_logindex
JOIN categorylinks ON cl_from = log_page
LEFT JOIN page ON page_id = log_page
JOIN archive ON ar_namespace = log_namespace AND ar_title = log_title -- this filters out redirects left by moves
WHERE cl_to = @cat
  AND page_id IS NULL
  AND ar_page_id = log_page;

SQL

Checking query status...