how to force drupal to create tables with ENGINE=MyISAM

Question

this is a popular error in drupal that you guys might have seen this before :

PDOException: SQLSTATE[42S02]: Base table or view not found, table .... does not exist.

when I'm trying to install drupal7, it reports an error that semaphore table does not exist. I tried to create it manually by this query

CREATE TABLE IF NOT EXISTS `semaphore` (
  `name` varchar(255) NOT NULL DEFAULT '' COMMENT 'Primary Key: Unique name.',
  `value` varchar(255) NOT NULL DEFAULT '' COMMENT 'A value for the semaphore.',
  `expire` double NOT NULL COMMENT 'A Unix timestamp with microseconds indicating when the semaphore should expire.',
  PRIMARY KEY (`name`),
  KEY `value` (`value`),
  KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Table for holding semaphores, locks, flags, etc. that...';

it reported the same error. I changed InnoDB to myISAM and it worked!!! I found out that tables with the InnoDB engine are not supported in mysql on the server and unfortunately drupal7 creates all the tables with this engine by default. is there any way to change this default to MyISAM ?

Answer 1

The schema definitions can take an argument of 'mysql_engine', which defaults to 'InnoDB', in createTableSql().

In theory you could thus alter all the defined schemas with a custom module, and change the engine.

It's highly questionable to do so though. If you value your site and data, you should NOT use MyISAM unless you understand the difference the two engines.

Switch to a provider that supports InnoDB instead. MySQL itself defaults to InnoDB as of 5.5. I would be very suspicious of any provider that didn't support InnoDB.

Answer 2

Try this way: (from here: https://www.drupal.org/node/1089724 )

In includes>database>mysql>shema.inc, on line 84, I changed 'mysql_engine' => 'InnoDB' to 'mysql_engine' => 'MyISAM'.

It works for me.

Answer 3

Define 'mysql_engine' => 'MyISAM' in your hook_schima as given below:

function hook_schema() {
$schema['table_name'] = array(
'description' => 'Your description.',
'fields' => array(
  'id' => array(
    'description' => 'Auto increment id.',
    'type' => 'serial',
    'unsigned' => TRUE,
    'not null' => TRUE,
  ),
  'old_id' => array(
    'description' => 'The old id.',
    'type' => 'int',
    'unsigned' => TRUE,
    'not null' => TRUE,
  ),
  'Comments' => array(
    'description' => 'Add comment for table.',
    'type' => 'varchar',
    'length' => 200,
    'not null' => FALSE,
  ),
 ),
 'primary key' => array('id'),
 'mysql_engine' => 'MyISAM',
 );
 return $schema;
}