The built-in InnoDB in MySQL can access only tables in the “Antelope” file format, that is, in the REDUNDANT or COMPACT row format. If you have created tables in COMPRESSED or DYNAMIC format, then they will use tablespaces in the new “Barracuda” file format, and it will be necessary to downgrade these tables.

First, identify the tables that require conversion, by executing this command:

SELECT table_schema, table_name, row_format
FROM information_schema.tables
WHERE engine='innodb'
AND row_format NOT IN ('Redundant', 'Compact');

Next, for each table that requires conversion, run the following command:

ALTER TABLE table_name ROW_FORMAT=COMPACT;

This command will copy the table and its indexes to a new tablespace in the “Antelope” format. See Chapter 2, Fast Index Creation in the InnoDB Storage Engine for a discussion of exactly how such index creation operations are performed.

Before you shut down the InnoDB Plugin and start the basic built-in InnoDB in MySQL, you should review the configuration files. Changes to the startup options will not take effect until the server is restarted.

The InnoDB Plugin introduces several configuration parameters that are not recognized by the built-in InnoDB in MySQL, including: innodb_file_format, innodb_file_format_check, and innodb_strict_mode. You must remove these parameters from the configuration file, so that the built-in InnoDB in MySQL will be able to start.

If the InnoDB Plugin was installed as a dynamic plugin, the startup option skip_innodb must have been set to disable the built-in InnoDB in MySQL. This option has to be removed, so that the built-in InnoDB in MySQL will be enabled the next time the server is started.

In MySQL, configuration options can be specified in the mysqld command line or the option file (my.cnf or my.ini). See the MySQL manual on Using Option Files for more information.

The following applies if the InnoDB Plugin was installed as a dynamic library with the INSTALL PLUGIN command, as described in Section 8.2, “Dynamically Installing the Binary InnoDB Plugin”.

Issue the command UNINSTALL PLUGIN for every “plugin” supplied by the library ha_innodb.so (or ha_innodb.dll on Windows). Note that the following commands will initiate a shutdown of the InnoDB Plugin:

SET GLOBAL innodb_fast_shutdown=0;
UNINSTALL PLUGIN INNODB;
UNINSTALL PLUGIN INNODB_CMP;
UNINSTALL PLUGIN INNODB_CMP_RESET;
UNINSTALL PLUGIN INNODB_CMPMEM;
UNINSTALL PLUGIN INNODB_CMPMEM_RESET;
UNINSTALL PLUGIN INNODB_TRX;
UNINSTALL PLUGIN INNODB_LOCKS;
UNINSTALL PLUGIN INNODB_LOCK_WAITS;

Due to MySQL Bug #33731, please ensure that the plugin definitions were actually deleted from the database, so that they will not be loaded again:

SELECT * FROM mysql.plugin;
DELETE FROM mysql.plugin WHERE name='…';

Restart the server. For the details of the shutdown procedure, see the MySQL manual on The Shutdown Process.

If you have built MySQL from source code and replaced the built-in InnoDB in MySQL with the InnoDB Plugin in the source tree as discussed in Section 8.3, “Building the InnoDB Plugin from Source Code”, you will have a special version of the mysqld executable that contains the InnoDB Plugin. To “uninstall” the InnoDB Plugin, you will replace this executable with something that is built from an unmodified MySQL source code distribution.

Before shutting down the version of the MySQL server with built-in InnoDB Plugin, you must enable “slow” shutdown:

SET GLOBAL innodb_fast_shutdown=0;

For the details of the shutdown procedure, see the MySQL manual on The Shutdown Process.

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 will result in ERROR 1146 (42S02): Table 'test.t' doesn't exist. Furthermore, a message “unknown table type” will appear in the error log.

In the InnoDB Plugin, you may rebuild an incompatible table by issuing a statement ALTER TABLE table_name ROW_FORMAT=COMPACT.

As noted in Section 10.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, in order 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.

The reason “slow” shutdown (innodb_fast_shutdown=0) is recommended is because the InnoDB Plugin may write special records to the transaction undo log that will cause problems if the built-in InnoDB in MySQL attempts to read the log. Specifically, these special records will be 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. Furthermore, the built-in InnoDB in MySQL will fail to roll back incomplete transactions that affect tables that it is unable to read (tables in COMPRESSED or DYNAMIC format).

Note that a “normal” shutdown will 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 will roll 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 will prevent 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 will write 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 will refuse to start if it sees any unknown entries in the redo log.

When InnoDB is shut down cleanly, it will flush all unwritten changes from the buffer pool to the data files and make a checkpoint in the redo log. When InnoDB is subsequently restarted, it will scan the redo log starting from the last checkpoint. After a clean shutdown, InnoDB crash recovery will only then see 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.

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 will fail to recognize such pages, and it will assume that compressed pages in the doublewrite buffer are corrupted. It will also wrongly assume that the tablespace (the .ibd file) will consist 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 will not be 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. For more information, see the MySQL manual on Forcing InnoDB Recovery.

Secondary indexes are usually non-unique, and insertions into 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 record is inserted into a non-unique secondary index page that is not in the buffer pool, InnoDB will insert 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 will also occur whenever a secondary index page is loaded to the buffer pool.

A “normal” shutdown will 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 Plugin 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 will merge 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 Plugin.

To disable insert buffer merges, you may 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 the MySQL manual on Forcing InnoDB Recovery.