Sunday, February 5, 2023

MYSQL::SQL Error(3100): Error on observer while running replication hook 'before commit' in MYSQL innodb cluster group replication.#STOP GROUP REPLICATION

 Error from application team end: SQL Error(3100): Error on observer while running replication hook 'before commit' in MYSQL innodb cluster group replication.

========================================================================

In this case, we just go to each node and run the following command to stop group replication:

Ideally, the step would be the following:

1. Execute the following command on all nodes to stop the group replication:

mysql> STOP GROUP_REPLICATION;

2. Run the following command on all nodes in the cluster:

mysql> SELECT @@GLOBAL.GTID_EXECUTED \G

Find the node that contains highest GTID set. Reference: How Do I Know Which Member To Start First In Group Replication After All Members (The Entire Group) Shutdown ( Doc ID 2232850.1 )

3.. Once you find the node that contains highest GTID set, then you could connect it to the shell and run the following command:

MySQL JS> dba.rebootClusterFromCompleteOutage()

this should bring all nodes back online.

Then, you could restart the router to fetch the latest metadata.

This should bring the nodes ONLINE if they went out of the cluster due to network issue. If it went out of the cluster due to applier thread error, then it cannot join the group until you fix the issue manually.

=================================================================

Commands executed:

mysql> STOP GROUP_REPLICATION;

Query OK, 0 rows affected (19.43 sec)

mysql> SELECT @@GLOBAL.GTID_EXECUTED \G

*************************** 1. row ***************************

@@GLOBAL.GTID_EXECUTED: 28179efb-72c5-11e4-b496-0050569242d9:1-13995703,

329fcb1a-c0dd-11eb-82b1-005056847352:1,

6a6ae23d-183f-11eb-ae83-005056846111:1-114606407:114931406-114935574,

ae4d4435-ecbc-11ea-a835-0050568415e0:1-348529,

d1f71bf8-c0dd-11eb-82b1-005056847352:1-253642653,

d9014b6b-1812-11eb-976b-005056846111:1-23

1 row in set (0.00 sec)

mysql>

@MYSQL SHELL:

MySQL Shell 8.0.20-commercial

Copyright (c) 2016, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates.

Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.

 MySQL  JS > \c loginadmin@CLUSTERNODE3_IP:3306

Creating a session to 'loginadmin@CLUSTERNODE3_IP:3306'

Please provide the password for 'loginadmin@CLUSTERNODE3_IP:3306': **************

Save password for 'loginadmin@CLUSTERNODE3_IP:3306'? [Y]es/[N]o/Ne[v]er (default No):

Fetching schema names for autocompletion... Press ^C to stop.

Your MySQL connection id is 5215

Server version: 8.0.20-commercial MySQL Enterprise Server - Commercial

No default schema selected; type \use <schema> to set one.

 MySQL  CLUSTERNODE3_IP:3306 ssl  JS > var cluster =dba.getCluster();

Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) (RuntimeError)

 MySQL  CLUSTERNODE3_IP:3306 ssl  JS > cluster.status();

TypeError: Cannot read property 'status' of undefined

 MySQL  CLUSTERNODE3_IP:3306 ssl  JS > dba.rebootClusterFromCompleteOutage()

Restoring the default cluster from complete outage...


The instance 'CLUSTERNODE2_IP:3306' was part of the cluster configuration.

Would you like to rejoin it to the cluster? [y/N]: y


The instance 'CLUSTERNODE1_IP:3306' was part of the cluster configuration.

Would you like to rejoin it to the cluster? [y/N]: y


Disabling super_read_only mode on instance 'CLUSTERNODE3_IP:3306'.

WARNING: The member will only proceed according to its exitStateAction if auto-rejoin fails (i.e. all retry attempts are exhausted).


CLUSTERNODE3_IP:3306 was restored.

Rejoining 'CLUSTERNODE2_IP:3306' to the cluster.

Rejoining 'CLUSTERNODE1_IP:3306' to the cluster.

The cluster was successfully rebooted.


<Cluster:prodcluster>

 MySQL  CLUSTERNODE3_IP:3306 ssl  JS > cluster.status();

TypeError: Cannot read property 'status' of undefined

 MySQL  CLUSTERNODE3_IP:3306 ssl  JS > var cluster =dba.getCluster();

 MySQL  CLUSTERNODE3_IP:3306 ssl  JS > cluster.status();

{

    "clusterName": "prodcluster",

    "defaultReplicaSet": {

        "name": "default",

        "primary": "CLUSTERNODE3_IP:3306",

        "ssl": "REQUIRED",

        "status": "OK",

        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

        "topology": {

            "CLUSTERNODE1_IP:3306": {

                "address": "CLUSTERNODE1_IP:3306",

                "mode": "R/O",

                "readReplicas": {},

                "replicationLag": null,

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.20"

            },

            "CLUSTERNODE2_IP:3306": {

                "address": "CLUSTERNODE2_IP:3306",

                "mode": "R/O",

                "readReplicas": {},

                "replicationLag": null,

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.20"

            },

            "CLUSTERNODE3_IP:3306": {

                "address": "CLUSTERNODE3_IP:3306",

                "mode": "R/W",

                "readReplicas": {},

                "replicationLag": null,

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.20"

            }

        },

        "topologyMode": "Single-Primary"

    },

    "groupInformationSourceMember": "CLUSTERNODE3_IP:3306"

}

 MySQL  CLUSTERNODE3_IP:3306 ssl  JS > cluster.status();

{

    "clusterName": "prodcluster",

    "defaultReplicaSet": {

        "name": "default",

        "primary": "CLUSTERNODE3_IP:3306",

        "ssl": "REQUIRED",

        "status": "OK",

        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",

        "topology": {

            "CLUSTERNODE1_IP:3306": {

                "address": "CLUSTERNODE1_IP:3306",

                "mode": "R/O",

                "readReplicas": {},

                "replicationLag": null,

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.20"

            },

            "CLUSTERNODE2_IP:3306": {

                "address": "CLUSTERNODE2_IP:3306",

                "mode": "R/O",

                "readReplicas": {},

                "replicationLag": null,

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.20"

            },

            "CLUSTERNODE3_IP:3306": {

                "address": "CLUSTERNODE3_IP:3306",

                "mode": "R/W",

                "readReplicas": {},

                "replicationLag": null,

                "role": "HA",

                "status": "ONLINE",

                "version": "8.0.20"

            }

        },

        "topologyMode": "Single-Primary"

    },

    "groupInformationSourceMember": "CLUSTERNODE3_IP:3306"

}

 MySQL  CLUSTERNODE3_IP:3306 ssl  JS >

No comments:

Post a Comment

MYSQL::Setting Validate_Password componet for MySQL Database to ensure password policy settings

Inadequate Password Settings for MySQL Database We observed that the `validate_password%` settings on hostname `<insert hostname>` a...