How to convert MAIN mysql database to InnoDB from MyIsam

Question

I am trying to manage mysql group replication and I noticed a problem when manipulating users and grants. 10 of the main mysql tables in the main mysql database are MyIsam. So I cant add databases or user permissions because they fail and wont replicate. Master-master group replication requirs everything InnoDB.

ALTER TABLE works fine on regular custom databases/tables but how do you fix this on the main mysql database?

I tried this but they all fail:

ALTER TABLE mysql.db ENGINE = InnoDB;
ALTER TABLE mysql.tables_priv ENGINE = InnoDB;
ALTER TABLE mysql.user ENGINE = InnoDB;

ERROR: ERROR 1726 (HY000): Storage engine 'InnoDB' does not support system tables.

Another error running CREATE USER...

[ERROR] Plugin group_replication reported: 'Table db does not use the InnoDB storage engine. This is not compatible with Group Replication'

ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin group_replication.

Server version: 5.7.23-log MySQL Community Server

Iam not sure what the problem is here.. replication should work on MyISAM and InnoDB engines without problems... Do you use replication between different MySQL versions? — Raymond Nijland
All three boxes are same version. I dont want to use MyIsam at all. I want to change them to InnoDB. If i stop group replication, i can add the new users and permissions but when replication is ONLINE i get errors doing so. — Kyle Anderson
I think you are trying to solve the wrong problem. You cannot alter the engine of system tables (and it wouldn't have an effect on replication if you could), they are fixed and managed by the server (and if you really really want them to be InnoDB, upgrade to MySQL 8). The question you should ask (and elaborate on what you did, your configuration, and what exactly does/doesn't happen) is: "Why do my grant-statements do not replicate?" — Solarflare
Run any commands on regular databases replicates fine. Run any kind of CREATE/GRANT/ALTER adjusting mysql tables and get [ERROR] Plugin group_replication reported: 'Table db does not use the InnoDB storage engine. This is not compatible with Group Replication' — Kyle Anderson
this seems to have fixed the errors. I was not getting errors about passwords in logs though so makes no sense. I removed the validate password plugin... mysql> uninstall plugin validate_password; ... I am now able to revoke and create user and grant fine and they replicate. — Kyle Anderson

Rick James Rick James · Accepted Answer · 2018-10-13T18:25:23

DO NOT CHANGE THE ENGINE FOR SYSTEM TABLES

MySQL has not yet changed the code enough to allow for mysql.* to be anything other than MyISAM. MySQL 8.0 makes the change by turning the tables (the "data dictionary") into a InnoDB tables, with radically different structure and capabilities.

Since you are at 5.7.23, you are only one (big) step away from 8.0.xx. Consider upgrading.

Replication works with MyISAM tables, but clustering replication does not -- Galera and Group Replication deal with those MyISAM tables in other ways. See the documentation on what happens with GRANT, CREATE USER, etc. Do not use UPDATE and INSERT to manipulate the login-related tables.

(The Author of this Question seems to have fixed the problem by uninstalling a plugin.)

How to convert MAIN mysql database to InnoDB from MyIsam

1 Answers