12

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

Improve this question
1
  • I had the issue also in Wordpress/Civi and changed to InnoDB with no issues to date using phpAdmin!
    – Garry Hyman
    Sep 12, 2016 at 22:15

4 Answers 4

Reset to default
13

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;
Improve this answer
6
  • 1
    This solution worked on 2 Bluehost shared hosting Joomla sites for me. No cache clearing was necessary. Thank you.
    – isherwood
    Jan 31, 2016 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, 2016 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, 2018 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
    Dec 11, 2018 at 18:36
  • Catorghans answer worked for me. Thanks though!
    – Christia
    Dec 12, 2018 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.

Improve this answer
0
3

Single command to update all tables is:

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

Improve this answer
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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