13.7.11.4. Possible Problems

Failure to follow the downgrading procedure described in Section 13.7.11.3, “How to Downgrade” may lead to compatibility issues when files written by InnoDB 1.1 or the InnoDB Plugin are accessed by an earlier version of InnoDB. This section describes some internal recovery algorithms, to help explain why it is important to follow the downgrade procedure described above. It discusses the issues that may arise, and covers possible ways to fix them.

A general fix is to install the plugin as described in Section 13.7.9, “Installing the InnoDB Storage Engine” and then follow the downgrading procedure described in Section 13.7.11.3, “How to Downgrade”.

In the future, the file format management features described in Section 13.7.4, “InnoDB File Format Management” will guard against the types of problems described in this section.

13.7.11.4.1. Accessing COMPRESSED or DYNAMIC Tables

The built-in InnoDB in MySQL can only open tables that were created in REDUNDANT or COMPACT format. Starting with MySQL version 5.0.21, an attempt to open a table in some other format results in ERROR 1146 (42S02): Table 'test.t' doesn't exist. Also, a message “unknown table type” appears in the error log.

In InnoDB 1.1, you can rebuild an incompatible table by issuing a statement ALTER TABLE table_name ROW_FORMAT=COMPACT.

13.7.11.4.2. Issues with UNDO and REDO

As noted in Section 13.7.11.3, “How to Downgrade”, you should ensure a “slow” shutdown is done with the InnoDB storage engine, 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 storage engine.

We recommend “slow” shutdown (innodb_fast_shutdown=0) because the InnoDB storage engine 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 storage engine 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 Section 13.6.7.2, “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 storage engine 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 it may lead into severe corruption. See Section 13.6.7.2, “Forcing InnoDB Recovery” for more information.

13.7.11.4.3. Issues with the Doublewrite Buffer

InnoDB uses a novel file flush technique called doublewrite. Before writing pages to a data file, InnoDB first writes them to a contiguous area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer have completed does InnoDB write the pages to their proper positions in the data file. If the operating system crashes in the middle of a page write, InnoDB can later find a good copy of the page from the doublewrite buffer during recovery.

The doublewrite buffer may also contain compressed pages. However, the built-in InnoDB in MySQL cannot recognize such pages, and it assumes that compressed pages in the doublewrite buffer are corrupted. It also wrongly assumes that the tablespace (the .ibd file) consists of 16K byte pages. Thus, you may find InnoDB warnings in the error log of the form “a page in the doublewrite buffer is not within space bounds”.

The doublewrite buffer is not scanned after a clean shutdown. In an emergency, you may prevent crash recovery by setting innodb_force_recovery=6. However, this is strongly discouraged, because it may lead into severe corruption. See Section 13.6.7.2, “Forcing InnoDB Recovery” for more information.

13.7.11.4.4. Issues with the Insert Buffer

Secondary indexes are usually non-unique, and DML operations on secondary indexes happen in a relatively random order. This would cause a lot of random disk I/O operations without a special mechanism used in InnoDB called the insert buffer.

When a change is made to a non-unique secondary index page that is not in the buffer pool, InnoDB inserts the record into a special B-tree: the insert buffer. Periodically, the insert buffer is merged into the secondary index trees in the database. A merge also occurs whenever a secondary index page is loaded to the buffer pool.

A “normal” shutdown does not clear the insert buffer. A normal shutdown occurs when innodb_fast_shutdown=1, the default. If the insert buffer is not empty when the InnoDB storage engine is shut down, it may contain changes for tables in DYNAMIC or COMPRESSED format. Thus, starting the built-in InnoDB in MySQL on the data files may lead into a crash if the insert buffer is not empty.

A “slow” shutdown merges all changes from the insert buffer. To initiate a slow shutdown, execute the command SET GLOBAL innodb_fast_shutdown=0 before initiating the shutdown of the InnoDB storage engine.

To disable insert buffer merges, set innodb_force_recovery=4 so that you can back up the uncompressed tables with the built-in InnoDB in MySQL. Be sure not to use any WHERE conditions that would require access to secondary indexes. For more information, see Section 13.6.7.2, “Forcing InnoDB Recovery”.

In the InnoDB storage engine 1.0.3 and later, you can disable the buffering of new operations by setting the parameter innodb_change_buffering. See Section 13.7.7.4, “Controlling InnoDB Change Buffering” for details.

Copyright © 2010-2024 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout