Recipe 9.13. 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)   end   return engines end