How to check InnoDB support

InnoDb is a storage engine to MySQL table, it support transaction and row level locking. There commands in MySQL only works for InnoDB engine such as for update and start transaction. When I want to test some trasaction related commands in MySQL, they seems no effect.

If you get the same problem , one of the reasons is you may have the InnoDB engine disabled.

There are two methods to check whether the InnoDB engine is disabled, the first is command show engines as follows:

mysql> show engines;
  | Engine     | Support  | Comment                                                        |
  | MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance         |
  | MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      |
//| InnoDB     | DISABLED | Supports transactions, row-level locking, and foreign keys     |
  | BerkeleyDB | NO       | Supports transactions and page-level locking                   |
  | BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) |
  | EXAMPLE    | NO       | Example storage engine                                         |
  | ARCHIVE    | YES      | Archive storage engine                                         |
  | CSV        | NO       | CSV storage engine                                             |
  | ndbcluster | NO       | Clustered, fault-tolerant, memory-based tables                 |
  | FEDERATED  | DISABLED | Federated MySQL storage engine                                 |
  | MRG_MYISAM | YES      | Collection of identical MyISAM tables                          |
  | ISAM       | NO       | Obsolete storage engine                                        |
12 rows in set (0.00 sec)

The second method is show engine innodb status

mysql> show engine innodb status;
ERROR 1235 (42000): Cannot call SHOW INNODB STATUS because skip-innodb is defined

But you won't find it when try to create an InnoDB table when its disabled, you won't even get a warning. What MySQL will do is use the default engine, in most cases, it's MyISAM. In some distributed version, the InnoDB is disabled by default, so you better check it before do any transaction related operations, or you will be confused.

How to enable InnoDB?

On Windows, stop the mysqld service and then edit my.ini, it will located at path as below


On Linux it will be my.cnf.

And comment out the following line

# skip-innodb

And then restart MySQL server.