All multilingual views are now MyISAM?

Question

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?

Answer 1

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_%';