InnoDB Website

Plugin: Fixing Compressed Tables Created Before InnoDB Plugin 1.0.2

The following information is presented to help users who have created compressed tables using InnoDB Plugin Versions 1.0.0 or 1.0.1. As documented in the InnoDB Plugin manual, Section 9.3 “Converting Compressed Tables Created Before Version 1.0.2″, release 1.0.2 introduces an incompatible format for compressed tables. A problem can occur when the new InnoDB Plugin tries to purge deleted records from, or merge buffered inserts to, compressed tables. However, not all compressed tables will need to be rebuilt. This tip documents the procedures for identifying and repairing tables that do require the procedure.

If your existing database contains tables created with a previous release of the InnoDB Plugin you must first perform a “slow” shutdown of the MySQL server using the old InnoDB Plugin. SET GLOBAL innodb_fast_shutdown=0 before shutting down the old instance of the InnoDB Plugin.

After starting the MySQL server with the upgraded InnoDB Plugin, you must see if any compressed tables will have to be converted. First, enable InnoDB strict mode for more careful error checking: SET SESSION innodb_strict_mode=1. Then, for each compressed table, try to create an identical new table. You should perform the following steps:

1. List the compressed tables:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine=’innodb’ AND row_format=’COMPRESSED’;

2. For each table, display the table definition:
   SHOW CREATE TABLE table_schema.table_name\G
3. Re-issue the CREATE TABLE statement with a different table name.
4. If the table creation succeeds, drop the newly created table and proceed to the next compressed table.
5. If the table creation fails, try with a bigger KEY_BLOCK_SIZE until the creation succeeds. Then drop the newly created table and execute ALTER TABLE on the original table, using the KEY_BLOCK_SIZE with which the CREATE TABLE succeeded.

If it is unacceptable to increment the KEY_BLOCK_SIZE of some particular table, you may be able to recreate the table with shorter column prefix indexes, or to eliminate some of them. The problem is that column prefixes used in indexes (such the 100-byte prefix in CREATE INDEX idx ON t1 (c1(100))) take up a lot of room in B-tree nodes. A shorter prefix can reduce the selectivity of the index, but it also makes
the index records shorter, so that they fit within the page size. The shorter prefix may also mean more index entries fit within the B-tree node, making it more efficient.

This procedure will be more complex if you have referential constraints between tables, and a preferable approach may be to use mysqldump with the old InnoDB Plugin and load the data into a new database using InnoDB Plugin 1.0.2.