What Causes MySQL Tables to become Corrupt?

September 8th, 2009, 5:34 am

Hi

I've been googling for a while and im not getting any straight answers

What are all the possible causes of currupt tables in a MySQL database? Particularly for InnoDB tables

Can executing any kind of insert / update / delete query cause corruption?
Everything i've read suggests that corruption is caused at server level, such as the host doing upgrades or the server crashing.

Which table format is better / more reliable and why; InnoDB or MyISAM

[mk27]

September 8th, 2009, 8:46 am

You might want to google "SQL Injection".

Also, be more specific about what you mean by "corruption". Obviously, executing any insert/update/delete command could mess it up if done badly.

September 8th, 2009, 10:25 am

But how does a Sql injection make a table "corrupt"; can you think of an example of something that would cause this error to show in PHP my admin?

"Incorrect information in file:" .... (file name...)

You cant do repair table or anything. The data is just gone. It only affected InnoDB tables.

[spork]

September 8th, 2009, 12:01 pm

The MySQL documentation has a good section about what can cause a MyISAM table to become corrupted:

http://dev.mysql.com/doc/refman/5.1/en/ ... ables.html

September 8th, 2009, 12:37 pm

Nothing on InnoDB?

[spork]

September 10th, 2009, 11:55 am

Crap, didn't even see the InnoDB part of your question, sorry!

September 10th, 2009, 12:03 pm

No worries; guess it will forever be a mystery. I've been reading that innoDB is best for "transactions" and MyISAM is for "non-transactions"; can anyone explain the difference?

[UPSGuy]

September 10th, 2009, 2:49 pm

InnoDB's transaction-safe.

That means that data integrity won't be compromised throughout your query process.

And that means that you can use 'begin transaction' and 'rollback/commit' (SQL transactions)

And that means that you can run a series of queries, catch any exceptions as you like, and roll back your database changes if need be. Think of it as proofreading your changes - you line up a bunch of queries, you execute them, and then if you didn't have any hiccups, then you 'commit' them and all the changes take place. Otherwise, you 'rollback' and none of the queries will have affected your tables.

MyISAM uses atomic operations - just the opposite. When damage is done, then it's done. It's a little more old-school and it's thought by some to be a little faster (although these days that's a highly debatable point).

(Wasn't sure what your knowledge-level is, so I tried to break it down into all the forms you'll probably see discussed, but also tried to make it understandable, too If you want to read up a bit more on the differences, then you can have a look over here. If you want to read up on SQL transactions, there's a decent tutorial here. )

[spork]

September 10th, 2009, 3:19 pm

Yes, but what happens if the server or machine does down during a commit operation?

September 10th, 2009, 3:28 pm

Thanks UPS that second link is really enlightening. I've never needed to do a "transaction", but i can see the benefits.

Do you know if its possible that doing a bad query of sorts could cause problems with the physical table file on the server, rendering an error like this in phpmyadmin:

"Incorrect information in file:" .... (file name...)

From what i've read on various sites this is something at the server (eg. a server crash or an upgrade not done properly); do you agree? This only affected innodb and not myisam tables (which were only in myisam format for the fulltext feature).

[UPSGuy]

September 10th, 2009, 3:36 pm

That's a durability failure. Although, for that to happen the power would have to go out at exactly the right time (data will remain consistent and isolated, so it's all or nothing). Crash recovery is useful, but I suppose if we're being picky, you can't trust that 100% either. At worst, you lose that transaction, but on the other hand, you don't partially write anything, so you should remain in a usable state. ACID compliance takes care of a HUGE amount of worry, but if we're looking at the .0000001%, then you best have backups.

September 10th, 2009, 3:54 pm

UPSGuy wrote:

That's a durability failure.

ie something with the host's equipment?

UPSGuy wrote:

at exactly the right time

YOu mean a query was being done when a crash occured? Cant imagine this to be the case as its unlikely all the tables were being queried at the same time.

[UPSGuy]

September 10th, 2009, 4:25 pm

Don't buy in to that last post too much - spork kind of veered me into the rabbit hole, but I'll explain them anywho.

InnoDB tables are ACID-compliant, which gives them their transaction capability. ACID stands for Atomicity, Consistency, Isolation and Durability. Wikipedia does a much better job than I probably can, but that's where the durability comes in.

As for the timing, it wouldn't be while the query was running - it would be during the commit process. The precise exact time in spork's example would be during whatever mysql internal jobs are caused by the commit that finally persists the changes to the tables.

September 10th, 2009, 4:56 pm

So does working with an innodb table mean its a transaction, even if you dont go
BEGIN

INSERT etc

COMMIT
Im a little puzzled now; if innodb are transaction-"safe" and they're the only ones to be lost (as opposed to the myisam tables) in a crash / failure... :0
perhaps its all just part of the debate on choosing myisam or innodb?
Thanks for all the info tho i learnt some new things.

[UPSGuy]

September 10th, 2009, 5:04 pm

All the transaction stuff is really a matter of personal choice and, as you suggested, a feature to debate over when deciding which to use. It's probably not related to the corruption you've experienced. I don't see much out there, but which file does the error specify? The FRM file like this? Any way about it, recovery seems to be your best bet AFAICT.