Major MySQL Corruption

[TerryH]

Our site is now trying to recover from some major database corruption, and possibly the only option open to us will be reconstruction.

We operate a MediaWiki encyclopedia-type site with versions in English and in seven other languages. We use the latest version of MediaWiki running on a LAMP (Linux-Apache-MySQL-PHP) stack. For years our English site had been running Semantic MediaWiki and was up to version 0.7 of that then-experimental extension. About a month before our troubles began, we installed SMW 1.0 RC2.

One day we noticed that our edits would not save, because MySQL was complaining that a particular table was missing. The table seemed to be there--or at least phpMyAdmin said it was--but whenever one of us tried to browse it, we got an error message referencing error code 1017. Now I have found nothing on any MySQL forum that has any solution for Error Code 1017 other than dropping the table and re-creating it from a script.

I started to investigate further. To summarize my findings, four of our sites had damage to enough tables to make editing impossible, but not enough to stop the sites from loading or make them unreadable. So I was able to restore the sites to functionality, but only by dropping and recreating the affected tables.

But the other four sites had so many tables reporting Error Code 1017 that the content is effectively gone.

Strangely, the problems did not arise until two days after a backup was done. So when we saw the problems, I naturally recommended restoration from backup. Lo and behold, even the backups were corrupt, and in exactly the same way--this although we had been working with our databases for two days since.

Recently, after much labor, we have managed to get a detailed look at those backups. I looked around in them and found that the only files available are files having the extension .frm. The files with extensions .MYD and .MYI are missing--and without those files, restoration of the contents of those tables is impossible.

We conclude that when the backups were done, some key filesystem permissions had not been granted or had somehow been reset, and no one knew what had been done until it was too late. As a result, nothing was copied except some MySQL headers--no content, and no indices. At first this affected the backup, and then two days later it affected the actual database, forcing us to consider reconstruction.

Has anyone else seen anything like this?

TerryH

[Daniel_DBS]

Hmmm never had anything like that happen before...

I noticed in your profile you had a link to your Wiki and it is hosted at GoDaddy... I have read bad things about GoDaddy and MySQL before around on the net... Might I suggest you move to Jag???

[Daniel_DBS]

BTW... I couldn't help but notice when I ran a Ping test to your domain to get the IP it had an average ping time of 700ms

[mattsiegman]

MySQL error 1017 is really nasty. There are some methods to try and pull the data out, but they usually fail.

The even more interesting thing is that MySQL continues to function, pretending there is no corruption, and doesn't really warn you. Those errors most likely were in your backups too. The errors have a tendency to be something tiny that got corrupted very near the life of the data file, but didn't really change anything until much later in the data file's life.

Also, you should try to get the hard disk checked, because it is very possibly a hard drive issue. Sometimes, it's a hard drive issue, and even fdisk won't pick it up, but changing the HD fixes it.

If you see these start to happen more frequently, stay backed up, and get a new drive as soon as you can.

(I have gone through this before...)

[fredrc]

I help out on creationwiki.org with Terry and the site is definitely hosted on Jaguar. The registrar is Godaddy. The extra hops might be why our ping time is bad. Hmmm, "note to self, investigate using a different registrar".

The database loss is our main concern right now. We have 99% given up on recovering anything but it would be nice to learn something in the aftermath. Basically the site was working on Dec 16th, then on the 19th it had some major problems. We asked Jag support if what backups were available and they said they had one on the 17th. "Great, were saved" we thought. But the restore was worse than the problems.

(I don't know if I got the dates exactly right, but the 3 day span is correct).

I don't know about it as much as Terry, but here is my version. Because the restore didn't work we had Jaguar give us the database files they used to restore from in case we could use them on a non-live site and figure out how to recover some files. Well there were some files missing. We have several databases. One is working ok, (I had to run a recovery , but it worked), all its files where available. In several other databases there were several files missing. The main ones that would have allowed recovery to work.

That is about the limit of my direct experience on this issue. I don't work on the site as much as Terry. From chats with him, I have too working theories. 1. Jaguar's backup procedure doesn't work well on open databases. 2. Something strange out of our control changed permissions on files such that Jaguar's backup failed to copy all the files.

Going forward we will be doing our own backups (lesson learned) but because of this great loss, it would really help to understand what really happened, or what would more likely have happened.

One tech told us "Well we don't know anything about how MediaWiki works maybe it is the cause". I can understand why they can't solve app problems, but MediaWiki isn't some secret app with shoddy programmers. It is a huge open source project and we have been running it for several years without problems.

Does anyone know if Jaguar's stock backup procedure for semi-dedicated hosting can usually backup open MySql databases?

[fredrc]

Also, you should try to get the hard disk checked, because it is very possibly a hard drive issue. Sometimes, it's a hard drive issue, and even fdisk won't pick it up, but changing the HD fixes it.

Thanks for the tip. I hope that Jaguar doesn't have a bad hard drive on our server, or can figure out if they do quickly.

[Vin DSL]

I'm sorry if I missed it, but...

What type of account do you have here?

I confirmed that you're hosted here, but what type of account do you have?

I ate some 'bad' popcorn tonight, and my eyes aren't working too good...

[Vin DSL]

1) Going forward we will be doing our own backups (lesson learned)...

2) One tech told us "Well we don't know anything about how MediaWiki works maybe it is the cause"...

3) Does anyone know if Jaguar's stock backup procedure for semi-dedicated hosting can usually backup open MySql databases?

Okay, I had some more food, now I feel better...

1) Backup your own data!

3) Backup your own data!

2) Forget Tech Support! Get a dedicated server and install Squid. Your MW site is too busy for SDX...

[Vin DSL]

WoW!

I'm showing you changed hosts on:

10-24-2007
11-06-2007
12-11-2007

Is this true?

[Vin DSL]

Warning! Warning! Warning!

Don't eat Jolly Time 94% Fat Free Microwave Popcorn!

Somebody gave it to me... and it sucks!!!

[Vin DSL]

Hrm...

Too late to post an edit!

If you don't know what Squid is...

http://meta.wikimedia.org/wiki/Squid_caching

I wonder if Jolly Time Microwave Popcorn is made in China...

[Daniel_DBS]

WOAH! now this is just FREAKY!

Last night when I did that ping test and got your IP to look it up at ARIN, it posted an IP owned by GoD@ddy...

I just redid that ping test and now it is showing at Jag... Did you happen to have changed your nameservers in the past 2 or so days???

And I took a look at your MW site and it is pretty busy... Massive amount of data...

Since all of the articles and such are stored in a DB and you have over 3k articles that DB must be HUGE!

I definitely like Vin's suggestion of a dedi and SQuiD...

Anybody know if there is a DB file size limit????

[TerryH]

And I took a look at your MW site and it is pretty busy... Massive amount of data...

Since all of the articles and such are stored in a DB and you have over 3k articles that DB must be HUGE!

I definitely like Vin's suggestion of a dedi and SQuiD...

Anybody know if there is a DB file size limit????

I wouldn't know about that; my role in web-site administration and registration is more limited than are those of my colleagues. But in reply to your question of size:

The database that has more than three thousand articles is actually our English site. The trashing of the tables affected only a limited number of tables--tables that I was able to drop and re-create, and which didn't even have any content that I had really missed.

The four non-English databases that got totally wrecked--with nearly all tables now showing Error 1017--had a significant amount of data, the reconstruction of which will require much labor. But they did not have nearly as much data as the English site has.

Fred said it correctly: when we first heard that we had a December 17 backup, we thought we were saved. But at first I thought the backups hadn't been restored at all, that someone had just said they had restored the backups, when they hadn't. Then they restored them a second time, and I watched it happen. And that left things in the same sorry shape I had found them in, and worse than that, I had to redo all the repairs I had done to four of the databases, just to get them back into good working order.

In answer to the one who pointed out (correctly) what a nasty thing the 1017 error really is, I have thought ever since I realized the full extent of the damage that the hard drive was at fault.

Let me reiterate something else that Fred said: blaming MediaWiki strikes me as unfounded, unwarranted, and illogical. MediaWiki is the same project that drives Wikipedia. It is, as Fred says, an open-source project, built largely on the LAMP stack. Now I happen to believe that Wikipedia has got some problems, but technical problems, and especially technical problems of the sort that we have lately suffered, are not among them.

Terry

[Frank Broughton]

Static HTML! Old school - solid! Web 2.0 dynamic web pages.... trouble!

With my dynamic sites I have the database dumped and sent off site DAILY - I keep numerous layers of backups.

With a site with years of data no one thought of daily backups remotely? We humans are a curious lot aye? We learn from mistakes. I wish we did not have to go to the school of hard knocks. That place was and is my best teacher too - unfortunately!

I feel your pain! Ouch.....

[fredrc]

WoW!
I'm showing you changed hosts on: 10-24-2007; 11-06-2007; 12-11-2007
Is this true?

No. We switched to Jag from Yahoo about 2 years ago. somewhere around Jun 2006. Then we had Jag move us to a different server, one running PHP 5 and went semi-dedicated. We are on ewok now.

WOAH! now this is just FREAKY!... IP's changing. Did you happen to have changed your nameservers in the past 2 or so days???

No, no changes. And these strange ping results are a mystery to me. The inconsistent results is part of what I experienced when I was helping set things up and getting email to work. And it confused Jag tech support also. I thought I understood IP's and nameservers, now I think of it all as black magic. Probably some conspiracy by the creator's of carnivore.

--

Semi-dedicated has been performing ok for us. The site has lots of data, but the traffic isn't huge. And I am not sure if more features would be beneficial, without understanding them, it could end up just causing more problems. The site is non-profit, way non-profit.

As Terry explained, our main huge english wiki running in one database was recoverable. But the smaller wiki's in their own small databases were completely hosed. My theory of Jag not being able to backup open MySQL databases goes something like this. With the small korean database, most of it was in memory and the files were opened and the backup couldn't copy most of the files. But the huge English database was too big to be in memory so most was flushed out to disk and the backup copied the files just fine - well mostly, it did need recovery work.

To summarize: The symptoms. The Korean wiki (and others) were working on the 18th.
On the Dec 19th we got this "Error: 1017"
http://creationwiki.org/ko

The restored Dec 17th backup was just as bad as what was experienced on the 19th.

Why????

So far I have collected a few theories.
* Jag can't backup open databases (My own)
* Hard disk corruption caused backup/restore not to work.
* Some unknown thing is changing file permissions on us, causing backup/restore not to work.
* Neutrino's from space are zipping through the atmosphere ripping apart our brains and making us think ... oh wait, that's another dream.

Do you know if one of these can be excluded for logical reasons?
Well apart from the Neutrino's...

Thanks