Announcement

Collapse
No announcement yet.

how can I delete them all by a specific date

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

  • Mike Sullivan
    replied
    Trial and error is about the only way.

    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;
    Change the red number to, say, 1000. If you *don't* get the error, you can change the blue part to 1000, then set the red to 900 and rerun the query. Still no error? Set the blue to 900, and the red to 800. Repeat until you get the error.

    When you get the error, you should be able to close in your band of results until, you find the exact thread that's causing the problem (assuming there's only one). That is, the red number would be (for example) 456, and the blue would be 457 (the blue has to be greater than the red!). Then, 456 would be the troublesome thread, and we have to remove it.

    Although I do think this is some sort of MySQL bug. (Or is it a "feature"?)

    Leave a comment:


  • na9
    replied
    How can I know the thread that is causing the problem,there is hundreds of threads that I get when I use the query?

    Leave a comment:


  • Mike Sullivan
    replied
    I'd keep changing that number until you get the number of the thread that is causing the problem. Then we can just delete that thread.

    Leave a comment:


  • na9
    replied
    It worked when using 1999:
    minpost maxpost threadid dateline posts attachsum
    9143 10162 1999 981136327 4 0

    It worked with the number 1900 as well.
    But now what should I do?what could this help with?
    Last edited by na9; Sun 12 Aug '01, 12:32pm.

    Leave a comment:


  • Mike Sullivan
    replied
    Even when you adjusted the part I mentioned up to, say, 1999?

    Leave a comment:


  • na9
    replied
    Hello,

    I'm sorry , it did not work:

    MySQL said: Can't find record in 'post'

    counters still cannot be updated.

    Leave a comment:


  • Mike Sullivan
    replied
    I'm basically stumped then. All I can suggest trying it taking 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>=400 
    AND thread.threadid<2000 
    GROUP BY thread.threadid 
    ORDER BY threadid DESC;
    And adjusting the red part up specific increments until it doesn't happen (assuming it will stop).

    Leave a comment:


  • na9
    replied
    MySQL said: Can't find record in 'post'

    Leave a comment:


  • Mike Sullivan
    replied
    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;

    Leave a comment:


  • na9
    replied
    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.

    Leave a comment:


  • Mike Sullivan
    replied
    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;

    Leave a comment:


  • na9
    replied
    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

    Leave a comment:


  • Mike Sullivan
    replied
    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.

    Leave a comment:


  • na9
    replied
    I did, but the repair file did not find any problems.

    Leave a comment:


  • The Realist
    replied
    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

    Leave a comment:

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