Recently I was helping one of my friends in cleaning up his VBulletin 4 forum. VBulletin 4 has introduced Blog feature but the admin control panel doesn’t have extensive features to use it.
To my surprise, there are no mass blogs delete option in VB4, they might come up in the future.
My friend forum was spammed by almost 100K spam blog posts, so there was no way he could have done it through the admin control panel. But ultimately all the data is present in the MySQL tables used by VBulletin software. So I decided to dig around to do it from the database side.
I found first solution at VBulletin forum to delete entries from BLOG table.
The solution provided in the VB forum is not complete and just deletes the entry from the first table, there are so many other tables also involved. Also, my friend forum had some good blog posts also that he wanted to keep. So I looked into his database using phpMyAdmin and after checking different tables data, I was able to write queries to delete spam blog posts.
Here are the queries I used:
Copy-- deleting blog entries in VBulletin 4 Blog delete from blog where blogid > 18; delete from blog_text where blogid > 18; delete from blog_subscribeentry where blogid > 18; delete from blog_user where bloguserid not in (6,14); delete from blog_userstats where userid > 1000;
Let me explain why do we need to delete entries from all these tables.
BLOG: This is the primary table which contains blog basic details, each row corresponds to a blog entry. You can write a query based on username, blog id etc.
BLOG_TEXT: This is the table contain blog data, it is necessary to remove spam data from here also to free space. You should use the same condition to delete entries from this table as from BLOG table.
BLOG_SUBSCRIBEENTRY: This table contains the subscribers’ entry for each blog, so we should delete data from here also to free space.
BLOG_USER, BLOG_USERSTATS: These tables contains the users’ information that actually uses blog feature. If you are deleting any specific user then you should delete from this table too.
It took almost 2 hours to run these queries with some other modifications and I deleted almost 90K spam blogs and freed over 1 GB of DB space. His Webmasters Forum is running fine now.
- Make sure to have the tables backup before deleting the data.
- Make sure to run “Update Counters” after mass delete
- Make sure to ban and delete the spam users
- If you are on the dedicated server, I would suggest running these commands through the terminal, phpMyAdmin might hang if you are executing a query that takes more than 15 mins time.
- After all the deletes, make sure to run “flush tables” command to release the lock and then only update counters.
NOTE: I am not an expert in VBulletin software, so make sure you understand what you are doing. Also if I have missed any other tables, please let me know.