On Wednesday something screwed up my mysql database that I have AbulWiki on. I didn’t notice until this morning though. By that time my daily backups had run twice, so I didn’t have a last good version. To say the least, I freaked out a little bit.
I’ve spent the last couple hours on it and I was able to start up mysql in a safe mode and do a data dump and I don’t think I’ve lost any data after all. Which is good. Very good. I also found a data dump I’d done ten days ago in another place. Also good. But what is bad is that I still can’t get mysql to start except in the read only safe mode. Which means I can’t make the wiki actually run of course.
According to the errors in the logs, the issue is a corruption in one of the InnoDB tables. The start up in innodb_force_recovery mode lets me do a mysqldump and get everything. And it looks like there is no actual data loss. But then it looks like the next step is supposed to be to drop the tables that are causing errors on start up, and then restore from your backup. But whenever I try to drop tables, mysql crashes and restarts and the tables are still there.
After running in circles, I’m starting to think I just want to wipe out mysql and reinstall from scratch, then restore my (hopefully) good dumps. The mysql dump I just did in safe mode is refusing to be read by MySQL Administrator due to character set issues. Hmmm…. The one from 10 days ago can be read. As can a backup I did using the Admin tool instead of doing mysqldump on the command line. Actually, both of those were done with the Admin tool rather than mysqldump. Sigh. In either case though, I can’t do jack until I can get mysql to start in regular mode rather than innodb_force_recovery mode.
And I am late to work. I need to go do work stuff now rather than bang my head on this.
But it seems clear what I’ll be doing tonight when I get home.
Fighting with mysql and hoping that these data dumps I’ve done are really usable and I haven’t lost anything (or at least not much). Looking at the mysqldump ones (which are human readable even if the GUI restore tool isn’t happy with them) they look OK, but…
Sigh.
Bleh.
Not happy Sam this morning.
Gotta go to work.
Hey, mysql just keeps ‘tables’ in a single file per ‘table’… so just mv the table/file that’s corrupt elsewhere and start the db, then make the replcaement table and load the data…
also, why innodb? you don’t do transaction-like things so how about just making the table myisam and loading the data in that? :)
-Chris
Tried #1 before I left home this morning. DB didn’t like it and wouldn’t start at all. Of course, there is no actual indication as to which table is bad, so I was trying to remove the whole wiki db.
On the second… cause that’s what the wikimedia setup scripts made. It’s not like I set it up by hand. :-)
I’m thinking I may try setting it up on a second machine (probably my old laptop Zeus) next. Starting with a fresh mysql and trying to restore the latest data dump. If that works fine, then I’ll feel free to just wipe mysql completley off my main machine and then do the same thing there.