List:General Discussion« Previous MessageNext Message »
From:Bill Easton Date:October 28 2002 3:08pm
Subject:re: database corrupted after power switched off
View as plain text  
VR>Date: Mon, 28 Oct 2002 12:35:01 +0200
VR>From: Victoria Reznichenko <victoria.reznichenko@stripped>
VR>Subject: re: database corrupted after power switched off

VR>Tom,
VR>Monday, October 28, 2002, 11:59:16 AM, you wrote:

VR>TT> is it a normal behaviour that a sql databases gets corrupted if the
power of
VR>TT> the whole system will be switched off while an application is writing
to the
VR>TT> database ?

VR>Yes. Other situations when tables may become corrupted are listed
VR>here:
VR>     http://www.mysql.com/doc/en/Corrupted_MyISAM_tables.html

   ... which says "MyISAM table format is very reliable (all changes to a
table is
   written before the SQL statements returns)"  Evidently, this is NOT true.
(Perhaps,
   it used to be true for ISAM tables.  The documentation should be
changed.)

VR>TT> what can i do that this problem does no more appear ?

VR>You have to shutdown MySQL server correctly, buy UPS :-)

There are a couple of other choices (perhaps to be used in addition to UPS).

(1)  Use InnoDB tables.  Evidently, they are designed to survive such
things,
especially with innodb_flush_log_at_trx_commit=1.

We have survived having the server disconnected from the UPS.  I hope to not
find
out if it works twice :-)

(2)  If you have updates in infrequent bursts, such as with a server used
primarily
for queries or a single-user system, do FLUSH TABLES after each burst.  This
writes
the tables to disk.

We have a number of users with single-user local database a which are
synchronized with
the server from time to time, and we have no control over whether or not
they have UPS.
We did have the problem that the databases would be corrupted if power were
lost an
hour after updating.  Adding the FLUSH TABLES after each user interaction
that updates
the database solved this.  (In reality, we only flush the tables that have
been
modified.)








Thread
database corrupted after power switched offTom Thurnherr28 Oct
  • RE: database corrupted after power switched offEd Carp28 Oct
  • re: database corrupted after power switched offVictoria Reznichenko28 Oct
re: database corrupted after power switched offBill Easton28 Oct
  • Re: database corrupted after power switched offMichael T. Babcock28 Oct
  • Re: database corrupted after power switched offMark28 Oct
  • Re: database corrupted after power switched offBill Easton28 Oct
  • Re: database corrupted after power switched offMichael T. Babcock28 Oct
RE: database corrupted after power switched offJan Steinman28 Oct