how can I delete them all by a specific date

[na9]

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

[Mike Sullivan]

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!

[na9]

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

[Mike Sullivan]

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?

[na9]

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

[Mike Sullivan]

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?

[na9]

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.

[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

[na9]

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

[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]

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]

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]

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]

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]

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