>From normal usage, InnoDB is sure better, i.e.

quote from MySQL:
===============
The outstanding feature of InnoDB tables is also automatic crash
recovery - in case MySQL shutdown was unclean InnoDB tables will still
recover to the consistent state, you will not have to check or repair
them.
===============

But they are talking normal/common usage, how about in the Worst Case
Scenarios?

I heard that in some rare case, if InnoDB table was corrupted, there
are no method to repair them, is that true?

So how about MyISAM vs InnoDB, in Worst Case Scenarios?

howa wrote:
>>From normal usage, InnoDB is sure better, i.e.
>
> quote from MySQL:
> ===============
> The outstanding feature of InnoDB tables is also automatic crash
> recovery - in case MySQL shutdown was unclean InnoDB tables will still
> recover to the consistent state, you will not have to check or repair
> them.
> ===============
>
> But they are talking normal/common usage, how about in the Worst Case
> Scenarios?
>
> I heard that in some rare case, if InnoDB table was corrupted, there
> are no method to repair them, is that true?
>
> So how about MyISAM vs InnoDB, in Worst Case Scenarios?

Yesterday at my previous employer someone turned off the power to a rack
of servers. The MySQL server in the rack was running 6 instances of
MySQL, 5 replicas and 1 primary server. The primary server application
uses innodb tables exclusively. When the systems were powered back on
everything came up fine except that the binary log for master server was
corrupt. This was sort of nasty because it made the replicas for the
master very unhappy. I got called to them restart replication. We
had to do a complete copy to restore replication. So, I guess what I am
reporting is that in this case both innodb and myisam tables survived a
catastrophic system failure, but the binlog didn't.

The only innodb problems I have seen are with MySQL instances that are
configured to support innodb but do not have any innodb tables. Several
times I have seen the innodb structures get corrupt and keep the
instance from coming up. Since there were no innodb tables in the
instance my solution was just to delete the ibdata and log files and let
the MySQL startup recreate empty structures. I have never seen this for
a instance that has active innodb tables in it.

Bill

On Thu, 01 Nov 2007 18:58:02 +0100, Bill MacAllister <whm@stanford.edu>
wrote:
> Yesterday at my previous employer someone turned off the power to a rack
> of servers.

Hehe, as a stand alone a pretty funny sentence :P
--
Rik Wasmus

>quote from MySQL:
>===============
>The outstanding feature of InnoDB tables is also automatic crash
>recovery - in case MySQL shutdown was unclean InnoDB tables will still
>recover to the consistent state, you will not have to check or repair
>them.
>===============
>
>But they are talking normal/common usage, how about in the Worst Case
>Scenarios?
>
>I heard that in some rare case, if InnoDB table was corrupted, there
>are no method to repair them, is that true?

If you have a problem like a hard disk controller writing data to
the wrong sector, thereby blowing holes in your data (or worse,
header information identifying the data), chances are nothing will
fix it but restoring from backups.

>So how about MyISAM vs InnoDB, in Worst Case Scenarios?

In a true Worst Case Scenario, you don't get back anything. Your
server suffers a power surge (it got through the UPS, perhaps because
it was an EMP from a terrorist nuke) and goes down, frying electronics
in the process. You power it back up again, a fire starts, and the
resulting fire takes out all your hardware, all your on-site backups,
and all the people involved with the application.

The resulting fire is detected as a nuclear explosion. Due to
various foul-ups and the TSA letting a bottle of hair gel through
security causing DEFCON 1, the nuclear powers start World War III,
which takes out all of your off-site, on-planet backups, all of the
source code to MySQL, and all the MySQL developers. The EMP from
the nukes takes out any backups you may have had in orbit. Since
humans are now extinct, nobody really cares that your application
doesn't work.

Another worst-case scenario: your hardware suffers a problem. You
try to bring it back up, and it's obvious that the data is all
screwed up. The tape drive or CD/DVD-ROM drive you use to restore
your backups is damaged so that it destroys any backups you insert
in the drive by scratching, stretching, degaussing, or whatever.
Further, any drive you insert the damaged media into is also damaged.
(Didn't this really happen with Zip drives?)

Another worst-case scenario: your hardware develops random,
occasional bit errors that only sometimes cause problems. After a
year of tearing your hair out you finally realize what the problem
is. At this point you have a years worth of backups hopelessly
contaminated with bit errors.

gordonb.qjv0q@burditt.org (Gordon Burditt) wrote in
news:13ikr3aovmqn1ad@corp.supernews.com:

> In a true Worst Case Scenario, you don't get back anything. Your
> server suffers a power surge (it got through the UPS, perhaps because
> it was an EMP from a terrorist nuke) and goes down, frying electronics
> in the process. You power it back up again, a fire starts, and the
> resulting fire takes out all your hardware, all your on-site backups,
> and all the people involved with the application.
>
> The resulting fire is detected as a nuclear explosion. Due to
> various foul-ups and the TSA letting a bottle of hair gel through
> security causing DEFCON 1, the nuclear powers start World War III,
> which takes out all of your off-site, on-planet backups, all of the
> source code to MySQL, and all the MySQL developers. The EMP from
> the nukes takes out any backups you may have had in orbit. Since
> humans are now extinct, nobody really cares that your application
> doesn't work.
>
> Another worst-case scenario: your hardware suffers a problem. You
> try to bring it back up, and it's obvious that the data is all
> screwed up. The tape drive or CD/DVD-ROM drive you use to restore
> your backups is damaged so that it destroys any backups you insert
> in the drive by scratching, stretching, degaussing, or whatever.
> Further, any drive you insert the damaged media into is also damaged.
> (Didn't this really happen with Zip drives?)
>
> Another worst-case scenario: your hardware develops random,
> occasional bit errors that only sometimes cause problems. After a
> year of tearing your hair out you finally realize what the problem
> is. At this point you have a years worth of backups hopelessly
> contaminated with bit errors.


That's it, I'll never be able to sleep again!

On 11 2 , 8 16 , gordonb.qj...@burditt.org (Gordon Burditt) wrote:
rare case, if InnoDB table was corrupted, there

> In a true Worst Case Scenario, you don't get back anything. Your
> server suffers a power surge (it got through the UPS, perhaps because
> it was an EMP from a terrorist nuke) and goes down, frying electronics
> in the process. You power it back up again, a fire starts, and the
> resulting fire takes out all your hardware, all your on-site backups,
> and all the people involved with the application.

snipped.

Anyway, to make it more simple:


1. Will there be a case such that, if my table is using MyISAM, it can
be recovered, but now it can't as I am using InnoDB?

2. Will there be a case such that, if my table is using InnoDB, it can
be recovered, but now it can't as I am using MyISAM? (except the
reason InnoDB didn't corrupt...usually)

Thanks.