how can I delete them all by a specific date

[Mike Sullivan]

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"?)

[na9]

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

[Mike Sullivan]

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.

[na9]

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?

[Mike Sullivan]

Even when you adjusted the part I mentioned up to, say, 1999?

[na9]

Hello,

I'm sorry , it did not work:

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

counters still cannot be updated.

[Mike Sullivan]

I'm basically stumped then. All I can suggest trying it taking this query:

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).

[na9]

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

[Mike Sullivan]

Try this query:

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;

[na9]

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

[Mike Sullivan]

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

Run this query:

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:

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:

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;

[na9]

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

[Mike Sullivan]

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.

[na9]

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

[The Realist]

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