Comments on: Percona Distribution for MySQL: High Availability with Group Replication Solution

By: FAN

FAN — Fri, 07 May 2021 02:04:32 +0000

In reply to Marco tusa. use mydumper

By: Marco tusa

Marco tusa — Wed, 05 May 2021 08:23:28 +0000

In reply to FAN. FAN, I see your point and modified the procedure to deal with the possible locking issue. Btw what is the reason for you to use FTWRL that is mainly used for MyISAM? I am trying to understand the case here.

By: FAN

FAN — Thu, 29 Apr 2021 06:23:52 +0000

In reply to Marco Tusa. Hi Marco You don't seem to read my description carefully, I am using your script, but the cause of the above problem is not the script, it should be a mysql bug. in Percona8.0.22 can be stable reproduction. https://github.com/Percona-Lab/group_replication_tools/pull/1

By: Marco Tusa

Marco Tusa — Wed, 28 Apr 2021 13:15:35 +0000

In reply to FAN. Hi FAN, are you using the version of the SP I indicated in the blog or the one from ProxySQL documentation? Because the latter is not working with MySQL 8 and returns more than one row. The one in the blog is https://github.com/Percona-Lab/group_replication_tools/blob/master/GR_sys_view_forProxysql_v1.sql

By: FAN

FAN — Wed, 28 Apr 2021 12:57:55 +0000

another bug in proxysql2.0.18
https://github.com/sysown/proxysql/issues/3406

By: FAN

FAN — Wed, 28 Apr 2021 12:56:47 +0000

in Percona 8.0.22 , there seems a bug.
For example, there is a single-primary mode MGR cluster, create a session s1, execute select sys.gr_member_in_primary_partition(), then create a session s2, execute FTWRL, then execute select sys.gr_member_in_primary_partition() again at s2, then you will get an error: “Subquery returns more than 1 row”, which causes the query sys.gr_member_routing_candidate_status to also report an error, even when s2 executes unlock tables, s1 still reports an error. This causes the ProxySQL health check to fail and the node to be OFFLINE_HARD

The interim solution is

CREATE FUNCTION my_server_uuid() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);$$

CREATE VIEW gr_member_routing_candidate_status AS
SELECT
IFNULL((SELECT
IF(MEMBER_STATE = ‘ONLINE’
AND ((SELECT
COUNT(*)
FROM
performance_schema.replication_group_members
WHERE
MEMBER_STATE != ‘ONLINE’) >= ((SELECT
COUNT(*)
FROM
performance_schema.replication_group_members) / 2) = 0),
‘YES’,
‘NO’)
FROM
performance_schema.replication_group_members
JOIN
performance_schema.replication_group_member_stats rgms USING (member_id)
WHERE
rgms.MEMBER_ID = my_server_uuid()),
‘NO’) AS viable_candidate,
IF((SELECT
((SELECT
GROUP_CONCAT(performance_schema.global_variables.VARIABLE_VALUE
SEPARATOR ‘,’)
FROM
performance_schema.global_variables
WHERE
(performance_schema.global_variables.VARIABLE_NAME IN (‘read_only’ , ‘super_read_only’))) ‘OFF,OFF’)
),
‘YES’,
‘NO’) AS read_only,
IFNULL(sys.gr_applier_queue_length(), 0) AS transactions_behind,
IFNULL(sys.gr_transactions_to_cert(), 0) AS transactions_to_cert;$$