[BUG] mysql cluster topology server-with-orc all pod are primary

[JashBook]

Describe the bug

kbcli version  
Kubernetes: v1.28.3-vke.17
KubeBlocks: 1.0.0-beta.21
kbcli: 1.0.0-beta.9

To Reproduce
Steps to reproduce the behavior:

1. create orc cluster

apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: orcm-etklcj
  namespace: default
  annotations:
    "kubeblocks.io/extra-env": '{"ORC_TOPOLOGY_PASSWORD":"orchestrator","ORC_META_PASSWORD":"orchestrator","ORC_META_DATABASE":"orchestrator"}'
spec:
  clusterDef: orchestrator
  topology: raft
  terminationPolicy: WipeOut
  services:
    - name: orchestrator
      componentSelector: orchestrator
      spec:
        ports:
          - name: orc-http
            port: 80
  componentSpecs:
    - name: orchestrator
      serviceVersion: 3.2.6
      env:
        - name: ORC_TOPOLOGY_PASSWORD
          value: orchestrator
        - name: ORC_META_PASSWORD
          value: orchestrator
        - name: ORC_META_DATABASE
          value: orchestrator
      replicas: 3
      resources:
        requests:
          cpu: 500m
          memory: 0.5Gi
        limits:
          cpu: 500m
          memory: 0.5Gi
      volumeClaimTemplates:
        - name: data
          spec:
            accessModes:
              - ReadWriteOnce
            resources:
              requests:
                storage: 20Gi

3. create mysql cluster

apiVersion: apps.kubeblocks.io/v1
kind: Cluster
metadata:
  name: asmysql-etklcj
  namespace: default
  labels: 
    app.kubernetes.io/instance: asmysql-etklcj
  annotations:
spec:
  clusterDef: mysql
  topology: server-with-orc
  terminationPolicy: DoNotTerminate
  componentSpecs:
    - name: mysql
      serviceVersion: 8.0.39
      disableExporter: true
      replicas: 3      
      resources:
        limits:
          cpu: 500m
          memory: 0.5Gi
        requests:
          cpu: 500m
          memory: 0.5Gi      
      volumeClaimTemplates:
        - name: data
          spec:
            accessModes:
              - ReadWriteOnce
            resources:
              requests:
                storage: 20Gi
      serviceRefs:
      - name: orchestrator
        namespace: default
        clusterServiceSelector:
            cluster: orcm-etklcj
            service:
              component: orchestrator
              service: orchestrator
              port: orc-http

3. see error

➜  ~ kbcli cluster list-instances orcm-etklcj   
NAME                         NAMESPACE   CLUSTER       COMPONENT      STATUS    ROLE        ACCESSMODE   AZ              CPU(REQUEST/LIMIT)   MEMORY(REQUEST/LIMIT)   STORAGE     NODE                      CREATED-TIME                 
orcm-etklcj-orchestrator-0   default     orcm-etklcj   orchestrator   Running   secondary   Readonly     cn-shanghai-a   500m / 500m          512Mi / 512Mi           data:20Gi   172.16.0.36/172.16.0.36   Jan 18,2025 17:58 UTC+0800   
orcm-etklcj-orchestrator-1   default     orcm-etklcj   orchestrator   Running   secondary   Readonly     cn-shanghai-a   500m / 500m          512Mi / 512Mi           data:20Gi   172.16.0.17/172.16.0.17   Jan 18,2025 17:58 UTC+0800   
orcm-etklcj-orchestrator-2   default     orcm-etklcj   orchestrator   Running   primary     ReadWrite    cn-shanghai-a   500m / 500m          512Mi / 512Mi           data:20Gi   172.16.0.5/172.16.0.5     Jan 18,2025 17:58 UTC+0800   
➜  ~ kbcli cluster list-instances asmysql-etklcj
NAME                     NAMESPACE   CLUSTER          COMPONENT   STATUS    ROLE        ACCESSMODE   AZ              CPU(REQUEST/LIMIT)   MEMORY(REQUEST/LIMIT)   STORAGE     NODE                      CREATED-TIME                 
asmysql-etklcj-mysql-0   default     asmysql-etklcj   mysql       Running   primary     ReadWrite    cn-shanghai-a   500m / 500m          512Mi / 512Mi           data:20Gi   172.16.0.17/172.16.0.17   Jan 18,2025 18:01 UTC+0800   
asmysql-etklcj-mysql-1   default     asmysql-etklcj   mysql       Running   secondary   Readonly     cn-shanghai-a   500m / 500m          512Mi / 512Mi           data:20Gi   172.16.0.17/172.16.0.17   Jan 18,2025 18:01 UTC+0800  

 kubectl exec -it asmysql-etklcj-mysql-0 bash 
kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl exec [POD] -- [COMMAND] instead.
Defaulted container "mysql" out of: mysql, kbagent, config-manager, init-data (init), init-jq (init), init-kbagent (init), kbagent-worker (init)
bash-5.1# mysql -h127.0.0.1 -uroot -P3306 -p'ZEo2c656u3'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database db0;
Query OK, 1 row affected (0.00 sec)

mysql> use db0
Database changed
mysql> CREATE TABLE IF NOT EXISTS table_test (id SERIAL PRIMARY KEY , value text);
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+---------------+
| Tables_in_db0 |
+---------------+
| table_test    |
+---------------+
1 row in set (0.00 sec)

mysql> show status like 'Rpl%_status';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_replica_status | OFF   |
| Rpl_semi_sync_source_status  | OFF   |
+------------------------------+-------+
2 rows in set (0.01 sec)

mysql> show master status\G;
*************************** 1. row ***************************
             File: asmysql-etklcj-mysql-0-bin.000001
         Position: 4799
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 591bf64d-d583-11ef-967f-00163e6e9221:1-16
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show slave status\G;
Empty set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> 

kubectl exec -it asmysql-etklcj-mysql-1 bash 
kubectl exec [POD] [COMMAND] is DEPRECATED and will be removed in a future version. Use kubectl exec [POD] -- [COMMAND] instead.
Defaulted container "mysql" out of: mysql, kbagent, config-manager, init-data (init), init-jq (init), init-kbagent (init), kbagent-worker (init)
bash-5.1# mysql -h127.0.0.1 -uroot -P3306 -p'ZEo2c656u3'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.39 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database db1;
Query OK, 1 row affected (0.00 sec)

mysql> use db1
Database changed
mysql> CREATE TABLE IF NOT EXISTS table_test (id SERIAL PRIMARY KEY , value text);
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| table_test    |
+---------------+
1 row in set (0.00 sec)

mysql> show status like 'Rpl%_status';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Rpl_semi_sync_replica_status | OFF   |
| Rpl_semi_sync_source_status  | OFF   |
+------------------------------+-------+
2 rows in set (0.00 sec)

mysql> show master status\G;
*************************** 1. row ***************************
             File: asmysql-etklcj-mysql-1-bin.000001
         Position: 5929
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 591bbc6e-d583-11ef-965d-00163e6e9221:1-5,
591bf64d-d583-11ef-967f-00163e6e9221:1-16
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: asmysql-etklcj-mysql-mysql-0
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 1
              Master_Log_File: asmysql-etklcj-mysql-0-bin.000001
          Read_Master_Log_Pos: 4799
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 5049
        Relay_Master_Log_File: asmysql-etklcj-mysql-0-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4799
              Relay_Log_Space: 5253
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 591bf64d-d583-11ef-967f-00163e6e9221
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 591bf64d-d583-11ef-967f-00163e6e9221:1-16
            Executed_Gtid_Set: 591bbc6e-d583-11ef-965d-00163e6e9221:1-5,
591bf64d-d583-11ef-967f-00163e6e9221:1-16
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 1
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> 

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [e.g. iOS]
• Browser [e.g. chrome, safari]
• Version [e.g. 22]

Additional context
Add any other context about the problem here.

[shanshanying]

for mysql, topology has been renamted to orc,orc-proxysql,semisync,semisync-proxysql. I will check the PR and release.

@xuriwuyun pls pick apecloud/kubeblocks-addons#1359.

[xuriwuyun]

got it. By default, Orchestrator does not use semi-sync replication, and it does not set read_only on replica servers. we can configure the semi-sync plugin in the MySQL configuration file to enable semi-sync replication by default.

[kubeJocker]

No Double-Primary Issue, but Errant GTID Caused by CREATE DATABASE on Slave
Summary:

1. Initially enabled the Read-Only feature on the slave.
2. Semi-synchronous replication is supported as needed.