Untitled query #3924

SQL

-- 2> /dev/null; date; echo '
/* Files missing a description page
 * Runtime: 15 minutes <SLOW_OK>
 */
USE commonswiki_p;
SELECT STRAIGHT_JOIN
  CONCAT("[[:File:", REPLACE(img_name, "_", " "), "]]", IF(page_latest=0, " (bad history)", "")) AS "File",
  IF(img_media_type IN ("AUDIO", "VIDEO"), "—",CONCAT("<b>[[{{ns:6}}:", REPLACE(img_name, "_", " "), "|1x1px|alt=404|Removed]]</b>")) AS "Status",/*-*/
  DATE_FORMAT(IFNULL(img_timestamp, NOW()), "%Y-%m-%d") AS "Timestamp",
  CONCAT("{{user|", REPLACE(img_user_text, "_", " "), "}}") AS "Uploader",
  IFNULL(GROUP_CONCAT(log_action ORDER BY log_timestamp SEPARATOR ", "),"") AS "Actions",
  (SELECT COUNT(*) FROM globalimagelinks WHERE gil_to=img_name) AS "Usage",
  "" AS "NOT STATIC"
FROM image
LEFT JOIN page ON page_namespace=6 AND page_title=img_name
LEFT JOIN logging_logindex ON log_namespace=6 AND log_title=img_name
AND log_action NOT IN ("patrol")
WHERE page_title IS NULL
OR page_latest=0 /* Missing revisions */
GROUP BY img_name
;-- ' | mysql -ch commonswiki.labsdb commonswiki_p --html > ~/public_html/logs/zombie_images.html; date;
SQL

Checking query status...
