Group Replication + ProxySQL cluster sync — stale writer hostgroup entries and OFFLINE_HARD persistence

[On-Off]

Hi Rene / team,

We're running a MySQL InnoDB Cluster (Group Replication, 3 members, single primary mode) fronted by a 3-node ProxySQL deployment with admin-cluster enabled (admin-cluster_mysql_servers_save_to_disk=true, sync interval ~1s, 3 peers in proxysql_servers).

Proxysql Version: 3.0.3-291-gcce161b

Mysql version:  Percona 8.4.6-6

Setup

• mysql_group_replication_hostgroups: writer=2, backup_writer=3, reader=4, offline=1, max_writers=1, writer_is_also_reader=0, active=1
• GR monitor enabled (mysql-monitor_groupreplication_healthcheck_interval=2000)
• 3 ProxySQL pods in a cluster, each with independent runtime but synced mysql_servers config

Observed behavior

After a GR failover (seems like same older member was briefly PRIMARY for some time, then the new PRIMARY got promoted), we see persistent instability:

1. Stale entries in writer hostgroup (HG 2) — a read-only SECONDARY (e.g. gr-1) remains in mysql_servers and/or runtime_mysql_servers in HG 2, often as OFFLINE_HARD, alongside the current PRIMARY (gr-0 as ONLINE).

2. Config vs runtime desync — mysql_servers and runtime_mysql_servers diverge on the same pod. Example:

   • Config: HG 2 has secondary ONLINE (mysql_servers table)
   • Runtime: PRIMARY ONLINE + secondary OFFLINE_HARD in HG 2 (runtime_mysql_servers table)

3. Cluster nodes disagree — the 3 ProxySQL pods show different mysql_servers / runtime_mysql_servers at the same time. Stale rows appear to move between pods over seconds.

4. Manual cleanup doesn't stick — deleting stale HG 2 rows from mysql_servers on all pods and running LOAD MYSQL SERVERS TO RUNTIME helps briefly, but within ~20–30 seconds stale entries reappear (including OFFLINE_HARD in config).

   ex: Attached sample data(proxysql_sample_data.txt)

5. GR cluster is healthy — replication_group_members shows one PRIMARY (read_only=0) and two SECONDARY members (read_only=1), all ONLINE.

   mysql> SELECT MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, MEMBER_ROLE, MEMBER_VERSION  FROM performance_schema.replication_group_members;
   +-----------------------------------------------------------------+-------------+--------------+-------------+----------------+
   | MEMBER_HOST                                                     | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
   +-----------------------------------------------------------------+-------------+--------------+-------------+----------------+
   | gr-cluster-mysql-gr-1.gr-cluster-mysql-gr.mysql-prod.svc.local |        3306 | ONLINE       | SECONDARY   | 8.4.6          |
   | gr-cluster-mysql-gr-0.gr-cluster-mysql-gr.mysql-prod.svc.local |        3306 | ONLINE       | PRIMARY     | 8.4.6          |
   | gr-cluster-mysql-gr-2.gr-cluster-mysql-gr.mysql-prod.svc.local |        3306 | ONLINE       | SECONDARY   | 8.4.6          |
   +-------------------------------------------------------------------------------------------------------------+-------------+-
   

   
   

What we think is happening (ProxySQL-side)

• GR monitor correctly marks a read-only node in the writer HG as OFFLINE_HARD and promotes the PRIMARY, but does not remove the stale row from HG 2.
• SAVE MYSQL SERVERS TO DISK appears to persist transient runtime state (including OFFLINE_HARD ghosts) into mysql_servers.
• LOAD MYSQL SERVERS TO RUNTIME reloads stale config, reintroducing the secondary into HG 2 before the monitor corrects it again.
• Admin-cluster sync propagates differing config between peers asynchronously, so pods drift.

Impact

Our readiness check counts all rows in HG 2 (including OFFLINE_HARD), so when a ghost row exists in runtime, the count exceeds 1 and the pod fails health checks.

(We have taken an AI to change readiness check to only ONLINE ones)

Questions

1. For GR deployments, is it expected that stale writer HG rows remain as OFFLINE_HARD rather than being deleted or moved to the offline HG?
2. Should SAVE MYSQL SERVERS TO DISK persist OFFLINE_HARD entries that the GR monitor created in runtime? Is there a recommended pattern to avoid persisting monitor-managed transient state?
3. With admin-cluster and GR monitor both active, what is the recommended configuration to keep mysql_servers consistent across cluster nodes? Should admin-cluster_mysql_servers_save_to_disk be disabled when GR monitor manages topology dynamically?
4. Is there an admin command or setting to prune stale entries from a hostgroup after failover, or to prevent LOAD from reintroducing rows the monitor has invalidated?
5. For writer_is_also_reader=0, should the PRIMARY ever appear in the reader HG in config/runtime, or is that also stale state we should explicitly clean?

What we'd find helpful

• Recommended mysql_group_replication_hostgroups + admin-cluster settings for a 3-node ProxySQL cluster in front of InnoDB Cluster.
• Clarification on the intended lifecycle of OFFLINE_HARD rows in the writer HG (persist vs auto-remove)
• Any known issues or best practices around GR monitor + cluster sync interaction

Thanks for any guidance.