RE: database reverted to 18hr old state after power outage
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]
Re: database reverted to 18hr old state after power outage
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/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
database reverted to 18hr old state after power outage
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]
