query to delete attachments bigger than 100 kb.

[Aaow AnD wHiTe]

That's it :)

Does anyone how can we run a query that can delete attachments bigger than 100 kb?

We don't allow attachments bigger than 100kb, but we used to. We really need to get rid of those big attachments.

Thx in advance :)

[Admin]

DELETE FROM attachment WHERE LENGTH(filedata)>102400;

[Aaow AnD wHiTe]

Thx Chen :) However, it seems like it hasn't reduced our db's size :-?

[Aaow AnD wHiTe]

Ok, it shows the reduced size. Thx ^^

[Aaow AnD wHiTe]

Just bumping this old thread to ask if anyone knows what query can we use to delete attachments from a specific sub-forum ;)

We've used the Admin Functions hack by tubedogg but for some reason it doesn't reduce the total DB size in phpmyadmin or Freddie's Admin Quick Stats Hack :-?

Anyone?

[jaxo]

Would this work with attachments stored on the file system ?

[kh99]

This could be the record for bumping an old thread. What is it you're trying to do? A database query isn't going to remove files from the file system of course.

[jaxo]

hahaha, Just saw the date of the last post lol

I`m running vbulletin 4.2.2, I have over 50Gb of attachments in the file system, A lot of them are no longer relevant and some are pretty big in size, .. I`m would like to delete all attachments over 50Mb but doing it manually in the control panel would take forever as there is 15000+

Jack

[kh99]

You'd need a custom script I think, if you wanted to remove them from the database and from the file system. If you ran the above I'm not sure what would happen. You definitely wouldn't reclaim the file space unless you also removed the corresponding files form the filesystem yourself. Also, it wouldn't adjust everything that should be adjusted in the database, but that may or may not be a big deal, I'm not sure.

[Replicant]

If you run the above query it will do nothing because in VB4.2.2, there is no filedata in the attachment table. KH99 is correct though, you will need a script that will query the filedata table for file sizes, filedataid, and userid. Then it will have to traverse the file system using the attachment folder hierarchy and delete the files and thumbs, then go back and "clean" the attachment and filedata tables as well as scrub the posts that reference the attachments if you want it to be done correctly. There may be a couple more tables to be adjusted as well, but I haven't researched it that much.