Mysql innodb cluster node stucked on "RECOVERING"

3837224

Dear All,

I'm having some trouble on creating a new cluster after dissolving the old one.

I have recreated successfully the cluster, but one of the nodes is still blocked in status "RECOVERING".

MySQL  mysql-cluster-1.ferrarigroup.net:3306 ssl  JS > c.status()

{

    "clusterName": "clustermysql",

    "defaultReplicaSet": {

        "name": "default",

        "primary": "mysql-cluster-1.ferrarigroup.net:3306",

        "ssl": "REQUIRED",

        "status": "OK_NO_TOLERANCE",

        "statusText": "Cluster is NOT tolerant to any failures.",

        "topology": {

            "mysql-cluster-1.ferrarigroup.net:3306": {

                "address": "mysql-cluster-1.ferrarigroup.net:3306",

                "mode": "R/W",

                "readReplicas": {},

                "role": "HA",

                "status": "ONLINE"

            },

            "mysql-cluster-2.ferrarigroup.net:3306": {

                "address": "mysql-cluster-2.ferrarigroup.net:3306",

                "mode": "n/a",

                "readReplicas": {},

                "role": "HA",

                "status": "RECOVERING"

            }

        },

        "topologyMode": "Single-Primary"

    },

    "groupInformationSourceMember": "mysql-cluster-1.ferrarigroup.net:3306"

}

Even if I rejoin the node, the status is still blocked in recovering.

How can I check why the node cannot complete the recovery?

The DB is empty, no schema has been added.

Best regards,

Andrea

3770300

Try to skip one transaction on that server using the below commands,

get the gtid from below command

show variables like "%gtid%"

stop group_replication.

reset master;

set global gtid_purged="gtid from the above command  +1"

start group_replication

Gaz in Oz

What is your mysql version?

3837224

MySQL Commercial 8.0.15

Gaz in Oz

According to the documentation, you can get more detailed information by:

The output of the <em class="replaceable"><code>Cluster.status() operation can be extended to enable you to display information about the underlying Group Replication group used by the cluster. To see information about the groupName and memberId; and general statistics about the number of transactions checked, proposed, and rejected by instances issue:<em class="replaceable">Cluster</em><span class="punctuation token">.</span><span class="token function">status</span><span class="punctuation token">(</span><span class="punctuation token">{</span>extended<span class="punctuation token">:</span><span class="token boolean">true</span><span class="punctuation token">})</span>

...and

To see information about recovery and regular transaction I/O, applier worker thread statistics and any lags; applier coordinator statistics, if parallel apply is enabled; error, and other information from I/O and applier threads issue<em class="replaceable">Cluster</em><span class="punctuation token">.</span><span class="token function">status</span><span class="punctuation token">(</span><span class="punctuation token">{</span>queryMembers<span class="punctuation token">:</span><span class="token boolean">true</span><span class="punctuation token">}</span><span class="punctuation token">)</span>

https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-working-with-cluster.html#check-innodb-cluster-status

3837224

Many thanks for your suggestion, I got this error in the log of the node I'm trying to add: 2019-02-12T10:40:23.377634Z 2789 [ERROR] [MY-010584] [Repl] Slave SQL for channel 'group_replication_recovery': Error executing row event: 'Unknown database 'mysql_innodb_cluster_metadata'', Error_code: MY-001049 2019-02-12T10:40:23.377674Z 2789 [Warning] [MY-010584] [Repl] Slave: Unknown database 'mysql_innodb_cluster_metadata' Error_code: MY-001049 2019-02-12T10:40:23.377707Z 2789 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000001' position 1652 2019-02-12T10:40:23.377733Z 2597 [Note] [MY-011585] [Repl] Plugin group_replication reported: 'Terminating existing group replication donor connection and purging the corresponding logs.' 2019-02-12T10:40:23.377984Z 2788 [Note] [MY-011026] [Repl] Slave I/O thread killed while reading event for channel 'group_replication_recovery'. 2019-02-12T10:40:23.378026Z 2788 [Note] [MY-010570] [Repl] Slave I/O thread exiting for channel 'group_replication_recovery', read up to log 'mysql-bin.000001', position 5698 I don't know why the DB is missing or cannot be created. Should I set  super_read_only = off ?

3770300

Try to skip one transaction on that server using the below commands,

get the gtid from below command

show variables like "%gtid%"

stop group_replication.

reset master;

set global gtid_purged="gtid from the above command  +1"

start group_replication