Deleted pages breaking PAGESINCATEGORY (subcat)

Fork of Deleted pages breaking PAGESINCATEGORY (1 cat) by Cryptic

This query is marked as a draft This query has been published by Cryptic.

Pages whose rows in categorylinks weren't deleted when the pages were, for direct subcategories of a 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).

SQL

SET @parentcat = 'Speedy deletion';

SELECT log_namespace, log_title, GROUP_CONCAT(DISTINCT zc.cl_to SEPARATOR ' | ') AS cat
FROM logging_logindex
JOIN categorylinks AS zc ON zc.cl_from = log_page
JOIN archive ON ar_namespace = log_namespace AND ar_title = log_title AND ar_page_id = log_page -- this filters out redirects left by moves
JOIN page ON page_namespace = 14 AND page_title = zc.cl_to
JOIN categorylinks AS sc ON sc.cl_from = page_id
WHERE sc.cl_to = REPLACE(@parentcat, ' ', '_')
  AND NOT EXISTS (SELECT 1 FROM page WHERE page_id = log_page)
GROUP BY log_namespace, log_title;

​x
 
SET @parentcat = 'Speedy deletion';​SELECT log_namespace, log_title, GROUP_CONCAT(DISTINCT zc.cl_to SEPARATOR ' | ') AS catFROM logging_logindexJOIN categorylinks AS zc ON zc.cl_from = log_pageJOIN archive ON ar_namespace = log_namespace AND ar_title = log_title AND ar_page_id = log_page -- this filters out redirects left by movesJOIN page ON page_namespace = 14 AND page_title = zc.cl_toJOIN categorylinks AS sc ON sc.cl_from = page_idWHERE sc.cl_to = REPLACE(@parentcat, ' ', '_')  AND NOT EXISTS (SELECT 1 FROM page WHERE page_id = log_page)GROUP BY log_namespace, log_title;

By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.

Submit Query Stop Query

All SQL code is licensed under CC0 License.

SQL

Checking query status...