InnoDB Website

First check that InnoDB is not disabled in your my.cnf or my.ini file by typing:

 mysql> show variables like '%inno%';
 _________________________________ ________________________
| Variable_name                   | Value                  |
 _________________________________ ________________________
| have_innodb                     | YES                    |
...

You can create tables in the InnoDB format by specifying ENGINE=INNODB after the table creation statement:

CREATE TABLE t (a INT NOT NULL, b INT, PRIMARY KEY (a)) ENGINE=INNODB;

To alter a MyISAM table to the InnoDB format do:

ALTER TABLE yourtablename ENGINE=INNODB;

If you do not want to use a binary distribution, but want to compile MySQL/InnoDB yourself, download a source tarball from the MySQL download page. After extracting the files, go to the main directory of MySQL (usually named something like mysql-5.0.27), and type:

$ ./configure --with-innodb
$ make

On MySQL 5.1, ./configure expects a different parameter:

$ ./configure --with-plugins=innobase
$ make

Finally, you will have to install MySQL. For information on this, please consult the MySQL Reference
Manual.

The InnoDB Plugin can create primary key (clustered) and secondary indexes more quickly than the built-in InnoDB, by sorting the data rather than inserting rows one at a time into an empty index. No special recovery is needed if the server crashes while creating a secondary index. However, some manual steps are needed to complete recovery if a system crash occurs while rebuilding the clustered (primary key) index of a table.

Please read Chapter 2 of the User’s Guide for the InnoDB Plugin for background on the process of creating indexes, and specifically Section 2.5 Crash Recovery for an overview of the crash recovery scenario. Detailed steps for completing recovery following a crash are presented here.

Release 1.0.2 of the InnoDB Plugin introduced 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. This tip documents the procedures for identifying and repairing tables that require repair.

Beginning with InnoDB Plugin 1.0.3, InnoDB will automatically take advantage of your platform’s capabilities for more efficient locking and mutexing. This tip explains some technical detils about the platform requirements and compilation process to enable this new enhancement.

When the InnoDB Plugin starts, it will write a message to the log file to indicate whether atomic instructions will be used for mutexes, for both mutexes and read/write locks, or not at all, as follows:

InnoDB: Mutexes and rw_locks use GCC atomic builtins.
InnoDB: Mutexes use GCC atomic builtins, rw_locks do not.
InnoDB: Neither mutexes nor rw_locks use GCC atomic builtins.


See this expanded tip or Section 7.2 of the documentation for more information.

If you do not specify --with-plugins=innobase when compiling MySQL then InnoDB will be compiled as a dynamic library (ha_innodb.so or ha_innodb.dll) that can be loaded later with the INSTALL PLUGIN command. Be sure to use the InnoDB Plugin with a MySQL that was compiled with the same ./configure flags and environment.

Sometimes the operating system or the hardware can corrupt a data file page. If the page is not used in InnoDB tables, then mysqld may run ok. But when you try to back up the tablespace with ibbackup, you get errors like those below and ibbackup refuses to make the backup.

ibbackup: Re-reading page at offset 0 3185082368 in /sqldata/mts/ibdata15
ibbackup: Re-reading page at offset 0 3185082368 in /sqldata/mts/ibdata15
ibbackup: Error: page at offset 0 3185082368 in /sqldata/mts/ibdata15 seems corrupt!

You can use the little C program, innodb_page_checksum_reset.c, to reset the lsn and the checksum fields in one page, so that ibbackup will no longer complain. Of course, your main task is to find out what is wrong with the OS or the hardware that causes corrupt pages to appear. This C program will not fix the corruption!