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

Question

SETUP:

• Three mysql group replication nodes in master master master.
• All work fine. I can add users/dbs and insert/update data.
• Each node is bound to a private IP address.
• I created a bash script to connect to mysql to delete users.
• Deleting a database with a script works fine.

PROBLEM:

The following commands will NOT run. I can create users and databases and delete databases but cant delete the users. I cant tell if its a replication issue OR a privilege issue.

• DELETE FROM mysql.user WHERE user='testme123';
• DELETE FROM mysql.db WHERE user='testme123';
• DROP USER IF EXISTS 'testme123';

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

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

I get same error via local mysql console too logged in as root.

QUESTION(S):

• What could be preventing this?
• How can I troubleshoot what it is I am missing?

Answer 1

If you are using Group Replication (in 5.7 or 8.0), you must do all user authentication through the GRANT/DROP/CREATE USER/etc commands, not INSERT/UPDATE/DELETE/etc.

MyISAM is not replicated in Group Replication due to serious technical difficulties.

(The above comments apply to Galera / PXC, too.)

(Caveat: What I have said may not be strictly true, but I think it will keep you out of trouble, and address the Question at hand.)

Answer 2

In my case, I got the same error while restoring the backup from a single-primary mode MySQL Replication Group. I took backup using --single-transaction flag during mysqldump cmd.

$ mysqldump -uroot -p<root_password> -h<host> --set-gtid-purged=OFF --single-transaction --all-databases --triggers --routines --events < dump.sql

Here, see the usage of --single-transaction flag for the problem it causes.

$ mysqldump --help
...
--single-transaction 
                      Creates a consistent snapshot by dumping all tables in a
                      single transaction. Works ONLY for tables stored in
                      storage engines which support multiversioning (currently
                      only InnoDB does); the dump is NOT guaranteed to be
                      consistent for other storage engines. While a
                      --single-transaction dump is in process, to ensure a
                      valid dump file (correct table contents and binary log
                      position), no other connection should use the following
                      statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                      TRUNCATE TABLE, as consistent snapshot is not isolated
                      from them. Option automatically turns off --lock-tables.
...

So, after reading the suggestion @RickJames provided, I just remove the --single-transaction flag from the mysqldump cmd during backup and then restored it to a new Replication Group.

Note: MySQL server version was 5.7.25

Answer 3

One of the group replications requirement is - Every table that is to be replicated by the group must have a defined primary key, or primary key equivalent where the equivalent is a non-null unique key. [link]

So, If you are using a table without fulfilling this condition, server will throw this error. Use a table with Primary ID.