Identify mass uploads of the same file in WordPress Blog

Been hit by the WPML – WP 4.0 bug (Mostly caused by Media translation) and stuck with MANY corrupted uploads in your database? And having massive amounts of duplicate images?

Note: this assumes you have the wp_ DB table prefix
This will identify file uploads with the same filename, having over 300 uploads in the same second

Warning: Make a database backup first!

Query to identify the uploads:

Code:
SELECT * FROM `wp_posts` WHERE
ID IN (SELECT ID FROM
(SELECT post_title, post_modified, count(post_title) AS ttlcount FROM `wp_posts` 
WHERE `post_type` = 'attachment' 
AND post_name LIKE('%-2-%') group by post_title, post_modified HAVING ttlcount > 300)
AS SUB1
)
AND
post_modified IN (SELECT post_modified FROM
(SELECT post_title, post_modified, count(post_title) AS ttlcount FROM `wp_posts` 
WHERE `post_type` = 'attachment' 
AND post_name LIKE('%-2-%') group by post_title, post_modified HAVING ttlcount > 300)
AS SUB2
)
AND
`post_type` = 'attachment'
AND
post_name LIKE('%-2-%')

Query to mark the uploads as deleted
Note: They will not be auto-deleted as that also needs an insert into the postmeta table. They are kept and there for you to restore.

Code:
UPDATE wp_posts SET post_status = 'trash', post_type = 'attachment_trash'
WHERE
ID IN (SELECT ID FROM
(SELECT post_title, post_modified, count(post_title) AS ttlcount FROM `wp_posts` 
WHERE `post_type` = 'attachment' 
AND post_name LIKE('%-2-%') group by post_title, post_modified HAVING ttlcount > 300)
AS SUB1
)
AND
post_modified IN (SELECT post_modified FROM
(SELECT post_title, post_modified, count(post_title) AS ttlcount FROM `wp_posts` 
WHERE `post_type` = 'attachment' 
AND post_name LIKE('%-2-%') group by post_title, post_modified HAVING ttlcount > 300)
AS SUB2
)
AND
`post_type` = 'attachment'
AND
post_name LIKE('%-2-%')

Suggestie? Vraag of opmerking? Laat het ons weten!

Plaats hier je bericht

Reacties (0)