SQL querry to delete an entire category and threads inside

[geovadim]

Hello,

I need to delete many threads. I tried using admincp > forum manager > delete category. The server goes in timeout.

I need a querry to delete an entire category and threads inside as well.

I tried: DELETE FROM wp_posts WHERE post_category = 53;

I get the error desc: Table 'onlinesp_vb4.wp_posts' doesn't exist

I don't know what table prefix I have. I don't know to have any special table prefix.

May I get some help?

[kh99]

You can look in includes/config.php to see what your TABLE_PREFIX is. It's possible that you don't have any prefix, in which case the table name would just be "post".

While it's possible to delete a forum using SQL, I wouldn't recommend it because there's lots of things that need to be done to keep the database "clean". If it times out when trying to delete the category from the forum manager, maybe you should try going to the forum and using the "Delete threads" feature. Use "Select All" under the "Inline Mod" menu, then "Delete Threads" under the moderation tools menu at the bottom (and select "physically remove"). Then repeat that for each page of threads.

[geovadim]

Thank you for your answer. I tried to delete the threads from the inline moderation but I get a timeout. For three threads the database stalls for 1 minute almost. I need to delete 60.000 threads or so.

This is the error:
>Request Timeout
This request takes too long to process, it is timed out by the server. If it should not be timed out, please contact administrator of this web site to increase 'Connection Timeout'.

I have 2 databases. From the first one I must delete all the posts before the 27th on November 2012. After that I must merge this database with a second database which has posts and threads begging 2002 until the 27th of November 2012.

[kh99]

Hmm...if I had to guess I'd say it's the updating of the search index that's causing the timeout because that's done before any actual deletions. But I don't know of a way around it. I suppose it might be possible to delete your search index, delete the threads, then rebuild it (although rebuilding it might then time out). Do you have any way to increase the timeout? (Maybe talk to you host and tell them you just need it temporarily).

Hopefully someone else will have a suggestion.

[geovadim]

The host said that he gave me 10 minutes timeout, but when deleting threads it time out after 1 minute or so.

Normally I have to remain with around 100-200 threads. Thats all. I have to delete the rest.

Where can I delete the search index? And how can I rebuild it after? In the admincp?

[kh99]

I should start out by saying that I'm not sure about any of this, so you'd be trying it at your own risk and you should have a database backup if you don't already.

Anyway, in the admincp under Maintenance > Update Counters, in the Rebuild Search Index section, there's a link to remove the indexes. Of course after you do that no one will be able to search until you rebuild them (assuming it's a live site you're working on - if not it of course it won't matter).

[geovadim]

Thank you. I deleted the search index and I will try to delete again.

--------------- Added [DATE]1370296579[/DATE] at [TIME]1370296579[/TIME] ---------------

It seems that prune threads works. Shall I use prune? It can result a clean database?

[kh99]

Originally Posted by geovadim

It seems that prune threads works. Shall I use prune? It can result a clean database?

That should be OK. I guess I should have mentioned that, but I figured it would have the same problem.

Just out of curiosity, did you have the same problem even after deleting the indexes?

[geovadim]

After deleting indexes for some categories it worked to delete. It was a very helpful thing, thank you again.

But for some I get a database error, I don't know why. This is why now I try to prune.

In Prune mode it seems it progresses. It shows a page with points that advance, like something good happen.

--------------- Added [DATE]1370300296[/DATE] at [TIME]1370300296[/TIME] ---------------

When I try to Prune some forums I get the following database error. Any ideas why?

Database error in vBulletin 3.7.4:

Invalid SQL:

SELECT *
FROM thread AS thread
LEFT JOIN forum AS forum ON (forum.forumid = thread.forumid)
WHERE 1=1 AND thread.lastpost <= 1283899970;

MySQL Error : Lost connection to MySQL server during query
Error Number : 2013
Request Date : Tuesday, June 4th 2013 @ 02:52:50 AM
Error Date : Tuesday, June 4th 2013 @ 02:52:52 AM
Script : thread.php?do=dothreadsall
Referrer : hread.php?do=dothreads
IP Address :
MySQL Version :

[kh99]

I'm not sure. I think "lost connection to mysql server" means that the server had some error and just gave up. I think it could have been that the results were too big or something along those lines.