Author |
Message |
specialgreen Board Member
Joined: 03 Jun 2011
Posts: 2
|
Posted: 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
|
|
Back to top |
|
|
specialgreen Board Member
Joined: 03 Jun 2011
Posts: 2
|
Posted: 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 . 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
|
Posted: 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
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
|
Posted: 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
|
Posted: 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
|
Posted: 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.
Description: |
|
Download |
Filename: |
Prune_User_Posts_490.zip |
Filesize: |
19.92 KB |
Downloaded: |
1138 Time(s) |
|
|
Back to top |
|
|
|