MySQL group replication plugin (InnoDB cluster) support

[zipiju]

Hello,

Are there plans to modify MySQL database schema to be compatible with MySQL group replication plugin?
I'm in a process of setting up a new cluster (migration from Prosody) and had issues when database was stored on MySQL replicated cluster. Because of that I had to move the database to a single instance MySQL which makes HA less robust.
With MySQL group replication there are certain limitations as described here and here.
The schema I'm currently using was downloaded from https://raw.githubusercontent.com/processone/ejabberd/master/sql/mysql.sql and with that I was getting the below errors. These were with practically empty database with one test user, thus there might be more compatibility problems than the ones listed below.
The cluster this was tested on was a three node all nodes r/w cluster.

Thank you.

[error] <0.355.0>@ejabberd_sql:check_error/2:1229 SQL query 'Q118993903' at {mod_caps_sql,90} failed: "#HY000The table does not comply with the requirements by an external plugin."
[error] <0.355.0>@ejabberd_sql:check_error/2:1236 SQL query 'delete from caps_features where node='https://gajim.org' and subnode='4BDVr9GqUdmes9KqCybWLyrw+nA=';' failed: "#HY000The table does not comply with the requirements by an external plugin."
[error] <0.359.0>@ejabberd_sql:check_error/2:1229 SQL query 'Q112178794' at {mod_private_sql,53} failed: "#HY000The table does not comply with the requirements by an external plugin."
[error] <0.359.0>@ejabberd_sql:check_error/2:1236 SQL query 'UPDATE private_storage SET data='<roster xmlns=''roster:delimiter''>::</roster>' WHERE username='test' AND namespace='roster:delimiter'' failed: "#HY000The table does not comply with the requirements by an external plugin."

[ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table private_storage does not have any PRIMARY KEY. This is not compatible with Group Replication.'
[ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table rosterusers does not have any PRIMARY KEY. This is not compatible with Group Replication.'
[ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table caps_features does not have any PRIMARY KEY. This is not compatible with Group Replication.'

[licaon-kter]

Wasn't https://github.com/processone/ejabberd/blob/master/sql/mysql.new.sql and add https://docs.ejabberd.im/archive/20_04/toplevel/#new-sql-schema recommended?

[zipiju]

TBH not sure, I'm not certain which install guide I was going by. Will try with the new one and will report back. Thank you.

[zipiju]

With the new schema there still are some compatibility issues:

[error] <0.383.0>@ejabberd_sql:check_error/2:1236 SQL query 'delete from caps_features where node='https://gajim.org' and subnode='4BDVr9GqUdmes9KqCybWLyrw+nA=';' failed: "#HY000The table does not comply with the requirements by an external plugin."
[error] <0.383.0>@ejabberd_sql:check_error/2:1229 SQL query 'Q118993903' at {mod_caps_sql,90} failed: "#HY000The table does not comply with the requirements by an external plugin."
[error] <0.383.0>@ejabberd_sql:outer_transaction/3:556 SQL transaction restarts exceeded
** Restarts: 10
** Last abort reason: "#HY000The table does not comply with the requirements by an external plugin."
** Stacktrace: [{ejabberd_sql,restart,1,
                              [{file,"src/ejabberd_sql.erl"},{line,196}]},
                {lists,foreach,2,[{file,"lists.erl"},{line,1342}]},
                {ejabberd_sql,outer_transaction,3,
                              [{file,"src/ejabberd_sql.erl"},{line,545}]},
                {ejabberd_sql,run_sql_cmd,4,
                              [{file,"src/ejabberd_sql.erl"},{line,487}]},
                {p1_fsm,handle_msg,10,[{file,"src/p1_fsm.erl"},{line,582}]},
                {proc_lib,init_p_do_apply,3,
                          [{file,"proc_lib.erl"},{line,226}]}]
** When State == {state,<0.393.0>,mysql,undefined,<<"test.tld">>,
                        {{[],[]},0,unlimited},
                        undefined}

[error] <0.389.0>@ejabberd_sql:check_error/2:1236 SQL query 'insert into pubsub_state(nodeid, jid, affiliation, subscriptions) values (3, '[email protected]', 'o', '')' failed: "#HY000The table does not comply with the requirements by an external plugin."
[error] <0.389.0>@ejabberd_sql:check_error/2:1229 SQL query 'Q43820748' at {node_flat_sql,84} failed: "#HY000The table does not comply with the requirements by an external plugin."
[error] <0.602.0>@mod_pubsub:get_transaction_response/1:3894 Transaction aborted:
** exception throw: {aborted,"#HY000The table does not comply with the requirements by an external plugin."}
   in function  ejabberd_sql:restart/1 (src/ejabberd_sql.erl, line 196)
   in call from node_flat_sql:create_node/2 (src/node_flat_sql.erl, line 83)
   in call from node_pep_sql:create_node/2 (src/node_pep_sql.erl, line 73)
   in call from mod_pubsub:node_call/4 (src/mod_pubsub.erl, line 3802)
   in call from mod_pubsub:'-create_node/7-fun-0-'/7 (src/mod_pubsub.erl, line 1547)
   in call from mod_pubsub:'-do_transaction/4-fun-0-'/3 (src/mod_pubsub.erl, line 3855)
   in call from ejabberd_sql:outer_transaction/3 (src/ejabberd_sql.erl, line 545)
   in call from ejabberd_sql:run_sql_cmd/4 (src/ejabberd_sql.erl, line 487)

[ERROR] [MY-011542] [Repl] Plugin group_replication reported: 'Table caps_features does not have any PRIMARY KEY. This is not compatible with Group Replication.'

[prefiks]

I think it could be possible to change schema to be compatible with those requirements. Seems that most of missing requirements would be changing unique indexes to primary key (and that should be doable if replication could accept multicolumn primary keys).

I don't have plans right now to work on this, but i may take look in future.