Announcement

Collapse
No announcement yet.

how can I delete them all by a specific date

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • how can I delete them all by a specific date

    Hi,
    I really have a serious problem with my data base because I had some date problems in the past, and all the problem are in all threads and posts that was posted on a specific date.
    The problem is how can delete all threads and posts on a specific date.
    Can any when help me please?
    Thank you very much

  • #2
    What's your server's timezone? (what you have the timezone set to in the CP) And what's the span of dates/times that have the problem? (preferably down to the second -- I need to come up with a Unix timestamp)

    Also, can you enter your license info into your profile? See link in sig for more info. Thanks!

    Comment


    • #3
      Thanks for the fast reply,
      Sorry I don't no what's my servers time zone exactly because my server is not in the same area where I am, the server is in Bloomsburg, PA USA
      My time zone that has been set in my cp is:
      GMT -4:00 hours Atlantic Time
      which gives my board visitors GMT 0:00

      My problem is with all threads and posts that has been posted on 15-06-2001 and on 16-06-2001 because the time and date on the server stopped that day.

      992668322 is a number for a thread that was posted on 16-06-2001

      and the number :992563812 is for a reply that was posted on 15-06-2001

      the board is working fine now but the problem is that I can not update my search counters ,it keeps saying that there is an error in my data base and stops on threads that was posted on the same dates I have mentioned.
      My members are more than 9000 and my total posts are more than 200.000 posts
      -------------------------
      And by the way I have entered my license info into my profile.


      Thanks

      Comment


      • #4
        If the only reason you want to delete them is because you can't update counters, it'd probably be easier to fix the counter issue. So what's the database error you're receiving?

        Comment


        • #5
          Hello,
          O.K
          Now I have another new problem:it's updating the threads counts and here is the error I get:
          ****************************************************
          Database error in vBulletin Control Panel: Invalid SQL: SELECT MIN(post.postid) AS minpost, MAX(post.postid) AS maxpost, thread.threadid, MAX(post.dateline) AS dateline,
          (COUNT(*)-1) AS posts,
          SUM(attachment.visible) AS attachsum
          FROM post,thread
          LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid
          WHERE thread.threadid=post.threadid
          AND thread.threadid>=0
          AND thread.threadid<2000
          GROUP BY thread.threadid
          ORDER BY threadid DESC
          mysql error: Can't find record in 'post'
          mysql error number: 1032
          Date: Friday 10th of August 2001 07:55:29 PM
          Script: /vb/admin/misc.php
          *************************************************
          And here is the error when updating the search index:
          *************************************************


          Database error in vBulletin Control Panel: Invalid SQL: SELECT DISTINCT thread.threadid,post.postid FROM thread,post WHERE thread.threadid=post.threadid AND thread.threadid>=300 AND thread.threadid<400 AND thread.visible=1 ORDER BY thread.threadid
          mysql error: Can't find record in 'post'
          mysql error number: 1032
          Date: Friday 10th of August 2001 07:57:18 PM
          Script: /vb/admin/misc.php?action=buildsearchindex&startat=300&perpage=100&autoredirect=1&totalthreads=274&d oprocess=0

          Comment


          • #6
            Copy repair.php from the extras/ directory in your zip to your main vB directory, run it, and follow the on screen instructions to do a repair. Does that help?

            Comment


            • #7
              Hello,
              I don't have any problem with the data base it self,the problem is date field in the post table that has the problem because it's mixed up between post table and thread table on the dates that I have mentioned.
              What happened exactly on the date 16-06-2001 was that users wer posting normaly and latly on the same day my servers time have stoped and useres kept posting on that date untill the server time was reset but was one day late on 15 -06-2001 so after that the new posts became before the original poster in the thread it self then I new that the date was one day back so I changed the date to the correct dateon the server to 16-06-2001.
              when updating my search index it stopps on threads that was posted on that day and I kept deleting them till every thing was ok but after upgrading my board from 2.0.1 to 2.0.3 I no longer can update my threads count or the search index.
              Last edited by na9; Sat 11 Aug '01, 5:10am.

              Comment


              • #8
                repair.php?check=1&iterations=4&forum=vbdatabase

                Regarding the above link, change the end bit vbdatabase to the name of your forum, if its called forum then:

                repair.php?check=1&iterations=4&forum=forum

                Comment


                • #9
                  I did, but the repair file did not find any problems.

                  Comment


                  • #10
                    Frankly, I've never seen the error message you're getting. I've seen time screwups before and none created that error.

                    But...

                    DELETE FROM thread WHERE dateline>992563812 AND dateline<992668322;

                    DELETE FROM post WHERE dateline>992563812 AND dateline<992668322;

                    As always, backup before running any queries that change/remove data.

                    Comment


                    • #11
                      Hello,
                      Thanks for the fast reply,
                      I have deleted all threads and posts that was posted on 15-06-2001 and 16-06-2001 but I still can not update threads counters and the search index ?

                      our crew gave up and we have no solution for this problem at all.

                      Frankly, I've never seen the error message you're getting. I've seen time screwups before and none created that error
                      Maybee you never seen this problem before because not to many forums did reach the number of posts that our board reached which is till this moment:207,498 posts.

                      any other solutions?
                      Thank you very much

                      Comment


                      • #12
                        This is going to return a lot of results, so be prepared.

                        Run this query:

                        Code:
                        SELECT MIN(post.postid) AS minpost, MAX(post.postid) AS maxpost, thread.threadid, MAX(post.dateline) AS dateline, 
                        (COUNT(*)-1) AS posts, 
                        SUM(attachment.visible) AS attachsum 
                        FROM post,thread 
                        LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid 
                        WHERE thread.threadid=post.threadid 
                        AND thread.threadid>=0 
                        AND thread.threadid<2000 
                        GROUP BY thread.threadid 
                        ORDER BY threadid DESC;
                        Does it give an error?

                        How about this one:

                        Code:
                        SELECT MIN(post.postid) AS minpost, MAX(post.postid) AS maxpost, thread.threadid, MAX(post.dateline) AS dateline, 
                        (COUNT(*)-1) AS posts, 
                        SUM(attachment.visible) AS attachsum 
                        FROM post,thread 
                        LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid 
                        WHERE thread.threadid=post.threadid 
                        AND thread.threadid>=300 
                        AND thread.threadid<2000 
                        GROUP BY thread.threadid 
                        ORDER BY threadid DESC;
                        And this one:

                        Code:
                        SELECT MIN(post.postid) AS minpost, MAX(post.postid) AS maxpost, thread.threadid, MAX(post.dateline) AS dateline, 
                        (COUNT(*)-1) AS posts, 
                        SUM(attachment.visible) AS attachsum 
                        FROM post,thread 
                        LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid 
                        WHERE thread.threadid=post.threadid 
                        AND thread.threadid>=400 
                        AND thread.threadid<2000 
                        GROUP BY thread.threadid 
                        ORDER BY threadid DESC;

                        Comment


                        • #13
                          I was so prepared
                          But all I got was:
                          MySQL said: Can't find record in 'post'

                          for all queries
                          by the way I have deleted all attachments and polls that was on the 15 and the 16-06-2001 .

                          any other solutions?
                          Thanks
                          Last edited by na9; Sat 11 Aug '01, 2:22pm.

                          Comment


                          • #14
                            Try this query:

                            Code:
                            SELECT MIN(post.postid) AS minpost, MAX(post.postid) AS maxpost, thread.threadid, MAX(post.dateline) AS dateline, 
                            (COUNT(*)-1) AS posts, 
                            SUM(attachment.visible) AS attachsum 
                            FROM post,thread 
                            LEFT JOIN attachment ON attachment.attachmentid=post.attachmentid 
                            WHERE post.threadid=thread.threadid
                            AND thread.threadid>=400 
                            AND thread.threadid<2000 
                            GROUP BY thread.threadid 
                            ORDER BY threadid DESC;

                            Comment


                            • #15
                              MySQL said: Can't find record in 'post'

                              Comment

                              widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
                              Working...
                              X