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.

How to safley delete posts using SQL


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



Joined: 27 Feb 2018

Posts: 5



PostPosted: Tue Feb 27, 2018 10:14 pm 
Post subject: How to safley delete posts using SQL

Hi,

I've just taken up the challenge of moderating an old PhpBB version 2.0.22 which I know is very old now. In time we can upgrade it but that requires the hosting of the site to be resolved which is a bit undecided right now. There are 15 or so forums which all have a ton of messages with DELETED for the subject and the same for the author. I don't know what the story was here - I'm guessing someone ran some admin routine over the database to remove spam but instead of truly removing the posts we are left with all these stub messages with no replies just DELETED topic and no body and DELETED for the author.

In another screen I can usually click on the author name, view their posts and if it is spam then I can choose to delete their usename and all their posts but with these messages there is no link to view all messages by DELETED.

Due to the large number (I estimate between 50-75,000 of these posts) ideally need a quick way of deleting them all in one go. Perhaps by SQL - is there a safe way of doing that?

Thanks!
Wod
Back to top
lumpy burgertushie
Board Member



Joined: 19 Nov 2008

Posts: 216


flag
PostPosted: Wed Feb 28, 2018 12:53 am 
Post subject: Re: How to safley delete posts using SQL

the username shows as "deleted"?

what user id number is shown for the user named "deleted" if you can find that you can probably delete all posts by that user ID from the database in one go.

robert
Back to top
Vendethiel
Board Member



Joined: 26 Oct 2014

Posts: 60



PostPosted: Wed Feb 28, 2018 10:15 am 
Post subject: Re: How to safley delete posts using SQL

Make sure you're changing the last post id / first post id of phpbb_topics so it doesn't fuck up.
Your forums might also store that somewhere.

_________________
Developer on EzArena, the ADR premod.
Developer on Icy Phoenix, the phpBB hybrid cms.
Developer on IntegraMOD.
Back to top
wod
Board Member



Joined: 27 Feb 2018

Posts: 5



PostPosted: Wed Feb 28, 2018 11:14 am 
Post subject: Re: How to safley delete posts using SQL

lumpy burgertushie wrote:
the username shows as "deleted"?

what user id number is shown for the user named "deleted" if you can find that you can probably delete all posts by that user ID from the database in one go.

robert


There is no userid as the links at the bottom of the post 'my profile' etc are missing. I think someone may have deleted posts from the database but only partially and this DELETED stub user/message is the artefact. I do have the 'delete this' button for these posts but there are so many of them.

Are there any mass deletion tools that safely operate on the database?
Back to top
Jim_UK
Board Member



Joined: 19 Nov 2008

Posts: 541
Location: North West UK


flag
PostPosted: Wed Feb 28, 2018 2:01 pm 
Post subject: Re: How to safley delete posts using SQL

You should be able to find the username "deleted" in the database and the user ID will be there.
There was a mod on phpbbhacks.com that would delete all the posts of a user at the same time as you deleted their account but my experience with it was not good. I installed it on my PC and ran it on my test site and it crashed the PC.

Jim

_________________
The truth is out there.
Unfortunately they will not let you anywhere near it!
Back to top
wod
Board Member



Joined: 27 Feb 2018

Posts: 5



PostPosted: Thu Mar 01, 2018 10:02 am 
Post subject: Re: How to safley delete posts using SQL

Jim_UK wrote:
You should be able to find the username "deleted" in the database and the user ID will be there.
There was a mod on phpbbhacks.com that would delete all the posts of a user at the same time as you deleted their account but my experience with it was not good. I installed it on my PC and ran it on my test site and it crashed the PC.

Jim


I do have such functionality, but I'm not familiar enough with PhPBB to say if it's part of the core code or an add-on. I've only recently started moderating this forum and have no knowledge of the history of the site. On the 'My Profile' screen (the screen I get to when I click the link 'my profile' on a spammers posting), I see a drop-down with three options - delete: 1) user only, 2) user and his(her) postings and 3) User with all postings. I've been using option 3) extensively lately, however I've just run a careful check and it works fine - it does not leave a DELETED user/message behind.

At this stage I don't have access to the back-end so can't run the query. My original question was more of a theoretical one, like 'is this something people do normally/safely?' It appears that it isn't so we might have to revert to cleaning up the mess by hand using the moderator panel.
Back to top
Jim_UK
Board Member



Joined: 19 Nov 2008

Posts: 541
Location: North West UK


flag
PostPosted: Thu Mar 01, 2018 10:15 am 
Post subject: Re: How to safley delete posts using SQL

wod wrote:
I see a drop-down with three options - delete: 1) user only, 2) user and his(her) postings and 3) User with all postings. I've been using option 3) extensively lately, however I've just run a careful check and it works fine - it does not leave a DELETED user/message behind.


That is not a standard function of phpBB2. It is an add on Mod
Do you have ftp access to the server? If so Mysqldumper gives access to manage the database

The Starfoxtj Admin tool kit also gave the facility to delete a user and all their posts but I never used it.

Jim



mysqldumper.jpg
 Description:
 Filesize:  68.64 KB
 Viewed:  30 Time(s)

mysqldumper.jpg



_________________
The truth is out there.
Unfortunately they will not let you anywhere near it!
Back to top
Salvatos
Board Member



Joined: 19 Feb 2009

Posts: 415
Location: Québec


flag
PostPosted: Thu Mar 01, 2018 10:48 am 
Post subject: Re: How to safley delete posts using SQL

Looking at the delete_post function should tell us what operations need to be performed for a clean removal:
Code:
function delete_post($mode, &$post_data, &$message, &$meta, &$forum_id, &$topic_id, &$post_id, &$poll_id)
{
   global $board_config, $lang, $db, $phpbb_root_path, $phpEx;
   global $userdata, $user_ip;

   if ($mode != 'poll_delete')
   {
      include($phpbb_root_path . 'includes/functions_search.'.$phpEx);

      $sql = "DELETE FROM " . POSTS_TABLE . "
         WHERE post_id = $post_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting post', '', __LINE__, __FILE__, $sql);
      }

      $sql = "DELETE FROM " . POSTS_TEXT_TABLE . "
         WHERE post_id = $post_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting post', '', __LINE__, __FILE__, $sql);
      }

      if ($post_data['last_post'])
      {
         if ($post_data['first_post'])
         {
            $forum_update_sql .= ', forum_topics = forum_topics - 1';
            $sql = "DELETE FROM " . TOPICS_TABLE . "
               WHERE topic_id = $topic_id
                  OR topic_moved_id = $topic_id";
            if (!$db->sql_query($sql))
            {
               message_die(GENERAL_ERROR, 'Error in deleting post', '', __LINE__, __FILE__, $sql);
            }

            $sql = "DELETE FROM " . TOPICS_WATCH_TABLE . "
               WHERE topic_id = $topic_id";
            if (!$db->sql_query($sql))
            {
               message_die(GENERAL_ERROR, 'Error in deleting post', '', __LINE__, __FILE__, $sql);
            }
         }
      }

      remove_search_post($post_id);
   }

   if ($mode == 'poll_delete' || ($mode == 'delete' && $post_data['first_post'] && $post_data['last_post']) && $post_data['has_poll'] && $post_data['edit_poll'])
   {
      $sql = "DELETE FROM " . VOTE_DESC_TABLE . "
         WHERE topic_id = $topic_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting poll', '', __LINE__, __FILE__, $sql);
      }

      $sql = "DELETE FROM " . VOTE_RESULTS_TABLE . "
         WHERE vote_id = $poll_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting poll', '', __LINE__, __FILE__, $sql);
      }

      $sql = "DELETE FROM " . VOTE_USERS_TABLE . "
         WHERE vote_id = $poll_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting poll', '', __LINE__, __FILE__, $sql);
      }
   }

   if ($mode == 'delete' && $post_data['first_post'] && $post_data['last_post'])
   {
      $meta = '<meta http-equiv="refresh" content="3;url=' . append_sid("viewforum.$phpEx?" . POST_FORUM_URL . '=' . $forum_id) . '">';
      $message = $lang['Deleted'];
   }
   else
   {
      $meta = '<meta http-equiv="refresh" content="3;url=' . append_sid("viewtopic.$phpEx?" . POST_TOPIC_URL . '=' . $topic_id) . '">';
      $message = (($mode == 'poll_delete') ? $lang['Poll_delete'] : $lang['Deleted']) . '<br /><br />' . sprintf($lang['Click_return_topic'], '<a href="' . append_sid("viewtopic.$phpEx?" . POST_TOPIC_URL . "=$topic_id") . '">', '</a>');
   }

   $message .=  '<br /><br />' . sprintf($lang['Click_return_forum'], '<a href="' . append_sid("viewforum.$phpEx?" . POST_FORUM_URL . "=$forum_id") . '">', '</a>');

   return;
}

Namely, which tables to remove entries from, how to deal with polls, when a topic should be deleted in the process, etc. If you have a safe way to target the problematic posts and only them (e.g. topic == "DELETED"), it shouldn't be overly difficult to design a script that runs the deletion routine on all of them at once. Failing that, an SQL query to find all posts that match that subject in phpbb_posts_text, followed by a few queries to remove those post_ids/topic_ids from the appropriate tables, should be easy enough to run if you can get SQL access. According to your description, I don't think you would need to worry aboud the poll tables.
Back to top
wod
Board Member



Joined: 27 Feb 2018

Posts: 5



PostPosted: Thu Mar 01, 2018 11:57 am 
Post subject: Re: How to safley delete posts using SQL

Salvatos wrote:
Looking at the delete_post function should tell us what operations need to be performed for a clean removal:
Code:
function delete_post($mode, &$post_data, &$message, &$meta, &$forum_id, &$topic_id, &$post_id, &$poll_id)
{
   global $board_config, $lang, $db, $phpbb_root_path, $phpEx;
   global $userdata, $user_ip;

   if ($mode != 'poll_delete')
   {
      include($phpbb_root_path . 'includes/functions_search.'.$phpEx);

      $sql = "DELETE FROM " . POSTS_TABLE . "
         WHERE post_id = $post_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting post', '', __LINE__, __FILE__, $sql);
      }

      $sql = "DELETE FROM " . POSTS_TEXT_TABLE . "
         WHERE post_id = $post_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting post', '', __LINE__, __FILE__, $sql);
      }

      if ($post_data['last_post'])
      {
         if ($post_data['first_post'])
         {
            $forum_update_sql .= ', forum_topics = forum_topics - 1';
            $sql = "DELETE FROM " . TOPICS_TABLE . "
               WHERE topic_id = $topic_id
                  OR topic_moved_id = $topic_id";
            if (!$db->sql_query($sql))
            {
               message_die(GENERAL_ERROR, 'Error in deleting post', '', __LINE__, __FILE__, $sql);
            }

            $sql = "DELETE FROM " . TOPICS_WATCH_TABLE . "
               WHERE topic_id = $topic_id";
            if (!$db->sql_query($sql))
            {
               message_die(GENERAL_ERROR, 'Error in deleting post', '', __LINE__, __FILE__, $sql);
            }
         }
      }

      remove_search_post($post_id);
   }

   if ($mode == 'poll_delete' || ($mode == 'delete' && $post_data['first_post'] && $post_data['last_post']) && $post_data['has_poll'] && $post_data['edit_poll'])
   {
      $sql = "DELETE FROM " . VOTE_DESC_TABLE . "
         WHERE topic_id = $topic_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting poll', '', __LINE__, __FILE__, $sql);
      }

      $sql = "DELETE FROM " . VOTE_RESULTS_TABLE . "
         WHERE vote_id = $poll_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting poll', '', __LINE__, __FILE__, $sql);
      }

      $sql = "DELETE FROM " . VOTE_USERS_TABLE . "
         WHERE vote_id = $poll_id";
      if (!$db->sql_query($sql))
      {
         message_die(GENERAL_ERROR, 'Error in deleting poll', '', __LINE__, __FILE__, $sql);
      }
   }

   if ($mode == 'delete' && $post_data['first_post'] && $post_data['last_post'])
   {
      $meta = '<meta http-equiv="refresh" content="3;url=' . append_sid("viewforum.$phpEx?" . POST_FORUM_URL . '=' . $forum_id) . '">';
      $message = $lang['Deleted'];
   }
   else
   {
      $meta = '<meta http-equiv="refresh" content="3;url=' . append_sid("viewtopic.$phpEx?" . POST_TOPIC_URL . '=' . $topic_id) . '">';
      $message = (($mode == 'poll_delete') ? $lang['Poll_delete'] : $lang['Deleted']) . '<br /><br />' . sprintf($lang['Click_return_topic'], '<a href="' . append_sid("viewtopic.$phpEx?" . POST_TOPIC_URL . "=$topic_id") . '">', '</a>');
   }

   $message .=  '<br /><br />' . sprintf($lang['Click_return_forum'], '<a href="' . append_sid("viewforum.$phpEx?" . POST_FORUM_URL . "=$forum_id") . '">', '</a>');

   return;
}

Namely, which tables to remove entries from, how to deal with polls, when a topic should be deleted in the process, etc. If you have a safe way to target the problematic posts and only them (e.g. topic == "DELETED"), it shouldn't be overly difficult to design a script that runs the deletion routine on all of them at once. Failing that, an SQL query to find all posts that match that subject in phpbb_posts_text, followed by a few queries to remove those post_ids/topic_ids from the appropriate tables, should be easy enough to run if you can get SQL access. According to your description, I don't think you would need to worry aboud the poll tables.


I think the first approach would be safest, thanks! Maybe I can adopt this approach if I get access to the back end, otherwise it's hand deletion of these items for the time being. Thanks for all your helpful comments guys.
Back to top
Vendethiel
Board Member



Joined: 26 Oct 2014

Posts: 60



PostPosted: Thu Mar 01, 2018 1:11 pm 
Post subject: Re: How to safley delete posts using SQL

If you do, make sure to check your website's version of
Code:
delete_post
, it's probably been modded.
_________________
Developer on EzArena, the ADR premod.
Developer on Icy Phoenix, the phpBB hybrid cms.
Developer on IntegraMOD.
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
 
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.0194 seconds using 18 queries. (SQL 0.0027 Parse 0.0025 Other 0.0142)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo