InnoDB
InnoDB is a transaction-safe (ACID compliant) storage engine with foreign key support along with commit, rollback, and crash recovery capabilities. InnoDB is not a standalone database product, but is distributed as a part of the MySQL database.
History
InnoDB was first released as a part of MySQL in 2001.
MySQL had a licensing agreement with Innobase which enabled them to provide InnoDB under a commercial license to paying customers, alongside the distribution as a GPL (open source and free as in beer) product.
In October 2005, Oracle purchased the Finland-based Innobase Oy. Since then, MySQL renewed the contract with Oracle Corporation.
SUN released a new storage engine, called "Falcon" in January, 2007 which is going to replace InnoDB as the default transactional engine in MySQL 6.0. Apparently a migration path will also be provided to move InnoDB sites over to Falcon.
On 26 December 2008 Percona announced that they've created a forked version of InnoDB, called XtraDB, with additional features and performance enhancements. This is by no means the first InnoDB fork and it will be interesting to see if they will succeed and how Oracle will respond.
Enabling InnoDB
To check if InnoDB is enabled in MySQL, run the "show engines" command:
mysql> show engines; +------------+----------+----------------------------------------------------------------+ | Engine | Support | Comment | +------------+----------+----------------------------------------------------------------+ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | DISABLED | Supports transactions and page-level locking |
If it's disabled, remove or comment the "skip-innodb" line in your /etc/my.cnf file and restart MySQL:
#skip-innodb
Creating tables
To create a new InnoDB table, use the ENGINE=INNODB clause. Example:
CREATE TABLE Emp ( EmpID SMALLINT UNSIGNED NOT NULL, YearBorn YEAR NOT NULL, CityBorn VARCHAR(40) NOT NULL DEFAULT 'Unknown' ) ENGINE=INNODB;
To change a table to InnoDB:
ALTER TABLE your_table ENGINE=INNODB;
Enabling transaction support
By default, autocommit is enabled and MySQL will commit all statements. To enable transaction support, execute:
SET autocommit = 0,
A COMMIT or ROLLBACK will now be required to ends transactions (similar to how the Oracle database handles transactions).
Also see
- Berkeley DB, Oracle owned database library that is also available with MySQL.
