What Causes MySQL Tables to become Corrupt?

  • BooGiE_MaN
  • Proficient
  • Proficient
  • User avatar
  • Posts: 387
  • Loc: Cape Town, South Africa

Post 3+ Months Ago

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
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • mk27
  • Proficient
  • Proficient
  • User avatar
  • Posts: 334

Post 3+ Months Ago

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.
  • BooGiE_MaN
  • Proficient
  • Proficient
  • User avatar
  • Posts: 387
  • Loc: Cape Town, South Africa

Post 3+ Months Ago

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
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6241
  • Loc: Seattle, WA

Post 3+ Months Ago

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
  • BooGiE_MaN
  • Proficient
  • Proficient
  • User avatar
  • Posts: 387
  • Loc: Cape Town, South Africa

Post 3+ Months Ago

Nothing on InnoDB?
  • spork
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6241
  • Loc: Seattle, WA

Post 3+ Months Ago

Crap, didn't even see the InnoDB part of your question, sorry!
  • BooGiE_MaN
  • Proficient
  • Proficient
  • User avatar
  • Posts: 387
  • Loc: Cape Town, South Africa

Post 3+ Months Ago

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
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

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
  • Brewmaster
  • Silver Member
  • User avatar
  • Posts: 6241
  • Loc: Seattle, WA

Post 3+ Months Ago

Yes, but what happens if the server or machine does down during a commit operation?
  • BooGiE_MaN
  • Proficient
  • Proficient
  • User avatar
  • Posts: 387
  • Loc: Cape Town, South Africa

Post 3+ Months Ago

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
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

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. :)
  • BooGiE_MaN
  • Proficient
  • Proficient
  • User avatar
  • Posts: 387
  • Loc: Cape Town, South Africa

Post 3+ Months Ago

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
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

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.
  • BooGiE_MaN
  • Proficient
  • Proficient
  • User avatar
  • Posts: 387
  • Loc: Cape Town, South Africa

Post 3+ Months Ago

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
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

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.
  • BooGiE_MaN
  • Proficient
  • Proficient
  • User avatar
  • Posts: 387
  • Loc: Cape Town, South Africa

Post 3+ Months Ago

Yes it was the FRM files. Im hoping the host did try a recovery tool before restoring a month old backup; next time i'll know to find out if they did. Thanks for your help.
  • UPSGuy
  • Lurker ಠ_ಠ
  • Web Master
  • User avatar
  • Posts: 2733
  • Loc: Nashville, TN

Post 3+ Months Ago

No problem - sucks that they only had a month-old as the most recent. I'd press for something a little more recent myself. Maybe you could look at third-party solutions and implement something yourself without their help. Glad they at least got something restored for you, though.

Post Information

  • Total Posts in this topic: 17 posts
  • Users browsing this forum: No registered users and 103 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.