Media files needing purging (WP0 related)
ActivePublic
Actions

Authored by Dispenser on Sep 7 2017, 6:22 PM.

Tags

Referenced Files

	F10170526: Media files needing purging (WP0 related)
	Oct 13 2017, 1:32 AM

	F9383540: Media files needing purging (WP0 related)
	Sep 7 2017, 10:58 PM

	F9379595: Media files needing purging (WP0 related)
	Sep 7 2017, 6:22 PM

Subscribers

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

Media files needing purging (WP0 related)ActivePublicActions