Database corrupted, need professional assistance.

[Theater]

Hello,

Unfortunately some tables in my database got corrupted and died a painful death. It happened at 7PM CST yesterday, and the initial cause was a network switch failure. This is the error message at that time:

Database error in vBulletin :

mysql_connect() [<a href='function.mysql-connect'>function.mysql-connect</a>]: Can't connect to MySQL server on '10.10.10.11' (4)
<<snipped my path>>/class_core.php on line 311I have two servers for my community. One for the files, and the other for the database. The site was still up but not the database. After 8 minutes it seemed the switch issue had been resolved, but then I started getting several weird error messages:

Database error in vBulletin 3.8.2:

Invalid SQL:

SELECT IF(visible = 2, 1, 0) AS isdeleted,



thread.*


FROM thread AS thread






WHERE thread.threadid = 36357;

MySQL Error : Can't find file: 'thread' (errno: 2)
Error Number : 1017

Also:

Database error in vBulletin 3.8.2:

Invalid SQL:

SELECT
userfield.*, usertextfield.*, user.*, UNIX_TIMESTAMP(passworddate) AS passworddate, user.languageid AS saved_languageid,
IF(displaygroupid=0, user.usergroupid, displaygroupid) AS displaygroupid,
language.phrasegroup_global AS phrasegroup_global,
language.phrasegroup_adv_portal AS phrasegroup_adv_portal,
language.phrasegroup_calendar AS phrasegroup_calendar,
language.phrasegroup_postbit AS phrasegroup_postbit,
language.phrasegroup_prefix AS phrasegroup_prefix,
language.options AS lang_options,
language.languagecode AS lang_code,
language.charset AS lang_charset,
language.locale AS lang_locale,
language.imagesoverride AS lang_imagesoverride,
language.dateoverride AS lang_dateoverride,
language.timeoverride AS lang_timeoverride,
language.registereddateoverride AS lang_registereddateoverride,
language.calformat1override AS lang_calformat1override,
language.calformat2override AS lang_calformat2override,
language.logdateoverride AS lang_logdateoverride,
language.decimalsep AS lang_decimalsep,
language.thousandsep AS lang_thousandsep
, cmpspages.layout AS cmpslayout
FROM user AS user
LEFT JOIN userfield AS userfield ON (user.userid = userfield.userid)
LEFT JOIN usertextfield AS usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN language AS language ON (language.languageid = IF(user.languageid = 0, 1, user.languageid))
LEFT JOIN adv_pages_user AS cmpspages ON (user.userid = cmpspages.userid AND cmpspages.pageid = 1)
WHERE user.userid = 69970;

MySQL Error : Can't find file: 'user' (errno: 2)
Error Number : 1017


Etc, etc. Anyway, I tried to have my managed host provider fix this with a repair option through the server since I wasn't able to log into my admin account to do it from AdminCP. They said they could not, so at this point I was going to have them use a backup. The only problem is that when they setup the new database server for us, they never implemented any backup procedure for that box so the only backup I have available is from 2 months ago.

I'm missing 15,000+ posts, a few thousand member registrations, not to mention a heck of a lot of work to the forum itself. So I do have the corrupted current database, and if anyone is willing to help I'd appreciate it. These are the tables they had listed as corrupted:

mydatabasename_dbase.postparsed mydatabasename_dbase.profilevisitor mydatabasename_dbase.search mydatabasename_dbase.strikes mydatabasename_dbase.tagsearch mydatabasename_dbase.thread mydatabasename_dbase.threadviews mydatabasename_dbase.user mydatabasename_dbase.useractivation mydatabasename_dbase.word

I have several mods installed, more recently vBExperience, and I think that mod in particular might have played a part in this if say the database server stopped communicating with the file server halfway through a cronjob. I don't know for sure, but I just have a gut feeling. I've PM'd the mod author to find out for sure.

Any ideas? :confused:

[UKBusinessLive]

Hello,

I'm missing 15,000+ posts, a few thousand member registrations, not to mention a heck of a lot of work to the forum itself. So I do have the corrupted current database, and if anyone is willing to help I'd appreciate it. These are the tables they had listed as corrupted:

I have several mods installed, more recently vBExperience, and I think that mod in particular might have played a part in this if say the database server stopped communicating with the file server halfway through a cronjob. I don't know for sure, but I just have a gut feeling. I've PM'd the mod author to find out for sure.

Any ideas? :confused:

This might be a long shot but did you have any backups?? :confused: Install the back ups and explain the problem, if your board is a busy as you say you should of been doing backups every week :eek:

You might be able to get some help at the MYSQL Developer Zone, if anyone knows about MYSQL its got to be them, they will tell you for definate if the databases can be repaired or not, at least you can go to stage two and upload your backup from 2 months ago

heres their site http://dev.mysql.com/

Good luck and let us know how you got on

;)

[Theater]

For now the site is running the 2 month old backup, but we're still missing on a TON of changes and data within that period of time. Yes, I am fully aware that we should have weekly backups and I'm doing my best not to flip out on my host. It's a long story, but there should have been a backup going.

Thanks for the tip, I'll give that a look.

--------------- Added 1243733496 at 1243733496 ---------------

It appears that some actual tables are empty like THREAD for example, so restoring it at this point doesn't seem likely. I am able to see 526,307 entries in POST which seems right. The USERS table only has 21 (out of ~80k).

Speaking of which... is there a way for me to add an Admin account through phpMyAdmin? I just want to be able to switch it back to the bad database for a moment, log into the AdminCP, and pull down the Styles file so it will at least save me that part of setting back up.

How do tables get dumped like that anyway? Would it be caused by a mod perhaps?

[TheLastSuperman]

For now the site is running the 2 month old backup, but we're still missing on a TON of changes and data within that period of time. Yes, I am fully aware that we should have weekly backups and I'm doing my best not to flip out on my host. It's a long story, but there should have been a backup going.

Thanks for the tip, I'll give that a look.

--------------- Added 1243733496 at 1243733496 ---------------

It appears that some actual tables are empty like THREAD for example, so restoring it at this point doesn't seem likely. I am able to see 526,307 entries in POST which seems right. The USERS table only has 21 (out of ~80k).

Speaking of which... is there a way for me to add an Admin account through phpMyAdmin? I just want to be able to switch it back to the bad database for a moment, log into the AdminCP, and pull down the Styles file so it will at least save me that part of setting back up.

How do tables get dumped like that anyway? Would it be caused by a mod perhaps?

Well it depends on what happened to the server... think of a server just like your desktop (minus the OBVIOUS differences you technical members :p) and what if a power surge came through? Hard Drive or Motherboard could be shot, it's the same with a running/live server in terms of the Database, for example... there were 1k members online interacting with it and creating threads, using mod functions and all when it crashed so something had to give apparently... :o :erm:

UKBL is right and it's not always your hosts fault, we have the options to do it ourselves via phpmyadmin AND or you can install MySQL Dumper etc.

I would do as UKBL suggest and let the MySQL guys/gals let you know what they think.

In terms of checking the old DB, simply record your DB info from the current config then key in the old DB's name & pw then upload, now check the forums.. they're back but the corrupted one and fyi your members will see this too but it's a quick way to check things out, then re-edit the config and upload again to have the restored forums back.

Stuff like this has happened to the best of us, we can beat ourselves up over no backups but best thing is to learn from it, I do them weekly now both via automatic backup and through me manually doing it just in case eh?! ;).

S-MAN

[HMBeaty]

You should try this out

https://vborg.vbsupport.ru/showthread.php?t=201319

I use it to back my database up every few days for now.

[Theater]

I actually just got phpmyadmin access tonight along with shell access I had to demand through the owner of the host company. We're on a managed hosting solution and pay a premium, but I will checkout that mod Redlinemotorsports to see how well that works out for me. Anyway, moving on from how the backups should be handled...

The site does pull up with the old corrupt database, but I cannot login since the USER table is practically empty. I need a way to insert myself in that USER table I think. Would there be anyway to recover either THREAD or USER table using just the POST table? It seems like it has all the information in there.

[Andy]

Have you tried using phpmyadmin to repair the user and thread tables?

[Theater]

Yeah, but nothing really to repair on an empty table however. My only hope I think is to use the POST table to somehow regenerate both THREAD and USER tables. Everything else looks fine, or well enough for the forum to operate again.

--------------- Added 30 May 2009 at 23:38 ---------------

Ah, no such luck unless I hear otherwise. The Grand Poobah has spoken here (http://www.vbulletin.com/forum/showpost.php?p=1742186&postcount=4). I'm going to take one of the handful of users in the USER table and change the e-mail to mine, and see if I can reset the password, set the ID in config, log into AdminCP, and snag that style file from the corrupt db.

[Dismounted]

My only hope I think is to use the POST table to somehow regenerate both THREAD and USER tables.
I see your logic - but usernames are not stored in the post table, so they would all be lost anyway.
I'm going to take one of the handful of users in the USER table and change the e-mail to mine, and see if I can reset the password, set the ID in config, log into AdminCP, and snag that style file from the corrupt db.
Not sure if you can access the front-end, but if you can, register yourself a user and then upload tools.php from the do_not_upload directory. You can run the "Restore Admin Access" tool.

[Theater]

I see your logic - but usernames are not stored in the post table, so they would all be lost anyway.

Not sure if you can access the front-end, but if you can, register yourself a user and then upload tools.php from the do_not_upload directory. You can run the "Restore Admin Access" tool.

The post table does include the username:

99882

Thank you for the tip about the tools.php file. It will save me a lot of work if I can grab the latest style file. I will admit this part is my fault since it's so easy to download it to my desktop. Sigh...

[Dismounted]

The post table does include the username:
My bad then - didn't know they denormalised it. You could probably rebuild the user table then, with some work.

[Theater]

Okay, new problem.. well maybe not a problem but I want to check with you guys.

The database is restored to the backup made 2 months ago. The site server only runs weekly backups so it still has all the latest files and attachments. Will having those attachments conflict with the old database, or will they just not conflict but sit there in the file system?

Basically I want to know is when someone posts a new attachment, is it going to clash with the current and updated attachments if they're in filesystem format?

[Dismounted]

They should be fine (and be overwritten when needed). However, you can "clean" the folder by moving attachments into the database, removing the directory, then moving them back into the file system.