List:General Discussion« Previous MessageNext Message »
From:Michael S. Fischer Date:August 20 2003 6:48pm
Subject:RE: database reverted to 18hr old state after power outage
View as plain text  
The database does write the data to disk when an UPDATE or INSERT is
sent, but not synchronously.  (IOW, it doesn't call fsync() after each
write.)  So there is no guarantee that the data will be in the tables
when a power failure occurs.  This is a tradeoff MySQL makes for speed
because it's not an ACID-compliant database.

That said, most operating systems flush the buffer cache to disk every
few seconds, and so 18 hours of data loss sounds pretty excessive to me.
I can't help but wonder if the filesystem metadata pertaining to your
MySQL tables was corrupted when the power was cut and the subsequent
filesystem check corrected it in such a way that eliminated the more
recent data.

What the other poster was suggesting was that if the tables can't be
restored to the state immediately prior to the power outage, perhaps
they can be restored from the 18-hour-old version of the database along
with the data stored in the logs (assuming you've configured mysqld to
write logs).  

--Michael


> -----Original Message-----
> From: Brian Chan [mailto:bchan@stripped] 
> Sent: Wednesday, August 20, 2003 8:29 AM
> To: colbey@stripped
> Cc: mysql@stripped
> Subject: Re: database reverted to 18hr old state after power outage
> 
> 
> So basically all the changes from those 18hrs were stored in 
> logs and not yet commited to the database?  So maybe if I 
> just commited the logs every so often I wouldn't have this problem?
> 
> 
> 
> >
> > It depends if you had any kind of query logging enabled (binary or 
> > text) .. If you started safe_mysqld with -l (that's text logging of 
> > queries) .. or configured my.cnf with bin-log (that's binary 
> > logging)..
> >
> > You should be able to pipe/patch the logs against the 
> database and let 
> > it run all the updates...
> >
> > I fixed all my errors by simply:
> > 1. show tables
> > 2. check table name1, name2, etc..
> > 3. any problems listed above
> > 4. repair table name1   (repeat)
> >
> >
> > On Wed, 20 Aug 2003, Brian Chan wrote:
> >
> >> hi,
> >>
> >> the power outage in ontario caused my server to crash.  after i 
> >> booted up again it was as if the past 18 hrs never 
> happened.  all the 
> >> changes to the
> >> database were gone.  is there anyway to recover the 
> missing data?  it's
> >> probably too late now since new data has been put into the database
> >> since
> >> then...
> >>
> >> I now also have errors with my database.  I run myisamchk 
> >> /var/lib/mysql/*/*.MYI -e -o -r and I get many errors such as:
> >> Found block that points outside data file at 9464
> >> or
> >> Found block with too small length at 980; Skipped
> >>
> >> Is there anyway I can resolve these errors?
> >>
> >> Thanks,
> >> Brian.
> >>
> >> --
> >> MySQL General Mailing List
> >> For list archives: http://lists.mysql.com/mysql
> >> To unsubscribe: 
> >> http://lists.mysql.com/mysql?unsub=1
> >>
> >
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?> unsub=michael@stripped
> 
> 

Thread
database reverted to 18hr old state after power outageBrian Chan20 Aug
  • Re: database reverted to 18hr old state after power outagecolbey20 Aug
    • Re: database reverted to 18hr old state after power outageBrian Chan20 Aug
      • RE: database reverted to 18hr old state after power outageMichael S. Fischer21 Aug