vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=251)
-   -   sql query to remove all posts and replies (https://vborg.vbsupport.ru/showthread.php?t=321243)

chikuru 12-30-2015 12:11 PM
sql query to remove all posts and replies
 
sql query to remove all posts and replies of a member?
Its not possible via web interface because it is timing out.
Number of posts to be removed are 100k+

Dragonsys 12-30-2015 01:03 PM
Code:
DELETE FROM " . TABLE_PREFIX ."posts WHERE userid = YOUR_USER
If running from ACP,just change YOUR_USER to the userid you wish to remove.
If running from phpMyAdmin or such, change " . TABLE_PREFIX ." to the actual table prefix

chikuru 01-05-2016 02:04 AM
Which should I rebuild after executing this?
The threads are still showing in forumdisplay and says invalid thread specified when clicked

Dragonsys 01-05-2016 02:06 AM
Post counts and search index

chikuru 01-12-2016 12:12 PM
Quote:
Originally Posted by Dragonsys (Post 2562006)
Post counts and search index
I rebuilt both Post Counts and Search Index after running that query but the deleted posts still showing in forumhome and forumdisplay.. :confused:

Dave 01-12-2016 12:30 PM
Have you tried "Rebuild Thread Information" and "Rebuild Forum Information"?

chikuru 01-12-2016 12:51 PM
Quote:
Originally Posted by Dave (Post 2562534)
Have you tried "Rebuild Thread Information" and "Rebuild Forum Information"?
Tried that but didnt help..
You have other suggestion Dave?

Dave 01-12-2016 12:59 PM
Wait so you executed the following query right?
HTML Code:
DELETE FROM posts WHERE userid = YOUR_USER
Since in vBulletin threads have a one to many relation to posts, it means that if the user started a thread, it will cause this problem because there's no post bound to it anymore.

You also have to execute a query such as:
HTML Code:
DELETE FROM thread WHERE postuserid = USERID_OF_USER

chikuru 01-12-2016 01:29 PM
Quote:
Originally Posted by Dave (Post 2562537)
Wait so you executed the following query right?
HTML Code:
DELETE FROM posts WHERE userid = YOUR_USER
Since in vBulletin threads have a one to many relation to posts, it means that if the user started a thread, it will cause this problem because there's no post bound to it anymore.

You also have to execute a query such as:
HTML Code:
DELETE FROM thread WHERE postuserid = USERID_OF_USER
the 2nd query did it. Then rebuilt threads and forums. It worked!
Thanks for your great help Dave! As well as Dragonsys for always helping the vb community :up:

Dragonsys 01-12-2016 01:42 PM
Quote:
Originally Posted by Dave (Post 2562537)
Wait so you executed the following query right?
HTML Code:
DELETE FROM posts WHERE userid = YOUR_USER
Since in vBulletin threads have a one to many relation to posts, it means that if the user started a thread, it will cause this problem because there's no post bound to it anymore.

You also have to execute a query such as:
HTML Code:
DELETE FROM thread WHERE postuserid = USERID_OF_USER
Thanks Dave, I forgot that one.

squidsk 01-12-2016 02:05 PM
And since the thread likely had posts not by the user you're removing deleting posts for you now have orphaned posts (i.e. posts not attached to an existing thread).

To delete them run the following:

Code:
DELETE FROM post WHERE NOT threadid IN (SELECT threadid FROM thread)
Note: this will remove all posts that are not associated with a thread, not just the ones related to threads you have just deleted.

chikuru 01-12-2016 02:09 PM
Great! Thanks for the addition squidsk!
Im sure this thread will also help other forum owners who deletes thousands of spams in one go.


All times are GMT. The time now is 04:57 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01861 seconds
  • Memory Usage 1,738KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (2)bbcode_code_printable
  • (6)bbcode_html_printable
  • (4)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (12)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete