Mass Deleting Posts via phpmyadmin

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • ImageConstrux
    Senior Member
    • Feb 2002
    • 188

    Mass Deleting Posts via phpmyadmin

    I have an acquaintance/client who contacted me because they're in need of help cleaning up their site. Long story short they've done a terrible job of keeping up with moderation and now they have 426,000 posts of which about 415,000 are spam and awaiting moderation. They had 80,000+ spam user registrations that I was able to pare down via phpmyadmin down to the 2,500 or so real members, but 400K posts is a bit daunting a task to do piecemeal.

    And unfortunately it can't be pruned because due to the sheer volume of posts in need of moderation, vb AdminCP crashes every time you attempt removal.

    I'm looking to just delete every post that came after a specified date - what command could I run to affect that result?

    Thanks,

    Jeff

  • snakes1100
    Senior Member
    • Aug 2001
    • 1249

    #2
    You shouldnt delete anything via pma, not even users.

    You will need to modify the servers settings to perform the proper prune via the admincp, there are a few settings that need to be changed that might create issues.

    PhP
    memory_limit
    max_execution_time
    disable suhosin for the prune (as there are a few settings that need changed, easier to disable while you do this)

    MySQL
    wait_timeout

    Just to name a few, if you want to prune many at one time.
    Gentoo Geek

    Comment

    • Mark.B
      vBulletin Support
      • Feb 2004
      • 24286
      • 6.0.X

      #3
      As snakes1100 says, please don't delete anything directly in the database.
      That way lies madness, sadness and personal ruin.
      MARK.B
      vBulletin Support
      ------------
      My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
      My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

      Comment

      • ImageConstrux
        Senior Member
        • Feb 2002
        • 188

        #4
        You see how advising not to do something a certain way (or partial instructions) but not providing a means to DO the thing is of dubious value, right? Give me something to work with, Mark B. Absent that, mass deletion via phpmyadmin appears to be the best solution.

        Comment

        • Mark.B
          vBulletin Support
          • Feb 2004
          • 24286
          • 6.0.X

          #5
          Originally posted by ImageConstrux
          You see how advising not to do something a certain way (or partial instructions) but not providing a means to DO the thing is of dubious value, right? Give me something to work with, Mark B. Absent that, mass deletion via phpmyadmin appears to be the best solution.
          There is no fully supported way to do this, and if you go ahead and delete directly in the database then you are quite likely to run into problems. Saying "it's the best solution" isn't going to alter that.

          snakes1100 has provided some sound advice on how you might accomplish what you want without resorting to direct database deletions.

          Whichever method you choose, make sure you take a full database backup before doing anything - that way if the worst happens and you trash the database, you can just put it back again.
          MARK.B
          vBulletin Support
          ------------
          My Unofficial vBulletin 6.0.0 Demo: https://www.talknewsuk.com
          My Unofficial vBulletin Cloud Demo: https://www.adminammo.com

          Comment

          • Lats
            Senior Member
            • Mar 2002
            • 3671

            #6
            If you wish to go ahead...
            Code:
            DELETE FROM
             post
            WHERE
             dateline >= UNIX_TIMESTAMP('2008-10-20 00:00:00')
            Change the date accordingly
            Lats...

            Comment

            • BirdOPrey5
              Senior Member
              • Jul 2008
              • 9613
              • 5.6.3

              #7
              Originally posted by Lats
              If you wish to go ahead...
              Code:
              DELETE FROM
              post
              WHERE
              dateline >= UNIX_TIMESTAMP('2008-10-20 00:00:00')
              Change the date accordingly
              That would be a bad idea as it would leave thousands of orphaned threads in the thread table.

              There simply is no good way to delete manually.

              Perhaps look for some custom made script on vBulletin.org that can run in batches.

              Comment

              • Lats
                Senior Member
                • Mar 2002
                • 3671

                #8
                Good point, add this...
                Code:
                DELETE FROM
                 thread
                WHERE
                 dateline >= UNIX_TIMESTAMP('2008-10-20 00:00:00')
                Lats...

                Comment

                • BirdOPrey5
                  Senior Member
                  • Jul 2008
                  • 9613
                  • 5.6.3

                  #9
                  *IF* you go with he manual deletion which we do not support, to have any chance of working, after you've made the mysql deletions you will need to go to Admin CP-> Maintenance -> General Update Tools, and update forums, threads, users, remove orphan threads and posts, and finally rebuild the search index.

                  Comment

                  Related Topics

                  Collapse

                  • LyndonReid
                    How to clean up spam
                    by LyndonReid
                    I want to revive an old forum of mine. I have 80,000 posts in moderation. Is there away I can mass delete this posts and ban users but include some searches (so I delete delete legitimate posts). It...
                    Thu 19 Feb '15, 7:07pm
                  • WoodburyMan
                    Remove Moderated Topics
                    by WoodburyMan
                    Greetings!
                    I'm resurrecting an old vBull 4.2.x forum that was largest left alone for a few years. I upgraded to the latest 5.6.4 P1 and it's running so far so well. The issue is over a few years...
                    Wed 1 Sep '21, 12:00pm
                  Working...