Identify mass uploads of the same file in WordPress

By.

min read

My profile

Share this:

Been hit by the WPML – WP 4.0 bug and stuck with MANY corrupted uploads in your database?

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

[b:5ce500961a]Warning: Make a database backup first![/b:5ce500961a]

[b:5ce500961a]Query to identify the uploads:
[/b:5ce500961a]
[code:1:5ce500961a]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-%’)[/code:1:5ce500961a]

[b:5ce500961a]Query to mark the uploads as deleted[/b:5ce500961a]
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:1:5ce500961a]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-%’)[/code:1:5ce500961a]

Share this:

Leave a Reply

Your email address will not be published. Required fields are marked *