2

After the most recent CiviCRM security update to 5.56.2, a lot of my D7 sites (maybe all my multilingual ones?) are showing the old MyISAM Database Engine warning.

I took one example and discovered that it was all the civicrm db 'views' (not the drupal views) that were (now?) MyISAM. I'm using MariaDB 10.3

I did confirm that the default storage engine is InnoDB.

I presume it happened as part of the db upgrade part of the civi version upgrade, which was done via the command line using cv, i.e. cv upgrade:db

I believe the db upgrade will rebuild the views.

Anyone else have a similar experience and/or solution? Or is this not an issue and it's just a false reporting problem?

9
  • The only views I think are multilingual, and that should use the default engine. Are they from an extension?
    – Demerit
    Jan 9, 2023 at 16:11
  • Yes, it's exactly all the multilingual views. I can also see this issue with a D9 site with Mariadb 10.4, so it's not specific to D7 or mariadb 10.3.
    – Alan Dixon
    Jan 9, 2023 at 20:58
  • Are you sure it's views? It might also be temp tables. Try SELECT TABLE_NAME FROM information_schema.TABLES WHERE ENGINE = 'MyISAM' AND TABLE_SCHEMA = '<your db name>' AND TABLE_NAME LIKE 'civicrm_%' AND TABLE_NAME NOT LIKE 'civicrm_tmp_%' - replace "your db name"
    – Demerit
    Jan 9, 2023 at 21:35
  • Definitely only the views, I did a dump by table to confirm.
    – Alan Dixon
    Jan 9, 2023 at 22:44
  • Ok the above sql is what civi checks to show that status check so I'm out of ideas then.
    – Demerit
    Jan 9, 2023 at 22:54

1 Answer 1

2

Use SELECT TABLE_NAME FROM information_schema.TABLES WHERE ENGINE = 'MyISAM' AND TABLE_SCHEMA = '<your db name>' AND TABLE_NAME LIKE 'civicrm_%' AND TABLE_NAME NOT LIKE 'civicrm_tmp_%' - replace "your db name" - to find which tables it thinks are wrong. That is the same query the status check uses.

Those temp tables you're seeing in the comments above are from years ago so I'm not sure why they would show up just now, but that must be it: https://github.com/civicrm/civicrm-core/pull/12311/commits/c78f866ee1bcacd88801275c9a8ada9119e2b558

To clarify the confusion inherent in the question: MyISAM views (for multilingual sites) are expected and normal!

And, for bonus points, here's some SQL to generate a DROP statement to remove the offending old tables:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' )  AS statement FROM information_schema.tables  WHERE ENGINE = 'MYISAM' AND table_name like 'civicrm_%';
2
  • 1
    Thank you, that's perfect! FWIW, the filesystem dates on those tables are from when I migrated these sites to a new server, so I suspect these tables were from a backup that wasn't configured to remove old files!
    – Alan Dixon
    Jan 11, 2023 at 15:29
  • Ah that explains that.
    – Demerit
    Jan 11, 2023 at 16:29

Your Answer

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

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