Database problems!

[doc_sameer]

Hi.. i installed my board a week back & since then, my forum had 135 members, 800+ threads & around 6000 posts..

but since yesterday, the members complained that they could see a single post by a user in all threads & there were some log in problems..

I immediately downloaded a database backup which is 22 MB in size..

But today, when i logged in, it shows 129 users & only around 5000 posts..

Also, when i click in new posts, it just takes me to the search page with empty fields..

I tried optimising & repairing tables, but it doesnt help..

Plz advise.. its urgent

[Colin F]

Did you do anything before this started?

Also, do you know of any specific posts, threads or members that are missing?

[doc_sameer]

No.. nothing... i had just changed the permissions of registered users that they cant delete their own posts..

I am just informed by our host that they are changing server.. can this happen coz of it?

& even if they are.. how can one post show in all the threads? & how can clicking on "new posts" lead to search page?

btw, i think the users no.129-135 are missing & threads after no. t=854 & posts after 5627..

Also, theres an error i got through forum on the email id:

 Database error in vBulletin 3.6.8:

Invalid SQL:

                        SELECT MAX(dateline) AS dateline
                        FROM post AS post
                        WHERE threadid = 263
                                AND dateline < 
                                AND visible = 1;

MySQL Error  : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND visible = 1' at line 5
Error Number : 1064
Date         : Monday, December 17th 2007 @ 08:27:18 AM
Script       : [URL]http://farsa.gr/forum/newreply.php?do=postreply&t=263[/URL]
Referrer     : [URL]http://farsa.gr/forum/showthread.php?t=263&page=4[/URL]
IP Address   : 122.168.44.12
Username     : sahej
Classname    : vb_database

 Database error in vBulletin 3.6.8:

Invalid SQL:
INSERT INTO post
        (showsignature, allowsmilie, username, userid, title, pagetext, iconid, visible, parentid, threadid, dateline, ipaddress, attach)
VALUES
        (0, 1, 'sahej', 67, 'problem with the Koenigsegg CCXR theme', 'hey supertonic\nreally nice themes.......\n\nyour [B]Koenigsegg CCXR [/B]has just 1 problem that on the idle screen when we dial a no. instead of the black background it is white background with white no. on it.....\nso pls if you change it....... with both the icon sets\n\ni really liked that theme....', 11, 1, 1519, 263, 1197908877, '122.168.44.12', 0);

MySQL Error  : Duplicate entry '0' for key 1
Error Number : 1062
Date         : Monday, December 17th 2007 @ 08:27:57 AM
Script       : [URL]http://farsa.gr/forum/newreply.php?do=postreply&t=263[/URL]
Referrer     : [URL]http://farsa.gr/forum/newreply.php?do=postreply&t=263[/URL]
IP Address   : 122.168.44.12
Username     : sahej
Classname    : vb_database

[doc_sameer]

When i try to post a new thread now, its giving me this error:

Database error in vBulletin 3.6.8:

Invalid SQL:
INSERT INTO thread
    (postusername, postuserid, title, iconid, visible, forumid, threadprefix, similar, dateline, open, attach, lastpost, lastposter, replycount, hiddencount, deletedcount)
VALUES
    ('doc_sameer', 1, 'testing', 0, 1, 3, '[misc]', '639', 1197982652, 1, 0, 1197982652, 'doc_sameer', 0, 0, 0);

MySQL Error  : Duplicate entry '0' for key 1
Error Number : 1062
Date         : Tuesday, December 18th 2007 @ 04:57:32 AM
Script       : http://farsa.gr/forum/newthread.php?do=postthread&f=3
Referrer     : http://farsa.gr/forum/newthread.php?do=newthread&f=3
IP Address   : 122.169.164.121
Username     : doc_sameer
Classname    : vb_database

[doc_sameer]

Plz help! the problem continues, i cant post anything or make a new thread...

BUMP

[Steve Machol]

See this page for a solution to the 'duplicate key' error:

http://www.vbulletin.com/docs/html/mysql_duplicatekey

[doc_sameer]

Thanks for your help.. i can post new threads now.

I need to know the following:

1.What caused it?
2.Did my backing up of the database be a reason & why?
3.How can i prevent it in the future.
4.Is there a way to restore the lost posts & users? (coz i still cant see the ones that were lost)

[Steve Machol]

1. As per that page:

This error is most commonly caused by plugins or addons that incorrectly set auto increment fields within vBulletin. Please remove your plugins or addons and verify that the database keys are set correctly.

It can also be caused by backing up a newer MySQL database with the "Compatibility Option" if this is the case, the MYSQL backup does not contain auto-increment information and the proper indexes are not created when the backup is restored.

2. Yes.

3. See above.

4. Only if you have a good backup with all the missing posts.

[doc_sameer]

Thanks for the support, i had corrected the tables & now when i moved back to the backup, everything is normal & recovered the missing posts & users.