Chapter 13. Storage Engines
Prev |  | Next

## Chapter 13. Storage Engines

MySQL supports several storage engines that act as handlers for different table types. MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables:

- The original storage engine was `ISAM`, which managed non-transactional tables. This engine has been replaced by `MyISAM` and should no longer be used. It is deprecated in MySQL 4.1, and is removed in subsequent MySQL release series.

- In MySQL 3.23.0, the `MyISAM` and `HEAP` storage engines were introduced. `MyISAM` is an improved replacement for `ISAM`. The `HEAP` storage engine provides in-memory tables. The `MERGE` storage engine was added in MySQL 3.23.25. It allows a collection of identical `MyISAM` tables to be handled as a single table. All three of these storage engines handle non-transactional tables, and all are included in MySQL by default. Note that the `HEAP` storage engine has been renamed the `MEMORY` engine.

- The `InnoDB` and `BDB` storage engines that handle transaction-safe tables were introduced in later versions of MySQL 3.23. Both are available in source distributions as of MySQL 3.23.34a. `BDB` is included in MySQL-Max binary distributions on those operating systems that support it. `InnoDB` also is included in MySQL-Max binary distributions for MySQL 3.23. Beginning with MySQL 4.0, `InnoDB` is included by default in all MySQL binary distributions. In source distributions, you can enable or disable either engine by configuring MySQL as you like.

- The `EXAMPLE` storage engine was added in MySQL 4.1.3. It is a “stub” engine that does nothing. You can create tables with this engine, but no data can be stored in them or retrieved from them. The purpose of this engine is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.

- `NDB Cluster` is the storage engine used by MySQL Cluster to implement tables that are partitioned over many computers. It is available in source code distributions as of MySQL 4.1.2 and binary distributions as of MySQL-Max 4.1.3.

  MySQL Cluster is covered in a separate chapter of this Manual. See Chapter 14, MySQL Cluster, for more information.

- The `ARCHIVE` storage engine was added in MySQL 4.1.3. It is used for storing large amounts of data without indexes in a very small footprint.

- The `CSV` storage engine was added in MySQL 4.1.4. This engine stores data in text files using comma-separated values format.

- The `BLACKHOLE` storage engine was added in MySQL 4.1.11. This engine accepts but does not store data and retrievals always return an empty set.

This chapter describes each of the MySQL storage engines except for `NDB Cluster`, which is covered in Chapter 14, MySQL Cluster.

When you create a new table, you can specify which storage engine to use by adding an `ENGINE` or `TYPE` table option to the `CREATE TABLE` statement:

    CREATE TABLE t (i INT) ENGINE = INNODB;
    CREATE TABLE t (i INT) TYPE = MEMORY;

`ENGINE` is the preferred term, but cannot be used before MySQL 4.0.18. `TYPE` is available beginning with MySQL 3.23.0, the first version of MySQL for which multiple storage engines were available. `TYPE` is supported for backward compatibility but is deprecated.

If you omit the `ENGINE` or `TYPE` option, the default storage engine is used. Normally, this is `MyISAM`, but you can change it by using the `--default-storage-engine` or `--default-table-type` server startup option, or by setting the `default-storage-engine` or `default-table-type` option in the `my.cnf` configuration file.

You can set the default storage engine to be used during the current session by setting the `storage_engine` or `table_type` variable:

    SET storage_engine=MYISAM;
    SET table_type=BDB;

When MySQL is installed on Windows using the MySQL Configuration Wizard, the `InnoDB` storage engine can be selected as the default instead of `MyISAM`. See Section 2.3.4.6, “The Database Usage Dialog”.

To convert a table from one storage engine to another, use an `ALTER TABLE` statement that indicates the new engine:

    ALTER TABLE t ENGINE = MYISAM;
    ALTER TABLE t TYPE = BDB;

See Section 12.1.5, “`CREATE TABLE` Syntax”, and Section 12.1.2, “`ALTER TABLE` Syntax”.

If you try to use a storage engine that is not compiled in or that is compiled in but deactivated, MySQL instead creates a table using the default storage engine, usually `MyISAM)`. (Before MySQL, `MyISAM` is always used for unavailable storage engines.) type `MyISAM`. This behavior is convenient when you want to copy tables between MySQL servers that support different storage engines. (For example, in a replication setup, perhaps your master server supports transactional storage engines for increased safety, but the slave servers use only non-transactional storage engines for greater speed.)

This automatic substitution of the default storage engine for unavailable engines can be confusing for new MySQL users. In MySQL 4.1, a warning is generated when a storage engine is automatically changed.

For new tables, MySQL always creates an `.frm` file to hold the table and column definitions. The table's index and data may be stored in one or more other files, depending on the storage engine. The server creates the `.frm` file above the storage engine level. Individual storage engines create any additional files required for the tables that they manage.

A database may contain tables of different types. That is, tables need not all be created with the same storage engine.

Transaction-safe tables (TSTs) have several advantages over non-transaction-safe tables (NTSTs):

- They are safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.

- You can combine many statements and accept them all at the same time with the `COMMIT` statement (if autocommit is disabled).

- You can execute `ROLLBACK` to ignore your changes (if autocommit is disabled).

- If an update fails, all of your changes are reverted. (With non-transaction-safe tables, all changes that have taken place are permanent.)

- Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

You can combine transaction-safe and non-transaction-safe tables in the same statements to get the best of both worlds. However, although MySQL supports several transaction-safe storage engines, for best results, you should not mix different storage engines within a transaction with autocommit disabled. For example, if you do this, changes to non-transaction-safe tables still are committed immediately and cannot be rolled back. For information about this and other problems that can occur in transactions that use mixed storage engines, see Section 12.4.1, “`START TRANSACTION`, `COMMIT`, and `ROLLBACK` Syntax”.

Note that to use the `InnoDB` storage engine in MySQL 3.23, you must configure at least the `innodb_data_file_path` startup option. In 4.0 and up, `InnoDB` uses default configuration values if you specify none. See Section 13.2.4, “`InnoDB` Configuration”.

Non-transaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:

- Much faster

- Lower disk space requirements

- Less memory required to perform updates

## 13.1. The `MyISAM` Storage Engine

`MyISAM` is the default storage engine as of MySQL 3.23. It is based on the `ISAM` code but has many useful extensions.

Each `MyISAM` table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm` file stores the table format. The data file has an `.MYD` (`MYData`) extension. The index file has an `.MYI` (`MYIndex`) extension.

To specify explicitly that you want a `MyISAM` table, indicate that with an `ENGINE` table option:

    CREATE TABLE t (i INT) ENGINE = MYISAM;

The older term `TYPE` is supported as a synonym for `ENGINE` for backward compatibility, but `ENGINE` is the preferred term from MySQL 4.0.18 on and `TYPE` is deprecated.

Normally, the `ENGINE` or `TYPE` option is unnecessary; `MyISAM` is the default storage engine unless the default has been changed. To ensure that `MyISAM` is used in situations where the default might have been changed, specify the storage engine explicitly.

You can check or repair `MyISAM` tables with the mysqlcheck client or myisamchk utility. You can also compress `MyISAM` tables with myisampack to take up much less space. See Section 4.5.3, “mysqlcheck — A Table Maintenance and Repair Program”, Section 5.7.4.1, “Using myisamchk for Crash Recovery”, and Section 4.6.4, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.

The following characteristics of the `MyISAM` storage engine are improvements over the older `ISAM` engine:

- All data values are stored with the low byte first. This makes the data machine and operating system independent. The only requirements for binary portability are that the machine uses two's-complement signed integers and IEEE floating-point format. These requirements are widely used among mainstream machines. Binary compatibility might not be applicable to embedded systems, which sometimes have peculiar processors.

  There is no significant speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it takes little more processing to read an unaligned byte in order than in reverse order. Also, the code in the server that fetches column values is not time critical compared to other code.

- All numeric key values are stored with the high byte first to allow better index compression.

- Large files (up to 63-bit file length) are supported on filesystems and operating systems that support large files.

- The maximum number of indexes per table is 64 (32 before MySQL 4.1.2). This can be changed by changing the source and recompiling. The maximum number of columns per index is 16.

- The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used.

- Index files are usually much smaller with `MyISAM` than with `ISAM`. This means that `MyISAM` normally uses less system resources than `ISAM`, but needs more CPU time when inserting data into a compressed index.

- When rows are inserted in sorted order (as when you are using an `AUTO_INCREMENT` column), the index tree is split so that the high node only contains one key. This improves space utilization in the index tree.

- Internal handling of one `AUTO_INCREMENT` column per table is supported. `MyISAM` automatically updates this column for `INSERT/UPDATE`. This makes `AUTO_INCREMENT` columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted as they are with `ISAM`. (When an `AUTO_INCREMENT` column is defined as the last column of a multiple-column index, reuse of values deleted from the top of a sequence does occur.) The `AUTO_INCREMENT` value can be reset with `ALTER TABLE` or myisamchk.

- Dynamic-sized rows are much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted.

- `MyISAM` supports concurrent inserts: If a table has no free blocks in the middle of the data file, you can `INSERT` new rows into it at the same time that other threads are reading from the table. A free block can occur as a result of deleting rows or an update of a dynamic length row with more data than its current contents. When all free blocks are used up (filled in), future inserts become concurrent again. See Section 7.3.3, “Concurrent Inserts”.

- You can put the data file and index file on different directories to get more speed with the `DATA DIRECTORY` and `INDEX DIRECTORY` table options to `CREATE TABLE`. See Section 12.1.5, “`CREATE TABLE` Syntax”.

- `BLOB` and `TEXT` columns can be indexed.

- `NULL` values are allowed in indexed columns. This takes 0-1 bytes per key.

- As of MySQL 4.1, each character column can have a different character set.

- There is a flag in the `MyISAM` index file that indicates whether the table was closed correctly. If mysqld is started with the `--myisam-recover` option, `MyISAM` tables are automatically checked when opened, and are repaired if the table wasn't closed properly.

- myisamchk marks tables as checked if you run it with the `--update-state` option. myisamchk --fast checks only those tables that don't have this mark.

- myisamchk --analyze stores statistics for portions of keys, not only for whole keys as in `ISAM`.

- myisampack can pack `BLOB` and `VARCHAR` columns; pack_isam cannot.

`MyISAM` also supports the following features, which MySQL will be able to use in the near future:

- Support for a true `VARCHAR` type; a `VARCHAR` column starts with a length stored in one or two bytes.

- Tables with `VARCHAR` columns may have fixed or dynamic row length.

- The sum of the lengths of the `VARCHAR` and `CHAR` columns in a table may be up to 64KB.

- Arbitrary length `UNIQUE` constraints.

Additional resources

- A forum dedicated to the `MyISAM` storage engine is available at http://forums.mysql.com/list.php?21.

### 13.1.1. `MyISAM` Startup Options

The following options to mysqld can be used to change the behavior of `MyISAM` tables. For additional information, see Section 5.1.2, “Command Options”.

- `--myisam-recover=`mode``

  Set the mode for automatic recovery of crashed `MyISAM` tables.

- `--delay-key-write=ALL`

  Don't flush key buffers between writes for any `MyISAM` table.

  ### Note

  If you do this, you should not access `MyISAM` tables from another program (such as from another MySQL server or with myisamchk) when the tables are in use. Doing so risks index corruption. Using `--external-locking` does not eliminate this risk.

The following system variables affect the behavior of `MyISAM` tables. For additional information, see Section 5.1.3, “System Variables”.

- `bulk_insert_buffer_size`

  The size of the tree cache used in bulk insert optimization.

  ### Note

  This is a limit per thread!

- `myisam_max_extra_sort_file_size`

  Used to help MySQL to decide when to use the slow but safe key cache index creation method.

  ### Note

  This parameter is given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.

- `myisam_max_sort_file_size`

  The maximum size of the temporary file that MySQL is allowed to use while re-creating a `MyISAM` index (during `REPAIR TABLE`, `ALTER TABLE`, or `LOAD DATA INFILE`). If the file size would be larger than this value, the index is created using the key cache instead, which is slower. This variable was added in MySQL 3.23.37.

  ### Note

  The value is given in megabytes before 4.0.3 and in bytes thereafter.

- `myisam_sort_buffer_size`

  Set the size of the buffer used when recovering tables.

Automatic recovery is activated if you start mysqld with the `--myisam-recover` option. In this case, when the server opens a `MyISAM` table, it checks whether the table is marked as crashed or whether the open count variable for the table is not 0 and you are running the server with external locking disabled. If either of these conditions is true, the following happens:

- The server checks the table for errors.

- If the server finds an error, it tries to do a fast table repair (with sorting and without re-creating the data file).

- If the repair fails because of an error in the data file (for example, a duplicate-key error), the server tries again, this time re-creating the data file.

- If the repair still fails, the server tries once more with the old repair option method (write row by row without sorting). This method should be able to repair any type of error and has low disk space requirements.

MySQL Enterprise Subscribers to MySQL Enterprise Monitor receive notification if the `--myisam-recover` option has not been set. For more information see, http://www.mysql.com/products/enterprise/advisors.html.

If the recovery wouldn't be able to recover all rows from previously completed statements and you didn't specify `FORCE` in the value of the `--myisam-recover` option, automatic repair aborts with an error message in the error log:

    Error: Couldn't repair table: test.g00pages

If you specify `FORCE`, a warning like this is written instead:

    Warning: Found 344 of 354 rows when repairing ./test/g00pages

Note that if the automatic recovery value includes `BACKUP`, the recovery process creates files with names of the form ``tbl_name-datetime`.BAK`. You should have a cron script that automatically moves these files from the database directories to backup media.

### 13.1.2. Space Needed for Keys

`MyISAM` tables use B-tree indexes. You can roughly calculate the size for the index file as `(key_length+4)/0.67`, summed over all keys. This is for the worst case when all keys are inserted in sorted order and the table doesn't have any compressed keys.

String indexes are space compressed. If the first index part is a string, it is also prefix compressed. Space compression makes the index file smaller than the worst-case figure if a string column has a lot of trailing space or is a `VARCHAR` column that is not always used to the full length. Prefix compression is used on keys that start with a string. Prefix compression helps if there are many strings with an identical prefix.

In `MyISAM` tables, you can also prefix compress numbers by specifying the `PACK_KEYS=1` table option when you create the table. Numbers are stored with the high byte first, so this helps when you have many integer keys that have an identical prefix.

### 13.1.3. `MyISAM` Table Storage Formats

`MyISAM` supports three different storage formats. Two of them, fixed and dynamic format, are chosen automatically depending on the type of columns you are using. The third, compressed format, can be created only with the myisampack utility (see Section 4.6.4, “myisampack — Generate Compressed, Read-Only MyISAM Tables”).

When you use `CREATE TABLE` or `ALTER TABLE` for a table that has no `BLOB` or `TEXT` columns, you can force the table format to `FIXED` or `DYNAMIC` with the `ROW_FORMAT` table option.

See Section 12.1.5, “`CREATE TABLE` Syntax”, for information about `ROW_FORMAT`.

You can decompress (unpack) compressed `MyISAM` tables using myisamchk `--unpack`; see Section 4.6.2, “myisamchk — MyISAM Table-Maintenance Utility”, for more information.

#### 13.1.3.1. Static (Fixed-Length) Table Characteristics

Static format is the default for `MyISAM` tables. It is used when the table contains no variable-length columns (`VARCHAR`, `VARBINARY`, `BLOB`, or `TEXT`). Each row is stored using a fixed number of bytes.

Of the three `MyISAM` storage formats, static format is the simplest and most secure (least subject to corruption). It is also the fastest of the on-disk formats due to the ease with which rows in the data file can be found on disk: To look up a row based on a row number in the index, multiply the row number by the row length to calculate the row position. Also, when scanning a table, it is very easy to read a constant number of rows with each disk read operation.

The security is evidenced if your computer crashes while the MySQL server is writing to a fixed-format `MyISAM` file. In this case, myisamchk can easily determine where each row starts and ends, so it can usually reclaim all rows except the partially written one. Note that `MyISAM` table indexes can always be reconstructed based on the data rows.

Static-format tables have these characteristics:

- `CHAR` and `BINARY` columns are space-padded to the column width. This is also true for `NUMERIC` and `DECIMAL` columns.

- Very quick.

- Easy to cache.

- Easy to reconstruct after a crash, because rows are located in fixed positions.

- Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use `OPTIMIZE TABLE` or myisamchk -r.

- Usually require more disk space than dynamic-format tables.

#### 13.1.3.2. Dynamic Table Characteristics

Dynamic storage format is used if a `MyISAM` table contains any variable-length columns (`VARCHAR`, `VARBINARY`, `BLOB`, or `TEXT`), or if the table was created with the `ROW_FORMAT=DYNAMIC` table option.

Dynamic format is a little more complex than static format because each row has a header that indicates how long it is. A row can become fragmented (stored in non-contiguous pieces) when it is made longer as a result of an update.

You can use `OPTIMIZE TABLE` or myisamchk -r to defragment a table. If you have fixed-length columns that you access or change frequently in a table that also contains some variable-length columns, it might be a good idea to move the variable-length columns to other tables just to avoid fragmentation.

Dynamic-format tables have these characteristics:

- All string columns are dynamic except those with a length less than four.

- Each row is preceded by a bitmap that indicates which columns contain the empty string (for string columns) or zero (for numeric columns). Note that this does not include columns that contain `NULL` values. If a string column has a length of zero after trailing space removal, or a numeric column has a value of zero, it is marked in the bitmap and not saved to disk. Non-empty strings are saved as a length byte plus the string contents.

- Much less disk space usually is required than for fixed-length tables.

- Each row uses only as much space as is required. However, if a row becomes larger, it is split into as many pieces as are required, resulting in row fragmentation. For example, if you update a row with information that extends the row length, the row becomes fragmented. In this case, you may have to run `OPTIMIZE TABLE` or myisamchk -r from time to time to improve performance. Use myisamchk -ei to obtain table statistics.

- More difficult than static-format tables to reconstruct after a crash, because rows may be fragmented into many pieces and links (fragments) may be missing.

- The expected row length for dynamic-sized rows is calculated using the following expression:

      3
      + (number of columns + 7) / 8
      + (number of char columns)
      + (packed size of numeric columns)
      + (length of strings)
      + (number of NULL columns + 7) / 8

  There is a penalty of 6 bytes for each link. A dynamic row is linked whenever an update causes an enlargement of the row. Each new link is at least 20 bytes, so the next enlargement probably goes in the same link. If not, another link is created. You can find the number of links using myisamchk -ed. All links may be removed with `OPTIMIZE TABLE` or myisamchk -r.

#### 13.1.3.3. Compressed Table Characteristics

Compressed storage format is a read-only format that is generated with the myisampack tool.

All MySQL distributions as of version 3.23.19 include myisampack by default. (This version is when MySQL was placed under the GPL.) For earlier versions, myisampack was included only with licenses or support agreements, but the server still can read tables that were compressed with myisampack. Compressed tables can be uncompressed with myisamchk. (For the `ISAM` storage engine, compressed tables can be created with pack_isam and uncompressed with isamchk.)

Compressed tables have the following characteristics:

- Compressed tables take very little disk space. This minimizes disk usage, which is helpful when using slow disks (such as CD-ROMs).

- Each row is compressed separately, so there is very little access overhead. The header for a row takes up one to three bytes depending on the biggest row in the table. Each column is compressed differently. There is usually a different Huffman tree for each column. Some of the compression types are:

  - Suffix space compression.

  - Prefix space compression.

  - Numbers with a value of zero are stored using one bit.

  - If values in an integer column have a small range, the column is stored using the smallest possible type. For example, a `BIGINT` column (eight bytes) can be stored as a `TINYINT` column (one byte) if all its values are in the range from `-128` to `127`.

  - If a column has only a small set of possible values, the data type is converted to `ENUM`.

  - A column may use any combination of the preceding compression types.

- Can be used for fixed-length or dynamic-length rows.

### Note

While a compressed table is read only, and you cannot therefore update or add rows in the table, DDL (Data Definition Language) operations are still valid. For example, you may still use `DROP` to drop the table, and `TRUNCATE` to empty the table.

### 13.1.4. `MyISAM` Table Problems

The file format that MySQL uses to store data has been extensively tested, but there are always circumstances that may cause database tables to become corrupted. The following discussion describes how this can happen and how to handle it.

#### 13.1.4.1. Corrupted `MyISAM` Tables

Even though the `MyISAM` table format is very reliable (all changes to a table made by an SQL statement are written before the statement returns), you can still get corrupted tables if any of the following events occur:

- The mysqld process is killed in the middle of a write.

- An unexpected computer shutdown occurs (for example, the computer is turned off).

- Hardware failures.

- You are using an external program (such as myisamchk) to modify a table that is being modified by the server at the same time.

- A software bug in the MySQL or `MyISAM` code.

Typical symptoms of a corrupt table are:

- You get the following error while selecting data from the table:

      Incorrect key file for table: '...'. Try to repair it

- Queries don't find rows in the table or return incomplete results.

You can check the health of a `MyISAM` table using the `CHECK TABLE` statement, and repair a corrupted `MyISAM` table with `REPAIR TABLE`. When mysqld is not running, you can also check or repair a table with the myisamchk command. See Section 12.5.2.3, “`CHECK TABLE` Syntax”, Section 12.5.2.6, “`REPAIR TABLE` Syntax”, and Section 4.6.2, “myisamchk — MyISAM Table-Maintenance Utility”.

If your tables become corrupted frequently, you should try to determine why this is happening. The most important thing to know is whether the table became corrupted as a result of a server crash. You can verify this easily by looking for a recent `restarted mysqld` message in the error log. If there is such a message, it is likely that table corruption is a result of the server dying. Otherwise, corruption may have occurred during normal operation. This is a bug. You should try to create a reproducible test case that demonstrates the problem. See Section A.1.4.2, “What to Do If MySQL Keeps Crashing”, and MySQL Internals: Porting.

MySQL Enterprise Find out about problems before they occur. Subscribe to the MySQL Enterprise Monitor for expert advice about the state of your servers. For more information see, http://www.mysql.com/products/enterprise/advisors.html.

#### 13.1.4.2. Problems from Tables Not Being Closed Properly

Each `MyISAM` index file (`.MYI` file) has a counter in the header that can be used to check whether a table has been closed properly. If you get the following warning from `CHECK TABLE` or myisamchk, it means that this counter has gone out of sync:

    clients are using or haven't closed the table properly

This warning doesn't necessarily mean that the table is corrupted, but you should at least check the table.

The counter works as follows:

- The first time a table is updated in MySQL, a counter in the header of the index files is incremented.

- The counter is not changed during further updates.

- When the last instance of a table is closed (because a `FLUSH TABLES` operation was performed or because there is no room in the table cache), the counter is decremented if the table has been updated at any point.

- When you repair the table or check the table and it is found to be okay, the counter is reset to zero.

- To avoid problems with interaction with other processes that might check the table, the counter is not decremented on close if it was zero.

In other words, the counter can become incorrect only under these conditions:

- A `MyISAM` table is copied without first issuing `LOCK TABLES` and `FLUSH TABLES`.

- MySQL has crashed between an update and the final close. (Note that the table may still be okay, because MySQL always issues writes for everything between each statement.)

- A table was modified by myisamchk --recover or myisamchk --update-state at the same time that it was in use by mysqld.

- Multiple mysqld servers are using the table and one server performed a `REPAIR TABLE` or `CHECK TABLE` on the table while it was in use by another server. In this setup, it is safe to use `CHECK TABLE`, although you might get the warning from other servers. However, `REPAIR TABLE` should be avoided because when one server replaces the data file with a new one, this is not known to the other servers.

  In general, it is a bad idea to share a data directory among multiple servers. See Section 5.8, “Running Multiple MySQL Servers on the Same Machine”, for additional discussion.

## 13.2. The `InnoDB` Storage Engine

### 13.2.1. `InnoDB` Overview

`InnoDB` provides MySQL with a transaction-safe (`ACID` compliant) storage engine that has commit, rollback, and crash recovery capabilities. `InnoDB` does locking on the row level and also provides an Oracle-style consistent non-locking read in `SELECT` statements. These features increase multi-user concurrency and performance. There is no need for lock escalation in `InnoDB` because row-level locks fit in very little space. `InnoDB` also supports `FOREIGN KEY` constraints. You can freely mix `InnoDB` tables with tables from other MySQL storage engines, even within the same statement.

`InnoDB` has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

Fully integrated with MySQL Server, the `InnoDB` storage engine maintains its own buffer pool for caching data and indexes in main memory. `InnoDB` stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, `MyISAM` tables where each table is stored using separate files. `InnoDB` tables can be of any size even on operating systems where file size is limited to 2GB.

`InnoDB` is included in binary distributions by default as of MySQL 4.0. For information about `InnoDB` support in MySQL 3.23, see Section 13.2.3, “`InnoDB` in MySQL 3.23”. Starting from MySQL 4.1.5, the improved Windows installer makes `InnoDB` the MySQL default storage engine on Windows.

`InnoDB` is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on `InnoDB`. Mytrix, Inc. stores over 1TB of data in `InnoDB`, and another site handles an average load of 800 inserts/updates per second in `InnoDB`.

`InnoDB` is published under the same GNU GPL License Version 2 (of June 1991) as MySQL. For more information on MySQL licensing, see http://www.mysql.com/company/legal/licensing/.

A forum dedicated to the `InnoDB` storage engine is available at http://forums.mysql.com/list.php?22.

### 13.2.2. `InnoDB` Contact Information

Contact information for Innobase Oy, producer of the `InnoDB` engine:

    Web site: http://www.innodb.com/
    Email: <sales@innodb.com>
    Phone: +358-9-6969 3250 (office)
           +358-40-5617367 (mobile)

    Innobase Oy Inc.
    World Trade Center Helsinki
    Aleksanterinkatu 17
    P.O.Box 800
    00101 Helsinki
    Finland

### 13.2.3. `InnoDB` in MySQL 3.23

Beginning with MySQL 4.0, `InnoDB` is enabled by default, so the following information applies only to MySQL 3.23.

`InnoDB` tables are included in the MySQL source distribution starting from 3.23.34a and are activated in the MySQL-Max binaries of the 3.23 series. For Windows, the MySQL-Max binaries are included in the standard distribution.

If you have downloaded a binary version of MySQL that includes support for `InnoDB`, simply follow the instructions of the MySQL manual for installing a binary version of MySQL. If you have MySQL 3.23 installed, the simplest way to install MySQL-Max is to replace the executable mysqld server with the corresponding executable from the MySQL-Max distribution. MySQL and MySQL-Max differ only in the server executable. See Section 2.8, “Installing MySQL from `tar.gz` Packages on Other Unix-Like Systems”, and Section 5.2, “The mysqld-max Extended MySQL Server”.

To compile the MySQL source code with `InnoDB` support, download MySQL 3.23.34a or newer from http://www.mysql.com/ and configure MySQL with the `--with-innodb` option. See Section 2.9, “MySQL Installation Using a Source Distribution”.

To use `InnoDB` tables with MySQL 3.23, you must specify configuration parameters in the `[mysqld]` section of the `my.cnf` option file. On Windows, you can use `my.ini` instead. If you do not configure `InnoDB` in the option file, `InnoDB` does not start. (From MySQL 4.0 on, `InnoDB` uses default parameters if you do not specify any. However, to get best performance, it is still recommended that you use parameters appropriate for your system, as discussed in Section 13.2.4, “`InnoDB` Configuration”.)

In MySQL 3.23, you must specify at the minimum an `innodb_data_file_path` value to configure the `InnoDB` data files. For example, to configure `InnoDB` to use a single 500MB data file, place the following setting in the `[mysqld]` section of your option file:

    [mysqld]
    innodb_data_file_path=ibdata1:500M

`InnoDB` creates the `ibdata1` file in the MySQL data directory by default. To specify the location explicitly, specify an `innodb_data_home_dir` setting. See Section 13.2.4, “`InnoDB` Configuration”.

### 13.2.4. `InnoDB` Configuration

To enable `InnoDB` tables in MySQL 3.23, see Section 13.2.3, “`InnoDB` in MySQL 3.23”.

From MySQL 4.0 on, the `InnoDB` storage engine is enabled by default. If you don't want to use `InnoDB` tables, you can add the `skip-innodb` option to your MySQL option file.

### Note

`InnoDB` provides MySQL with a transaction-safe (`ACID` compliant) storage engine that has commit, rollback, and crash recovery capabilities. However, it cannot do so if the underlying operating system or hardware does not work as advertised. Many operating systems or disk subsystems may delay or reorder write operations to improve performance. On some operating systems, the very system call that should wait until all unwritten data for a file has been flushed — `fsync()` — might actually return before the data has been flushed to stable storage. Because of this, an operating system crash or a power outage may destroy recently committed data, or in the worst case, even corrupt the database because of write operations having been reordered. If data integrity is important to you, you should perform some “pull-the-plug” tests before using anything in production. On Mac OS X 10.3 and up, `InnoDB` uses a special `fcntl()` file flush method. Under Linux, it is advisable to disable the write-back cache.

On ATAPI hard disks, a command such `hdparm -W0 /dev/hda` may work to disable the write-back cache. Beware that some drives or disk controllers may be unable to disable the write-back cache.

Two important disk-based resources managed by the `InnoDB` storage engine are its tablespace data files and its log files.

### Note

If you specify no `InnoDB` configuration options, MySQL 4.0 and above create an auto-extending 10MB data file named `ibdata1` and two 5MB log files named `ib_logfile0` and `ib_logfile1` in the MySQL data directory. (In MySQL 4.0.0 and 4.0.1, the data file is 64MB and not auto-extending.) In MySQL 3.23, `InnoDB` does not start if you provide no configuration options. To get good performance, you should explicitly provide `InnoDB` parameters as discussed in the following examples. Naturally, you should edit the settings to suit your hardware and requirements.

The examples shown here are representative. See Section 13.2.5, “`InnoDB` Startup Options and System Variables” for additional information about `InnoDB`-related configuration parameters.

To set up the `InnoDB` tablespace files, use the `innodb_data_file_path` option in the `[mysqld]` section of the `my.cnf` option file. On Windows, you can use `my.ini` instead. The value of `innodb_data_file_path` should be a list of one or more data file specifications. If you name more than one data file, separate them by semicolon (“`;`”) characters:

    innodb_data_file_path=datafile_spec1[;datafile_spec2]...

For example, a setting that explicitly creates a tablespace having the same characteristics as the MySQL 4.0 default is as follows:

    [mysqld]
    innodb_data_file_path=ibdata1:10M:autoextend

This setting configures a single 10MB data file named `ibdata1` that is auto-extending. No location for the file is given, so by default, `InnoDB` creates it in the MySQL data directory.

Sizes are specified using `M` or `G` suffix letters to indicate units of MB or GB.

A tablespace containing a fixed-size 50MB data file named `ibdata1` and a 50MB auto-extending file named `ibdata2` in the data directory can be configured like this:

    [mysqld]
    innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

The full syntax for a data file specification includes the filename, its size, and several optional attributes:

    file_name:file_size[:autoextend[:max:max_file_size]]

The `autoextend` attribute and those following can be used only for the last data file in the `innodb_data_file_path` line. `autoextend` is available starting from MySQL 3.23.50 and 4.0.2.

If you specify the `autoextend` option for the last data file, `InnoDB` extends the data file if it runs out of free space in the tablespace. The increment is 8MB at a time by default. It can be modified by changing the `innodb_autoextend_increment` system variable.

If the disk becomes full, you might want to add another data file on another disk. Instructions for reconfiguring an existing tablespace are given in Section 13.2.8, “Adding and Removing `InnoDB` Data and Log Files”.

`InnoDB` is not aware of the filesystem maximum file size, so be cautious on filesystems where the maximum file size is a small value such as 2GB. To specify a maximum size for an auto-extending data file, use the `max` attribute. The following configuration allows `ibdata1` to grow up to a limit of 500MB:

    [mysqld]
    innodb_data_file_path=ibdata1:10M:autoextend:max:500M

`InnoDB` creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use the `innodb_data_home_dir` option. For example, to use two files named `ibdata1` and `ibdata2` but create them in the `/ibdata` directory, configure `InnoDB` like this:

    [mysqld]
    innodb_data_home_dir = /ibdata
    innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

### Note

`InnoDB` does not create directories, so make sure that the `/ibdata` directory exists before you start the server. This is also true of any log file directories that you configure. Use the Unix or DOS `mkdir` command to create any necessary directories.

`InnoDB` forms the directory path for each data file by textually concatenating the value of `innodb_data_home_dir` to the data file name, adding a pathname separator (slash or backslash) between values if necessary. If the `innodb_data_home_dir` option is not mentioned in `my.cnf` at all, the default value is the “dot” directory `./`, which means the MySQL data directory. (The MySQL server changes its current working directory to its data directory when it begins executing.)

If you specify `innodb_data_home_dir` as an empty string, you can specify absolute paths for the data files listed in the `innodb_data_file_path` value. The following example is equivalent to the preceding one:

    [mysqld]
    innodb_data_home_dir =
    innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

A simple `my.cnf` example. Suppose that you have a computer with 128MB RAM and one hard disk. The following example shows possible configuration parameters in `my.cnf` or `my.ini` for `InnoDB`. The example assumes the use of MySQL-Max 3.23.50 or later or MySQL 4.0.2 or later because it uses the `autoextend` attribute. The example suits most users, both on Unix and Windows, who do not want to distribute `InnoDB` data files and log files onto several disks. It creates an auto-extending data file `ibdata1` and two `InnoDB` log files `ib_logfile0` and `ib_logfile1` in the MySQL data directory. Also, the small archived `InnoDB` log file `ib_arch_log_0000000000` that `InnoDB` creates automatically ends up in the data directory.

    [mysqld]
    # You can write your other MySQL server options here
    # ...
    # Data files must be able to hold your data and indexes.
    # Make sure that you have enough free disk space.
    innodb_data_file_path = ibdata1:10M:autoextend
    #
    # Set buffer pool size to 50-80% of your computer's memory
    set-variable = innodb_buffer_pool_size=70M
    set-variable = innodb_additional_mem_pool_size=10M
    #
    # Set the log file size to about 25% of the buffer pool size
    set-variable = innodb_log_file_size=20M
    set-variable = innodb_log_buffer_size=8M
    #
    innodb_flush_log_at_trx_commit=1

Make sure that the MySQL server has the proper access rights to create files in the data directory. More generally, the server must have access rights in any directory where it needs to create data files or log files.

Note that data files must be less than 2GB in some filesystems. The combined size of the log files must be less than 4GB. The combined size of data files must be at least 10MB.

When you create an `InnoDB` tablespace for the first time, it is best that you start the MySQL server from the command prompt. `InnoDB` then prints the information about the database creation to the screen, so you can see what is happening. For example, on Windows, if mysqld is located in `C:\Program Files\MySQL\MySQL Server 4.1\bin`, you can start it like this:

    C:\> "C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld" --console

If you do not send server output to the screen, check the server's error log to see what `InnoDB` prints during the startup process.

See Section 13.2.6, “Creating the `InnoDB` Tablespace”, for an example of what the information displayed by `InnoDB` should look like.

You can place `InnoDB` options in the `[mysqld]` group of any option file that your server reads when it starts. The locations for option files are described in Section 4.2.2.2, “Using Option Files”.

If you installed MySQL on Windows using the installation and configuration wizards, the option file will be the `my.ini` file located in your MySQL installation directory. See Section 2.3.4.14, “The Location of the my.ini File”.

If your PC uses a boot loader where the `C:` drive is not the boot drive, your only option is to use the `my.ini` file in your Windows directory (typically `C:\WINDOWS` or `C:\WINNT`). You can use the `SET` command at the command prompt in a console window to print the value of `WINDIR`:

    C:\> SET WINDIR
    windir=C:\WINDOWS

If you want to make sure that mysqld reads options only from a specific file, you can use the `--defaults-file` option as the first option on the command line when starting the server:

    mysqld --defaults-file=your_path_to_my_cnf

An advanced `my.cnf` example. Suppose that you have a Linux computer with 2GB RAM and three 60GB hard disks at directory paths `/`, `/dr2` and `/dr3`. The following example shows possible configuration parameters in `my.cnf` for `InnoDB`.

    [mysqld]
    # You can write your other MySQL server options here
    # ...
    innodb_data_home_dir =
    #
    # Data files must be able to hold your data and indexes
    innodb_data_file_path = /ibdata/ibdata1:2000M;/dr2/ibdata/ibdata2:2000M:autoextend
    #
    # Set buffer pool size to 50-80% of your computer's memory,
    # but make sure on Linux x86 total memory usage is < 2GB
    set-variable = innodb_buffer_pool_size=1G
    set-variable = innodb_additional_mem_pool_size=20M
    innodb_log_group_home_dir = /dr3/iblogs
    #
    # innodb_log_arch_dir must be the same as innodb_log_group_home_dir
    # (starting from 4.0.6, you can omit it)
    innodb_log_arch_dir = /dr3/iblogs
    set-variable = innodb_log_files_in_group=2
    #
    # Set the log file size to about 25% of the buffer pool size
    set-variable = innodb_log_file_size=250M
    set-variable = innodb_log_buffer_size=8M
    #
    innodb_flush_log_at_trx_commit=1
    set-variable = innodb_lock_wait_timeout=50
    #
    # Uncomment the next lines if you want to use them
    #set-variable = innodb_thread_concurrency=5

In some cases, database performance improves if all data is not placed on the same physical disk. Putting log files on a different disk from data is very often beneficial for performance. The example illustrates how to do this. It places the two data files on different disks and places the log files on the third disk. `InnoDB` fills the tablespace beginning with the first data file. You can also use raw disk partitions (raw devices) as `InnoDB` data files, which may speed up I/O. See Section 13.2.4.2, “Using Raw Devices for the Shared Tablespace”.

### Warning

On 32-bit GNU/Linux x86, you must be careful not to set memory usage too high. `glibc` may allow the process heap to grow over thread stacks, which crashes your server. It is a risk if the value of the following expression is close to or exceeds 2GB:

    innodb_buffer_pool_size
    + key_buffer_size
    + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
    + max_connections*2MB

Each thread uses a stack (often 2MB, but only 256KB in MySQL AB binaries) and in the worst case also uses `sort_buffer_size + read_buffer_size` additional memory.

In MySQL 4.1, by compiling MySQL yourself, you can use up to 64GB of physical memory in 32-bit Windows. See the description for `innodb_buffer_pool_awe_mem_mb` in Section 13.2.5, “`InnoDB` Startup Options and System Variables”.

How to tune other mysqld server parameters? The following values are typical and suit most users:

    [mysqld]
    skip-external-locking
    set-variable = max_connections=200
    set-variable = read_buffer_size=1M
    set-variable = sort_buffer_size=1M
    #
    # Set key_buffer to 5 - 50% of your RAM depending on how much
    # you use MyISAM tables, but keep key_buffer_size + InnoDB
    # buffer pool size < 80% of your RAM
    set-variable = key_buffer_size=...

#### 13.2.4.1. Using Per-Table Tablespaces

### Note

There is a known bug in versions prior to 4.1.8 that manifests itself if you specify `innodb_file_per_table` in `my.cnf`. If you shut down mysqld, then records may disappear from the secondary indexes of a table. See Bug#7496 for more information and workarounds. This is fixed in 4.1.9, but another bug (Bug#8021) bit the Windows version in 4.1.9, and in the Windows version of 4.1.9 you must put the line `innodb_flush_method=unbuffered` to your `my.cnf` or `my.ini` to get mysqld to work.

Starting from MySQL 4.1.1, you can store each `InnoDB` table and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.

Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of the remaining `InnoDB` tables.

If you need to downgrade to 4.0, you must make table dumps and re-create the whole `InnoDB` tablespace. If you have not created new `InnoDB` tables under MySQL 4.1.1 or later, and need to downgrade quickly, you can also do a direct downgrade to the MySQL 4.0.18 or later in the 4.0 series. Before doing the direct downgrade to 4.0.x, you have to end all client connections to the mysqld server that is to be downgraded, and let it run the purge and insert buffer merge operations to completion, so that `SHOW INNODB STATUS` shows the main thread in the state `waiting for server activity`. Then you can shut down mysqld and start 4.0.18 or later in the 4.0 series.

You can enable multiple tablespaces by adding a line to the `[mysqld]` section of `my.cnf`:

    [mysqld]
    innodb_file_per_table

After restarting the server, `InnoDB` stores each newly created table into its own file ``tbl_name`.ibd` in the database directory where the table belongs. This is similar to what the `MyISAM` storage engine does, but `MyISAM` divides the table into a data file ``tbl_name`.MYD` and the index file ``tbl_name`.MYI`. For `InnoDB`, the data and the indexes are stored together in the `.ibd` file. The ``tbl_name`.frm` file is still created as usual.

If you remove the `innodb_file_per_table` line from `my.cnf` and restart the server, `InnoDB` creates tables inside the shared tablespace files again.

`innodb_file_per_table` affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using `.ibd` files, but you can still access tables that exist in the shared tablespace. If you remove the option and restart the server, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.

### Note

`InnoDB` always needs the shared tablespace because it puts its internal data dictionary and undo logs there. The `.ibd` files are not sufficient for `InnoDB` to operate.

### Note

You cannot freely move `.ibd` files between database directories as you can with `MyISAM` table files. This is because the table definition that is stored in the `InnoDB` shared tablespace includes the database name, and because `InnoDB` must preserve the consistency of transaction IDs and log sequence numbers.

To move an `.ibd` file and the associated table from one database to another, use a `RENAME TABLE` statement:

    RENAME TABLE db1.tbl_name TO db2.tbl_name;

If you have a “clean” backup of an `.ibd` file, you can restore it to the MySQL installation from which it originated as follows:

1. Issue this `ALTER TABLE` statement:

       ALTER TABLE tbl_name DISCARD TABLESPACE;

   ### Caution

   This statement deletes the current `.ibd` file.

2. Put the backup `.ibd` file back in the proper database directory.

3. Issue this `ALTER TABLE` statement:

       ALTER TABLE tbl_name IMPORT TABLESPACE;

In this context, a “clean” `.ibd` file backup means:

- There are no uncommitted modifications by transactions in the `.ibd` file.

- There are no unmerged insert buffer entries in the `.ibd` file.

- Purge has removed all delete-marked index records from the `.ibd` file.

- mysqld has flushed all modified pages of the `.ibd` file from the buffer pool to the file.

You can make a clean backup `.ibd` file using the following method:

1. Stop all activity from the mysqld server and commit all transactions.

2. Wait until `SHOW INNODB STATUS` shows that there are no active transactions in the database, and the main thread status of `InnoDB` is `Waiting for server activity`. Then you can make a copy of the `.ibd` file.

Another method for making a clean copy of an `.ibd` file is to use the commercial InnoDB Hot Backup tool:

1. Use InnoDB Hot Backup to back up the `InnoDB` installation.

2. Start a second mysqld server on the backup and let it clean up the `.ibd` files in the backup.

#### 13.2.4.2. Using Raw Devices for the Shared Tablespace

Starting from MySQL 3.23.41, you can use raw disk partitions as data files in the shared tablespace. By using a raw disk, you can perform non-buffered I/O on Windows and on some Unix systems without filesystem overhead, which might improve performance.

When you create a new data file, you must put the keyword `newraw` immediately after the data file size in `innodb_data_file_path`. The partition must be at least as large as the size that you specify. Note that 1MB in `InnoDB` is 1024 × 1024 bytes, whereas 1MB in disk specifications usually means 1,000,000 bytes.

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

The next time you start the server, `InnoDB` notices the `newraw` keyword and initializes the new partition. However, do not create or change any `InnoDB` tables yet. Otherwise, when you next restart the server, `InnoDB` reinitializes the partition and your changes are lost. (Starting from MySQL 3.23.44, as a safety measure `InnoDB` prevents users from modifying data when any partition with `newraw` is specified.)

After `InnoDB` has initialized the new partition, stop the server, change `newraw` in the data file specification to `raw`:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=/dev/hdd1:5Graw;/dev/hdd2:2Graw

Then restart the server and `InnoDB` allows changes to be made.

On Windows, starting from 4.1.1, you can allocate a disk partition as a data file like this:

    [mysqld]
    innodb_data_home_dir=
    innodb_data_file_path=//./D::10Gnewraw

The `//./` corresponds to the Windows syntax of `\\.\` for accessing physical drives.

When you use raw disk partitions, be sure that they have permissions that allow read and write access by the account used for running the MySQL server.

### 13.2.5. `InnoDB` Startup Options and System Variables

This section describes the `InnoDB`-related command options and system variables. System variables that take a numeric value can be specified as `--`var_name`=`value`` on the command line or as ``var_name`=`value`` in option files. Many of the system variables can be changed at runtime (see Section 5.1.4.2, “Dynamic System Variables”). (Before MySQL 4.0.2, system variable values should be specified using `--set-variable` syntax.) For more information on specifying options and system variables, see Section 4.2.2, “Specifying Program Options”.

### Note

It is not a good idea to configure `InnoDB` to use datafiles or logfiles on NFS volumes. Otherwise, the files might be locked by other processes and become unavailable for use by MySQL.

MySQL Enterprise The MySQL Enterprise Monitor provides expert advice on InnoDB start-up options and related system variables. For more information see, http://www.mysql.com/products/enterprise/advisors.html.

`InnoDB` command options:

- `--innodb`

  Enables the `InnoDB` storage engine, if the server was compiled with `InnoDB` support. Use `--skip-innodb` to disable `InnoDB`.

- `--innodb_status_file`

  Causes `InnoDB` to create a file named ``<datadir>`/innodb_status.`<pid>`` in the MySQL data directory. `InnoDB` periodically writes the output of `SHOW ENGINE INNODB STATUS` to this file. This option is available as of MySQL 4.0.21.

`InnoDB` system variables:

- `innodb_additional_mem_pool_size`

  The size in bytes of a memory pool `InnoDB` uses to store data dictionary information and other internal data structures. The more tables you have in your application, the more memory you need to allocate here. If `InnoDB` runs out of memory in this pool, it starts to allocate memory from the operating system, and writes warning messages to the MySQL error log. The default value is 1MB.

- `innodb_autoextend_increment`

  The increment size (in MB) for extending the size of an auto-extending tablespace when it becomes full. The default value is 8. This variable is available starting from MySQL 4.0.24 and 4.1.5. As of MySQL 4.0.24 and 4.1.6, it can be changed at runtime as a global system variable.

- `innodb_buffer_pool_awe_mem_mb`

  The size of the buffer pool (in MB), if it is placed in the AWE memory. This is relevant only in 32-bit Windows. If your 32-bit Windows operating system supports more than 4GB memory, using so-called “Address Windowing Extensions,” you can allocate the `InnoDB` buffer pool into the AWE physical memory using this variable. The maximum possible value for this variable is 63000. If it is greater than 0, `innodb_buffer_pool_size` is the window in the 32-bit address space of mysqld where `InnoDB` maps that AWE memory. A good value for `innodb_buffer_pool_size` is 500MB. This variable is available as of MySQL 4.1.0.

  To take advantage of AWE memory, you will need to recompile MySQL yourself. The current project settings needed for doing this can be found in the `innobase/os/os0proj.c` source file.

- `innodb_buffer_pool_size`

  The size in bytes of the memory buffer `InnoDB` uses to cache data and indexes of its tables. The larger you set this value, the less disk I/O is needed to access data in tables. On a dedicated database server, you may set this to up to 80% of the machine physical memory size. However, do not set it too large because competition for physical memory might cause paging in the operating system.

- `innodb_data_file_path`

  The paths to individual data files and their sizes. The full directory path to each data file is acquired by concatenating `innodb_data_home_dir` to each path specified here. The file sizes are specified in MB or GB (1024MB) by appending `M` or `G` to the size value. The sum of the sizes of the files must be at least 10MB. On some operating systems, files must be less than 2GB. If you do not specify `innodb_data_file_path`, the default behavior starting from 4.0 is to create a single 10MB auto-extending data file named `ibdata1`. Starting from 3.23.44, you can set the file size larger than 4GB on those operating systems that support big files. You can also use raw disk partitions as data files. See Section 13.2.4.2, “Using Raw Devices for the Shared Tablespace”.

- `innodb_data_home_dir`

  The common part of the directory path for all `InnoDB` data files. If you do not set this value, the default is the MySQL data directory. You can specify this also as an empty string, in which case you can use absolute file paths in `innodb_data_file_path`.

- `innodb_fast_shutdown`

  If you set this variable to 0, `InnoDB` does a full purge and an insert buffer merge before a shutdown. These operations can take minutes, or even hours in extreme cases. If you set this variable to 1, `InnoDB` skips these operations at shutdown. The default value is 1 starting from 3.23.50.

- `innodb_file_io_threads`

  The number of file I/O threads in `InnoDB`. Normally, this should be left at the default value of 4, but disk I/O on Windows may benefit from a larger number. On Unix, increasing the number has no effect; `InnoDB` always uses the default value. This variable is available as of MySQL 3.23.37.

- `innodb_file_per_table`

  ### Note

  A bug in versions <= 4.1.8 if you specify `innodb_file_per_table` in `my.cnf`! If you shut down mysqld, then records may disappear from the secondary indexes of a table. See Bug#7496 for more information and workarounds. This is fixed in 4.1.9, but another bug (Bug#8021) bit the Windows version in 4.1.9, and in the Windows version of 4.1.9 you must put the line `innodb_flush_method=unbuffered` in your `my.cnf` or `my.ini` to get mysqld to work.

  If this variable is enabled, `InnoDB` creates each new table using its own `.ibd` file for storing data and indexes, rather than in the shared tablespace. The default is to create tables in the shared tablespace. See Section 13.2.4.1, “Using Per-Table Tablespaces”. This variable is available as of MySQL 4.1.1.

- `innodb_flush_log_at_trx_commit`

  When `innodb_flush_log_at_trx_commit` is set to 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When this value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When set to 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

  The default value of this variable is 1, which is the value that is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. If you set the value to 0, then any mysqld process crash can erase the last second of transactions. If you set the value to 2, then only an operating system crash or a power outage can erase the last second of transactions. However, `InnoDB`'s crash recovery is not affected and thus crash recovery does work regardless of the value. Note that many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the `InnoDB` database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor. The default value of this variable is 1 (prior to MySQL 4.0.13, the default is 0).

  Note: For the greatest possible durability and consistency in a replication setup using `InnoDB` with transactions, you should use `innodb_flush_log_at_trx_commit=1`, `sync_binlog=1`, and `innodb_safe_binlog` in your master server `my.cnf` file.

- `innodb_flush_method`

  If set to `fdatasync` (the default), `InnoDB` uses `fsync()` to flush both the data and log files. If set to `O_DSYNC`, `InnoDB` uses `O_SYNC` to open and flush the log files, but uses `fsync()` to flush the data files. If `O_DIRECT` is specified (available on some GNU/Linux versions starting from MySQL 4.0.14), `InnoDB` uses `O_DIRECT` to open the data files, and uses `fsync()` to flush both the data and log files. Note that starting from MySQL 3.23.41, `InnoDB` uses `fsync()` instead of `fdatasync()`, and it does not use `O_DSYNC` by default because there have been problems with it on many varieties of Unix. This variable is relevant only for Unix. On Windows, the flush method is always `async_unbuffered` and cannot be changed. This variable is available as of MySQL 3.23.40.

  Different values of this variable can have a marked effect on `InnoDB performance`. For example, on some systems where `InnoDB` data and log files are located on a SAN, it has been found that setting `innodb_flush_method` to `O_DIRECT` can degrade performance of simple `SELECT` statements by a factor of three.

- `innodb_force_recovery`

  The crash recovery mode.

  ### Warning

  This variable should be set greater than 0 only in an emergency situation when you want to dump your tables from a corrupt database! Possible values are from 1 to 6. The meanings of these values are described in Section 13.2.9.1, “Forcing `InnoDB` Recovery”. As a safety measure, `InnoDB` prevents any changes to its data when this variable is greater than 0. This variable is available starting from MySQL 3.23.44.

- `innodb_lock_wait_timeout`

  The timeout in seconds an `InnoDB` transaction may wait for a lock before being rolled back. `InnoDB` automatically detects transaction deadlocks in its own lock table and rolls back the transaction. The default is 50 seconds. A lock wait for a MySQL table lock does not happen inside `InnoDB`, and this timeout does not apply to that wait.

- `innodb_locks_unsafe_for_binlog`

  This variable controls next-key locking in `InnoDB` searches and index scans. By default, this variable is 0 (disabled), which means that next-key locking is enabled.

  Normally, `InnoDB` uses an algorithm called next-key locking. `InnoDB` performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on any index records it encounters. Thus, the row-level locks are actually index record locks. The locks that `InnoDB` sets on index records also affect the “gap” preceding that index record. If a user has a shared or exclusive lock on record R in an index, another user cannot insert a new index record immediately before R in the order of the index. Enabling this variable causes `InnoDB` not to use next-key locking in searches or index scans. Next-key locking is still used to ensure foreign key constraints and duplicate key checking. Note that enabling this variable may cause phantom problems: Suppose that you want to read and lock all children from the `child` table with an identifier value larger than 100, with the intention of updating some column in the selected rows later:

      SELECT * FROM child WHERE id > 100 FOR UPDATE;

  Suppose that there is an index on the `id` column. The query scans that index starting from the first record where `id` is larger than 100. If the locks set on the index records do not lock out inserts made in the gaps, another client can insert a new row into the table. If you execute the same `SELECT` within the same transaction, you see a new row in the result set returned by the query. This also means that if new items are added to the database, `InnoDB` does not guarantee serializability Therefore, if this variable is enabled `InnoDB` guarantees at most isolation level `READ COMMITTED`. (Conflict serializability is still guaranteed.) This variable is available as of MySQL 4.1.4.

- `innodb_log_arch_dir`

  The directory where fully written log files would be archived if we used log archiving. The value of this variable should currently be set the same as `innodb_log_group_home_dir`. Starting from MySQL 4.0.6, there is no need to set this variable.

- `innodb_log_archive`

  Whether to log `InnoDB` archive files. This variable is unused. Recovery from a backup is done by MySQL using its own log files, so there is no need to archive `InnoDB` log files. The default for this variable is 0.

- `innodb_log_buffer_size`

  The size in bytes of the buffer that `InnoDB` uses to write to the log files on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

- `innodb_log_file_size`

  The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB on 32-bit computers. The default is 5MB. Sensible values range from 1MB to 1/`N`-th of the size of the buffer pool, where `N` is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

- `innodb_log_files_in_group`

  The number of log files in the log group. `InnoDB` writes to the files in a circular fashion. The default (and recommended) is 2.

- `innodb_log_group_home_dir`

  The directory path to the `InnoDB` log files. It must have the same value as `innodb_log_arch_dir`. If you do not specify any `InnoDB` log variables, the default is to create two 5MB files names `ib_logfile0` and `ib_logfile1` in the MySQL data directory.

- `innodb_max_dirty_pages_pct`

  This is an integer in the range from 0 to 100. The default is 90. The main thread in `InnoDB` tries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value. Available starting from 4.0.13 and 4.1.1.

- `innodb_max_purge_lag`

  This variable controls how to delay `INSERT`, `UPDATE` and `DELETE` operations when the purge operations are lagging (see Section 13.2.13, “Implementation of Multi-Versioning”). The default value of this variable is 0, meaning that there are no delays. `innodb_max_purge_lag` is available as of MySQL 4.0.22 and 4.1.6.

  The `InnoDB` transaction system maintains a list of transactions that have delete-marked index records by `UPDATE` or `DELETE` operations. Let the length of this list be `purge_lag`. When `purge_lag` exceeds `innodb_max_purge_lag`, each `INSERT`, `UPDATE` and `DELETE` operation is delayed by ((`purge_lag`/`innodb_max_purge_lag`)×10)–5 milliseconds. The delay is computed in the beginning of a purge batch, every ten seconds. The operations are not delayed if purge cannot run because of an old consistent read view that could see the rows to be purged.

  A typical setting for a problematic workload might be 1 million, assuming that our transactions are small, only 100 bytes in size, and we can allow 100MB of unpurged rows in our tables.

- `innodb_mirrored_log_groups`

  The number of identical copies of log groups to keep for the database. Currently, this should be set to 1.

- `innodb_open_files`

  This variable is relevant only if you use multiple tablespaces in `InnoDB`. It specifies the maximum number of `.ibd` files that `InnoDB` can keep open at one time. The minimum value is 10. The default is 300. This variable is available as of MySQL 4.1.1.

  The file descriptors used for `.ibd` files are for `InnoDB` only. They are independent of those specified by the `--open-files-limit` server option, and do not affect the operation of the table cache.

- `innodb_safe_binlog`

  Adds consistency guarantees between the content of `InnoDB` tables and the binary log. See Section 5.3.4, “The Binary Log”.

- `innodb_table_locks`

  Starting from MySQL 4.0.20, and 4.1.2, `InnoDB` honors `LOCK TABLES`. If `AUTOCOMMIT=0`, `InnoDB` honors `LOCK TABLES`; MySQL does not return from `LOCK TABLE .. WRITE` until all other threads have released all their locks to the table. The default value of `innodb_table_locks` is 1, which means that `LOCK TABLES` causes InnoDB to lock a table internally if `AUTOCOMMIT=0`.

- `innodb_thread_concurrency`

  `InnoDB` tries to keep the number of operating system threads concurrently inside `InnoDB` less than or equal to the limit given by this variable. The default value is 8. If you have low performance and `SHOW INNODB STATUS` reveals many threads waiting for semaphores, you may have thread thrashing and should try setting this variable lower or higher. If you have a computer with many processors and disks, you can try setting the value higher to better utilize the resources of your computer. A recommended value is the sum of the number of processors and disks your system has. A value of 500 or greater disables the concurrency checking. This variable is available starting from MySQL 3.23.44 and 4.0.1.

- `sync_binlog`

  If the value of this variable is positive, the MySQL server synchronizes its binary log to disk (`fdatasync()`) after every `sync_binlog` writes to this binary log. Note that there is one write to the binary log per statement if in autocommit mode, and otherwise one write per transaction. The default value is 0 which does no sync'ing to disk. A value of 1 is the safest choice, because in case of crash you lose at most one statement/transaction from the binary log; but it is also the slowest choice (unless the disk has a battery-backed cache, which makes sync'ing very fast). This variable was added in MySQL 4.1.3.

### 13.2.6. Creating the `InnoDB` Tablespace

Suppose that you have installed MySQL and have edited your option file so that it contains the necessary `InnoDB` configuration parameters. Before starting MySQL, you should verify that the directories you have specified for `InnoDB` data files and log files exist and that the MySQL server has access rights to those directories. `InnoDB` does not create directories, only files. Check also that you have enough disk space for the data and log files.

It is best to run the MySQL server mysqld from the command prompt when you first start the server with `InnoDB` enabled, not from the mysqld_safe wrapper or as a Windows service. When you run from a command prompt you see what mysqld prints and what is happening. On Unix, just invoke mysqld. On Windows, use the `--console` option.

When you start the MySQL server after initially configuring `InnoDB` in your option file, `InnoDB` creates your data files and log files, and prints something like this:

    InnoDB: The first specified datafile /home/heikki/data/ibdata1
    did not exist:
    InnoDB: a new database to be created!
    InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
    InnoDB: Database physically writes the file full: wait...
    InnoDB: datafile /home/heikki/data/ibdata2 did not exist:
    new to be created
    InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
    InnoDB: Database physically writes the file full: wait...
    InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist:
    new to be created
    InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size
    to 5242880
    InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist:
    new to be created
    InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size
    to 5242880
    InnoDB: Doublewrite buffer not found: creating new
    InnoDB: Doublewrite buffer created
    InnoDB: Creating foreign key constraint system tables
    InnoDB: Foreign key constraint system tables created
    InnoDB: Started
    mysqld: ready for connections

At this point `InnoDB` has initialized its tablespace and log files. You can connect to the MySQL server with the usual MySQL client programs like mysql. When you shut down the MySQL server with mysqladmin shutdown, the output is like this:

    010321 18:33:34  mysqld: Normal shutdown
    010321 18:33:34  mysqld: Shutdown Complete
    InnoDB: Starting shutdown...
    InnoDB: Shutdown completed

You can look at the data file and log directories and you see the files created there. The log directory also contains a small file named `ib_arch_log_0000000000`. That file resulted from the database creation, after which `InnoDB` switched off log archiving. When MySQL is started again, the data files and log files have been created already, so the output is much briefer:

    InnoDB: Started
    mysqld: ready for connections

Starting from MySQL 4.1.1, you can add the option `innodb_file_per_table` to `my.cnf` to make `InnoDB` store each table to its own `.ibd` file in the same MySQL database directory where the `.frm` file is created. See Section 13.2.4.1, “Using Per-Table Tablespaces”.

#### 13.2.6.1. Dealing with `InnoDB` Initialization Problems

If `InnoDB` prints an operating system error during a file operation, usually the problem has one of the following causes:

- You did not create the `InnoDB` data file directory or the `InnoDB` log directory.

- mysqld does not have access rights to create files in those directories.

- mysqld cannot read the proper `my.cnf` or `my.ini` option file, and consequently does not see the options that you specified.

- The disk is full or a disk quota is exceeded.

- You have created a subdirectory whose name is equal to a data file that you specified, so the name cannot be used as a filename.

- There is a syntax error in the `innodb_data_home_dir` or `innodb_data_file_path` value.

If something goes wrong when `InnoDB` attempts to initialize its tablespace or its log files, you should delete all files created by `InnoDB`. This means all `ibdata` files and all `ib_logfile` files. In case you have already created some `InnoDB` tables, delete the corresponding `.frm` files for these tables (and any `.ibd` files if you are using multiple tablespaces) from the MySQL database directories as well. Then you can try the `InnoDB` database creation again. It is best to start the MySQL server from a command prompt so that you see what is happening.

### 13.2.7. Creating and Using `InnoDB` Tables

To create an `InnoDB` table, specify an `ENGINE = InnoDB` option in the `CREATE TABLE` statement:

    CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;

The older term `TYPE` is supported as a synonym for `ENGINE` for backward compatibility, but `ENGINE` is the preferred term and `TYPE` is deprecated.

The statement creates a table and an index on column `a` in the `InnoDB` tablespace that consists of the data files that you specified in `my.cnf`. In addition, MySQL creates a file `customers.frm` in the `test` directory under the MySQL database directory. Internally, `InnoDB` adds an entry for the table to its own data dictionary. The entry includes the database name. For example, if `test` is the database in which the `customers` table is created, the entry is for `'test/customers'`. This means you can create a table of the same name `customers` in some other database, and the table names do not collide inside `InnoDB`.

You can query the amount of free space in the `InnoDB` tablespace by issuing a `SHOW TABLE STATUS` statement for any `InnoDB` table. The amount of free space in the tablespace appears in the `Comment` section in the output of `SHOW TABLE STATUS`. For example:

    SHOW TABLE STATUS FROM test LIKE 'customers'

Note that the statistics `SHOW` displays for `InnoDB` tables are only approximate. They are used in SQL optimization. Table and index reserved sizes in bytes are accurate, though.

#### 13.2.7.1. How to Use Transactions in `InnoDB` with Different APIs

By default, each client that connects to the MySQL server begins with autocommit mode enabled, which automatically commits every SQL statement as you execute it. To use multiple-statement transactions, you can switch autocommit off with the SQL statement `SET AUTOCOMMIT = 0` and use `COMMIT` and `ROLLBACK` to commit or roll back your transaction. If you want to leave autocommit on, you can enclose your transactions within `START TRANSACTION` and either `COMMIT` or `ROLLBACK`. Before MySQL 4.0.11, you have to use the keyword `BEGIN` instead of `START TRANSACTION`. The following example shows two transactions. The first is committed and the second is rolled back.

    shell> mysql test

    mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
        -> TYPE=InnoDB;
    Query OK, 0 rows affected (0.00 sec)
    mysql> BEGIN;
    Query OK, 0 rows affected (0.00 sec)
    mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
    Query OK, 1 row affected (0.00 sec)
    mysql> COMMIT;
    Query OK, 0 rows affected (0.00 sec)
    mysql> SET AUTOCOMMIT=0;
    Query OK, 0 rows affected (0.00 sec)
    mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
    Query OK, 1 row affected (0.00 sec)
    mysql> ROLLBACK;
    Query OK, 0 rows affected (0.00 sec)
    mysql> SELECT * FROM CUSTOMER;
    +------+--------+
    | A    | B      |
    +------+--------+
    |   10 | Heikki |
    +------+--------+
    1 row in set (0.00 sec)
    mysql>

In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C call interface of MySQL, you can send transaction control statements such as `COMMIT` to the MySQL server as strings just like any other SQL statements such as `SELECT` or `INSERT`. Some APIs also offer separate special transaction commit and rollback functions or methods.

#### 13.2.7.2. Converting `MyISAM` Tables to `InnoDB`

Important: Do not convert MySQL system tables in the `mysql` database (such as `user` or `host`) to the `InnoDB` type. This is an unsupported operation. The system tables must always be of the `MyISAM` type.

If you want all your (non-system) tables to be created as `InnoDB` tables, you can, starting from the MySQL 3.23.43, add the line `default-table-type=innodb` to the `[mysqld]` section of your server option file.

`InnoDB` does not have a special optimization for separate index creation the way the `MyISAM` storage engine does. Therefore, it does not pay to export and import the table and create indexes afterward. The fastest way to alter a table to `InnoDB` is to do the inserts directly to an `InnoDB` table. That is, use `ALTER TABLE ... TYPE=INNODB`, or create an empty `InnoDB` table with identical definitions and insert the rows with `INSERT INTO ... SELECT * FROM ...`.

If you have `UNIQUE` constraints on secondary keys, starting from MySQL 3.23.52, you can speed up a table import by turning off the uniqueness checks temporarily during the import operation:

    SET UNIQUE_CHECKS=0;
    ... import operation ...
    SET UNIQUE_CHECKS=1;

For big tables, this saves a lot of disk I/O because `InnoDB` can then use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys. `UNIQUE_CHECKS` allows but does not require storage engines to ignore duplicate keys.

To get better control over the insertion process, it might be good to insert big tables in pieces:

    INSERT INTO newtable SELECT * FROM oldtable
       WHERE yourkey > something AND yourkey <= somethingelse;

After all records have been inserted, you can rename the tables.

During the conversion of big tables, you should increase the size of the `InnoDB` buffer pool to reduce disk I/O. Do not use more than 80% of the physical memory, though. You can also increase the sizes of the `InnoDB` log files.

Make sure that you do not fill up the tablespace: `InnoDB` tables require a lot more disk space than `MyISAM` tables. If an `ALTER TABLE` operation runs out of space, it starts a rollback, and that can take hours if it is disk-bound. For inserts, `InnoDB` uses the insert buffer to merge secondary index records to indexes in batches. That saves a lot of disk I/O. For rollback, no such mechanism is used, and the rollback can take 30 times longer than the insertion.

In the case of a runaway rollback, if you do not have valuable data in your database, it may be advisable to kill the database process rather than wait for millions of disk I/O operations to complete. For the complete procedure, see Section 13.2.9.1, “Forcing `InnoDB` Recovery”.

#### 13.2.7.3. How `AUTO_INCREMENT` Handling Works in `InnoDB`

If you specify an `AUTO_INCREMENT` column for an `InnoDB` table, the table handle in the `InnoDB` data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk.

`InnoDB` uses the following algorithm to initialize the auto-increment counter for a table `t` that contains an `AUTO_INCREMENT` column named `ai_col`: After a server startup, for the first insert into a table `t`, `InnoDB` executes the equivalent of this statement:

    SELECT MAX(ai_col) FROM t FOR UPDATE;

`InnoDB` increments by one the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. If the table is empty, `InnoDB` uses the value `1`. If a user invokes a `SHOW TABLE STATUS` statement that displays output for the table `t` and the auto-increment counter has not been initialized, `InnoDB` initializes but does not increment the value and stores it for use by later inserts. This initialization uses a normal exclusive-locking read on the table and the lock lasts to the end of the transaction.

`InnoDB` follows the same procedure for initializing the auto-increment counter for a freshly created table.

After the auto-increment counter has been initialized, if a user does not explicitly specify a value for an `AUTO_INCREMENT` column, `InnoDB` increments the counter by one and assigns the new value to the column. If the user inserts a row that explicitly specifies the column value, and the value is bigger than the current counter value, the counter is set to the specified column value.

When accessing the auto-increment counter, `InnoDB` uses a special table-level `AUTO-INC` lock that it keeps to the end of the current SQL statement, not to the end of the transaction. The special lock release strategy was introduced to improve concurrency for inserts into a table containing an `AUTO_INCREMENT` column. Nevertheless, two transactions cannot have the `AUTO-INC` lock on the same table simultaneously, which can have a performance impact if the `AUTO-INC` lock is held for a long time. That might be the case for a statement such as `INSERT INTO t1 ... SELECT ... FROM t2` that inserts all rows from one table into another.

`InnoDB` uses the in-memory auto-increment counter as long as the server runs. When the server is stopped and restarted, `InnoDB` reinitializes the counter for each table for the first `INSERT` to the table, as described earlier.

You may see gaps in the sequence of values assigned to the `AUTO_INCREMENT` column if you roll back transactions that have generated numbers using the counter.

If a user specifies `NULL` or `0` for the `AUTO_INCREMENT` column in an `INSERT`, `InnoDB` treats the row as if the value had not been specified and generates a new value for it.

The behavior of the auto-increment mechanism is not defined if a user assigns a negative value to the column or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.

An `AUTO_INCREMENT` column must be the first column listed if it is part of a multiple-column index in an `InnoDB` table.

Beginning with MySQL 4.1.12, `InnoDB` supports the `AUTO_INCREMENT = `n`` table option in `ALTER TABLE` statements, to set the initial counter value or alter the current counter value. The same is true as of MySQL 4.1.14 for `CREATE TABLE`. The effect of this option is canceled by a server restart, for reasons discussed earlier in this section.

#### 13.2.7.4. `FOREIGN KEY` Constraints

Starting from MySQL 3.23.44, `InnoDB` features foreign key constraints.

The syntax of a foreign key constraint definition in `InnoDB` looks like this:

    [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name, ...)
        [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
        [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Foreign keys definitions are subject to the following conditions:

- Both tables must be `InnoDB` tables and they must not be `TEMPORARY` tables.

- Corresponding columns in the foreign key and the referenced key must have similar internal data types inside `InnoDB` so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For non-binary (character) string columns, the character set and collation must be the same.

- In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Starting with MySQL 4.1.2, such an index is created on the referencing table automatically if it does not exist.

- In the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

- Index prefixes on foreign key columns are not supported. One consequence of this is that `BLOB` and `TEXT` columns cannot be included in a foreign key, because indexes on those columns must always include a prefix length.

- If the `CONSTRAINT `symbol`` clause is given, the `symbol` value must be unique in the database. If the clause is not given, `InnoDB` creates the name automatically.

`InnoDB` rejects any `INSERT` or `UPDATE` operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table. The action `InnoDB` takes for any `UPDATE` or `DELETE` operation that attempts to update or delete a candidate key value in the parent table that has some matching rows in the child table is dependent on the referential action specified using `ON UPDATE` and `ON DELETE` subclauses of the `FOREIGN KEY` clause. When the user attempts to delete or update a row from a parent table, and there are one or more matching rows in the child table, `InnoDB` supports five options regarding the action to be taken:

- `CASCADE`: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. `ON DELETE CASCADE` is supported starting from MySQL 3.23.50 and `ON UPDATE CASCADE` is supported starting from 4.0.8. Between two tables, you should not define several `ON UPDATE CASCADE` clauses that act on the same column in the parent table or in the child table.

- `SET NULL`: Delete or update the row from the parent table and set the foreign key column or columns in the child table to `NULL`. This is valid only if the foreign key columns do not have the `NOT NULL` qualifier specified. `ON DELETE SET NULL` is available starting from MySQL 3.23.50 and `ON UPDATE SET NULL` is available starting from 4.0.8.

  If you specify a `SET NULL` action, make sure that you have not declared the columns in the child table as `NOT NULL`.

- `NO ACTION`: In standard sQL, `NO ACTION` means no action in the sense that an attempt to delete or update a primary key value will not be allowed to proceed if there is a related foreign key value in the referenced table. Starting from 4.0.18 `InnoDB` rejects the delete or update operation for the parent table.

- `RESTRICT`: Rejects the delete or update operation for the parent table. `NO ACTION` and `RESTRICT` are the same as omitting the `ON DELETE` or `ON UPDATE` clause. (Some database systems have deferred checks, and `NO ACTION` is a deferred check. In MySQL, foreign key constraints are checked immediately, so `NO ACTION` and `RESTRICT` are the same.)

- `SET DEFAULT`: This action is recognized by the parser, but `InnoDB` rejects table definitions containing `ON DELETE SET DEFAULT` or `ON UPDATE SET DEFAULT` clauses.

Note that `InnoDB` supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.

`InnoDB` requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. Starting with MySQL 4.1.2, the index on the foreign key is created automatically. In older versions, the indexes must be created explicitly or the creation of foreign key constraints fails.

If MySQL reports an error number 1005 from a `CREATE TABLE` statement, and the error message refers to errno 150, table creation failed because a foreign key constraint was not correctly formed. Similarly, if an `ALTER TABLE` fails and it refers to errno 150, that means a foreign key definition would be incorrectly formed for the altered table. Starting from MySQL 4.0.13, you can use `SHOW INNODB STATUS` to display a detailed explanation of the latest `InnoDB` foreign key error in the server.

Starting from MySQL 3.23.50, `InnoDB` does not check foreign key constraints on those foreign key or referenced key values that contain a `NULL` column.

### Note

Currently, triggers are not activated by cascaded foreign key actions.

You cannot create a table with a column name that matches the name of an internal InnoDB column (including `DB_ROW_ID`, `DB_TRX_ID`, `DB_ROLL_PTR` and `DB_MIX_ID`). In versions of MySQL before 4.1.19 this would cause a crash, since 4.1.19 the server will report error 1005 and refers to `errno` -1 in the error message.

Deviation from SQL standards: If there are several rows in the parent table that have the same referenced key value, `InnoDB` acts in foreign key checks as if the other parent rows with the same key value do not exist. For example, if you have defined a `RESTRICT` type constraint, and there is a child row with several parent rows, `InnoDB` does not allow the deletion of any of those parent rows.

`InnoDB` performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints.

Deviation from SQL standards: A `FOREIGN KEY` constraint that references a non-`UNIQUE` key is not standard SQL. It is an `InnoDB` extension to standard SQL.

Deviation from SQL standards: If `ON UPDATE CASCADE` or `ON UPDATE SET NULL` recurses to update the same table it has previously updated during the cascade, it acts like `RESTRICT`. This means that you cannot use self-referential `ON UPDATE CASCADE` or `ON UPDATE SET NULL` operations. This is to prevent infinite loops resulting from cascaded updates. A self-referential `ON DELETE SET NULL`, on the other hand, is possible from 4.0.13. A self-referential `ON DELETE CASCADE` has been possible since `ON DELETE` was implemented. Since 4.0.21, cascading operations may not be nested more than 15 levels.

Deviation from SQL standards: Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, `InnoDB` checks `UNIQUE` and `FOREIGN KEY` constraints row-by-row. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the whole SQL statement has been processed. Until `InnoDB` implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself via a foreign key.

Here is a simple example that relates `parent` and `child` tables through a single-column foreign key:

    CREATE TABLE parent (id INT NOT NULL,
                         PRIMARY KEY (id)
    ) TYPE=INNODB;
    CREATE TABLE child (id INT, parent_id INT,
                        INDEX par_ind (parent_id),
                        FOREIGN KEY (parent_id) REFERENCES parent(id)
                          ON DELETE CASCADE
    ) TYPE=INNODB;

A more complex example in which a `product_order` table has foreign keys for two other tables. One foreign key references a two-column index in the `product` table. The other references a single-column index in the `customer` table:

    CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
                          price DECIMAL,
                          PRIMARY KEY(category, id)) TYPE=INNODB;
    CREATE TABLE customer (id INT NOT NULL,
                           PRIMARY KEY (id)) TYPE=INNODB;
    CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
                                product_category INT NOT NULL,
                                product_id INT NOT NULL,
                                customer_id INT NOT NULL,
                                PRIMARY KEY(no),
                                INDEX (product_category, product_id),
                                FOREIGN KEY (product_category, product_id)
                                  REFERENCES product(category, id)
                                  ON UPDATE CASCADE ON DELETE RESTRICT,
                                INDEX (customer_id),
                                FOREIGN KEY (customer_id)
                                  REFERENCES customer(id)) TYPE=INNODB;

Starting from MySQL 3.23.50, `InnoDB` allows you to add a new foreign key constraint to a table by using `ALTER TABLE`:

    ALTER TABLE tbl_name
        ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
        REFERENCES tbl_name (index_col_name, ...)
        [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
        [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]

Remember to create the required indexes first. You can also add a self-referential foreign key constraint to a table using `ALTER TABLE`.

Starting from MySQL 4.0.13, `InnoDB` supports the use of `ALTER TABLE` to drop foreign keys:

    ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

If the `FOREIGN KEY` clause included a `CONSTRAINT` name when you created the foreign key, you can refer to that name to drop the foreign key. (A constraint name can be given as of MySQL 4.0.18.) Otherwise, the `fk_symbol` value is internally generated by `InnoDB` when the foreign key is created. To find out the symbol when you want to drop a foreign key, use the `SHOW CREATE TABLE` statement. For example:

    mysql> SHOW CREATE TABLE ibtest11c\G
    *************************** 1. row ***************************
           Table: ibtest11c
    Create Table: CREATE TABLE `ibtest11c` (
      `A` int(11) NOT NULL auto_increment,
      `D` int(11) NOT NULL default '0',
      `B` varchar(200) NOT NULL default '',
      `C` varchar(175) default NULL,
      PRIMARY KEY  (`A`,`D`,`B`),
      KEY `B` (`B`,`C`),
      KEY `C` (`C`),
      CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
    REFERENCES `ibtest11a` (`A`, `D`)
    ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
    REFERENCES `ibtest11a` (`B`, `C`)
    ON DELETE CASCADE ON UPDATE CASCADE
    ) TYPE=InnoDB CHARSET=latin1
    1 row in set (0.01 sec)

    mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;

You cannot add a foreign key and drop a foreign key in separate clauses of a single `ALTER TABLE` statement. Separate statements are required.

If `ALTER TABLE` for an `InnoDB` table results in changes to column values (for example, because a column is truncated), `InnoDB`'s `FOREIGN KEY` constraint checks do not notice possible violations caused by changing the values.

Starting from MySQL 3.23.50, the `InnoDB` parser allows table and column identifiers in a `FOREIGN KEY ... REFERENCES ...` clause to be quoted within backticks. (Alternatively, double quotes can be used if the `ANSI_QUOTES` SQL mode is enabled.) The `InnoDB` parser also takes into account the setting of the `lower_case_table_names` system variable.

Before MySQL 3.23.50, `ALTER TABLE` or `CREATE INDEX` should not be used in connection with tables that have foreign key constraints or that are referenced in foreign key constraints: Any `ALTER TABLE` removes all foreign key constraints defined for the table. You should not use `ALTER TABLE` with the referenced table, either. Instead, use `DROP TABLE` and `CREATE TABLE` to modify the schema. When MySQL does an `ALTER TABLE` it may internally use `RENAME TABLE`, and that confuses the foreign key constraints that refer to the table. In MySQL, a `CREATE INDEX` statement is processed as an `ALTER TABLE`, so the same considerations apply.

Starting from MySQL 3.23.50, `InnoDB` returns the foreign key definitions of a table as part of the output of the `SHOW CREATE TABLE` statement:

    SHOW CREATE TABLE tbl_name;

mysqldump also produces correct definitions of tables to the dump file, and does not forget about the foreign keys.

You can also display the foreign key constraints for a table like this:

    SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';

The foreign key constraints are listed in the `Comment` column of the output.

When performing foreign key checks, `InnoDB` sets shared row-level locks on child or parent records it has to look at. `InnoDB` checks foreign key constraints immediately; the check is not deferred to transaction commit.

To make it easier to reload dump files for tables that have foreign key relationships, mysqldump automatically includes a statement in the dump output to set `FOREIGN_KEY_CHECKS` to 0 as of MySQL 4.1.1. This avoids problems with tables having to be reloaded in a particular order when the dump is reloaded. For earlier versions, you can disable the variable manually within mysql when loading the dump file like this:

    mysql> SET FOREIGN_KEY_CHECKS = 0;
    mysql> SOURCE dump_file_name;
    mysql> SET FOREIGN_KEY_CHECKS = 1;

This allows you to import the tables in any order if the dump file contains tables that are not correctly ordered for foreign keys. It also speeds up the import operation. Setting `FOREIGN_KEY_CHECKS` to 0 can also be useful for ignoring foreign key constraints during `LOAD DATA` or `ALTER TABLE` operations. However, even if `FOREIGN_KEY_CHECKS=0`, InnoDB does not allow the creation of a foreign key constraint where a column references a non-matching column type. Also, if an `InnoDB` table has foreign key constraints, `ALTER TABLE` cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first. `FOREIGN_KEY_CHECKS` is available starting from MySQL 3.23.52 and 4.0.3.

`InnoDB` does not allow you to drop a table that is referenced by a `FOREIGN KEY` constraint, unless you do `SET FOREIGN_KEY_CHECKS=0`. When you drop a table, the constraints that were defined in its create statement are also dropped.

If you re-create a table that was dropped, it must have a definition that conforms to the foreign key constraints referencing it. It must have the right column names and types, and it must have indexes on the referenced keys, as stated earlier. If these are not satisfied, MySQL returns error number 1005 and refers to errno 150 in the error message.

#### 13.2.7.5. `InnoDB` and MySQL Replication

MySQL replication works for `InnoDB` tables as it does for `MyISAM` tables. It is also possible to use replication in a way where the storage engine on the slave is not the same as the original storage engine on the master. For example, you can replicate modifications to an `InnoDB` table on the master to a `MyISAM` table on the slave.

To set up a new slave for a master, you have to make a copy of the `InnoDB` tablespace and the log files, as well as the `.frm` files of the `InnoDB` tables, and move the copies to the slave. If the `innodb_file_per_table` variable is enabled, you must also copy the `.ibd` files as well. For the proper procedure to do this, see Section 13.2.9, “Backing Up and Recovering an `InnoDB` Database”.

If you can shut down the master or an existing slave, you can take a cold backup of the `InnoDB` tablespace and log files and use that to set up a slave. To make a new slave without taking down any server you can also use the commercial `InnoDB` Hot Backup tool.

There are minor limitations in `InnoDB` replication:

- `LOAD TABLE FROM MASTER` does not work for `InnoDB` type tables. There are workarounds: 1) dump the table on the master and import the dump file into the slave, or 2) use `ALTER TABLE `tbl_name` TYPE=MyISAM` on the master before setting up replication with `LOAD TABLE `tbl_name` FROM MASTER`, and then use `ALTER TABLE` to alter the master table back to the `InnoDB` type afterward. However, this should not be done for tables that have foreign key definitions because the definitions will be lost.

- Before MySQL 4.0.6, `SLAVE STOP` did not respect the boundary of a multiple-statement transaction. An incomplete transaction would be rolled back, and the next `SLAVE START` would only execute the remaining part of the half transaction. That would cause replication to fail.

- Before MySQL 4.0.6, a slave crash in the middle of a multiple-statement transaction would cause the same problem as `SLAVE STOP`.

- Before MySQL 4.0.11, replication of the `SET FOREIGN_KEY_CHECKS=0` statement does not work properly.

Most of these limitations can be eliminated by using more recent server versions for which the limitations do not apply.

Transactions that fail on the master do not affect replication at all. MySQL replication is based on the binary log where MySQL writes SQL statements that modify data. A transaction that fails (for example, because of a foreign key violation, or because it is rolled back) is not written to the binary log, so it is not sent to slaves. See Section 12.4.1, “`START TRANSACTION`, `COMMIT`, and `ROLLBACK` Syntax”.

Replication and `CASCADE`. Cascading actions for `InnoDB` tables on the master are replicated to the slave only if both the master's and slave' versions of the tables sharing the foreign key relation use `InnoDB`. For example, suppose you have started replication, and then create two tables on the master using the following `CREATE TABLE` statements:

    CREATE TABLE fc1 (
        i INT PRIMARY KEY,
        j INT
    ) ENGINE = InnoDB;

    CREATE TABLE fc2 (
        m INT PRIMARY KEY,
        n INT,
        FOREIGN KEY ni (n) REFERENCES fc1 (i)
            ON DELETE CASCADE
    ) ENGINE = InnoDB;

Suppose that the slave does not have `InnoDB` support enabled. If this is the case, then the tables on the slave are created, but they use the `MyISAM` storage engine, and the `FOREIGN KEY` option is ignored. Now we insert some rows into the tables on the master:

    master> INSERT INTO fc1 VALUES (1, 1), (2, 2);
    Query OK, 2 rows affected (0.09 sec)
    Records: 2  Duplicates: 0  Warnings: 0

    master> INSERT INTO fc2 VALUES (1, 1), (2, 2), (3, 1);
    Query OK, 3 rows affected (0.19 sec)
    Records: 3  Duplicates: 0  Warnings: 0

At this point, on both the master and the slave, table `fc1` contains 2 rows, and table `fc2` contains 3 rows, as shown here:

    master> SELECT * FROM fc1;
    +---+------+
    | i | j    |
    +---+------+
    | 1 |    1 |
    | 2 |    2 |
    +---+------+
    2 rows in set (0.00 sec)

    master> SELECT * FROM fc2;
    +---+------+
    | m | n    |
    +---+------+
    | 1 |    1 |
    | 2 |    2 |
    | 3 |    1 |
    +---+------+
    3 rows in set (0.00 sec)

    slave> SELECT * FROM fc1;
    +---+------+
    | i | j    |
    +---+------+
    | 1 |    1 |
    | 2 |    2 |
    +---+------+
    2 rows in set (0.00 sec)

    slave> SELECT * FROM fc2;
    +---+------+
    | m | n    |
    +---+------+
    | 1 |    1 |
    | 2 |    2 |
    | 3 |    1 |
    +---+------+
    3 rows in set (0.00 sec)

Now suppose that you perform the following `DELETE` statement on the master:

    master> DELETE FROM fc1 WHERE i=1;
    Query OK, 1 row affected (0.09 sec)

Due to the cascade, table `fc2` on the master now contains only 1 row:

    master> SELECT * FROM fc2;
    +---+---+
    | m | n |
    +---+---+
    | 2 | 2 |
    +---+---+
    1 row in set (0.00 sec)

However, the cascade does not propagate to the slave. The slave's copy of `fc2` still contains all of the rows that were originally inserted:

    slave> SELECT * FROM fc2;
    +---+---+
    | m | n |
    +---+---+
    | 1 | 1 |
    | 3 | 1 |
    | 2 | 2 |
    +---+---+
    3 rows in set (0.00 sec)

This difference is due to the fact that the cascading deletes are handled internally by the `InnoDB` storage engine, which means that none of the changes are logged.

### 13.2.8. Adding and Removing `InnoDB` Data and Log Files

This section describes what you can do when your `InnoDB` tablespace runs out of room or when you want to change the size of the log files.

From MySQL 3.23.50 and 4.0.2, the easiest way to increase the size of the `InnoDB` tablespace is to configure it from the beginning to be auto-extending. Specify the `autoextend` attribute for the last data file in the tablespace definition. Then `InnoDB` increases the size of that file automatically in 8MB increments when it runs out of space. Starting with MySQL 4.0.24 and 4.1.5, the increment size can be changed by setting the value of the `innodb_autoextend_increment` system variable, which is measured in MB.

Alternatively, you can increase the size of your tablespace by adding another data file. To do this, you have to shut down the MySQL server, change the tablespace configuration to add a new data file to the end of `innodb_data_file_path`, and start the server again.

If your last data file was defined with the keyword `autoextend`, the procedure for reconfiguring the tablespace must take into account the size to which the last data file has grown. Obtain the size of the data file, round it down to the closest multiple of 1024 × 1024 bytes (= 1MB), and specify the rounded size explicitly in `innodb_data_file_path`. Then you can add another data file. Remember that only the last data file in the `innodb_data_file_path` can be specified as auto-extending.

As an example, assume that the tablespace has just one auto-extending data file `ibdata1`:

    innodb_data_home_dir =
    innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

Suppose that this data file, over time, has grown to 988MB. Here is the configuration line after modifying the original data file to not be auto-extending and adding another auto-extending data file:

    innodb_data_home_dir =
    innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

When you add a new file to the tablespace configuration, make sure that it does not exist. `InnoDB` will create and initialize the file when you restart the server.

Currently, you cannot remove a data file from the tablespace. To decrease the size of your tablespace, use this procedure:

1. Use mysqldump to dump all your `InnoDB` tables.

2. Stop the server.

3. Remove all the existing tablespace files, including the `ibdata` and `ib_log` files. If you want to keep a backup copy of the information, then copy all the `ib*` files to another location before the removing the files in your MySQL installation.

4. Remove any `.frm` files for `InnoDB` tables.

5. Configure a new tablespace.

6. Restart the server.

7. Import the dump files.

If you want to change the number or the size of your `InnoDB` log files, you must stop the MySQL server and make sure that it shuts down without errors (to ensure that there is no information for outstanding transactions in the logs). Then copy the old log files into a safe place just in case something went wrong in the shutdown and you need them to recover the tablespace. Delete the old log files from the log file directory, edit `my.cnf` to change the log file configuration, and start the MySQL server again. mysqld sees that no log files exist at startup and tells you that it is creating new ones.

### 13.2.9. Backing Up and Recovering an `InnoDB` Database

The key to safe database management is making regular backups.

InnoDB Hot Backup is an online backup tool you can use to backup your `InnoDB` database while it is running. InnoDB Hot Backup does not require you to shut down your database and it does not set any locks or disturb your normal database processing. InnoDB Hot Backup is a non-free (commercial) add-on tool with an annual license fee of €390 per computer on which the MySQL server is run. See the InnoDB Hot Backup home page for detailed information and screenshots.

If you are able to shut down your MySQL server, you can make a binary backup that consists of all files used by `InnoDB` to manage its tables. Use the following procedure:

1. Shut down your MySQL server and make sure that it shuts down without errors.

2. Copy all your data files (`ibdata` files and `.ibd` files) into a safe place.

3. Copy all your `ib_logfile` files to a safe place.

4. Copy your `my.cnf` configuration file or files to a safe place.

5. Copy all the `.frm` files for your `InnoDB` tables to a safe place.

Replication works with `InnoDB` tables, so you can use MySQL replication capabilities to keep a copy of your database at database sites requiring high availability.

In addition to making binary backups as just described, you should also regularly make dumps of your tables with mysqldump. The reason for this is that a binary file might be corrupted without you noticing it. Dumped tables are stored into text files that are human-readable, so spotting table corruption becomes easier. Also, because the format is simpler, the chance for serious data corruption is smaller. mysqldump also has a `--single-transaction` option that you can use to make a consistent snapshot without locking out other clients.

To be able to recover your `InnoDB` database to the present from the binary backup just described, you have to run your MySQL server with binary logging turned on. Then you can apply the binary log to the backup database to achieve point-in-time recovery:

    mysqlbinlog yourhostname-bin.123 | mysql

To recover from a crash of your MySQL server, the only requirement is to restart it. `InnoDB` automatically checks the logs and performs a roll-forward of the database to the present. `InnoDB` automatically rolls back uncommitted transactions that were present at the time of the crash. During recovery, mysqld displays output something like this:

    InnoDB: Database was not shut down normally.
    InnoDB: Starting recovery from log files...
    InnoDB: Starting log scan based on checkpoint at
    InnoDB: log sequence number 0 13674004
    InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
    InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
    InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
    InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
    ...
    InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
    InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
    InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
    InnoDB: 1 uncommitted transaction(s) which must be rolled back
    InnoDB: Starting rollback of uncommitted transactions
    InnoDB: Rolling back trx no 16745
    InnoDB: Rolling back of trx no 16745 completed
    InnoDB: Rollback of uncommitted transactions completed
    InnoDB: Starting an apply batch of log records to the database...
    InnoDB: Apply batch completed
    InnoDB: Started
    mysqld: ready for connections

If your database gets corrupted or your disk fails, you have to do the recovery from a backup. In the case of corruption, you should first find a backup that is not corrupted. After restoring the base backup, do the recovery from the binary log files using mysqlbinlog and mysql to restore the changes performed after the backup was made.

In some cases of database corruption it is enough just to dump, drop, and re-create one or a few corrupt tables. You can use the `CHECK TABLE` SQL statement to check whether a table is corrupt, although `CHECK TABLE` naturally cannot detect every possible kind of corruption. You can use `innodb_tablespace_monitor` to check the integrity of the file space management inside the tablespace files.

In some cases, apparent database page corruption is actually due to the operating system corrupting its own file cache, and the data on disk may be okay. It is best first to try restarting your computer. Doing so may eliminate errors that appeared to be database page corruption.

#### 13.2.9.1. Forcing `InnoDB` Recovery

If there is database page corruption, you may want to dump your tables from the database with `SELECT INTO OUTFILE`. Usually, most of the data obtained in this way is intact. Even so, the corruption may cause `SELECT * FROM `tbl_name`` statements or `InnoDB` background operations to crash or assert, or even to cause `InnoDB` roll-forward recovery to crash. Starting from MySQL 3.23.44, there is an `InnoDB` variable that you can use to force the `InnoDB` storage engine to start up, and you can also prevent background operations from running, so that you are able to dump your tables. For example, you can add the following line to the `[mysqld]` section of your option file before restarting the server:

    [mysqld]
    innodb_force_recovery = 4

Before MySQL 4.0, use this syntax instead:

    [mysqld]
    set-variable = innodb_force_recovery=4

The allowable non-zero values for `innodb_force_recovery` follow. A larger number includes all precautions of smaller numbers. If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 6 is more drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

- `1` (`SRV_FORCE_IGNORE_CORRUPT`)

  Let the server run even if it detects a corrupt page. Try to make `SELECT * FROM `tbl_name`` jump over corrupt index records and pages, which helps in dumping tables.

- `2` (`SRV_FORCE_NO_BACKGROUND`)

  Prevent the main thread from running. If a crash would occur during the purge operation, this recovery value prevents it.

- `3` (`SRV_FORCE_NO_TRX_UNDO`)

  Do not run transaction rollbacks after recovery.

- `4` (`SRV_FORCE_NO_IBUF_MERGE`)

  Prevent also insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.

- `5` (`SRV_FORCE_NO_UNDO_LOG_SCAN`)

  Do not look at undo logs when starting the database: `InnoDB` treats even incomplete transactions as committed.

- `6` (`SRV_FORCE_NO_LOG_REDO`)

  Do not do the log roll-forward in connection with recovery.

Starting from MySQL 3.23.53 and 4.0.4, you can `SELECT` from tables to dump them, or `DROP` or `CREATE` a table even if forced recovery is used. If you know that a certain table is causing a crash in rollback, you can drop it. You can use this also to stop a runaway rollback caused by a failing mass import or `ALTER TABLE`. You can kill the mysqld process and set `innodb_force_recovery` to `3` to bring the database up without the rollback, then `DROP` the table that is causing the runaway rollback.

The database must not otherwise be used with any non-zero value of `innodb_force_recovery`. As a safety measure, `InnoDB` prevents users from performing `INSERT`, `UPDATE`, or `DELETE` operations when `innodb_force_recovery` is greater than 0.

#### 13.2.9.2. Checkpoints

`InnoDB` implements a checkpoint mechanism known as “fuzzy” checkpointing. `InnoDB` flushes modified database pages from the buffer pool in small batches. There is no need to flush the buffer pool in one single batch, which would in practice stop processing of user SQL statements during the checkpointing process.

During crash recovery, `InnoDB` looks for a checkpoint label written to the log files. It knows that all modifications to the database before the label are present in the disk image of the database. Then `InnoDB` scans the log files forward from the checkpoint, applying the logged modifications to the database.

`InnoDB` writes to its log files on a rotating basis. All committed modifications that make the database pages in the buffer pool different from the images on disk must be available in the log files in case `InnoDB` has to do a recovery. This means that when `InnoDB` starts to reuse a log file, it has to make sure that the database page images on disk contain the modifications logged in the log file that `InnoDB` is going to reuse. In other words, `InnoDB` must create a checkpoint and this often involves flushing of modified database pages to disk.

The preceding description explains why making your log files very large may save disk I/O in checkpointing. It often makes sense to set the total size of the log files as big as the buffer pool or even bigger. The drawback of using large log files is that crash recovery can take longer because there is more logged information to apply to the database.

### 13.2.10. Moving an `InnoDB` Database to Another Machine

On Windows, `InnoDB` always stores database and table names internally in lowercase. To move databases in a binary format from Unix to Windows or from Windows to Unix, you should have all table and database names in lowercase. A convenient way to accomplish this is to add the following line to the `[mysqld]` section of your `my.cnf` or `my.ini` file before creating any databases or tables:

    [mysqld]
    set-variable = lower_case_table_names=1

Like `MyISAM` data files, `InnoDB` data and log files are binary-compatible on all platforms having the same floating-point number format. You can move an `InnoDB` database simply by copying all the relevant files listed in Section 13.2.9, “Backing Up and Recovering an `InnoDB` Database”. If the floating-point formats differ but you have not used `FLOAT` or `DOUBLE` data types in your tables, then the procedure is the same: simply copy the relevant files. If the formats differ and your tables contain floating-point data, you must use mysqldump to dump your tables on one machine and then import the dump files on the other machine.

One way to increase performance is to switch off autocommit mode when importing data, assuming that the tablespace has enough space for the big rollback segment that the import transactions generate. Do the commit only after importing a whole table or a segment of a table.

### 13.2.11. `InnoDB` Transaction Model and Locking

In the `InnoDB` transaction model, the goal is to combine the best properties of a multi-versioning database with traditional two-phase locking. `InnoDB` does locking on the row level and runs queries as non-locking consistent reads by default, in the style of Oracle. The lock table in `InnoDB` is stored so space-efficiently that lock escalation is not needed: Typically several users are allowed to lock every row in the database, or any random subset of the rows, without `InnoDB` running out of memory.

#### 13.2.11.1. `InnoDB` Lock Modes

`InnoDB` implements standard row-level locking where there are two types of locks:

- A shared (`S`) lock allows a transaction to read a row (tuple).

- An exclusive (`X`) lock allows a transaction to update or delete a row.

If transaction `T1` holds a shared (`S`) lock on tuple `t`, then

- A request from some distinct transaction `T2` for an `S` lock on `t` can be granted immediately. As a result, both `T1` and `T2` hold an `S` lock on `t`.

- A request from some distinct transaction `T2` for an `X` lock on `t` cannot be granted immediately.

If a transaction `T1` holds an exclusive (`X`) lock on tuple `t`, then a request from some distinct transaction `T2` for a lock of either type on `t` cannot be granted immediately. Instead, transaction `T2` has to wait for transaction `T1` to release its lock on tuple `t`.

Additionally, `InnoDB` supports multiple granularity locking which allows coexistence of record locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table locks in `InnoDB`. The idea behind intention locks is for a transaction to indicate which type of lock (shared or exclusive) it will require later for a row in that table. There are two types of intention locks used in `InnoDB` (assume that transaction `T` has requested a lock of the indicated type on table `R`):

- Intention shared (`IS`): Transaction `T` intends to set `S` locks on individual rows in table `R`.

- Intention exclusive (`IX`): Transaction `T` intends to set `X` locks on those rows.

The intention locking protocol is as follows:

- Before a given transaction can acquire an `S` lock on a given row, it must first acquire an `IS` or stronger lock on the table containing that row.

- Before a given transaction can acquire an `X` lock on a given row, it must first acquire an `IX` lock on the table containing that row.

These rules can be conveniently summarized by means of a lock type compatibility matrix:

| `X` | `IX` | `S` | `IS`
`X` | Conflict | Conflict | Conflict | Conflict
`IX` | Conflict | Compatible | Conflict | Compatible
`S` | Conflict | Conflict | Compatible | Compatible
`IS` | Conflict | Compatible | Compatible | Compatible

A lock is granted to a requesting transaction if it is compatible with existing locks. A lock is not granted to a requesting transaction if it conflicts with existing locks. A transaction waits until the conflicting existing lock is released. If a lock request conflicts with an existing lock and cannot be granted because it would cause deadlock, an error occurs.

Thus, intention locks do not block anything except full table requests (for example, `LOCK TABLES ... WRITE`). The main purpose of `IX` and `IS` locks is to show that someone is locking a row, or going to lock a row in the table.

The following example illustrates how an error can occur when a lock request would cause a deadlock. The example involves two clients, A and B.

First, client A creates a table containing one row, and then begins a transaction. Within the transaction, A obtains an `S` lock on the row by selecting it in share mode:

    mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
    Query OK, 0 rows affected (1.07 sec)

    mysql> INSERT INTO t (i) VALUES(1);
    Query OK, 1 row affected (0.09 sec)

    mysql> START TRANSACTION;
    Query OK, 0 rows affected (0.00 sec)

    mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
    +------+
    | i    |
    +------+
    |    1 |
    +------+
    1 row in set (0.10 sec)

Next, client B begins a transaction and attempts to delete the row from the table:

    mysql> START TRANSACTION;
    Query OK, 0 rows affected (0.00 sec)

    mysql> DELETE FROM t WHERE i = 1;

The delete operation requires an `X` lock. The lock cannot be granted because it is incompatible with the `S` lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks.

Finally, client A also attempts to delete the row from the table:

    mysql> DELETE FROM t WHERE i = 1;
    ERROR 1213 (40001): Deadlock found when trying to get lock;
    try restarting transaction

Deadlock occurs here because client A needs an `X` lock to delete the row. However, that lock request cannot be granted because client B already has a request for an `X` lock and is waiting for client A to release its `S` lock. Nor can the `S` lock held by A be upgraded to an `X` lock because of the prior request by B for an `X` lock. As a result, `InnoDB` generates an error for client A and releases its locks. At that point, the lock request for client B can be granted and B deletes the row from the table.

#### 13.2.11.2. `InnoDB` and `AUTOCOMMIT`

In `InnoDB`, all user activity occurs inside a transaction. If the autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts new connections with autocommit enabled.

If the autocommit mode is switched off with `SET AUTOCOMMIT = 0`, then we can consider that a user always has a transaction open. An SQL `COMMIT` or `ROLLBACK` statement ends the current transaction and a new one starts. A `COMMIT` means that the changes made in the current transaction are made permanent and become visible to other users. A `ROLLBACK` statement, on the other hand, cancels all modifications made by the current transaction. Both statements release all `InnoDB` locks that were set during the current transaction.

If the connection has autocommit enabled, the user can still perform a multiple-statement transaction by starting it with an explicit `START TRANSACTION` or `BEGIN` statement and ending it with `COMMIT` or `ROLLBACK`.

#### 13.2.11.3. `InnoDB` and `TRANSACTION ISOLATION LEVEL`

In terms of the SQL:1992 transaction isolation levels, the `InnoDB` default is `REPEATABLE READ`. Starting from MySQL 4.0.5, `InnoDB` offers all four different transaction isolation levels described by the SQL standard. You can set the default isolation level for all connections by using the `--transaction-isolation` option on the command line or in an option file. For example, you can set the option in the `[mysqld]` section of an option file like this:

    [mysqld]
    transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
                             | REPEATABLE-READ | SERIALIZABLE}

A user can change the isolation level for a single session or for all new incoming connections with the `SET TRANSACTION` statement. Its syntax is as follows:

    SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
                           {READ UNCOMMITTED | READ COMMITTED
                            | REPEATABLE READ | SERIALIZABLE}

Note that there are hyphens in the level names for the `--transaction-isolation` option, but not for the `SET TRANSACTION` statement.

The default behavior is to set the isolation level for the next (not started) transaction. If you use the `GLOBAL` keyword, the statement sets the default transaction level globally for all new connections created from that point on (but not for existing connections). You need the `SUPER` privilege to do this. Using the `SESSION` keyword sets the default transaction level for all future transactions performed on the current connection.

Any client is free to change the session isolation level (even in the middle of a transaction), or the isolation level for the next transaction.

Before MySQL 3.23.50, `SET TRANSACTION` had no effect on `InnoDB` tables. Before 4.0.5, only `REPEATABLE READ` and `SERIALIZABLE` were available.

You can determine the global and session transaction isolation levels by checking the value of the `tx_isolation` system variable with these statements:

    SELECT @@global.tx_isolation;
    SELECT @@tx_isolation;

In row-level locking, `InnoDB` uses next-key locking. That means that besides index records, `InnoDB` can also lock the “gap” preceding an index record to block insertions by other users immediately before the index record. A next-key lock refers to a lock that locks an index record and the gap before it. A gap lock refers to a lock that only locks a gap before some index record. Next-key locking for searches or index scans can be disabled by enabling the `innodb_locks_unsafe_for_binlog` system variable.

A detailed description of each isolation level in `InnoDB` follows:

- `READ UNCOMMITTED`

  `SELECT` statements are performed in a non-locking fashion, but a possible earlier version of a record might be used. Thus, using this isolation level, such reads are not consistent. This is also called a “dirty read.” Otherwise, this isolation level works like `READ COMMITTED`.

- `READ COMMITTED`

  A somewhat Oracle-like isolation level. All `SELECT ... FOR UPDATE` and `SELECT ... LOCK IN SHARE MODE` statements lock only the index records, not the gaps before them, and thus allow the free insertion of new records next to locked records. `UPDATE` and `DELETE` statements using a unique index with a unique search condition lock only the index record found, not the gap before it. In range-type `UPDATE` and `DELETE` statements, `InnoDB` must set next-key or gap locks and block insertions by other users to the gaps covered by the range. This is necessary because “phantom rows” must be blocked for MySQL replication and recovery to work.

  Consistent reads behave as in Oracle: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 13.2.11.4, “Consistent Non-Locking Read”.

- `REPEATABLE READ`

  This is the default isolation level of `InnoDB`. `SELECT ... FOR UPDATE`, `SELECT ... LOCK IN SHARE MODE`, `UPDATE`, and `DELETE` statements that use a unique index with a unique search condition lock only the index record found, not the gap before it. With other search conditions, these operations employ next-key locking, locking the index range scanned with next-key or gap locks, and block new insertions by other users.

  In consistent reads, there is an important difference from the `READ COMMITTED` isolation level: All consistent reads within the same transaction read the same snapshot established by the first read. This convention means that if you issue several plain `SELECT` statements within the same transaction, these `SELECT` statements are consistent also with respect to each other. See Section 13.2.11.4, “Consistent Non-Locking Read”.

- `SERIALIZABLE`

  This level is like `REPEATABLE READ`, but `InnoDB` implicitly converts all plain `SELECT` statements to `SELECT ... LOCK IN SHARE MODE`.

#### 13.2.11.4. Consistent Non-Locking Read

A consistent read means that `InnoDB` uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by earlier statements within the same transaction. Note that the exception to the rule causes the following anomaly: if you update some rows in a table, a `SELECT` will see the latest version of the updated rows, but it might also see older versions of any rows. If other users simultaneously update the same table, the anomaly means that you may see the table in a state that never existed in the database.

If you are running with the default `REPEATABLE READ` isolation level, all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

Consistent read is the default mode in which `InnoDB` processes `SELECT` statements in `READ COMMITTED` and `REPEATABLE READ` isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other users are free to modify those tables at the same time a consistent read is being performed on the table.

Note that consistent read does not work over `DROP TABLE` and over `ALTER TABLE`. Consistent read does not work over `DROP TABLE` because MySQL can't use a table that has been dropped and `InnoDB` destroys the table. Consistent read does not work over `ALTER TABLE` because `ALTER TABLE` works by making a temporary copy of the original table and deleting the original table when the temporary copy is built. When you reissue a consistent read within a transaction, rows in the new table are not visible because those rows did not exist when the transaction's snapshot was taken.

#### 13.2.11.5. `SELECT ... FOR UPDATE` and `SELECT ... LOCK IN SHARE MODE` Locking Reads

In some circumstances, a consistent read is not convenient. For example, you might want to add a new row into your table `child`, and make sure that the child has a parent in table `parent`. The following example shows how to implement referential integrity in your application code.

Suppose that you use a consistent read to read the table `parent` and indeed see the parent of the child in the table. Can you safely add the child row to table `child`? No, because it may happen that meanwhile some other user deletes the parent row from the table `parent` without you being aware of it.

The solution is to perform the `SELECT` in a locking mode using `LOCK IN SHARE MODE`:

    SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

Performing a read in share mode means that we read the latest available data, and set a shared mode lock on the rows we read. A shared mode lock prevents others from updating or deleting the row we have read. Also, if the latest data belongs to a yet uncommitted transaction of another client connection, we wait until that transaction commits. After we see that the preceding query returns the parent `'Jones'`, we can safely add the child record to the `child` table and commit our transaction.

Let us look at another example: We have an integer counter field in a table `child_codes` that we use to assign a unique identifier to each child added to table `child`. Obviously, using a consistent read or a shared mode read to read the present value of the counter is not a good idea because two users of the database may then see the same value for the counter, and a duplicate-key error occurs if two users attempt to add children with the same identifier to the table.

Here, `LOCK IN SHARE MODE` is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when attempting to update the counter.

In this case, there are two good ways to implement the reading and incrementing of the counter: (1) update the counter first by incrementing it by 1 and only after that read it, or (2) read the counter first with a lock mode `FOR UPDATE`, and increment after that. The latter approach can be implemented as follows:

    SELECT counter_field FROM child_codes FOR UPDATE;
    UPDATE child_codes SET counter_field = counter_field + 1;

A `SELECT ... FOR UPDATE` reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL `UPDATE` would set on the rows.

The preceding description is merely an example of how `SELECT ... FOR UPDATE` works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

    UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
    SELECT LAST_INSERT_ID();

The `SELECT` statement merely retrieves the identifier information (specific to the current connection). It does not access any table.

Locks set by `IN SHARE MODE` and `FOR UPDATE` reads are released when the transaction is committed or rolled back.

### Note

Locking of rows for update using `SELECT FOR UPDATE` only applies when `AUTOCOMMIT` is switched off. If `AUTOCOMMIT` is on, then the rows matching the specification are not locked.

#### 13.2.11.6. Next-Key Locking: Avoiding the Phantom Problem

In row-level locking, `InnoDB` uses an algorithm called next-key locking. `InnoDB` performs the row-level locking in such a way that when it searches or scans an index of a table, it sets shared or exclusive locks on the index records it encounters. Thus, the row-level locks are actually index record locks.

The next-key locks that `InnoDB` sets on index records also affect the “gap” before that index record. If a user has a shared or exclusive lock on record `R` in an index, another user cannot insert a new index record immediately before `R` in the index order. (A gap lock refers to a lock that only locks a gap before some index record.)

This next-key locking of gaps is done to prevent the so-called “phantom problem.” Suppose that you want to read and lock all children from the `child` table having an identifier value greater than 100, with the intention of updating some column in the selected rows later:

    SELECT * FROM child WHERE id > 100 FOR UPDATE;

Suppose that there is an index on the `id` column. The query scans that index starting from the first record where `id` is bigger than 100. If the locks set on the index records would not lock out inserts made in the gaps, a new row might meanwhile be inserted to the table. If you execute the same `SELECT` within the same transaction, you would see a new row in the result set returned by the query. This is contrary to the isolation principle of transactions: A transaction should be able to run so that the data it has read does not change during the transaction. If we regard a set of rows as a data item, the new “phantom” child would violate this isolation principle.

When `InnoDB` scans an index, it can also lock the gap after the last record in the index. Just that happens in the previous example: The locks set by `InnoDB` prevent any insert to the table where `id` would be bigger than 100.

You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking allows you to “lock” the non-existence of something in your table.

#### 13.2.11.7. An Example of Consistent Read in `InnoDB`

Suppose that you are running in the default `REPEATABLE READ` isolation level. When you issue a consistent read (that is, an ordinary `SELECT` statement), `InnoDB` gives your transaction a timepoint according to which your query sees the database. If another transaction deletes a row and commits after your timepoint was assigned, you do not see the row as having been deleted. Inserts and updates are treated similarly.

You can advance your timepoint by committing your transaction and then doing another `SELECT`.

This is called multi-versioned concurrency control.

                   User A                 User B

               SET AUTOCOMMIT=0;      SET AUTOCOMMIT=0;
    time
    |          SELECT * FROM t;
    |          empty set
    |                                 INSERT INTO t VALUES (1, 2);
    |
    v          SELECT * FROM t;
               empty set
                                      COMMIT;

               SELECT * FROM t;
               empty set

               COMMIT;

               SELECT * FROM t;
               ---------------------
               |    1    |    2    |
               ---------------------
               1 row in set

In this example, user A sees the row inserted by B only when B has committed the insert and A has committed as well, so that the timepoint is advanced past the commit of B.

If you want to see the “freshest” state of the database, you should use either the `READ COMMITTED` isolation level or a locking read:

    SELECT * FROM t LOCK IN SHARE MODE;

#### 13.2.11.8. Locks Set by Different SQL Statements in `InnoDB`

A locking read, an `UPDATE`, or a `DELETE` generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter if there are `WHERE` conditions in the statement that would exclude the row. `InnoDB` does not remember the exact `WHERE` condition, but only knows which index ranges were scanned. The record locks are normally next-key locks that also block inserts to the “gap” immediately before the record.

If the locks to be set are exclusive, `InnoDB` always retrieves also the clustered index record and sets a lock on it.

If you do not have indexes suitable for your statement and MySQL has to scan the whole table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table. It is important to create good indexes so that your queries do not unnecessarily need to scan many rows.

For `SELECT ... FOR UPDATE` or `SELECT ... IN SHARE MODE`, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the `WHERE` clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a `UNION`, scanned (and locked) rows from a table might be inserted into a temporary table before evaluation whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution.

`InnoDB` sets specific types of locks as follows:

- `SELECT ... FROM` is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to `SERIALIZABLE`. For `SERIALIZABLE` level, this sets shared next-key locks on the index records it encounters.

- `SELECT ... FROM ... LOCK IN SHARE MODE` sets shared next-key locks on all index records the read encounters.

- `SELECT ... FROM ... FOR UPDATE` sets exclusive next-key locks on all index records the read encounters.

- `UPDATE ... WHERE ...` sets an exclusive next-key lock on every record the search encounters.

- `DELETE FROM ... WHERE ...` sets an exclusive next-key lock on every record the search encounters.

- `INSERT INTO ... VALUES (...)` sets an exclusive lock on the inserted row. Note that this lock is not a next-key lock and does not prevent other users from inserting to the gap before the inserted row. If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

- `REPLACE` is done like an `INSERT` if there is no collision on a unique key. Otherwise, an exclusive next-key lock is placed on the row that has to be updated.

- While initializing a previously specified `AUTO_INCREMENT` column on a table, `InnoDB` sets an exclusive lock on the end of the index associated with the `AUTO_INCREMENT` column. In accessing the auto-increment counter, `InnoDB` uses a specific table lock mode `AUTO-INC` where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Note that other clients cannot insert into the table while the `AUTO-INC` table lock is held; see Section 13.2.11.2, “`InnoDB` and `AUTOCOMMIT`”.

  Before MySQL 3.23.50, `SHOW TABLE STATUS` applied to a table with an `AUTO_INCREMENT` column sets an exclusive row-level lock to the high end of the `AUTO_INCREMENT` index. This means also that `SHOW TABLE STATUS` could cause a deadlock of transactions, something that may surprise users. Starting from MySQL 3.23.50, `InnoDB` fetches the value of a previously initialized `AUTO_INCREMENT` column without setting any locks.

- `INSERT INTO T SELECT ... FROM S WHERE ...` sets an exclusive (non-next-key) lock on each row inserted into `T`. It does the search on `S` as a consistent read, but sets shared next-key locks on `S` if MySQL binary logging is turned on. `InnoDB` has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement has to be executed in exactly the same way it was done originally.

- `CREATE TABLE ... SELECT ...` performs the `SELECT` as a consistent read or with shared locks, as in the previous item.

- If a `FOREIGN KEY` constraint is defined on a table, any insert, update, or delete that requires the constraint condition to be checked sets shared record-level locks on the records that it looks at to check the constraint. `InnoDB` also sets these locks in the case where the constraint fails.

- `LOCK TABLES` sets table locks, but it is the higher MySQL layer above the `InnoDB` layer that sets these locks. Beginning with MySQL 4.0.20 and 4.1.2, `InnoDB` is aware of table locks if `innodb_table_locks=1` (the default) and `AUTOCOMMIT=0`, and the MySQL layer above `InnoDB` knows about row-level locks. Otherwise, InnoDB's automatic deadlock detection cannot detect deadlocks where such table locks are involved. Also, because the higher MySQL layer does not know about row-level locks, it is possible to get a table lock on a table where another user currently has row-level locks. However, this does not endanger transaction integrity, as discussed in Section 13.2.11.10, “Deadlock Detection and Rollback”. See also Section 13.2.17, “Restrictions on `InnoDB` Tables”.

#### 13.2.11.9. Implicit Transaction Commit and Rollback

By default, MySQL begins each client connection with autocommit mode enabled. When autocommit is enabled, MySQL does a commit after each SQL statement if that statement did not return an error. If an SQL statement returns an error, the commit or rollback behavior depends on the error. See Section 13.2.16, “`InnoDB` Error Handling”.

If you have the autocommit mode off and close a connection without explicitly committing the final transaction, MySQL rolls back that transaction.

For details about which statements implicitly end a transaction, as if you had done a `COMMIT` before executing the statement, see Section 12.4.3, “Statements That Cause an Implicit Commit”.

#### 13.2.11.10. Deadlock Detection and Rollback

`InnoDB` automatically detects a deadlock of transactions and rolls back a transaction or transactions to break the deadlock. Starting from MySQL 4.0.5, `InnoDB` tries to pick small transactions to roll back, the size of a transaction being determined by the number of rows inserted, updated, or deleted. Prior to 4.0.5, `InnoDB` always rolled back the transaction whose lock request was the last one to build a deadlock, that is, a cycle in the “waits-for” graph of transactions.

Beginning with MySQL 4.0.20 and 4.1.2, `InnoDB` is aware of table locks if `innodb_table_locks=1` (the default) and `AUTOCOMMIT=0`, and the MySQL layer above `InnoDB` knows about row-level locks. Before that, `InnoDB` cannot detect deadlocks where a table lock set by a MySQL `LOCK TABLES` statement is involved, or if a lock set by another storage engine than `InnoDB` is involved. You have to resolve these situations by setting the value of the `innodb_lock_wait_timeout` system variable.

When `InnoDB` performs a complete rollback of a transaction, all locks set by the transaction are released. However, if just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because `InnoDB` stores row locks in a format such that it cannot know afterward which lock was set by which statement.

#### 13.2.11.11. How to Cope with Deadlocks

Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.

`InnoDB` uses automatic row-level locking. You can get deadlocks even in the case of transactions that just insert or delete a single row. That is because these operations are not really “atomic”; they automatically set locks on the (possibly several) index records of the row inserted or deleted.

You can cope with deadlocks and reduce the likelihood of their occurrence with the following techniques:

- Use `SHOW INNODB STATUS` to determine the cause of the latest deadlock. That can help you to tune your application to avoid deadlocks. This strategy can be used as of MySQL 3.23.52 and 4.0.3, depending on your MySQL series. From 4.1.2 on, use `SHOW ENGINE INNODB STATUS`.

- Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.

- Commit your transactions often. Small transactions are less prone to collision.

- If you are using locking reads (`SELECT ... FOR UPDATE` or `... LOCK IN SHARE MODE`), try using a lower isolation level such as `READ COMMITTED`.

- Access your tables and rows in a fixed order. Then transactions form well-defined queues and do not deadlock.

- Add well-chosen indexes to your tables. Then your queries need to scan fewer index records and consequently set fewer locks. Use `EXPLAIN SELECT` to determine which indexes the MySQL server regards as the most appropriate for your queries.

- Use less locking. If you can afford to allow a `SELECT` to return data from an old snapshot, do not add the clause `FOR UPDATE` or `LOCK IN SHARE MODE` to it. Using the `READ COMMITTED` isolation level is good here, because each consistent read within the same transaction reads from its own fresh snapshot.

- If nothing else helps, serialize your transactions with table-level locks. The correct way to use `LOCK TABLES` with transactional tables, such as `InnoDB` tables, is to set `AUTOCOMMIT = 0` and not to call `UNLOCK TABLES` until after you commit the transaction explicitly. For example, if you need to write to table `t1` and read from table `t2`, you can do this:

      SET AUTOCOMMIT=0;
      LOCK TABLES t1 WRITE, t2 READ, ...;
      ... do something with tables t1 and t2 here ...
      COMMIT;
      UNLOCK TABLES;

  Table-level locks make your transactions queue nicely, and deadlocks are avoided.

- Another way to serialize transactions is to create an auxiliary “semaphore” table that contains just a single row. Have each transaction update that row before accessing other tables. In that way, all transactions happen in a serial fashion. Note that the `InnoDB` instant deadlock detection algorithm also works in this case, because the serializing lock is a row-level lock. With MySQL table-level locks, the timeout method must be used to resolve deadlocks.

- In applications that use `AUTOCOMMIT=1` and MySQL's `LOCK TABLES` command, `InnoDB`'s internal table locks that were present from 4.0.20 to 4.0.23 can cause deadlocks. Starting from 4.0.22, you can set `innodb_table_locks=0` in `my.cnf` to fall back to the old behavior and remove the problem. 4.0.24 does not set `InnoDB` table locks if `AUTOCOMMIT=1`.

### 13.2.12. `InnoDB` Performance Tuning Tips

- In `InnoDB`, having a long `PRIMARY KEY` wastes a lot of disk space because its value must be stored with every secondary index record. (See Section 13.2.14, “`InnoDB` Table and Index Structures”.) Create an `AUTO_INCREMENT` column as the primary key if your primary key is long.

- If the Unix `top` tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. Maybe you are making too many transaction commits, or the buffer pool is too small. Making the buffer pool bigger can help, but do not set it equal to more than 80% of physical memory.

- Wrap several modifications into one transaction. `InnoDB` must flush the log to disk at each transaction commit if that transaction made modifications to the database. The rotation speed of a disk is typically at most 167 revolutions/second, which constrains the number of commits to the same 167th of a second if the disk does not “fool” the operating system.

- If you can afford the loss of some of the latest committed transactions if a crash occurs, you can set the `innodb_flush_log_at_trx_commit` parameter to 0. `InnoDB` tries to flush the log once per second anyway, although the flush is not guaranteed.

- Make your log files big, even as big as the buffer pool. When `InnoDB` has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint. Small log files cause many unnecessary disk writes. The drawback of big log files is that the recovery time is longer.

- Make the log buffer quite large as well (on the order of 8MB).

- Use the `VARCHAR` data type instead of `CHAR` if you are storing variable-length strings or if the column may contain many `NULL` values. A `CHAR(`N`)` column always takes `N` characters to store data, even if the string is shorter or its value is `NULL`. Smaller tables fit better in the buffer pool and reduce disk I/O.

- (Relevant from 3.23.39 up.) In some versions of GNU/Linux and Unix, flushing files to disk with the Unix `fsync()` call (which `InnoDB` uses by default) and other similar methods is surprisingly slow. If you are dissatisfied with database write performance, you might try setting the `innodb_flush_method` parameter to `O_DSYNC`. Although `O_DSYNC` seems to be slower on most systems, yours might not be one of them.

- (Verified using MySQL 4.1, assumed for other MySQL versions, given that this is a platform architecture issue.) When using the `InnoDB` storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), it is important to mount any filesystems used for storing `InnoDB`-related files using the `forcedirectio` option. (The default on Solaris 10/x86_64 is not to use this option.) Failure to use `forcedirectio` causes a serious degradation of `InnoDB`'s speed and performance on this platform.

  When using the `InnoDB` storage engine with a large `innodb_buffer_pool_size` value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), a significant performance gain can be achieved by placing `InnoDB` data files and log files on raw devices or on a separate direct I/O UFS filesystem (using mount option `forcedirectio`; see `mount_ufs(1M)`). Users of the Veritas filesystem VxFS should use the mount option `convosync=direct`.

  Other MySQL data files, such as those for `MyISAM` tables, should not be placed on a direct I/O filesystem. Executables or libraries must not be placed on a direct I/O filesystem.

- When importing data into `InnoDB`, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it with `SET AUTOCOMMIT` and `COMMIT` statements:

      SET AUTOCOMMIT=0;
      ... SQL import statements ...
      COMMIT;

  If you use the mysqldump option `--opt`, you get dump files that are fast to import into an `InnoDB` table, even without wrapping them with the `SET AUTOCOMMIT` and `COMMIT` statements.

- Beware of big rollbacks of mass inserts: `InnoDB` uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on server startup. The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or to use a special procedure. See Section 13.2.9.1, “Forcing `InnoDB` Recovery”.

- Beware also of other big disk-bound operations. Use `DROP TABLE` and `CREATE TABLE` to empty a table, not `DELETE FROM `tbl_name``.

- Use the multiple-row `INSERT` syntax to reduce communication overhead between the client and the server if you need to insert many rows:

      INSERT INTO yourtable VALUES (1,2), (5,5), ...;

  This tip is valid for inserts into any table, not just `InnoDB` tables.

- If you have `UNIQUE` constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

      SET UNIQUE_CHECKS=0;
      ... import operation ...
      SET UNIQUE_CHECKS=1;

  For big tables, this saves a lot of disk I/O because `InnoDB` can use its insert buffer to write secondary index records in a batch. Be certain that the data contains no duplicate keys. `UNIQUE_CHECKS` allows but does not require storage engines to ignore duplicate keys.

- If you have `FOREIGN KEY` constraints in your tables, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by turning the foreign key checks off for a while in the import session:

      SET FOREIGN_KEY_CHECKS=0;
      ... import operation ...
      SET FOREIGN_KEY_CHECKS=1;

  For big tables, this can save a lot of disk I/O.

- If you often have recurring queries for tables that are not updated frequently, use the query cache available as of MySQL 4.0:

      [mysqld]
      query_cache_type = ON
      query_cache_size = 10M

  In MySQL 4.0, the query cache works only with autocommit enabled. This restriction is removed in MySQL 4.1.1 and up.

- Unlike `MyISAM`, `InnoDB` does not store an index cardinality value in its tables. Instead, `InnoDB` computes a cardinality for a table the first time it accesses it after startup. With a large number of tables, this might take significant time. It is the initial table open operation that is important, so to “warm up” a table for later use, you might want to use it immediately after start up by issuing a statement such as `SELECT 1 FROM `tbl_name` LIMIT 1`.

MySQL Enterprise For optimization recommendations geared to your specific circumstances subscribe to the MySQL Enterprise Monitor. For more information see, http://www.mysql.com/products/enterprise/advisors.html.

#### 13.2.12.1. `SHOW ENGINE INNODB STATUS` and the `InnoDB` Monitors

Starting from MySQL 3.23.42, `InnoDB` includes `InnoDB` Monitors that print information about the `InnoDB` internal state. Starting from MySQL 3.23.52 and 4.0.3, you can use the `SHOW INNODB STATUS` SQL statement at any time to fetch the output of the standard `InnoDB` Monitor to your SQL client. (In MySQL 4.1.2 and up, the statement is `SHOW ENGINE INNODB STATUS`.) The information is useful in performance tuning. If you are using the mysql interactive SQL client, the output is more readable if you replace the usual semicolon statement terminator by `\G`: For a discussion about the `InnoDB` lock modes, see Section 13.2.11.1, “`InnoDB` Lock Modes”.

    mysql> SHOW INNODB STATUS\G

Another way to use `InnoDB` Monitors is to let them periodically write data to the standard output of the mysqld server. In this case, no output is sent to clients. When switched on, `InnoDB` Monitors print data about every 15 seconds. Server output usually is directed to the `.err` log in the MySQL data directory. This data is useful in performance tuning. On Windows, you must start the server from a command prompt in a console window with the `--console` option if you want to direct the output to the window rather than to the error log.

Monitor output includes the following types of information:

- Table and record locks held by each active transaction

- Lock waits of a transactions

- Semaphore waits of threads

- Pending file I/O requests

- Buffer pool statistics

- Purge and insert buffer merge activity of the main `InnoDB` thread

To cause the standard `InnoDB` Monitor to write to the standard output of mysqld, use the following SQL statement:

    CREATE TABLE innodb_monitor (a INT) TYPE=InnoDB;

The monitor can be stopped by issuing the following statement:

    DROP TABLE innodb_monitor;

The `CREATE TABLE` syntax is just a way to pass a command to the `InnoDB` engine through MySQL's SQL parser: The only things that matter are the table name `innodb_monitor` and that it be an `InnoDB` table. The structure of the table is not relevant at all for the `InnoDB` Monitor. If you shut down the server, the monitor does not restart automatically when you restart the server. You must drop the monitor table and issue a new `CREATE TABLE` statement to start the monitor. (This syntax may change in a future release.)

In a similar way, you can start `innodb_lock_monitor`, which is otherwise the same as `innodb_monitor` but also prints a lot of lock information. A separate `innodb_tablespace_monitor` prints a list of created file segments existing in the tablespace and also validates the tablespace allocation data structures. Starting from 3.23.44, there is `innodb_table_monitor` with which you can print the contents of the `InnoDB` internal data dictionary.

A sample of `InnoDB` Monitor output:

    mysql> SHOW INNODB STATUS\G
    *************************** 1. row ***************************
    Status:
    =====================================
    030709 13:00:59 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 18 seconds
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
    --Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the
    semaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
    a writer (thread id 32782) has reserved it in mode wait exclusive
    number of readers 1, waiters flag 1
    Last time read locked in file btr0sea.c line 731
    Last time write locked in file btr0sea.c line 1347
    Mutex spin waits 0, rounds 0, OS waits 0
    RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits
    375485
    ------------------------
    LATEST FOREIGN KEY ERROR
    ------------------------
    030709 13:00:59 Transaction:
    TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831
    inserting
    15 lock struct(s), heap size 2496, undo log entries 9
    MySQL thread id 25, query id 4668733 localhost heikki update
    insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
    Foreign key constraint fails for table test/ibtest11a:
    ,
      CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`,
      `D`) ON DELETE CASCADE ON UPDATE CASCADE
    Trying to add in child table, in index PRIMARY tuple:
     0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2:
     len 4; hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4:
     len 7; hex 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
    But in parent table test/ibtest11b, in index PRIMARY,
    the closest match we can find is record:
    RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex
    80000005; asc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex
    0000111ef3eb; asc ......;; 4: len 7; hex 800001001e0084; asc .......;; 5:
    len 3; hex 6b6864; asc khd;;
    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    030709 12:59:58
    *** (1) TRANSACTION:
    TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733
    inserting
    LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
    MySQL thread id 21, query id 4553379 localhost heikki update
    INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t',
    'e187358f','g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d
    %H:%i'),7
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
    symbole trx id 0 290252780 lock mode S waiting
    Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;
    asc aa35818;; 1:
    *** (2) TRANSACTION:
    TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782
    inserting
    130 lock struct(s), heap size 11584, undo log entries 437
    MySQL thread id 23, query id 4554396 localhost heikki update
    REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','',
    NULL,'h396', NULL, NULL, 7.31,7.31,7.31,200)
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
    symbole trx id 0 290251546 lock_mode X locks rec but not gap
    Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;
    asc aa35818;; 1:
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
    symbole trx id 0 290251546 lock_mode X locks gap before rec insert intention
    waiting
    Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230;
    asc aa35720;; 1:
    *** WE ROLL BACK TRANSACTION (1)
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 0 290328385
    Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
    Total number of lock structs in row lock hash table 70
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
    MySQL thread id 32, query id 4668737 localhost heikki
    show innodb status
    ---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id
    38929 inserting
    1 lock struct(s), heap size 320
    MySQL thread id 29, query id 4668736 localhost heikki update
    insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjg
    jlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
    ---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id
    28684 committing
    1 lock struct(s), heap size 320, undo log entries 1
    MySQL thread id 19, query id 4668734 localhost heikki update
    insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgj
    gjlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
    ---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id
    36880 starting index read
    LOCK WAIT 2 lock struct(s), heap size 320
    MySQL thread id 27, query id 4668644 localhost heikki Searching rows for
    update
    update ibtest11a set B = 'kHdkkkk' where A = 89572
    ------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
    RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index
    PRIMARY trx id 0 290328327 lock_mode X waiting
    Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00;
    asc supremum.;;
    ------------------
    ---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id
    34831 rollback of SQL statement
    ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
    MySQL thread id 25, query id 4668733 localhost heikki update
    insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
    ---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id
    32782
    58 lock struct(s), heap size 5504, undo log entries 159
    MySQL thread id 23, query id 4668732 localhost heikki update
    REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t',
    'e200498f','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d
    %H:%i'),
    ---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id
    30733 inserting
    4 lock struct(s), heap size 1024, undo log entries 165
    MySQL thread id 21, query id 4668735 localhost heikki update
    INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','',
    NULL,'h321', NULL, NULL, 7.31,7.31,7.31,200)
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (write thread)
    Pending normal aio reads: 0, aio writes: 0,
     ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
    Pending flushes (fsync) log: 0; buffer pool: 0
    151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
    25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf for space 0: size 1, free list len 19, seg size 21,
    85004 inserts, 85004 merged recs, 26669 merges
    Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
    1877.67 hash searches/s, 5121.10 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number 18 1212842764
    Log flushed up to   18 1212665295
    Last checkpoint at  18 1135877290
    0 pending log writes, 0 pending chkp writes
    4341 log i/o's done, 1.22 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total memory allocated 84966343; in additional pool allocated 1402624
    Buffer pool size   3200
    Free buffers       110
    Database pages     3074
    Modified db pages  2674
    Pending reads 0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages read 171380, created 51968, written 194688
    28.72 reads/s, 20.72 creates/s, 47.55 writes/s
    Buffer pool hit rate 999 / 1000
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    Main thread process no. 3004, id 7176, state: purging
    Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
    1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================

Some notes on the output:

- If the `TRANSACTIONS` section reports lock waits, your applications may have lock contention. The output can also help to trace the reasons for transaction deadlocks.

- The `SEMAPHORES` section reports threads waiting for a semaphore and statistics on how many times threads have needed a spin or a wait on a mutex or a rw-lock semaphore. A large number of threads waiting for semaphores may be a result of disk I/O, or contention problems inside `InnoDB`. Contention can be due to heavy parallelism of queries or problems in operating system thread scheduling. Setting `innodb_thread_concurrency` smaller than the default value can help in such situations.

- The `BUFFER POOL AND MEMORY` section gives you statistics on pages read and written. You can calculate from these numbers how many data file I/O operations your queries currently are doing.

- The `ROW OPERATIONS` section shows what the main thread is doing.

Beginning with MySQL 4.0.19, `InnoDB` sends diagnostic output to stderr or files instead of stdout or fixed-size memory buffers, to avoid potential buffer overflow errors. As a side effect, the output of `SHOW INNODB STATUS` is written to a status file in the MySQL data directory every fifteen seconds. The name of the file is `innodb_status.`pid``, where `pid` is the server process ID. `InnoDB` removes the file for a normal shutdown. If abnormal shutdowns have occurred, instances of these status files may be present and must be removed manually. Before removing them, you might want to examine them to see whether they contain useful information about the cause of abnormal shutdowns. Beginning with MySQL 4.0.21, the `innodb_status.`pid`` file is created only if the configuration option `innodb_status_file=1` is set.

### 13.2.13. Implementation of Multi-Versioning

Because `InnoDB` is a multi-versioned storage engine, it must keep information about old versions of rows in the tablespace. This information is stored in a data structure called a rollback segment (after an analogous data structure in Oracle).

Internally, `InnoDB` adds two fields to each row stored in the database. A 6-byte field indicates the transaction identifier for the last transaction that inserted or updated the row. Also, a deletion is treated internally as an update where a special bit in the row is set to mark it as deleted. Each row also contains a 7-byte field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.

`InnoDB` uses the information in the rollback segment to perform the undo operations needed in a transaction rollback. It also uses the information to build earlier versions of a row for a consistent read.

Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits. Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which `InnoDB` has assigned a snapshot that in a consistent read could need the information in the update undo log to build an earlier version of a database row.

You must remember to commit your transactions regularly, including those transactions that issue only consistent reads. Otherwise, `InnoDB` cannot discard data from the update undo logs, and the rollback segment may grow too big, filling up your tablespace.

The physical size of an undo log record in the rollback segment is typically smaller than the corresponding inserted or updated row. You can use this information to calculate the space need for your rollback segment.

In the `InnoDB` multi-versioning scheme, a row is not physically removed from the database immediately when you delete it with an SQL statement. Only when `InnoDB` can discard the update undo log record written for the deletion can it also physically remove the corresponding row and its index records from the database. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement that did the deletion.

In a scenario where the user inserts and deletes rows in smallish batches at about the same rate in the table, it is possible that the purge thread starts to lag behind, and the table grows bigger and bigger, making everything disk-bound and very slow. Even if the table would carry just 10MB of useful data, it may grow to occupy 10GB with all the dead rows. In such a case, it would be good to throttle new row operations, and allocate more resources for the purge thread. Starting with MySQL 4.0.22 and 4.1.6, the `innodb_max_purge_lag` system variable exists for exactly this purpose. See Section 13.2.5, “`InnoDB` Startup Options and System Variables”, for more information.

### 13.2.14. `InnoDB` Table and Index Structures

MySQL stores its data dictionary information for tables in `.frm` files in database directories. This is true for all MySQL storage engines. But every `InnoDB` table also has its own entry in the `InnoDB` internal data dictionary inside the tablespace. When MySQL drops a table or a database, it has to delete both an `.frm` file or files, and the corresponding entries inside the `InnoDB` data dictionary. This is the reason why you cannot move `InnoDB` tables between databases simply by moving the `.frm` files. It is also the reason why `DROP DATABASE` did not work for `InnoDB` type tables before MySQL 3.23.44.

Every `InnoDB` table has a special index called the clustered index where the data for the rows is stored. If you define a `PRIMARY KEY` on your table, the index of the primary key is the clustered index.

If you do not define a `PRIMARY KEY` for your table, MySQL picks the first `UNIQUE` index that has only `NOT NULL` columns as the primary key and `InnoDB` uses it as the clustered index. If there is no such index in the table, `InnoDB` internally generates a clustered index where the rows are ordered by the row ID that `InnoDB` assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. If a table is large, the clustered index architecture often saves a disk I/O when compared to the traditional solution. (In many database systems, data storage uses a different page from the index record.)

In `InnoDB`, the records in non-clustered indexes (also called secondary indexes) contain the primary key value for the row. `InnoDB` uses this primary key value to search for the row from the clustered index. Note that if the primary key is long, the secondary indexes use more space.

`InnoDB` compares `CHAR` and `VARCHAR` strings of different lengths such that the remaining length in the shorter string is treated as if padded with spaces.

#### 13.2.14.1. Physical Structure of an Index

All `InnoDB` indexes are B-trees where the index records are stored in the leaf pages of the tree. The default size of an index page is 16KB. When new records are inserted, `InnoDB` tries to leave 1/16 of the page free for future insertions and updates of the index records.

If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full. If the fill factor of an index page drops below 1/2, `InnoDB` tries to contract the index tree to free the page.

#### 13.2.14.2. Insert Buffering

It is a common situation in database applications that the primary key is a unique identifier and new rows are inserted in the ascending order of the primary key. Thus, the insertions to the clustered index do not require random reads from a disk.

On the other hand, 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`.

If an index record should be inserted to a non-unique secondary index, `InnoDB` checks whether the secondary index page is in the buffer pool. If that is the case, `InnoDB` does the insertion directly to the index page. If the index page is not found in the buffer pool, `InnoDB` inserts the record to a special insert buffer structure. The insert buffer is kept so small that it fits entirely in the buffer pool, and insertions can be done very fast.

Periodically, the insert buffer is merged into the secondary index trees in the database. Often it is possible to merge several insertions to the same page of the index tree, saving disk I/O operations. It has been measured that the insert buffer can speed up insertions into a table up to 15 times.

The insert buffer merging may continue to happen after the inserting transaction has been committed. In fact, it may continue to happen after a server shutdown and restart (see Section 13.2.9.1, “Forcing `InnoDB` Recovery”).

The insert buffer merging may take many hours, when many secondary indexes must be updated, and many rows have been inserted. During this time, disk I/O will be increased, which can cause significant slowdown on disk-bound queries. Another significant background I/O operation is the purge thread (see Section 13.2.13, “Implementation of Multi-Versioning”).

#### 13.2.14.3. Adaptive Hash Indexes

If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. `InnoDB` has a mechanism that monitors index searches made to the indexes defined for a table. If `InnoDB` notices that queries could benefit from building a hash index, it does so automatically.

Note that the hash index is always built based on an existing B-tree index on the table. `InnoDB` can build a hash index on a prefix of any length of the key defined for the B-tree, depending on the pattern of searches that `InnoDB` observes for the B-tree index. A hash index can be partial: It is not required that the whole B-tree index is cached in the buffer pool. `InnoDB` builds hash indexes on demand for those pages of the index that are often accessed.

In a sense, `InnoDB` tailors itself through the adaptive hash index mechanism to ample main memory, coming closer to the architecture of main-memory databases.

#### 13.2.14.4. Physical Row Structure

Records in `InnoDB` tables have the following characteristics:

- Each index record contains a six-byte header. The header is used to link together consecutive records, and also in row-level locking.

- Records in the clustered index contain fields for all user-defined columns. In addition, there is a six-byte field for the transaction ID and a seven-byte field for the roll pointer.

- If no primary key was defined for a table, each clustered index record also contains a six-byte row ID field.

- Each secondary index record contains also all the fields defined for the clustered index key.

- A record contains also a pointer to each field of the record. If the total length of the fields in a record is less than 128 bytes, the pointer is one byte; otherwise, two bytes. The array of these pointers is called the record directory. The area where these pointers point is called the data part of the record.

- Internally, `InnoDB` stores fixed-length character columns such as `CHAR(10)` in a fixed-length format. `InnoDB` truncates trailing spaces from `VARCHAR` columns. Note that MySQL may internally convert `CHAR` columns to `VARCHAR`. See Section 12.1.5.1, “Silent Column Specification Changes”.

- An SQL `NULL` value reserves 1 or 2 bytes in the record directory. Besides that, an SQL `NULL` value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data part of the record. The motivation behind reserving the fixed space for `NULL` values is that it enables an update of the column from `NULL` to a non-`NULL` value to be done in place without causing fragmentation of the index page.

### 13.2.15. `InnoDB` File Space Management and Disk I/O

#### 13.2.15.1. `InnoDB` Disk I/O

`InnoDB` uses simulated asynchronous disk I/O: `InnoDB` creates a number of threads to take care of I/O operations, such as read-ahead.

There are two read-ahead heuristics in `InnoDB`:

- In sequential read-ahead, if `InnoDB` notices that the access pattern to a segment in the tablespace is sequential, it posts in advance a batch of reads of database pages to the I/O system.

- In random read-ahead, if `InnoDB` notices that some area in a tablespace seems to be in the process of being fully read into the buffer pool, it posts the remaining reads to the I/O system.

`InnoDB` uses a novel file flush technique called doublewrite. It adds safety to recovery following an operating system crash or a power outage, and improves performance on most varieties of Unix by reducing the need for `fsync()` operations.

Doublewrite means that before writing pages to a data file, `InnoDB` first writes them to a contiguous tablespace area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer has 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.

#### 13.2.15.2. File Space Management

The data files that you define in the configuration file form the tablespace of `InnoDB`. The files are simply concatenated to form the tablespace. There is no striping in use. Currently, you cannot define where within the tablespace your tables are allocated. However, in a newly created tablespace, `InnoDB` allocates space starting from the first data file.

The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of 64 consecutive pages. The “files” inside a tablespace are called segments in `InnoDB`. The term “rollback segment” is somewhat confusing because it actually contains many tablespace segments.

Two segments are allocated for each index in `InnoDB`. One is for non-leaf nodes of the B-tree, the other is for the leaf nodes. The idea here is to achieve better sequentiality for the leaf nodes, which contain the data.

When a segment grows inside the tablespace, `InnoDB` allocates the first 32 pages to it individually. After that `InnoDB` starts to allocate whole extents to the segment. `InnoDB` can add to a large segment up to 4 extents at a time to ensure good sequentiality of data.

Some pages in the tablespace contain bitmaps of other pages, and therefore a few extents in an `InnoDB` tablespace cannot be allocated to segments as a whole, but only as individual pages.

When you ask for available free space in the tablespace by issuing a `SHOW TABLE STATUS` statement, `InnoDB` reports the extents that are definitely free in the tablespace. `InnoDB` always reserves some extents for cleanup and other internal purposes; these reserved extents are not included in the free space.

When you delete data from a table, `InnoDB` contracts the corresponding B-tree indexes. Whether the freed space becomes available for other users depends on whether the pattern of deletes frees individual pages or extents to the tablespace. Dropping a table or deleting all rows from it is guaranteed to release the space to other users, but remember that deleted rows are physically removed only in an (automatic) purge operation after they are no longer needed for transaction rollbacks or consistent reads. (See Section 13.2.13, “Implementation of Multi-Versioning”.)

#### 13.2.15.3. Defragmenting a Table

If there are random insertions into or deletions from the indexes of a table, the indexes may become fragmented. Fragmentation means that the physical ordering of the index pages on the disk is not close to the index ordering of the records on the pages, or that there are many unused pages in the 64-page blocks that were allocated to the index.

A symptom of fragmentation is that a table takes more space than it “should” take. How much that is exactly, is difficult to determine. All `InnoDB` data and indexes are stored in B-trees, and their fill factor may vary from 50% to 100%. Another symptom of fragmentation is that a table scan such as this takes more time than it “should” take:

    SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;

(In the preceding query, we are “fooling” the SQL optimizer into scanning the clustered index, rather than a secondary index.) Most disks can read 10 to 50MB/s, which can be used to estimate how fast a table scan should run.

It can speed up index scans if you periodically perform a “null” `ALTER TABLE` operation:

    ALTER TABLE tbl_name TYPE=InnoDB;

That causes MySQL to rebuild the table. Another way to perform a defragmentation operation is to use mysqldump to dump the table to a text file, drop the table, and reload it from the dump file.

If the insertions to an index are always ascending and records are deleted only from the end, the `InnoDB` filespace management algorithm guarantees that fragmentation in the index does not occur.

### 13.2.16. `InnoDB` Error Handling

Error handling in `InnoDB` is not always the same as specified in the SQL standard. According to the standard, any error during an SQL statement should cause the rollback of that statement. `InnoDB` sometimes rolls back only part of the statement, or the whole transaction. The following items describe how `InnoDB` performs error handling:

- If you run out of file space in the tablespace, a MySQL `Table is full` error occurs and `InnoDB` rolls back the SQL statement.

- A transaction deadlock or a timeout in a lock wait causes `InnoDB` to roll back the whole transaction.

  When a transaction rollback occurs due to a deadlock or lock wait timeout, it cancels the effect of the statements within the transaction. But if the start-transaction statement was `START TRANSACTION` or `BEGIN` statement, rollback does not cancel that statement. Further SQL statements become part of the transaction until the occurrence of `COMMIT`, `ROLLBACK`, or some SQL statement that causes an implicit commit.

- A duplicate-key error rolls back the SQL statement, if you have not specified the `IGNORE` option in your statement.

- A `row too long error` rolls back the SQL statement.

- Other errors are mostly detected by the MySQL layer of code (above the `InnoDB` storage engine level), and they roll back the corresponding SQL statement. Locks are not released in a rollback of a single SQL statement.

During such implicit rollbacks, as well as during the explicit `ROLLBACK` SQL statement, `SHOW PROCESSLIST` displays "Rolling back" in the `State` column for the connection (starting from MySQL 4.1.8).

#### 13.2.16.1. `InnoDB` Error Codes

The following is a non-exhaustive list of common `InnoDB`-specific errors that you may encounter, with information about why each occurs and how to resolve the problem.

- `1005 (ER_CANT_CREATE_TABLE)`

  Cannot create table. If the error message refers to `errno` 150, table creation failed because a foreign key constraint was not correctly formed. If the error message refers to `errno` -1, table creation probably failed because the table included a column name that matched the name of an internal InnoDB table.

- `1016 (ER_CANT_OPEN_FILE)`

  Cannot find the `InnoDB` table from the `InnoDB` data files, although the `.frm` file for the table exists. See Section 13.2.18.1, “Troubleshooting `InnoDB` Data Dictionary Operations”.

- `1114 (ER_RECORD_FILE_FULL)`

  `InnoDB` has run out of free space in the tablespace. You should reconfigure the tablespace to add a new data file.

- `1205 (ER_LOCK_WAIT_TIMEOUT)`

  Lock wait timeout expired. Transaction was rolled back.

- `1213 (ER_LOCK_DEADLOCK)`

  Transaction deadlock. You should rerun the transaction.

- `1216 (ER_NO_REFERENCED_ROW)`

  You are trying to add a row but there is no parent row, and a foreign key constraint fails. You should add the parent row first.

- `1217 (ER_ROW_IS_REFERENCED)`

  You are trying to delete a parent row that has children, and a foreign key constraint fails. You should delete the children first.

#### 13.2.16.2. Operating System Error Codes

To print the meaning of an operating system error number, use the perror program that comes with the MySQL distribution.

The following table provides a list of some common Linux system error codes. For a more complete list, see Linux source code.

- `1 (EPERM)`

  Operation not permitted

- `2 (ENOENT)`

  No such file or directory

- `3 (ESRCH)`

  No such process

- `4 (EINTR)`

  Interrupted system call

- `5 (EIO)`

  I/O error

- `6 (ENXIO)`

  No such device or address

- `7 (E2BIG)`

  Arg list too long

- `8 (ENOEXEC)`

  Exec format error

- `9 (EBADF)`

  Bad file number

- `10 (ECHILD)`

  No child processes

- `11 (EAGAIN)`

  Try again

- `12 (ENOMEM)`

  Out of memory

- `13 (EACCES)`

  Permission denied

- `14 (EFAULT)`

  Bad address

- `15 (ENOTBLK)`

  Block device required

- `16 (EBUSY)`

  Device or resource busy

- `17 (EEXIST)`

  File exists

- `18 (EXDEV)`

  Cross-device link

- `19 (ENODEV)`

  No such device

- `20 (ENOTDIR)`

  Not a directory

- `21 (EISDIR)`

  Is a directory

- `22 (EINVAL)`

  Invalid argument

- `23 (ENFILE)`

  File table overflow

- `24 (EMFILE)`

  Too many open files

- `25 (ENOTTY)`

  Inappropriate ioctl for device

- `26 (ETXTBSY)`

  Text file busy

- `27 (EFBIG)`

  File too large

- `28 (ENOSPC)`

  No space left on device

- `29 (ESPIPE)`

  Illegal seek

- `30 (EROFS)`

  Read-only file system

- `31 (EMLINK)`

  Too many links

The following table provides a list of some common Windows system error codes. For a complete list see the Microsoft Web site.

- `1 (ERROR_INVALID_FUNCTION)`

  Incorrect function.

- `2 (ERROR_FILE_NOT_FOUND)`

  The system cannot find the file specified.

- `3 (ERROR_PATH_NOT_FOUND)`

  The system cannot find the path specified.

- `4 (ERROR_TOO_MANY_OPEN_FILES)`

  The system cannot open the file.

- `5 (ERROR_ACCESS_DENIED)`

  Access is denied.

- `6 (ERROR_INVALID_HANDLE)`

  The handle is invalid.

- `7 (ERROR_ARENA_TRASHED)`

  The storage control blocks were destroyed.

- `8 (ERROR_NOT_ENOUGH_MEMORY)`

  Not enough storage is available to process this command.

- `9 (ERROR_INVALID_BLOCK)`

  The storage control block address is invalid.

- `10 (ERROR_BAD_ENVIRONMENT)`

  The environment is incorrect.

- `11 (ERROR_BAD_FORMAT)`

  An attempt was made to load a program with an incorrect format.

- `12 (ERROR_INVALID_ACCESS)`

  The access code is invalid.

- `13 (ERROR_INVALID_DATA)`

  The data is invalid.

- `14 (ERROR_OUTOFMEMORY)`

  Not enough storage is available to complete this operation.

- `15 (ERROR_INVALID_DRIVE)`

  The system cannot find the drive specified.

- `16 (ERROR_CURRENT_DIRECTORY)`

  The directory cannot be removed.

- `17 (ERROR_NOT_SAME_DEVICE)`

  The system cannot move the file to a different disk drive.

- `18 (ERROR_NO_MORE_FILES)`

  There are no more files.

- `19 (ERROR_WRITE_PROTECT)`

  The media is write protected.

- `20 (ERROR_BAD_UNIT)`

  The system cannot find the device specified.

- `21 (ERROR_NOT_READY)`

  The device is not ready.

- `22 (ERROR_BAD_COMMAND)`

  The device does not recognize the command.

- `23 (ERROR_CRC)`

  Data error (cyclic redundancy check).

- `24 (ERROR_BAD_LENGTH)`

  The program issued a command but the command length is incorrect.

- `25 (ERROR_SEEK)`

  The drive cannot locate a specific area or track on the disk.

- `26 (ERROR_NOT_DOS_DISK)`

  The specified disk or diskette cannot be accessed.

- `27 (ERROR_SECTOR_NOT_FOUND)`

  The drive cannot find the sector requested.

- `28 (ERROR_OUT_OF_PAPER)`

  The printer is out of paper.

- `29 (ERROR_WRITE_FAULT)`

  The system cannot write to the specified device.

- `30 (ERROR_READ_FAULT)`

  The system cannot read from the specified device.

- `31 (ERROR_GEN_FAILURE)`

  A device attached to the system is not functioning.

- `32 (ERROR_SHARING_VIOLATION)`

  The process cannot access the file because it is being used by another process.

- `33 (ERROR_LOCK_VIOLATION)`

  The process cannot access the file because another process has locked a portion of the file.

- `34 (ERROR_WRONG_DISK)`

  The wrong diskette is in the drive. Insert %2 (Volume Serial Number: %3) into drive %1.

- `36 (ERROR_SHARING_BUFFER_EXCEEDED)`

  Too many files opened for sharing.

- `38 (ERROR_HANDLE_EOF)`

  Reached the end of the file.

- `39 (ERROR_HANDLE_DISK_FULL)`

  The disk is full.

- `87 (ERROR_INVALID_PARAMETER)`

  The parameter is incorrect. (If this error occurs on MySQL 4.1.9 on Windows and you have set `innodb_file_per_table` in a server option file, this is Bug#8021, and a workaround is to add the line `innodb_flush_method=unbuffered` to the file as well.)

- `112 (ERROR_DISK_FULL)`

  The disk is full.

- `123 (ERROR_INVALID_NAME)`

  The filename, directory name, or volume label syntax is incorrect.

- `1450 (ERROR_NO_SYSTEM_RESOURCES)`

  Insufficient system resources exist to complete the requested service.

### 13.2.17. Restrictions on `InnoDB` Tables

- ### Warning

  Do not convert MySQL system tables in the `mysql` database from `MyISAM` to `InnoDB` tables! This is an unsupported operation. If you do this, MySQL does not restart until you restore the old system tables from a backup or re-generate them with the mysql_install_db script.

- ### Warning

  It is not a good idea to configure `InnoDB` to use datafiles or logfiles on NFS volumes. Otherwise, the files might be locked by other processes and become unavailable for use by MySQL.

- A table cannot contain more than 1000 columns.

- The internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.

- The maximum row length, except for `VARBINARY`, `VARCHAR`, `BLOB` and `TEXT` columns, is slightly less than half of a database page. That is, the maximum row length is about 8000 bytes. `LONGBLOB` and `LONGTEXT` columns must be less than 4GB, and the total row length, including also `BLOB` and `TEXT` columns, must be less than 4GB. `InnoDB` stores the first 768 bytes of a `VARBINARY`, `VARCHAR`, `BLOB`, or `TEXT` column in the row, and the rest into separate pages.

- On some older operating systems, files must be less than 2GB. This is not a limitation of `InnoDB` itself, but if you require a large tablespace, you will need to configure it using several smaller data files rather than one or a file large data files.

- The combined size of the `InnoDB` log files must be less than 4GB.

- The minimum tablespace size is 10MB. The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.

- `InnoDB` tables do not support `FULLTEXT` indexes.

- `InnoDB` tables do not support spatial data types.

- `ANALYZE TABLE` determines index cardinality (as displayed in the `Cardinality` column of `SHOW INDEX` output) by doing ten random dives to each of the index trees and updating index cardinality estimates accordingly. Note that because these are only estimates, repeated runs of `ANALYZE TABLE` may produce different numbers. This makes `ANALYZE TABLE` fast on `InnoDB` tables but not 100% accurate as it doesn't take all rows into account.

  MySQL uses index cardinality estimates only in join optimization. If some join is not optimized in the right way, you can try using `ANALYZE TABLE`. In the few cases that `ANALYZE TABLE` doesn't produce values good enough for your particular tables, you can use `FORCE INDEX` with your queries to force the use of a particular index, or set the `max_seeks_for_key` system variable to ensure that MySQL prefers index lookups over table scans. See Section 5.1.3, “System Variables”, and Section A.1.6, “Optimizer-Related Issues”.

- `SHOW TABLE STATUS` does not give accurate statistics on `InnoDB` tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization.

- `InnoDB` does not keep an internal count of rows in a table. (In practice, this would be somewhat complicated due to multi-versioning.) To process a `SELECT COUNT(*) FROM t` statement, `InnoDB` must scan an index of the table, which takes some time if the index is not entirely in the buffer pool. To get a fast count, you have to use a counter table you create yourself and let your application update it according to the inserts and deletes it does. If your table does not change often, using the MySQL query cache is a good solution. `SHOW TABLE STATUS` also can be used if an approximate row count is sufficient. See Section 13.2.12, “`InnoDB` Performance Tuning Tips”.

- On Windows, `InnoDB` always stores database and table names internally in lowercase. To move databases in binary format from Unix to Windows or from Windows to Unix, you should always use explicitly lowercase names when creating databases and tables.

- For an `AUTO_INCREMENT` column, you must always define an index for the table, and that index must contain just the `AUTO_INCREMENT` column. In `MyISAM` tables, the `AUTO_INCREMENT` column may be part of a multi-column index.

- Before MySQL 4.1.12, `InnoDB` does not support the `AUTO_INCREMENT` table option for setting the initial sequence value in an `ALTER TABLE` statement. Before MySQL 4.1.14, the same is true for `CREATE TABLE`. To set the value with `InnoDB`, insert a dummy row with a value one less and delete that dummy row, or insert the first row with an explicit value specified.

- While initializing a previously specified `AUTO_INCREMENT` column on a table, `InnoDB` sets an exclusive lock on the end of the index associated with the `AUTO_INCREMENT` column. In accessing the auto-increment counter, `InnoDB` uses a specific table lock mode `AUTO-INC` where the lock lasts only to the end of the current SQL statement, not to the end of the entire transaction. Note that other clients cannot insert into the table while the `AUTO-INC` table lock is held; see Section 13.2.11.2, “`InnoDB` and `AUTOCOMMIT`”.

- When you restart the MySQL server, `InnoDB` may reuse an old value that was generated for an `AUTO_INCREMENT` column but never stored (that is, a value that was generated during an old transaction that was rolled back).

- When an `AUTO_INCREMENT` column runs out of values, `InnoDB` wraps a `BIGINT` to `-9223372036854775808` and `BIGINT UNSIGNED` to `1`. However, `BIGINT` values have 64 bits, so do note that if you were to insert one million rows per second, it would still take nearly three hundred thousand years before `BIGINT` reached its upper bound. With all other integer type columns, a duplicate-key error results. This is similar to how `MyISAM` works, because it is mostly general MySQL behavior and not about any storage engine in particular.

- `DELETE FROM `tbl_name`` does not regenerate the table but instead deletes all rows, one by one.

- Under some conditions, `TRUNCATE `tbl_name`` for an `InnoDB` table is mapped to `DELETE FROM+ `tbl_name`` and doesn't reset the `AUTO_INCREMENT` counter. See Section 12.2.9, “`TRUNCATE` Syntax”.

- Before MySQL 4.0.14 or 4.1.0, if you tried to create a unique index on a prefix of a column you got an error:

      CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB;

  If you created a non-unique index on a prefix of a column, `InnoDB` created an index over the whole column. These restrictions were removed in MySQL 4.0.14.

- Before MySQL 4.0.20 or 4.1.2, the MySQL `LOCK TABLES` operation does not know about `InnoDB` row-level locks set by completed SQL statements. This means that you can get a table lock on a table even if there still exist transactions by other users who have row-level locks on the same table. Thus, your operations on the table may have to wait if they collide with these locks of other users. Also a deadlock is possible. However, this does not endanger transaction integrity, because the row-level locks set by `InnoDB` always take care of the integrity. Also, a table lock prevents other transactions from acquiring more row-level locks (in a conflicting lock mode) on the table.

- Beginning with MySQL 4.0.20 and 4.1.2, the MySQL `LOCK TABLES` operation acquires two locks on each table if `innodb_table_locks=1` (the default). In addition to a table lock on the MySQL layer, it also acquires an `InnoDB` table lock. Older versions of MySQL do not acquire `InnoDB` table locks. Beginning with MySQL 4.0.22 and 4.1.7, the old behavior can be selected by setting `innodb_table_locks=0`. If no `InnoDB` table lock is acquired, `LOCK TABLES` completes even if some records of the tables are being locked by other transactions.

- All `InnoDB` locks held by a transaction are released when the transaction is committed or aborted. Thus, it does not make much sense to invoke `LOCK TABLES` on `InnoDB` tables in `AUTOCOMMIT=1` mode, because the acquired `InnoDB` table locks would be released immediately.

- Sometimes it would be useful to lock further tables in the course of a transaction. Unfortunately, `LOCK TABLES` in MySQL performs an implicit `COMMIT` and `UNLOCK TABLES`. An `InnoDB` variant of `LOCK TABLES` has been planned that can be executed in the middle of a transaction.

- Before MySQL 3.23.52, replication always ran with autocommit enabled. Therefore consistent reads in the slave would also see partially processed transactions, and thus the read would not be really consistent in the slave. This restriction was removed in MySQL 3.23.52.

- The `LOAD TABLE FROM MASTER` statement for setting up replication slave servers does not work for `InnoDB` tables. A workaround is to alter the table to `MyISAM` on the master, then do the load, and after that alter the master table back to `InnoDB`. Do not do this if the tables use `InnoDB`-specific features such as foreign keys.

- The default database page size in `InnoDB` is 16KB. By recompiling the code, you can set it to values ranging from 8KB to 64KB. You must update the values of `UNIV_PAGE_SIZE` and `UNIV_PAGE_SIZE_SHIFT` in the `univ.i` source file.

- `InnoDB` has a limit of 1023 concurrent transactions that have created undo records by modifying data. Workarounds include keeping transactions as small and fast as possible and delaying changes until near the end of the transaction. Applications should commit transactions before doing time-consuming client-side operations.

### 13.2.18. `InnoDB` Troubleshooting

The following general guidelines apply to troubleshooting `InnoDB` problems:

- When an operation fails or you suspect a bug, you should look at the MySQL server error log, which is the file in the data directory that has a suffix of `.err`.

- When troubleshooting, it is usually best to run the MySQL server from the command prompt, rather than through the mysqld_safe wrapper or as a Windows service. You can then see what mysqld prints to the console, and so have a better grasp of what is going on. On Windows, you must start the server with the `--console` option to direct the output to the console window.

- Use the `InnoDB` Monitors to obtain information about a problem (see Section 13.2.12.1, “`SHOW ENGINE INNODB STATUS` and the `InnoDB` Monitors”). If the problem is performance-related, or your server appears to be hung, you should use `innodb_monitor` to print information about the internal state of `InnoDB`. If the problem is with locks, use `innodb_lock_monitor`. If the problem is in creation of tables or other data dictionary operations, use `innodb_table_monitor` to print the contents of the `InnoDB` internal data dictionary.

- If you suspect that a table is corrupt, run `CHECK TABLE` on that table.

MySQL Enterprise The MySQL Enterprise Monitor provides a number of advisors specifically designed for monitoring InnoDB tables. In some cases, these advisors can anticipate potential problems. For more information see, http://www.mysql.com/products/enterprise/advisors.html.

#### 13.2.18.1. Troubleshooting `InnoDB` Data Dictionary Operations

A specific issue with tables is that the MySQL server keeps data dictionary information in `.frm` files it stores in the database directories, whereas `InnoDB` also stores the information into its own data dictionary inside the tablespace files. If you move `.frm` files around, or use `DROP DATABASE` in MySQL versions before 3.23.44, or the server crashes in the middle of a data dictionary operation, the locations of the `.frm` files may end up out of synchrony with the locations recorded in the `InnoDB` internal data dictionary.

A symptom of an out-of-sync data dictionary is that a `CREATE TABLE` statement fails. If this occurs, you should look in the server's error log. If the log says that the table already exists inside the `InnoDB` internal data dictionary, you have an orphaned table inside the `InnoDB` tablespace files that has no corresponding `.frm` file. The error message looks like this:

    InnoDB: Error: table test/parent already exists in InnoDB internal
    InnoDB: data dictionary. Have you deleted the .frm file
    InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
    InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
    InnoDB: See the Restrictions section of the InnoDB manual.
    InnoDB: You can drop the orphaned table inside InnoDB by
    InnoDB: creating an InnoDB table with the same name in another
    InnoDB: database and moving the .frm file to the current database.
    InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
    InnoDB: succeed.

You can drop the orphaned table by following the instructions given in the error message. If you are still unable to use `DROP TABLE` successfully, the problem may be due to name completion in the mysql client. To work around this problem, start the mysql client with the `--skip-auto-rehash` option and try `DROP TABLE` again. (With name completion on, mysql tries to construct a list of table names, which fails when a problem such as just described exists.)

Another symptom of an out-of-sync data dictionary is that MySQL prints an error that it cannot open a `.InnoDB` file:

    ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)

In the error log you can find a message like this:

    InnoDB: Cannot find table test/child2 from the internal data dictionary
    InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
    InnoDB: have deleted and recreated InnoDB data files but have forgotten
    InnoDB: to delete the corresponding .frm files of InnoDB tables?

This means that there is an orphaned `.frm` file without a corresponding table inside `InnoDB`. You can drop the orphaned `.frm` file by deleting it manually.

If MySQL crashes in the middle of an `ALTER TABLE` operation, you may end up with an orphaned temporary table inside the `InnoDB` tablespace. With `innodb_table_monitor` you see a table whose name is `#sql-...`. Starting from MySQL 4.0.6, you can perform SQL statements also on tables whose name contains the character “`#`” if you enclose the name within backticks. Thus, you can drop such an orphaned table like any other orphaned table using the method described earlier. Note that to copy or rename a file in the Unix shell, you need to put the file name in double quotes if the file name contains “`#`”.

Older MySQL versions did not allow accessing any table with a name containing “`#`”. The solution in older MySQL versions is to use a special `InnoDB` mechanism available starting from MySQL 3.23.48. When you have an orphaned table `#sql-id` inside the tablespace, you can cause `InnoDB` to rename it to `rsql-id_recover_innodb_tmp_table` with the following statement:

    CREATE TABLE `rsql-id_recover_innodb_tmp_table`(...) TYPE=InnoDB;

## 13.3. The `MERGE` Storage Engine

The `MERGE` storage engine was introduced in MySQL 3.23.25. It is also known as the `MRG_MyISAM` engine.

A `MERGE` table is a collection of identical `MyISAM` tables that can be used as one. “Identical” means that all tables have identical column and index information. You cannot merge `MyISAM` tables in which the columns are listed in a different order, do not have exactly the same columns, or have the indexes in different order. However, any or all of the `MyISAM` tables can be compressed with myisampack. See Section 4.6.4, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. Differences in table options such as `AVG_ROW_LENGTH`, `MAX_ROWS`, or `PACK_KEYS` do not matter.

When you create a `MERGE` table, MySQL creates two files on disk. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm` file stores the table format, and an `.MRG` file contains the names of the tables that should be used as one. (Originally, all used tables had to be in the same database as the `MERGE` table itself. This restriction has been lifted as of MySQL 4.1.1.)

You can use `SELECT`, `DELETE`, `UPDATE`, and (as of MySQL 4.0) `INSERT` on `MERGE` tables. You must have `SELECT`, `UPDATE`, and `DELETE` privileges on the `MyISAM` tables that you map to a `MERGE` table.

### Note

The use of `MERGE` tables entails the following security issue: If a user has access to `MyISAM` table `t`, that user can create a `MERGE` table `m` that accesses `t`. However, if the user's privileges on `t` are subsequently revoked, the user can continue to access `t` by doing so through `m`. If this behavior is undesirable, you can start the server with the new `--skip-merge` option to disable the `MERGE` storage engine. This option is available as of MySQL 4.1.21.

If you `DROP` the `MERGE` table, you are dropping only the `MERGE` specification. The underlying tables are not affected.

To create a `MERGE` table, you must specify a `UNION=(`list-of-tables`)` clause that indicates which `MyISAM` tables you want to use as one. You can optionally specify an `INSERT_METHOD` option if you want inserts for the `MERGE` table to take place in the first or last table of the `UNION` list. Use a value of `FIRST` or `LAST` to cause inserts to be made in the first or last table, respectively. If you do not specify an `INSERT_METHOD` option or if you specify it with a value of `NO`, attempts to insert rows into the `MERGE` table result in an error.

The following example shows how to create a `MERGE` table:

    mysql> CREATE TABLE t1 (
        ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        ->    message CHAR(20)) ENGINE=MyISAM;
    mysql> CREATE TABLE t2 (
        ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        ->    message CHAR(20)) ENGINE=MyISAM;
    mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
    mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
    mysql> CREATE TABLE total (
        ->    a INT NOT NULL AUTO_INCREMENT,
        ->    message CHAR(20), INDEX(a))
        ->    ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

The older term `TYPE` is supported as a synonym for `ENGINE` for backward compatibility, but `ENGINE` is the preferred term from MySQL 4.0.18 on and `TYPE` is deprecated.

Note that the `a` column is indexed as a `PRIMARY KEY` in the underlying `MyISAM` tables, but not in the `MERGE` table. There it is indexed but not as a `PRIMARY KEY` because a `MERGE` table cannot enforce uniqueness over the set of underlying tables.

In MySQL 4.1.23 and higher, when a table that is part of a `MERGE` table is opened, the following checks are applied before opening each table. If any table fails the conformance checks, then the operation that triggered the opening of the table will fail. The conformance checks applied to each table are:

- Table must have exactly the same amount of columns that `MERGE` table has.

- Column order in the `MERGE` table must match the column order in the underlying tables.

- Additionally, the specification for each column in the parent `MERGE` table and the underlying table are compared. For each column, MySQL checks:

  - Column type in the underlying table equals the column type of `MERGE` table.

  - Column length in the underlying table equals the column length of `MERGE` table.

  - Column of underlying table and column of `MERGE` table can be `NULL`.

- Underlying table must have at least the same amount of keys that merge table has. The underlying table may have more keys than the `MERGE` table, but cannot have less.

  ### Note

  A known issue exists that keys on the some columns must be identical in order in both the `MERGE` table and the underlying `MyISAM` table. See Bug#33653.

  For each key:

  - Check if the key type of underlying table equals the key type of merge table.

  - Check if number of key parts (i.e. multiple columns within a compound key) in the underlying table key definition equals the number of key parts in merge table key definition.

  - For each key part:

    - Check if key part lengths are equal.

    - Check if key part types are equal.

    - Check if key part languages are equal.

    - Check if key part can be `NULL`.

After creating the `MERGE` table, you can issue queries that operate on the group of tables as a whole:

    mysql> SELECT * FROM total;
    +---+---------+
    | a | message |
    +---+---------+
    | 1 | Testing |
    | 2 | table   |
    | 3 | t1      |
    | 1 | Testing |
    | 2 | table   |
    | 3 | t2      |
    +---+---------+

To remap a `MERGE` table to a different collection of `MyISAM` tables, you can use one of the following methods:

- `DROP` the `MERGE` table and re-create it.

- Use `ALTER TABLE `tbl_name` UNION=(...)` to change the list of underlying tables.

`MERGE` tables can help you solve the following problems:

- Easily manage a set of log tables. For example, you can put data from different months into separate tables, compress some of them with myisampack, and then create a `MERGE` table to use them as one.

- Obtain more speed. You can split a big read-only table based on some criteria, and then put individual tables on different disks. A `MERGE` table on this could be much faster than using the big table.

- Perform more efficient searches. If you know exactly what you are looking for, you can search in just one of the split tables for some queries and use a `MERGE` table for others. You can even have many different `MERGE` tables that use overlapping sets of tables.

- Perform more efficient repairs. It is easier to repair individual tables that are mapped to a `MERGE` table than to repair a single large table.

- Instantly map many tables as one. A `MERGE` table need not maintain an index of its own because it uses the indexes of the individual tables. As a result, `MERGE` table collections are very fast to create or remap. (Note that you must still specify the index definitions when you create a `MERGE` table, even though no indexes are created.)

- If you have a set of tables from which you create a large table on demand, you should instead create a `MERGE` table on them on demand. This is much faster and saves a lot of disk space.

- Exceed the file size limit for the operating system. Each `MyISAM` table is bound by this limit, but a collection of `MyISAM` tables is not.

- You can create an alias or synonym for a `MyISAM` table by defining a `MERGE` table that maps to that single table. There should be no really notable performance impact from doing this (only a couple of indirect calls and `memcpy()` calls for each read).

The disadvantages of `MERGE` tables are:

- You can use only identical `MyISAM` tables for a `MERGE` table.

- You cannot use a number of `MyISAM` features in `MERGE` tables. For example, you cannot create `FULLTEXT` indexes on `MERGE` tables. (You can, of course, create `FULLTEXT` indexes on the underlying `MyISAM` tables, but you cannot search the `MERGE` table with a full-text search.)

- If the `MERGE` table is non-temporary, all underlying `MyISAM` tables must be non-temporary, too. If the `MERGE` table is temporary, the `MyISAM` tables can be any mix of temporary and non-temporary.

- `MERGE` tables use more file descriptors. If 10 clients are using a `MERGE` table that maps to 10 tables, the server uses (10 × 10) + 10 file descriptors. (10 data file descriptors for each of the 10 clients, and 10 index file descriptors shared among the clients.)

- Key reads are slower. When you read a key, the `MERGE` storage engine needs to issue a read on all underlying tables to check which one most closely matches the given key. To read the next key, the `MERGE` storage engine needs to search the read buffers to find the next key. Only when one key buffer is used up does the storage engine need to read the next key block. This makes `MERGE` keys much slower on `eq_ref` searches, but not much slower on `ref` searches. See Section 7.2.1, “Optimizing Queries with `EXPLAIN`”, for more information about `eq_ref` and `ref`.

Additional resources

- A forum dedicated to the `MERGE` storage engine is available at http://forums.mysql.com/list.php?93.

### 13.3.1. `MERGE` Table Problems

The following are known problems with `MERGE` tables:

- If you use `ALTER TABLE` to change a `MERGE` table to another storage engine, the mapping to the underlying tables is lost. Instead, the rows from the underlying `MyISAM` tables are copied into the altered table, which then uses the specified storage engine.

- Before MySQL 4.1.1, all underlying tables and the `MERGE` table itself had to be in the same database.

- `REPLACE` does not work.

- You cannot use `REPAIR TABLE`, `OPTIMIZE TABLE`, `DROP TABLE`, `ALTER TABLE`, `DELETE` without a `WHERE` clause, `TRUNCATE TABLE`, or `ANALYZE TABLE` on any of the tables that are mapped into an open `MERGE` table. If you do so, the `MERGE` table may still refer to the original table, which yields unexpected results. The easiest way to work around this deficiency is to ensure that no `MERGE` tables remain open by issuing a `FLUSH TABLES` statement prior to performing any of those operations.

  The unexpected results include the possibility that the operation on the `MERGE` table will report table corruption. However, if this occurs after operations on the underlying `MyISAM` tables such as those listed in the previous paragraph (`REPAIR TABLE`, `OPTIMIZE TABLE`, and so forth), the corruption message is spurious. To deal with this, issue a `FLUSH TABLES` statement after modifying the `MyISAM` tables.

- `DROP TABLE` on a table that is in use by a `MERGE` table does not work on Windows because the `MERGE` storage engine's table mapping is hidden from the upper layer of MySQL. Windows does not allow open files to be deleted, so you first must flush all `MERGE` tables (with `FLUSH TABLES`) or drop the `MERGE` table before dropping the table.

- A `MERGE` table cannot maintain uniqueness constraints over the entire table. When you perform an `INSERT`, the data goes into the first or last `MyISAM` table (depending on the value of the `INSERT_METHOD` option). MySQL ensures that unique key values remain unique within that `MyISAM` table, but not across all the tables in the collection.

- Before MySQL 3.23.49, `DELETE FROM `merge_table`` used without a `WHERE` clause only clears the mapping for the table. That is, it incorrectly empties the `.MRG` file rather than deleting records from the mapped tables.

- Using `RENAME TABLE` on an active `MERGE` table may corrupt the table. This is fixed in MySQL 4.1.x.

- In MySQL 4.1.23 and later, the definition of the `MyISAM` tables and the `MERGE` table are checked when the tables are accessed (for example, as part of a `SELECT` or `INSERT` statement). The checks ensure that the definitions of the tables and the parent `MERGE` table definition match by comparing column order, types, sizes and associated indexes. If there is a difference between the tables then an error will be returned and the statement will fail.

  Because these checks take place when the tables are opened, any changes to the definition of a single table, including column changes, column ordering and engine alterations will cause the statement to fail.

  For MySQL 4.1.22 and earlier:

  - When you create or alter `MERGE` table, there is no check to ensure that the underlying tables are existing `MyISAM` tables and have identical structures. When the `MERGE` table is used, MySQL checks that the row length for all mapped tables is equal, but this is not foolproof. If you create a `MERGE` table from dissimilar `MyISAM` tables, you are very likely to run into strange problems.

  - Similarly, if you create a `MERGE` table from non-`MyISAM` tables, or if you drop an underlying table or alter it to be a non-`MyISAM` table, no error for the `MERGE` table occurs until later when you attempt to use it.

  - Because the underlying `MyISAM` tables need not exist when the `MERGE` table is created, you can create the tables in any order, as long as you do not use the `MERGE` table until all of its underlying tables are in place. Also, if you can ensure that a `MERGE` table will not be used during a given period, you can perform maintenance operations on the underlying tables, such as backing up or restoring them, altering them, or dropping and recreating them. It is not necessary to redefine the `MERGE` table temporarily to exclude the underlying tables while you are operating on them.

- The order of indexes in the `MERGE` table and its underlying tables should be the same. If you use `ALTER TABLE` to add a `UNIQUE` index to a table used in a `MERGE` table, and then use `ALTER TABLE` to add a non-unique index on the `MERGE` table, the index ordering is different for the tables if there was already a non-unique index in the underlying table. (This happens because `ALTER TABLE` puts `UNIQUE` indexes before non-unique indexes to facilitate rapid detection of duplicate keys.) Consequently, queries on tables with such indexes may return unexpected results.

- If you encounter an error message similar to `ERROR 1017 (HY000): Can't find file: '`mm`.MRG' (errno: 2)` it generally indicates that some of the base tables are not using the MyISAM storage engine. Confirm that all tables are MyISAM.

- The `MERGE` storage engine does not support `INSERT DELAYED` statements.

## 13.4. The `MEMORY` (`HEAP`) Storage Engine

The `MEMORY` storage engine creates tables with contents that are stored in memory. Before MySQL 4.1, `MEMORY` tables are called `HEAP` tables. As of 4.1, `MEMORY` is the preferred term, although `HEAP` remains supported for backward compatibility.

Each `MEMORY` table is associated with one disk file. The filename begins with the table name and has an extension of `.frm` to indicate that it stores the table definition.

To specify explicitly that you want to create a `MEMORY` table, indicate that with an `ENGINE` table option:

    CREATE TABLE t (i INT) ENGINE = MEMORY;

The older term `TYPE` is supported as a synonym for `ENGINE` for backward compatibility, but `ENGINE` is the preferred term from MySQL 4.0.18 on and `TYPE` is deprecated.

As indicated by the name, `MEMORY` tables are stored in memory. They use hash indexes by default, which makes them very fast, and very useful for creating temporary tables. However, when the server shuts down, all rows stored in `MEMORY` tables are lost. The tables themselves continue to exist because their definitions are stored in `.frm` files on disk, but they are empty when the server restarts.

This example shows how you might create, use, and remove a `MEMORY` table:

    mysql> CREATE TABLE test TYPE=MEMORY
        ->     SELECT ip,SUM(downloads) AS down
        ->     FROM log_table GROUP BY ip;
    mysql> SELECT COUNT(ip),AVG(down) FROM test;
    mysql> DROP TABLE test;

`MEMORY` tables have the following characteristics:

- Space for `MEMORY` tables is allocated in small blocks. Tables use 100% dynamic hashing for inserts. No overflow area or extra key space is needed. No extra space is needed for free lists. Deleted rows are put in a linked list and are reused when you insert new data into the table. `MEMORY` tables also have none of the problems commonly associated with deletes plus inserts in hashed tables.

- `MEMORY` tables allow up to 32 indexes per table and 16 columns per index. Previously, the maximum key length supported by this storage engine was 255 bytes; as of MySQL 4.1.13, `MEMORY` tables support a maximum key length of 500 bytes. (See Section B.1.12, “Changes in MySQL 4.1.13 (15 July 2005)”.)

- Before MySQL 4.1, the `MEMORY` storage engine implements only hash indexes. From MySQL 4.1 on, hash indexes are still the default, but you can specify explicitly that a `MEMORY` table index should be a `HASH` or `BTREE` by adding a `USING` clause as shown here:

      CREATE TABLE lookup
          (id INT, INDEX USING HASH (id))
          ENGINE = MEMORY;
      CREATE TABLE lookup
          (id INT, INDEX USING BTREE (id))
          ENGINE = MEMORY;

  General characteristics of B-tree and hash indexes are described in Section 7.4.5, “How MySQL Uses Indexes”.

- You can have non-unique keys in a `MEMORY` table. (This is an uncommon feature for implementations of hash indexes.)

- If you have a hash index on a `MEMORY` table that has a high degree of key duplication (many index entries containing the same value), updates to the table that affect key values and all deletes are significantly slower. The degree of this slowdown is proportional to the degree of duplication (or, inversely proportional to the index cardinality). You can use a `BTREE` index to avoid this problem.

- As of MySQL 4.0.2, columns that are indexed can contain `NULL` values.

- `MEMORY` tables use a fixed-length row storage format.

- `MEMORY` tables cannot contain `BLOB` or `TEXT` columns.

- `MEMORY` supports `AUTO_INCREMENT` columns as of MySQL 4.1.0.

- As of MySQL 4.1, you can use `INSERT DELAYED` with `MEMORY` tables. See Section 12.2.4.2, “`INSERT DELAYED` Syntax”.

- `MEMORY` tables are shared among all clients (just like any other non-`TEMPORARY` table).

- `MEMORY` table contents are stored in memory, which is a property that `MEMORY` tables share with internal tables that the server creates on the fly while processing queries. However, the two types of tables differ in that `MEMORY` tables are not subject to storage conversion, whereas internal tables are:

  - If an internal table becomes too large, the server automatically converts it to an on-disk table. The size limit is determined by the value of the `tmp_table_size` system variable.

  - `MEMORY` tables are never converted to disk tables. To ensure that you don't accidentally do anything foolish, you can set the `max_heap_table_size` system variable to impose a maximum size on `MEMORY` tables. For individual tables, you can also specify a `MAX_ROWS` table option in the `CREATE TABLE` statement.

- The server needs sufficient memory to maintain all `MEMORY` tables that are in use at the same time.

- To free memory used by a `MEMORY` table when you no longer require its contents, you should execute `DELETE` or `TRUNCATE TABLE`, or remove the table altogether using `DROP TABLE`.

- If you want to populate a `MEMORY` table when the MySQL server starts, you can use the `--init-file` option. For example, you can put statements such as `INSERT INTO ... SELECT` or `LOAD DATA INFILE` into this file to load the table from a persistent data source. See Section 5.1.2, “Command Options”, and Section 12.2.5, “`LOAD DATA INFILE` Syntax”.

- If you are using replication, the master server's `MEMORY` tables become empty when it is shut down and restarted. However, a slave is not aware that these tables have become empty, so it returns out-of-date content if you select data from them. Beginning with MySQL 4.0.18, when a `MEMORY` table is used on the master for the first time since the master was started, a `DELETE FROM` statement is written to the master's binary log automatically, thus synchronizing the slave to the master again. Note that even with this strategy, the slave still has outdated data in the table during the interval between the master's restart and its first use of the table. However, if you use the `--init-file` option to populate the `MEMORY` table on the master at startup, it ensures that this time interval is zero.

- The memory needed for one row in a `MEMORY` table is calculated using the following expression:

      SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
      + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
      + ALIGN(length_of_row+1, sizeof(char*))

  `ALIGN()` represents a round-up factor to cause the row length to be an exact multiple of the `char` pointer size. `sizeof(char*)` is 4 on 32-bit machines and 8 on 64-bit machines.

Additional resources

- A forum dedicated to the `MEMORY` storage engine is available at http://forums.mysql.com/list.php?92.

## 13.5. The `BDB` (`BerkeleyDB`) Storage Engine

Sleepycat Software has provided MySQL with the Berkeley DB transactional storage engine. This storage engine typically is called `BDB` for short. `BDB` tables may have a greater chance of surviving crashes and are also capable of `COMMIT` and `ROLLBACK` operations on transactions.

Support for the `BDB` storage engine is included in MySQL source distributions, which come with a `BDB` distribution that is patched to make it work with MySQL. You cannot use a non-patched version of `BDB` with MySQL.

BDB support will be removed Note that, as of MySQL 5.1, `BDB` isn't supported any longer.

For general information about Berkeley DB, please visit the Sleepycat Web site, http://www.sleepycat.com/.

### 13.5.1. Operating Systems Supported by `BDB`

Currently, we know that the `BDB` storage engine works with the following operating systems:

- Linux 2.x Intel

- Sun Solaris (SPARC and x86)

- FreeBSD 4.x/5.x (x86, sparc64)

- IBM AIX 4.3.x

- SCO OpenServer

- SCO UnixWare 7.1.x

- Windows NT/2000/XP

The `BDB` storage engine does not work with the following operating systems:

- Linux 2.x Alpha

- Linux 2.x AMD64

- Linux 2.x IA-64

- Linux 2.x s390

- Mac OS X

### Note

The preceding lists are not complete. We update them as we receive more information.

If you build MySQL from source with support for `BDB` tables, but the following error occurs when you start mysqld, it means that the `BDB` storage engine is not supported for your architecture:

    bdb: architecture lacks fast mutexes: applications cannot be threaded
    Can't init databases

In this case, you must rebuild MySQL without `BDB` support or start the server with the `--skip-bdb` option.

### 13.5.2. Installing `BDB`

If you have downloaded a binary version of MySQL that includes support for Berkeley DB, simply follow the usual binary distribution installation instructions. (MySQL-Max distributions include `BDB` support.)

If you build MySQL from source, you can enable `BDB` support by invoking configure with the `--with-berkeley-db` option in addition to any other options that you normally use. Download a distribution for MySQL 3.23.34 or newer, change location into its top-level directory, and run this command:

    shell> ./configure --with-berkeley-db [other-options]

For more information, see Section 5.2, “The mysqld-max Extended MySQL Server”, Section 2.8, “Installing MySQL from `tar.gz` Packages on Other Unix-Like Systems”, and Section 2.9, “MySQL Installation Using a Source Distribution”.

### 13.5.3. `BDB` Startup Options

The following options to mysqld can be used to change the behavior of the `BDB` storage engine. For more information, see Section 5.1.2, “Command Options”.

- `--bdb-home=`path``

  The base directory for `BDB` tables. This should be the same directory that you use for `--datadir`.

- `--bdb-lock-detect=`method``

  The `BDB` lock detection method. The option value should be `DEFAULT`, `OLDEST`, `RANDOM`, or `YOUNGEST`.

- `--bdb-logdir=`file_name``

  The `BDB` log file directory.

- `--bdb-no-recover`

  Do not start Berkeley DB in recover mode.

- `--bdb-no-sync`

  Don't synchronously flush the `BDB` logs. This option is deprecated as of MySQL 4.0.18; use `--skip-sync-bdb-logs` instead (see the description for `--sync-bdb-logs`).

- `--bdb-shared-data`

  Start Berkeley DB in multi-process mode. (Do not use `DB_PRIVATE` when initializing Berkeley DB.)

- `--bdb-tmpdir=`path``

  The `BDB` temporary file directory.

- `--skip-bdb`

  Disable the `BDB` storage engine.

- `--sync-bdb-logs`

  Synchronously flush the `BDB` logs. This option is enabled by default. Use `--skip-sync-bdb-logs` to disable it. This option was added in MySQL 4.0.18.

If you use the `--skip-bdb` option, MySQL does not initialize the Berkeley DB library and this saves a lot of memory. However, if you use this option, you cannot use `BDB` tables. If you try to create a `BDB` table, MySQL uses the default storage engine instead.

Normally, you should start mysqld without the `--bdb-no-recover` option if you intend to use `BDB` tables. However, this may cause problems when you try to start mysqld if the `BDB` log files are corrupted. See Section 2.10.2.3, “Starting and Troubleshooting the MySQL Server”.

With the `bdb_max_lock` variable, you can specify the maximum number of locks that can be active on a `BDB` table. The default is 10,000. You should increase this if errors such as the following occur when you perform long transactions or when mysqld has to examine many rows to execute a query:

    bdb: Lock table is out of available locks
    Got error 12 from ...

You may also want to change the `binlog_cache_size` and `max_binlog_cache_size` variables if you are using large multiple-statement transactions. See Section 5.3.4, “The Binary Log”.

See also Section 5.1.3, “System Variables”.

### 13.5.4. Characteristics of `BDB` Tables

Each `BDB` table is stored on disk in two files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm` file stores the table format, and a `.db` file contains the table data and indexes.

To specify explicitly that you want a `BDB` table, indicate that with an `ENGINE` table option:

    CREATE TABLE t (i INT) ENGINE = BDB;

The older term `TYPE` is supported as a synonym for `ENGINE` for backward compatibility, but `ENGINE` is the preferred term from MySQL 4.0.18 on and `TYPE` is deprecated.

`BerkeleyDB` is a synonym for `BDB` in the `ENGINE` table option.

The `BDB` storage engine provides transactional tables. The way you use these tables depends on the autocommit mode:

- If you are running with autocommit enabled (which is the default), changes to `BDB` tables are committed immediately and cannot be rolled back.

- If you are running with autocommit disabled, changes do not become permanent until you execute a `COMMIT` statement. Instead of committing, you can execute `ROLLBACK` to forget the changes.

  You can start a transaction with the `START TRANSACTION` or `BEGIN` statement to suspend autocommit, or with `SET AUTOCOMMIT=0` to disable autocommit explicitly.

For more information about transactions, see Section 12.4.1, “`START TRANSACTION`, `COMMIT`, and `ROLLBACK` Syntax”.

The `BDB` storage engine has the following characteristics:

- `BDB` tables can have up to 31 indexes per table, 16 columns per index, and a maximum key size of 1024 bytes (500 bytes before MySQL 4.0).

- MySQL requires a primary key in each `BDB` table so that each row can be uniquely identified. If you don't create one explicitly by declaring a `PRIMARY KEY`, MySQL creates and maintains a hidden primary key for you. The hidden key has a length of five bytes and is incremented for each insert attempt. This key does not appear in the output of `SHOW CREATE TABLE` or `DESCRIBE`.

- The primary key is faster than any other index, because it is stored together with the row data. The other indexes are stored as the key data plus the primary key, so it's important to keep the primary key as short as possible to save disk space and get better speed.

  This behavior is similar to that of `InnoDB`, where shorter primary keys save space not only in the primary index but in secondary indexes as well.

- If all columns that you access in a `BDB` table are part of the same index or part of the primary key, MySQL can execute the query without having to access the actual row. In a `MyISAM` table, this can be done only if the columns are part of the same index.

- Sequential scanning is slower for `BDB` tables than for `MyISAM` tables because the data in `BDB` tables is stored in B-trees and not in a separate data file.

- Key values are not prefix- or suffix-compressed like key values in `MyISAM` tables. In other words, key information takes a little more space in `BDB` tables compared to `MyISAM` tables.

- There are often holes in the `BDB` table to allow you to insert new rows in the middle of the index tree. This makes `BDB` tables somewhat larger than `MyISAM` tables.

- `SELECT COUNT(*) FROM `tbl_name`` is slow for `BDB` tables, because no row count is maintained in the table.

- The optimizer needs to know the approximate number of rows in the table. MySQL solves this by counting inserts and maintaining this in a separate segment in each `BDB` table. If you don't issue a lot of `DELETE` or `ROLLBACK` statements, this number should be accurate enough for the MySQL optimizer. However, MySQL stores the number only on close, so it may be incorrect if the server terminates unexpectedly. It should not be fatal even if this number is not 100% correct. You can update the row count by using `ANALYZE TABLE` or `OPTIMIZE TABLE`. See Section 12.5.2.1, “`ANALYZE TABLE` Syntax”, and Section 12.5.2.5, “`OPTIMIZE TABLE` Syntax”.

- Internal locking in `BDB` tables is done at the page level.

- `LOCK TABLES` works on `BDB` tables as with other tables. If you do not use `LOCK TABLES`, MySQL issues an internal multiple-write lock on the table (a lock that does not block other writers) to ensure that the table is properly locked if another thread issues a table lock.

- To support transaction rollback, the `BDB` storage engine maintains log files. For maximum performance, you can use the `--bdb-logdir` option to place the `BDB` logs on a different disk than the one where your databases are located.

- MySQL performs a checkpoint each time a new `BDB` log file is started, and removes any `BDB` log files that are not needed for current transactions. You can also use `FLUSH LOGS` at any time to checkpoint the Berkeley DB tables.

  For disaster recovery, you should use table backups plus MySQL's binary log. See Section 5.7.1, “Database Backups”.

  ### Warning

  If you delete old log files that are still in use, `BDB` is not able to do recovery at all and you may lose data if something goes wrong.

- Applications must always be prepared to handle cases where any change of a `BDB` table may cause an automatic rollback and any read may fail with a deadlock error.

- If you get a full disk with a `BDB` table, you get an error (probably error 28) and the transaction should roll back. This contrasts with `MyISAM` and `ISAM` tables, for which mysqld waits for sufficient free disk space before continuing.

### 13.5.5. Restrictions on `BDB` Tables

The following list indicates restrictions that you must observe when using `BDB` tables:

- Each `BDB` table stores in its `.db` file the path to the file as it was created. This is done to enable detection of locks in a multi-user environment that supports symlinks. As a consequence of this, it is not possible to move `BDB` table files from one database directory to another.

- When making backups of `BDB` tables, you must either use mysqldump or else make a backup that includes the files for each `BDB` table (the `.frm` and `.db` files) as well as the `BDB` log files. The `BDB` storage engine stores unfinished transactions in its log files and requires them to be present when mysqld starts. The `BDB` logs are the files in the data directory with names of the form `log.`NNNNNNNNNN`` (ten digits).

- If a column that allows `NULL` values has a unique index, only a single `NULL` value is allowed. This differs from other storage engines, which allow multiple `NULL` values in unique indexes.

### 13.5.6. Errors That May Occur When Using `BDB` Tables

- If the following error occurs when you start mysqld after upgrading, it means that the current version of `BDB` doesn't support the old log file format:

      bdb:  Ignoring log file: .../log.NNNNNNNNNN:
      unsupported log version #

  In this case, you must delete all `BDB` logs from your data directory (the files that have names of the form `log.`NNNNNNNNNN``) and restart mysqld. We also recommend that you then use mysqldump --opt to dump your `BDB` tables, drop the tables, and restore them from the dump file.

- If autocommit mode is disabled and you drop a `BDB` table that is referenced in another transaction, you may get error messages of the following form in your MySQL error log:

      001119 23:43:56  bdb:  Missing log fileid entry
      001119 23:43:56  bdb:  txn_abort: Log undo failed for LSN:
                             1 3644744: Invalid

  This is not fatal, but the fix is not trivial. We recommend that you not drop `BDB` tables except while autocommit mode is enabled.

## 13.6. The `EXAMPLE` Storage Engine

The `EXAMPLE` storage engine was added in MySQL 4.1.3. It is a “stub” engine that does nothing. Its purpose is to serve as an example in the MySQL source code that illustrates how to begin writing new storage engines. As such, it is primarily of interest to developers.

The `EXAMPLE` storage engine is included in MySQL-Max binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the `--with-example-storage-engine` option.

To examine the source for the `EXAMPLE` engine, look in the `sql/examples` directory of a MySQL source distribution.

When you create an `EXAMPLE` table, the server creates a table format file in the database directory. The file begins with the table name and has an `.frm` extension. No other files are created. No data can be stored into the table. Retrievals return an empty result.

    mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE;
    Query OK, 0 rows affected (0.78 sec)

    mysql> INSERT INTO test VALUES(1),(2),(3);
    ERROR 1031 (HY000): Table storage engine for 'test' doesn't have this option

    mysql> SELECT * FROM test;
    Empty set (0.31 sec)

The `EXAMPLE` storage engine does not support indexing.

## 13.7. The `ARCHIVE` Storage Engine

The `ARCHIVE` storage engine was added in MySQL 4.1.3. It is used for storing large amounts of data without indexes in a very small footprint.

The `ARCHIVE` storage engine is included in MySQL binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the `--with-archive-storage-engine` option.

To examine the source for the `ARCHIVE` engine, look in the `sql` directory of a MySQL source distribution.

You can check whether the `ARCHIVE` storage engine is available with this statement:

    mysql> SHOW VARIABLES LIKE 'have_archive';

When you create an `ARCHIVE` table, the server creates a table format file in the database directory. The file begins with the table name and has an `.frm` extension. The storage engine creates other files, all having names beginning with the table name. The data and metadata files have extensions of `.ARZ` and `.ARM`, respectively. An `.ARN` file may appear during optimization operations.

The `ARCHIVE` engine supports `INSERT` and `SELECT`, but not `DELETE`, `REPLACE`, or `UPDATE`. It does support `ORDER BY` operations, `BLOB` columns, and basically all but spatial data types (see Section 15.4.1, “MySQL Spatial Data Types”). The `ARCHIVE` engine uses row-level locking.

Storage: Rows are compressed as they are inserted. The `ARCHIVE` engine uses `zlib` lossless data compression (see http://www.zlib.net/). You can use `OPTIMIZE TABLE` to analyze the table and pack it into a smaller format (for a reason to use `OPTIMIZE TABLE`, see later in this section). There are several types of insertions that are used:

- An `INSERT` statement just pushes rows into a compression buffer, and that buffer flushes as necessary. The insertion into the buffer is protected by a lock. A `SELECT` forces a flush to occur, unless the only insertions that have come in were `INSERT DELAYED` (those flush as necessary). See Section 12.2.4.2, “`INSERT DELAYED` Syntax”.

- A bulk insert is visible only after it completes, unless other inserts occur at the same time, in which case it can be seen partially. A `SELECT` never causes a flush of a bulk insert unless a normal insert occurs while it is loading.

Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A `SELECT` operation performs a complete table scan: When a `SELECT` occurs, it finds out how many rows are currently available and reads that number of rows. `SELECT` is performed as a consistent read. Note that lots of `SELECT` statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used. To achieve better compression, you can use `OPTIMIZE TABLE` or `REPAIR TABLE`. The number of rows in `ARCHIVE` tables reported by `SHOW TABLE STATUS` is always accurate. See Section 12.5.2.5, “`OPTIMIZE TABLE` Syntax”, Section 12.5.2.6, “`REPAIR TABLE` Syntax”, and Section 12.5.4.18, “`SHOW TABLE STATUS` Syntax”.

Additional resources

- A forum dedicated to the `ARCHIVE` storage engine is available at http://forums.mysql.com/list.php?112.

## 13.8. The `CSV` Storage Engine

The `CSV` storage engine was added in MySQL 4.1.4. This engine stores data in text files using comma-separated values format. It is unavailable on Windows until MySQL 5.1.

The `CSV` storage engine is included in MySQL-Max binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the `--with-csv-storage-engine` option.

To examine the source for the `CSV` engine, look in the `sql/examples` directory of a MySQL source distribution.

When you create a `CSV` table, the server creates a table format file in the database directory. The file begins with the table name and has an `.frm` extension. The storage engine also creates a data file. Its name begins with the table name and has a `.CSV` extension. The data file is a plain text file. When you store data into the table, the storage engine saves it into the data file in comma-separated values format.

    mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = CSV;
    Query OK, 0 rows affected (0.12 sec)

    mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0

    mysql> SELECT * FROM test;
    +------+------------+
    | i    | c          |
    +------+------------+
    |    1 | record one |
    |    2 | record two |
    +------+------------+
    2 rows in set (0.00 sec)

If you examine the `test.CSV` file in the database directory created by executing the preceding statements, its contents should look like this:

    "1","record one"
    "2","record two"

This format can be read, and even written, by spreadsheet applications such as Microsoft Excel or StarOffice Calc.

The `CSV` storage engine does not support indexing.

## 13.9. The `BLACKHOLE` Storage Engine

The `BLACKHOLE` storage engine was added in MySQL 4.1.11. This engine acts as a “black hole” that accepts data but throws it away and does not store it. Retrievals always return an empty result:

    mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
    Query OK, 0 rows affected (0.03 sec)

    mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
    Query OK, 2 rows affected (0.00 sec)
    Records: 2  Duplicates: 0  Warnings: 0

    mysql> SELECT * FROM test;
    Empty set (0.00 sec)

The `BLACKHOLE` storage engine is included in MySQL-Max binary distributions. To enable this storage engine if you build MySQL from source, invoke configure with the `--with-blackhole-storage-engine` option.

To examine the source for the `BLACKHOLE` engine, look in the `sql` directory of a MySQL source distribution.

When you create a `BLACKHOLE` table, the server creates a table format file in the database directory. The file begins with the table name and has an `.frm` extension. There are no other files associated with the table.

The `BLACKHOLE` storage engine supports all kinds of indexes. That is, you can include index declarations in the table definition.

You can check whether the `BLACKHOLE` storage engine is available with this statement:

    mysql> SHOW VARIABLES LIKE 'have_blackhole_engine';

Inserts into a `BLACKHOLE` table do not store any data, but if the binary log is enabled, the SQL statements are logged (and replicated to slave servers). This can be useful as a repeater or filter mechanism. For example, suppose that your application requires slave-side filtering rules, but transferring all binary log data to the slave first results in too much traffic. In such a case, it is possible to set up on the master host a “dummy” slave process whose default storage engine is `BLACKHOLE`, depicted as follows:

The master writes to its binary log. The “dummy” mysqld process acts as a slave, applying the desired combination of `replicate-do-*` and `replicate-ignore-*` rules, and writes a new, filtered binary log of its own. (See Section 6.8, “Replication Startup Options”.) This filtered log is provided to the slave.

The dummy process does not actually store any data, so there is little processing overhead incurred by running the additional mysqld process on the replication master host. This type of setup can be repeated with additional replication slaves.

Other possible uses for the `BLACKHOLE` storage engine include:

- Verification of dump file syntax.

- Measurement of the overhead from binary logging, by comparing performance using `BLACKHOLE` with and without binary logging enabled.

- `BLACKHOLE` is essentially a “no-op” storage engine, so it could be used for finding performance bottlenecks not related to the storage engine itself.

## 13.10. The `ISAM` Storage Engine

The original storage engine in MySQL was the `ISAM` engine. It was the only storage engine available until MySQL 3.23, when the improved `MyISAM` engine was introduced as the default. `ISAM` is deprecated. As of MySQL 4.1, it's included in the source but not enabled in binary distributions. It is not available in MySQL 5.0. Embedded MySQL server versions do not support `ISAM` tables by default.

Due to the deprecated status of `ISAM`, and because `MyISAM` is an improvement over `ISAM`, you are advised to convert any remaining `ISAM` tables to `MyISAM` as soon as possible. To convert an `ISAM` table to a `MyISAM` table, use an `ALTER TABLE` statement:

    mysql> ALTER TABLE tbl_name TYPE = MYISAM;

For more information about `MyISAM`, see Section 13.1, “The `MyISAM` Storage Engine”.

Each `ISAM` table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An `.frm` file stores the table definition. The data file has an `.ISD` extension. The index file has an `.ISM` extension.

`ISAM` uses B-tree indexes.

You can check or repair `ISAM` tables with the isamchk utility. See Section 5.7.4.1, “Using myisamchk for Crash Recovery”.

`ISAM` has the following properties:

- Compressed and fixed-length keys

- Fixed and dynamic record length

- 16 indexes per table, with 16 key parts per key

- Maximum key length 256 bytes (default)

- Data values are stored in machine format; this is fast, but machine/OS dependent

Many of the properties of `MyISAM` tables are also true for `ISAM` tables. However, there are also many differences. The following list describes some of the ways that `ISAM` is distinct from `MyISAM`:

- Not binary portable across OS/platforms.

- Can't handle tables larger than 4GB.

- Only supports prefix compression on strings.

- Smaller (more restrictive) key limits.

- Dynamic tables become more fragmented.

- Doesn't support `MERGE` tables.

- Tables are checked and repaired with isamchk rather than with myisamchk.

- Tables are compressed with pack_isam rather than with myisampack.

- Cannot be used with the `BACKUP TABLE` or `RESTORE TABLE` backup-related statements.

- Cannot be used with the `CHECK TABLE`, `REPAIR TABLE`, `OPTIMIZE TABLE`, or `ANALYZE TABLE` table-maintenance statements.

- No support for full-text searching or spatial data types.

- No support for multiple character sets per table.

- Indexes cannot be assigned to specific key caches.

Prev |  | Next
Chapter 12. SQL Statement Syntax | Home | Chapter 14. MySQL Cluster