phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

deleting 20k spam posts by one user


 
Search this topic... | Search General Support... | Search Box
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Author Message
specialgreen
Board Member



Joined: 03 Jun 2011

Posts: 2



PostPosted: Fri Jun 03, 2011 12:58 am 
Post subject: deleting 20k spam posts by one user

An account was created which posted about 20,000 spam posts. I'd like to delete them. In the newer phpbb versions, there is a feature to delete an account with all its posts, but that doesn't appear to be an option with phpbb2?
Back to top
dogs and things
Board Member



Joined: 18 Nov 2008

Posts: 628
Location: Spain


flag
PostPosted: Fri Jun 03, 2011 1:34 am 
Post subject: Re: deleting 20k spam posts by one user

Maybe you can get some ideas from this topic:
http://www.phpbb2refugees.com/viewtopic.php?t=553

_________________
phpBB2 will never die, I hope!
Back to top
specialgreen
Board Member



Joined: 03 Jun 2011

Posts: 2



PostPosted: Sat Jun 04, 2011 11:34 am 
Post subject: Re: deleting 20k spam posts by one user

I had some issues where the size of the DB meant that I couldn't back it up without HTTP timeouts, either using the Admin interface or phpMyAdmin's Export icon_sad.gif . I ended-up whacking the posts using phpMyAdmin's SQL interface:

Code:

DELETE FROM phpbb_posts_text WHERE post_id = ANY (SELECT post_id FROM phpbb_posts WHERE poster_id = '1462');
DELETE FROM phpbb_search_wordmatch WHERE post_id = ANY (SELECT post_id FROM phpbb_posts WHERE poster_id = '1462');
DELETE FROM phpbb_posts WHERE poster_id = '1462';


"Deleted rows: 3928282 (Query took 273.6839 sec)" on the phpbb_search_wordmatch. Now my DB is small enough to back-up again. So-far, the only side-effect is that the page counter is off.
Back to top
dogs and things
Board Member



Joined: 18 Nov 2008

Posts: 628
Location: Spain


flag
PostPosted: Sat Jun 04, 2011 2:16 pm 
Post subject: Re: deleting 20k spam posts by one user

You should be more carefull with doing things directly in the database. If you has screwed up things badly you would not have been able to fix them, due to the lack of a backup of the DB from before the screwup.

To try and fix your page counter problem you can copy this code:
Code:
<?php

## phpBB Sync All
##
## Author: uncle.f < soft at purple-yonder.com >
##
## This script performs the compete sync of all posts/topic/forums statistics
## including the approval data.
##
## Usage: copy this script to the 'admin' directory of your phpBB installation;
## you should get a new 'Sync All' lnk in the admin panel ('Forums' section).
## Simply click on the 'Sync All' link to synchronize everything.
## It would make sense to do that when there is low user activity on your forum,
## especially if it contains a lot of posts.

define('IN_PHPBB', 1);

if( !empty($setmodules) )
{
   $file = basename(__FILE__);
   $module['Forums']['Sync All'] = $file;
   return;
}

$phpbb_root_path = "./../";
require($phpbb_root_path . 'extension.inc');
require('./pagestart.' . $phpEx);
include($phpbb_root_path . 'includes/functions_admin.'.$phpEx);

// sync all topics and forums first
sync('all topics');
sync('all forums');

$end_msg = 'Topics and Forums sync is complete!';

// sync users' # of posts counters (only for MySQL 4.1+)
if (substr(SQL_LAYER,0,5) == 'mysql')
{
   $sql = "SHOW VARIABLES LIKE 'version'";
   if ( !($result = $db->sql_query($sql)) )
   {
      message_die(GENERAL_ERROR, 'Error gettting MySQL server information!', '', __LINE__, __FILE__, $sql);
   }

   if ( $total = $db->sql_fetchrow($result))
   {
      $db->sql_freeresult($result);

      if (substr($total['Value'],0,3) == '4.1' || substr($total['Value'],0,1) > 4)
      {
         $sql = "UPDATE " . USERS_TABLE . " u SET u.user_posts =
                 (SELECT COUNT(p.poster_id)
                  FROM " . POSTS_TABLE . " p
                  WHERE u.user_id = p.poster_id
                  GROUP BY p.poster_id)";

         if ( !$db->sql_query($sql) )
         {
            message_die(GENERAL_ERROR, 'Error updating user statistics!', '', __LINE__, __FILE__, $sql);
         }

         $end_msg = 'Topics, Forums and user posts counters sync is complete!';
      }
   }
}

message_die(GENERAL_MESSAGE, $end_msg);

?>

Save it as admin_sync_all.php and ftp it to your board's admin folder.

You now see under the administer forums section a new link, sync_all, click it and wait while the script performs the complete sync of all posts/topic/forums statistics, when it ends you'll see a confirmation message.

This files comes as a bonus with the Approval MOD and works very nicely for me, I donīt know if it will work for you.

See if it fixes your problem.

_________________
phpBB2 will never die, I hope!
Back to top
lumpy burgertushie
Board Member



Joined: 18 Nov 2008

Posts: 266


flag
PostPosted: Sat Jun 04, 2011 6:51 pm 
Post subject: Re: deleting 20k spam posts by one user

actually when making a db backup just don't include the three searc tables and your backup will be reduced by almost 2/3.

if you ever need the backup , you can rebuild the search tables with the rebuild search MOD.

robert
Back to top
Holger
Board Member



Joined: 19 Jan 2009

Posts: 509
Location: Hanover


flag
PostPosted: Tue Jun 07, 2011 3:08 am 
Post subject: Re: deleting 20k spam posts by one user

I am using the mysqldumper for backups, never had a problem with timeouts or such.
_________________
Love your data! Back it up!
Back to top
Jim_UK
Board Member



Joined: 19 Nov 2008

Posts: 656
Location: North West UK


flag
PostPosted: Thu Jun 09, 2011 4:38 pm 
Post subject: Re: deleting 20k spam posts by one user

specialgreen wrote:
An account was created which posted about 20,000 spam posts. I'd like to delete them. In the newer phpbb versions, there is a feature to delete an account with all its posts, but that doesn't appear to be an option with phpbb2?


There is a mod to delete a user together with all the posts they have made. Pretty sure I have seen it on phpbbhacks. Also fairly sure that I have the mod.

Jim

Edit
Found and attached



admin_delete_user_and_posts.zip
 Description:

Download
 Filename:  admin_delete_user_and_posts.zip
 Filesize:  19.09 KB
 Downloaded:  2411 Time(s)

Back to top
lumpy burgertushie
Board Member



Joined: 18 Nov 2008

Posts: 266


flag
PostPosted: Fri Jun 10, 2011 1:09 pm 
Post subject: Re: deleting 20k spam posts by one user

I have backed up/exported many databases in the hundreds of MB size on many different servers over the years and have never ever had phpmyadmin time out. Now, importing is another thing. The default php setting of 2MB applies here. That is why you will often have to use something like bigdump to do the import/restore.


robert
Back to top
Citrix
Board Member



Joined: 19 Feb 2012

Posts: 42



PostPosted: Wed Feb 22, 2012 3:24 pm 
Post subject: Re: deleting 20k spam posts by one user

there is a Mod to delete all messages and topics from one user at once.
you add a button to his profile page, you click on it and all his posts are gone in a second. No need to try to delete them one at a time.
This is good to clean your site from spammers... It will not work obviously is the spammer is not a registered member.
Back to top
vlad77
Board Member



Joined: 31 May 2015

Posts: 122


flag
PostPosted: Tue May 08, 2018 3:49 pm 
Post subject: Re: deleting 20k spam posts by one user

##############################################################
## MOD Title: Prune User Posts
## MOD Author: R45 < phpbb at rasadam dot com > (Adam Alkins) http://www.rasadam.com
## MOD Description: This is an admin module for your Administration Panel. It is a very
## Advanced version of the pruning tool available in phpBB. You can prune
## forums based on users (all or individuals), groups, IPs (ranges, specific,
## banned), Guests and more.



Prune_User_Posts_490.zip
 Description:

Download
 Filename:  Prune_User_Posts_490.zip
 Filesize:  19.92 KB
 Downloaded:  1138 Time(s)

Back to top
Display posts from previous:   
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 4 Hours
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0735 seconds using 18 queries. (SQL 0.0099 Parse 0.0106 Other 0.0531)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo