~Bad links from Commons to other wikis

SQL

USE commonswiki_p;
SELECT now();
#empty links, no target given
(SELECT CONCAT('# [[', IF (page_namespace = 14, ':Category:', ''), REPLACE (page_title, '_', ' '), ']] ',
               '→ [[:', iwl_prefix, ':', REPLACE (iwl_title, '_', ' '), ']]') AS result
FROM iwlinks
INNER JOIN page
ON page_id = iwl_from
AND page_namespace IN (0, 14)
AND page_id NOT IN (SELECT p2.page_id FROM page p2 INNER JOIN templatelinks
                    ON tl_from = p2.page_id AND tl_from_namespace IN (0, 14)
                    WHERE tl_title = 'Lang-mp' AND tl_namespace = 10)
AND page_id NOT IN (139945,   #MediaWiki
                    885242,   #SeaMonkey
                    194001,   #Wikimedia local chapters
                    67772844, #Wikipedia/1
                    10309740) #Wikipedia/2.0/worksheet
AND page_title NOT LIKE 'User_%'
WHERE iwl_title REGEXP '^ *$'
AND iwl_prefix NOT IN ('b', 'wikibooks', 'm', 'meta', 'species', 'wikispecies', 'wmf', 'wikimedia', 'foundation', 'phab', 'phabricator')
)/*
UNION
#broken interwikilinks
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':Category:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '→ [[:c:', REPLACE (iwl_title, '_', ' '), ']]') AS wikipage
FROM iwlinks
INNER JOIN page wp
ON wp.page_id = iwl_from
AND wp.page_namespace IN (0, 14)
AND wp.page_is_redirect = 0
AND wp.page_title NOT REGEXP @p
WHERE iwl_prefix ='commons'
AND iwl_title LIKE 'Category:%'
AND NOT EXISTS (SELECT 1 FROM commonswiki_p.page cp
                WHERE cp.page_title = SUBSTRING(iwl_title, 10)
                AND cp.page_namespace = 14)
)
UNION
#broken external interwiki links
(SELECT CONCAT('# [[', IF (wp.page_namespace = 14, ':Category:', ''), REPLACE (wp.page_title, '_', ' '), ']] ',
               '→ [[:c:', REPLACE ((SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), 36)), '_', ' '), ']]') AS wikipage
FROM externallinks
INNER JOIN page wp
ON wp.page_id = el_from
AND wp.page_namespace IN (0, 14)
AND wp.page_is_redirect = 0
AND wp.page_title NOT REGEXP @p
WHERE el_to LIKE 'https://commons.wikimedia.org/wiki/Category:%'
AND el_to NOT LIKE '%\%%' #temp
AND NOT EXISTS (SELECT 1 FROM commonswiki_p.page cp
                WHERE cp.page_namespace = 14
                AND cp.page_title = SUBSTRING(REPLACE (REPLACE (el_to, '#mw-subcategories', ''), CONCAT('?uselang=', @l), ''), 45)
               )
)*/
#ORDER BY wikipage
LIMIT 2000;
SQL

Checking query status...
