Can I lose data if I use InnoDB?

Question

During a power failure, is it possible to lose data if I use InnoDB? I remember reading such things.

Answer 1

First of all, here is a Pictorial Representation of InnoDB (courtesy of Percona CTO Vadim Tkachenko)

Strictly in terms of InnoDB, there is only lose data on power failure if certain settings as disabled.

There are two parts of the InnoDB Storage that, if disabled, can cause data loss on power failure.

Double Write Buffer

A record of all data and index pages that are supposed to be written back to the physical files of InnoDB table are written in the Double Write Buffer beforehand. The Double Write Buffer is contained with the system tablespace, better know as ibdata1. That way, in the event of a crash, the InnoDB Storage Engine has something to refer to during crash recovery. To ensure that the Double Write Buffer's pages are there for you after a MySQL crash, always leave innodb_doublewrite set to 1 (default) and do not start mysqld with --skip-innodb_doublewrite.

Transaction Log Flushing

The Log Buffer is used to collect changes to be written to the Transaciton Log Files (ib_logfile0, ib_logfile1). The default flushing behavior (innodb_flush_log_at_trx_commit = 1) is to flush the contents of the InnoDB log buffer to the log file at each transaction commit and the log file flushed to disk. Some change innodb_flush_log_at_trx_commit to 0 or 2 to increase InnoDB write performance. However, changing innodb_flush_log_at_trx_commit to anything other than 1 makes it possible to lose 1 second's worth of transaction upon an OS crash or power outage.

Your Comment

The only way to see data that is not committed would be to start your transactions with READ UNCOMMITTED isolation and never run COMMIT. You should always commit your transactions. Days of rollback info sounds like an abuse of InnoDB, your data, and your application.

SUMMARY

Although these are the default settings, you should make sure these settings are in place

[mysqld]
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1
auto_commit = 1