mgr-proxysql topology: writes fail with super-read-only out of the box

[loks0n]

mgr-proxysql topology: writes via ProxySQL fail with super-read-only out of the box

TL;DR

With the bundled mysql-1.0.2 addon and mgr-proxysql topology, every INSERT/UPDATE/CREATE statement sent through the ProxySQL endpoint fails with ERROR 1290: super-read-only. There are three independent bundled-config bugs that combine to produce this:

1. The ProxySQL native GR monitor cannot read the view it depends on, because the view is missing AND the proxysql MySQL user has no SELECT grant for it.
2. mysql_group_replication_hostgroups is empty, so ProxySQL has no GR-aware writer/reader/offline mapping.
3. The bundled mysql_query_rules route writes (and the catch-all) to the reader hostgroup numbers.

Together these mean ProxySQL does not know which node is the writer, and even if it did, the query rules send writes to a secondary.

Repro

KubeBlocks v1.0.2, mysql addon v1.0.2, fresh kind cluster. Apply a vanilla Cluster CR using mgr-proxysql (full manifest at the end):

kubectl exec -n kb-test mysql-0-proxysql-0 -- mysql -h 127.0.0.1 -P 6033 -uroot -p$ROOT_PW \
  -e "CREATE DATABASE foo;"
# ERROR 1290 (HY000) at line 1: The MySQL server is running with the
# --super-read-only option so it cannot execute this statement

Root cause #1 — proxysql MySQL user grants insufficient

$ kubectl exec ... mysql -uroot -p$PW -e "SHOW GRANTS FOR 'proxysql'@'%';"
GRANT REPLICATION CLIENT ON *.* TO `proxysql`@`%`

ProxySQL's native GR monitor expects to SELECT from sys.gr_member_routing_candidate_status. The bundled proxysql user only has REPLICATION CLIENT — no SELECT on sys.* and no SELECT on performance_schema.*. Even if the view existed, this user couldn't read it.

Root cause #2 — sys.gr_member_routing_candidate_status view does not exist

$ kubectl exec ... mysql -uroot -p$PW -e "SHOW TABLES IN sys LIKE 'gr_member%';"
(empty)

The view is not standard MySQL — it's normally created by MySQL Shell's dba.createCluster() in InnoDB Cluster setups. The bundled mysql addon ships stock MySQL with plain Group Replication, no InnoDB Cluster machinery, so the view is absent. ProxySQL's monitor fails:

$ kubectl exec ... mysql -uproxysql -p$PROXYSQL_PW \
  -e "SELECT * FROM sys.gr_member_routing_candidate_status;"
ERROR 1142 (42000) at line 1: SELECT command denied to user 'proxysql'@'localhost'
  for table 'gr_member_routing_candidate_status'

(MySQL returns "command denied" rather than "table doesn't exist" when both are true.)

ProxySQL's monitor log confirms it's hitting this error every cycle:

SELECT hostname, error FROM mysql_server_group_replication_log
  WHERE error IS NOT NULL ORDER BY time_start_us DESC LIMIT 6;
hostname                                          | error
mysql-...-mysql-0.mysql-...-mysql-headless.kb-test  | SELECT command denied to user 'proxysql'@... for table 'gr_member_routing_candidate_status'
... (repeated for every node, every poll)

Root cause #3 — bundled query rules route to the wrong hostgroups

$ kubectl exec ... mysql -h127.0.0.1 -P6032 -uadmin -p$ADMIN_PW \
  -e "SELECT rule_id, match_digest, destination_hostgroup FROM runtime_mysql_query_rules ORDER BY rule_id;"
rule_id  match_digest          destination_hostgroup
1        ^SELECT.*FOR UPDATE$  2
2        ^SELECT               3
3        .*                    2

By upstream ProxySQL convention (and by the mysql_group_replication_hostgroups schema KB itself uses), hostgroup 1 is the writer, 2 is reader, 3 is offline, 4 is backup-writer. The bundled rules send:

• SELECT FOR UPDATE → reader (should be writer; SELECT FOR UPDATE locks rows)
• plain SELECT → offline (should be reader)
• catch-all .* → reader (should be writer)

So even if the GR monitor worked, every write statement falls through to rule_id=3 and gets routed to a secondary.

Combined with the next bug, no INSERT can ever succeed.

Root cause #4 — mysql_users.default_hostgroup = 0 is undefined

$ kubectl exec ... mysql -h127.0.0.1 -P6032 -uadmin -p$ADMIN_PW \
  -e "SELECT username, default_hostgroup FROM runtime_mysql_users;"
username  default_hostgroup
root      0

Hostgroup 0 is not configured in mysql_servers. So the user's default is invalid; ProxySQL falls through to the (broken) query rules.

Root cause #5 — mysql_group_replication_hostgroups is empty

$ kubectl exec ... mysql -h127.0.0.1 -P6032 -uadmin -p$ADMIN_PW \
  -e "SELECT * FROM runtime_mysql_group_replication_hostgroups\G"
(empty)

This table is what tells ProxySQL "treat hostgroup N as writer, M as reader" and triggers GR-aware monitoring. With it empty, ProxySQL can't even attempt to follow GR primary changes, regardless of whether root causes #1–#4 are fixed.

Suggested fix

Bundled mgr-proxysql topology should ship:

1. The sys.gr_member_routing_candidate_status view + sys.gr_member_in_primary_partition() helper, applied to the primary at cluster init (e.g. via the postProvision lifecycle action of proxysql-mysql-1.0.2 ComponentDefinition or a one-shot bootstrap Job in the chart). View definitions replicate via GR DDL to the secondaries.
2. Additional grants for the proxysql MySQL user: SELECT ON sys.gr_member_routing_candidate_status, EXECUTE ON FUNCTION sys.gr_member_in_primary_partition, SELECT ON performance_schema.replication_group_members, SELECT ON performance_schema.replication_group_member_stats, SELECT ON performance_schema.global_variables.
3. Populated mysql_group_replication_hostgroups: (writer=1, backup_writer=4, reader=2, offline=3, active=1, max_writers=1, writer_is_also_reader=0, max_transactions_behind=100).
4. Corrected query rules:
   • ^SELECT.*FOR UPDATE$ → hostgroup 1 (writer)
   • ^SELECT → hostgroup 2 (reader)
   • default via mysql_users.default_hostgroup = 1, no catch-all rule needed.

Workaround

A consumer can author a one-shot Job that runs at install time and applies the above — but this is not something every consumer of the addon should have to write.

Reproduction manifest

apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: bundled-test
  namespace: kb-test
spec:
  clusterDef: mysql
  topology: mgr-proxysql
  terminationPolicy: Delete
  componentSpecs:
    - name: mysql
      serviceVersion: "8.0.39"
      replicas: 3
      resources:
        limits: { cpu: "1", memory: 1Gi }
        requests: { cpu: "200m", memory: 512Mi }
      volumeClaimTemplates:
        - name: data
          spec:
            storageClassName: csi-hostpath-sc
            accessModes: [ReadWriteOnce]
            resources: { requests: { storage: 5Gi } }
    - name: proxysql
      serviceVersion: "2.4.4"
      replicas: 2

Versions

• KubeBlocks: apecloud/kubeblocks:1.0.2
• mysql addon: mysql-1.0.2
• ComponentDefinition: mysql-mgr-8.0-1.0.2
• Storage backend in repro: csi-driver-host-path v1.17.0 on kind v1.35.0