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:[EMAIL PROTECTED] > Sent: Wednesday, August 20, 2003 8:29 AM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > 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/[EMAIL PROTECTED] > >> > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
