Table of Contents
There are times when you might want to use the InnoDB Plugin with a given database, and then downgrade to the built-in InnoDB in MySQL. One reason to do this is because you want to take advantage of a new InnoDB Plugin feature (such as “Fast Index Creation”), but revert to the standard built-in InnoDB in MySQL for production operation.
If you have created new tables using the InnoDB Plugin, you may
need to convert them to a format that the built-in InnoDB in MySQL can read.
Specifically, if you have created tables that use
ROW_FORMAT=COMPRESSED
or ROW_FORMAT=DYNAMIC
you must convert
them to a different format, if you plan to access these tables
with the built-in InnoDB in MySQL. If you do not do so, anomalous results may
occur.
Although InnoDB checks the format of tables and database files
(specifically *.ibd
files) for compatibility,
it is unable to start if there are buffered changes for “too
new format” tables in the redo log or in the system
tablespace. Thus it is important to carefully follow these
procedures when downgrading from the InnoDB Plugin to the
built-in InnoDB in MySQL, version 5.1.
This chapter describes the downgrade scenario, and the steps you should follow to ensure correct processing of your database.
Starting with version 5.0.21, the built-in InnoDB in MySQL checks the table type before opening a table. Until now, all InnoDB tables have been tagged with the same type, although some changes to the format have been introduced in MySQL versions 4.0, 4.1, and 5.0.
One of the important new features introduced with the InnoDB Plugin is support for identified file formats. This allows the InnoDB Plugin and versions of InnoDB since 5.0.21 to check for file compatibility. It also allows the user to preclude the use of features that would generate downward incompatibilities. By paying attention to the file format used, you can protect your database from corruptions, and ensure a smooth downgrade process.
In general, before using a database file created with the
InnoDB Plugin with the built-in InnoDB in MySQL you should verify that the
tablespace files (the *.ibd
files) are
compatible with the built-in InnoDB in MySQL. The InnoDB Plugin can read and
write tablespaces in both the formats “Antelope” and
“Barracuda”. The built-in InnoDB can only read and write
tablespaces in “Antelope” format. To make all tablespaces
“legible” to the built-in InnoDB in MySQL, you should follow the
instructions in Section 11.3, “How to Downgrade”
to reformat all tablespaces to be in the “Antelope” format.
Generally, after a “slow” shutdown of the
InnoDB Plugin (innodb_fast_shutdown=0
), it
should be safe to open the data files with the built-in InnoDB in MySQL. See
Section 11.4, “Possible Problems” for a discussion of
possible problems that can arise in this scenario and workarounds
for them.
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, the
corresponding tablespaces in the new “Barracuda” file format,
and it is 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 copies 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, review the configuration files. Changes to the startup options do not take effect until the server is restarted, or the InnoDB Plugin is uninstalled and reinstalled.
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
. See
Section C.1, “New Parameters” for a complete list of
new configuration parameters in the InnoDB Plugin. You can
include these parameters in the configuration file, only if you
use the loose_
form of the parameter names,
so that the built-in InnoDB in MySQL can start.
If the InnoDB Plugin was installed as a dynamic plugin, the
startup option ignore_builtin_innodb
or skip_innodb
must
have been set to disable the built-in InnoDB in MySQL. These options must be
removed, so that the built-in InnoDB in MySQL is enabled the next time the
server is started.
If the InnoDB Plugin was loaded using plugin-load
option.
This option has to be removed too.
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 9.3, “Installing the Precompiled InnoDB Plugin as a Shared Library”.
Issue the command UNINSTALL PLUGIN
for every
“plugin” supplied by the library
ha_innodb_plugin.so
(or
ha_innodb_plugin.dll
on Windows). Note that
the following commands 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 are actually deleted from the database, so that they are not 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 9.4, “Building the InnoDB Plugin from Source Code”, you have a
special version of the mysqld
executable that
contains the InnoDB Plugin. To “uninstall” the
InnoDB Plugin, you 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.
Failure to follow the downgrading procedure described in Section 11.3, “How to Downgrade” may lead to compatibility issues when files written by the InnoDB Plugin are accessed by the built-in InnoDB in MySQL. 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 Chapter 9, Installing the InnoDB Plugin and then follow the downgrading procedure described in Section 11.3, “How to Downgrade”.
In the future, the file format management features described in Chapter 4, InnoDB File-Format Management will guard against the types of problems described in this section.
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
'
.
Furthermore, a message “test.t
' doesn't existunknown table
type
” appears 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 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.
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. For more
information, see the MySQL manual on
Forcing
InnoDB Recovery.
Secondary indexes are usually nonunique, 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 nonunique 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 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 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 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.
In the InnoDB Plugin 1.0.3 and later, you can disable the
buffering of new operations by setting the parameter
innodb_change_buffering
. See
Section 7.4, “Controlling InnoDB Insert Buffering” for
details.