MySQL Cookbook, 2nd Edition

Determining Which Storage Engines the Server Supports

Problem

You want to know whether you can create a table using a given storage engine.

Solution

Use the SHOW ENGINES statement to ask the server which storage engines it supports.

Discussion

The SHOW ENGINES statement provides information about which storage engines the server supports. Its output looks like this:

mysql>SHOW ENGINES\G
*************************** 1. row ***************************
 Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
 Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
 Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
...

The Engine value indicates a storage engine name and the Support value indicates its status. If Support is YES or DEFAULT, the engine is available. If the value is NO or DISABLED, the engine is not available. The following Ruby method uses those rules to determine engine status and return a list of the engines that are available:

def get_storage_engines(dbh) engines = [] dbh.select_all("SHOW ENGINES").each do |engine, support| engines << engine if ["YES", "DEFAULT"].include?(support.upcase) ...