10

I just upgraded a large site to 4.7 (without any issues in the upgrade process by the way). The new "System Status" admin screen gives a warning as follows:

"Your database is configured to use the MyISAM database engine. CiviCRM requires InnoDB. You will need to convert any MyISAM tables in your database to InnoDB. Using MyISAM tables will result in data integrity issues."

MyISAM

This is news to me. I took the database over from another person and have upgraded it a number of times already. I looked at the database and it does have some strangeness. For the most part, each civicrm_ table is listed as INNODB, all the log_ tables show up as ARCHIVE, and at the bottom the global setting seems to be MYISAM. The one exception is the civicrm_system_log table, which is listed as MYISAM.

Is this going to cause problems, and is there an easy way to get everything on track?

PhpMyAdmin

...

PhpMyAdmin 2

  • I had the issue also in Wordpress/Civi and changed to InnoDB with no issues to date using phpAdmin! – Garry Hyman Sep 12 '16 at 22:15
12

I backed the database up, opened it up in Sequel Pro and went to the civicrm_system_log table. Under "Table Info" I could select "InnoDB" from the dropdown. Then I cleared the caches on the CiviCRM installation and the system status came back green ("System Status: OK").

Sequel Pro

Per a comment from George Lozier on another thread, the MySQL query to do the same is:

ALTER TABLE civicrm_system_log ENGINE=InnoDB;
  • 1
    This solution worked on 2 Bluehost shared hosting Joomla sites for me. No cache clearing was necessary. Thank you. – isherwood Jan 31 '16 at 3:56
  • 1
    For me it was not the log table but one called civicrm_task_action_temp.... But changing the table type worked. – thoni56 Aug 16 '16 at 12:57
  • I'm a site from 4.1.0 and do not see the system_log or task_action_temp tables. What else can I try? – Christia Dec 11 '18 at 12:09
  • @Christia Take a look at any temporary tables (see: civicrm.stackexchange.com/questions/26969/… ) -- or just go through tables one by one until you find the culprit(s). – Laryn - CEDC.org Dec 11 '18 at 18:36
  • Catorghans answer worked for me. Thanks though! – Christia Dec 12 '18 at 1:48
7

Sounds good. To be sure, please execute ///civicrm/menu/rebuild?reset=1&triggerRebuild=1 which fixes the trigger and function definitions in the database.

2

Single command to update all tables is:

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' engine=InnoDB;') FROM information_schema.TABLES WHERE ENGINE = 'MyISAM';

1

I had the issue which kept recurring - traced it to an extension that I use for exporting to excel - was creating the temp file in MyISAM - removed the extension and the issue went away.

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.