User:Ilmari Karonen/Queries/Zombie images
Jump to navigation
Jump to search
This is a list of images on Commons with no description page. This page documents (to the displeasure of the Wikimedia Foundation's data integrity division) files that had their metadata spontaneously deleted. If possible please restore from a database dump.
-- 2> /dev/null; date; echo '
/* Files missing a description page
* http://quarry.wmflabs.org/query/899
*
* License: Public Domain
* Runtime: 15 minutes <SLOW_OK>
*/
SELECT STRAIGHT_JOIN
CONCAT("[[:File:", REPLACE(img_name, "_", " "), "]]", IF(page_latest=0, " (bad history)", "")) AS "File",
DATE_FORMAT(img_timestamp, "%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"
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 /* Missing page table */
OR page_latest=0 /* Missing revisions */
GROUP BY img_name
;-- ' | mysql -ch s4.analytics.db.svc.eqiad.wmflabs commonswiki_p --html > ~/public_html/logs/zombie_images.html; date;
| File | Timestamp | Uploader | Actions | Usage |
|---|