11.4.2. Issues with UNDO and REDO

As noted in Section 11.3, “How to Downgrade”, you should ensure a slow shutdown is done with the InnoDB Plugin, before running with the built-in InnoDB in MySQL, to clean up all buffers. To initiate a slow shutdown, execute the command SET GLOBAL innodb_fast_shutdown=0 before initiating the shutdown of the InnoDB Plugin.

We recommend slow shutdown (innodb_fast_shutdown=0) because the InnoDB Plugin may write special records to the transaction undo log that cause problems if the built-in InnoDB in MySQL attempts to read the log. Specifically, these special records are written when a record in a COMPRESSED or DYNAMIC table is updated or deleted and the record contains columns stored off-page. The built-in InnoDB in MySQL cannot read these undo log records. Also, the built-in InnoDB in MySQL cannot roll back incomplete transactions that affect tables that it is unable to read (tables in COMPRESSED or DYNAMIC format).

Note that a normal shutdown does not necessarily empty the undo log. A normal shutdown occurs when innodb_fast_shutdown=1, the default. When InnoDB is shut down, some active transactions may have uncommitted modifications, or they may be holding a read view that prevents the purging of some version information from the undo log. The next time InnoDB is started after a normal shutdown (innodb_fast_shutdown=1), it rolls back any incomplete transactions and purge old version information. Therefore, it is important to perform a slow shutdown (innodb_fast_shutdown=0) as part of the downgrade process.

In case it is not possible to have the InnoDB Plugin clear the undo log, you can prevent the built-in InnoDB in MySQL from accessing the undo log by setting innodb_force_recovery=3. However, this is not a recommended approach, since in addition to preventing the purge of old versions, this recovery mode prevents the rollback of uncommitted transactions. For more information, see the MySQL manual on Forcing InnoDB Recovery.

When it comes to downgrading, there are also considerations with respect to redo log information. For the purpose of crash recovery, InnoDB writes to the log files information about every modification to the data files. When recording changes to tables that were created in DYNAMIC or COMPRESSED format, the InnoDB Plugin writes redo log entries that cannot be recognized by the built-in InnoDB in MySQL. The built-in InnoDB in MySQL refuses to start if it sees any unknown entries in the redo log.

When InnoDB is shut down cleanly, it flushes all unwritten changes from the buffer pool to the data files and makes a checkpoint in the redo log. When InnoDB is subsequently restarted, it scans the redo log starting from the last checkpoint. After a clean shutdown, InnoDB crash recovery only then sees the end-of-log marker in the redo log. In this case, the built-in InnoDB in MySQL would not see any unrecognizable redo log entries. This is a second reason why you should ensure a clean, slow shutdown of MySQL (innodb_fast_shutdown=0) before you attempt a downgrade.

In an emergency, you may prevent the redo log scan and the crash recovery from the redo log by setting the parameter innodb_force_recovery=6. However, this is strongly discouraged, because may lead into severe corruption. See the MySQL manual on Forcing InnoDB Recovery for more information.

Download this Manual
PDF (US Ltr) - 1.3Mb
PDF (A4) - 1.3Mb
EPUB - 269.7Kb
HTML Download (TGZ) - 209.6Kb
HTML Download (Zip) - 234.2Kb
User Comments
Sign Up Login You must be logged in to post a comment.