2

I have a Drupal 7 site and was wondering how can I tell if my database is using InnoDB ?

If its not using InnoDB, would I be able to just switch over easily? I just recently switched to MariaDB if that makes a difference.

  • The DB Tuner module did this for Drupal 6. – mpdonadio Jun 14 '13 at 16:02
  • 1
    FWIW, Drupal 7 should be defaulting to InnoDB (think drupal.org/node/301362 is the canonical ref). I am not even sure if 7 will work properly with MyISAM under load, as some parts assume proper transactions. – mpdonadio Jun 14 '13 at 18:19
4

To find out which tables are InnoDB and MyISAM

COUNTS

SELECT ENGINE,COUNT(1) TableCount FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
GROUP BY engine;

Since MyISAM is a non-transactional storage engine, it has no foreign key referential integrity to enforce. You could change all MyISAM tables to InnoDB, but with one exception: Before MySQL 5.6, FULLTEXT indexes are only available for MyISAM tables.

Here is how to find all tables using FULLTEXT indexes.

SELECT tbl.table_schema,tbl.table_name FROM
(
    SELECT table_schema,table_name
    FROM information_schema.tables
    WHERE engine='MyISAM' AND table_schema NOT IN
    ('information_schema','mysql','performance_schema')
) tbl
INNER JOIN
(
    SELECT table_schema,table_name
    FROM information_schema.statistics
    WHERE index_type='FULLTEXT'
) ndx
USING (table_schema,table_name);

If this comes back with no rows, you can convert all MyISAM tables to InnoDB without worry. If you have any tables with FULLTEXT indexes and you do not use them, you could drop the FULLTEXT indexes then convert the tables to InnoDB.

CONVERSION SCRIPTS

Here is a script to convert all MyISAM tables that do not have FULLTEXT indexes to InnoDB:

SQLSTMT="SELECT CONCAT('ALTER TABLE ',tbl.table_schema,'.',tbl.table_name,' ENGINE=InnoDB;')"
SQLSTMT="${SQLSTMT} FROM (SELECT table_schema,table_name FROM information_schema.tables"
SQLSTMT="${SQLSTMT} WHERE engine='MyISAM' AND table_schema NOT IN"
SQLSTMT="${SQLSTMT} ('information_schema','mysql','performance_schema')) tbl"
SQLSTMT="${SQLSTMT} INNER JOIN"
SQLSTMT="${SQLSTMT} (SELECT table_schema,table_name FROM information_schema.statistics"
SQLSTMT="${SQLSTMT} WHERE index_type='FULLTEXT') ndx USING (table_schema,table_name)"
mysql -uroot -p -ANe"${SQLSTMT}" > ConvertMyISAMToInnoDB.sql
mysql -uroot -p -A < ConvertMyISAMToInnoDB.sql

If you know for a certainty that there are no FULLTEXT indexes in use, you can do this:

MYSQL_CONN="-uroot -prootpassword"
SQLSTMT="SELECT CONCAT('ALTER TABLE ',table_schema,'.',"
SQLSTMT="${SQLSTMT} table_name,' ENGINE=InnoDB;') InnoDBConversionSQL"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE engine='MyISAM' AND"
SQLSTMT="${SQLSTMT} table_schema NOT IN ('information_schema','mysql','performance_schema')"
SQLSTMT="${SQLSTMT} ORDER BY (data_length+index_length)"
MYSQL_OPTIONS="-AN"
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} -e"${SQLSTMT}" > ConvertMyISAMToInnoDB.sql
mysql ${MYSQL_CONN} ${MYSQL_OPTIONS} < ConvertMyISAMToInnoDB.sql

I have suggested this in the past : How do I convert a database from MyISAM to InnoDB?

CAVEAT

Changing all tables to InnoDB will change the needs for MySQL's memory usage. Why?

  • 2
    @LesterPeabody look at his username. – AyeshK Jun 14 '13 at 18:53
  • Well shiver me timbers... – Lester Peabody Jun 14 '13 at 19:33
1

SHOW TABLE STATUS WHERE Name = 'table_name';
where a column with engine will appear showing you the engine type.

Converting should not be a problem The MySQL converting innodb reference can be used (Please backup your db before trying anything like this).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.