Page MenuHomePhabricator
Paste P5972

Media files needing purging (WP0 related)
ActivePublic

Authored by Dispenser on Sep 7 2017, 6:22 PM.
Tokens
"Yellow Medal" token, awarded by Steinsplitter.
for dbname in $(mysql -BN -h metawiki.labsdb -e 'SELECT dbname FROM meta_p.wiki WHERE is_closed = 0;'); do
for test_url in $(mysql -Nh ${dbname}.labsdb ${dbname}_p <<< '
SELECT CONCAT(
"https://upload.wikimedia.org",
REGEXP_REPLACE(
REPLACE(REPLACE(url, "wikimedia", "wikipedia"), "www.mediawiki.org", "mediawiki.wikipedia.org"),
"[a-z]+://([a-z0-9]+)[.]([a-z0-9]+)[.]org",
"/\\2/\\1"
),
REGEXP_REPLACE(MD5(log_title), "((.).).*", "/\\2/\\1/"),
log_title
) AS test_url
FROM logging
LEFT JOIN meta_p.wiki ON dbname=REPLACE(DATABASE(), "_p", "")
LEFT JOIN image ON img_name=log_title
WHERE img_name IS NULL
AND log_namespace=6 /*File:*/
-- /* Exclude .png, .jpeg, .jpg, .gif, .svg */
-- AND log_title NOT REGEXP "[.][PpJjGgSs][NnPpIiVv][Ee]?[GgFf]$"
/* Include flac, mp3, oga, ogg, ogv, opus, webm, wav */
AND log_title REGEXP "[.][FfMmOoWw][Ll]?[AaEeGgPp][BbUu]?[AaCcGgMmSsVv3]$"
/* type_action */
AND log_type = "delete"
AND log_action = "delete"
AND log_timestamp < DATE_FORMAT(NOW() - INTERVAL 30 MINUTE, "%Y%m%d%H%i%s")
AND log_timestamp > DATE_FORMAT(NOW() - INTERVAL 24 HOUR, "%Y%m%d%H%i%s")
ORDER BY log_timestamp;
-- ' ); do
# Test 15 times to be safe (https://phabricator.wikimedia.org/Z567#11856)
for i in $(seq 1 15); do
curl --head "$test_url" 2> /dev/null | grep "200 OK" 1> /dev/null
if [[ $? -eq 0 ]]; then
echo "$test_url"
break
fi
done
done
done