Percona operator ProxySQL doesn't allow me to rump mysqldump, error "connection is locked to hostgroup..."

MySQL & MariaDB Percona Operator for MySQL
1

Hi!

I am using the Percona Operator for MySQL 1.10 with the following cr.yaml:

apiVersion: pxc.percona.com/v1
kind: PerconaXtraDBCluster
metadata:
  finalizers:
  - delete-pxc-pods-in-order
  - delete-proxysql-pvc
  - delete-pxc-pvc
  name: db-cluster
  namespace: nicky-test
spec:
  allowUnsafeConfigurations: true
  crVersion: 1.10.0
  haproxy:
    enabled: false
  logcollector:
    enabled: true
    image: percona/percona-xtradb-cluster-operator:1.10.0-logcollector
  proxysql:
    affinity:
      antiAffinityTopologyKey: kubernetes.io/hostname
    enabled: true
    gracePeriod: 30
    image: percona/percona-xtradb-cluster-operator:1.10.0-proxysql
    podDisruptionBudget:
      maxUnavailable: 1
    size: 3
    volumeSpec:
      persistentVolumeClaim:
        resources:
          requests:
            storage: 6G
        storageClassName: local-path
  pxc:
    affinity:
      antiAffinityTopologyKey: kubernetes.io/hostname
    autoRecovery: true
    gracePeriod: 600
    image: percona/percona-xtradb-cluster:8.0.26-16.1
    podDisruptionBudget:
      maxUnavailable: 1
    size: 3
    volumeSpec:
      persistentVolumeClaim:
        resources:
          requests:
            storage: 6G
        storageClassName: local-path
  secretsName: db-cluster-db-secrets
  upgradeOptions:
    apply: 8.0-recommended
    schedule: 0 4 * * *
    versionServiceEndpoint: https://check.percona.com

After waiting until the cluster is up, I manually loaded a database dump I created with mysqldump from some other database. This works fine.

However, if I then want to dump this database again, I am running into issues.

What I do:

  • kubectl run --rm -it --image=ubuntu test-pod
  • mysqldump --opt --skip-lock-tables --skip-add-locks -uroot -psupersecret -hdb-cluster-proxysql mydbhere

The error I am getting:

mysqldump: Error: 'ProxySQL Error: connection is locked to hostgroup 11 but trying to reach hostgroup 10' when trying to dump tablespaces
mysqldump: Couldn't execute 'SELECT engine, table_type FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = DATABASE() AND table_name = 'cake_d_c_users_phinxlog'': ProxySQL Error: connection is locked to hostgroup 11 but trying to reach hostgroup 10 (9006)

This seems to be because the set @'s at the top will lock me to hostgroup 11 (the writer hostgroup), but then mysqldump wants to use hostgroup 10 to select data from the table schema.

I tried searching the internet for solutions, but I don’t know what the best approach is. Of course I could change the query rules to send everything to the writer, but that is clearly not what we want.

I also saw something about setting mysql-set_query_lock_on_hostgroup, which could work? But how do I set this with the operator? I saw I could set configuration: in the CR, but if I try:

configuration: |
  mysql_variables=
  {
    set_query_lock_on_hostgroup=0
  }

I get errors during startup of the pod. Do I need to specify the whole file? Or should I copy the default mysql_variables from the pod and add my line? Or do I also need the datadir line like the example in the documentation? I’m a bit lost and hope anyone can help.

Thanks!

1 Like
2

Hi @nickygerritsen !

If you are starting a cluster from the beginning I think it’s best to specify all options, so for example what worked for me is:

    configuration: |
      datadir="/var/lib/proxysql"

      admin_variables =
      {
              admin_credentials="proxyadmin:admin_password"
              mysql_ifaces="0.0.0.0:6032"
              refresh_interval=2000

              cluster_username="proxyadmin"
              cluster_password="admin_password"
              checksum_admin_variables=false
              checksum_ldap_variables=false
              checksum_mysql_variables=false
              cluster_check_interval_ms=200
              cluster_check_status_frequency=100
              cluster_mysql_query_rules_save_to_disk=true
              cluster_mysql_servers_save_to_disk=true
              cluster_mysql_users_save_to_disk=true
              cluster_proxysql_servers_save_to_disk=true
              cluster_mysql_query_rules_diffs_before_sync=1
              cluster_mysql_servers_diffs_before_sync=1
              cluster_mysql_users_diffs_before_sync=1
              cluster_proxysql_servers_diffs_before_sync=1
      }

      mysql_variables=
      {
              set_query_lock_on_hostgroup=0
              monitor_password="monitory"
              monitor_galera_healthcheck_interval=1000
              threads=2
              max_connections=2048
              default_query_delay=0
              default_query_timeout=9999000
              poll_timeout=2000
              interfaces="0.0.0.0:3306;0.0.0.0:33062"
              default_schema="information_schema"
              stacksize=1048576
              connect_timeout_server=10000
              monitor_history=60000
              monitor_connect_interval=20000
              monitor_ping_interval=10000
              ping_timeout_server=200
              commands_stats=true
              sessions_sort=true
              have_ssl=false
              ssl_p2s_ca=""
              ssl_p2s_cert=""
              ssl_p2s_key=""
              ssl_p2s_cipher="ECDHE-RSA-AES128-GCM-SHA256"
      }

But beware that you need to have the same credentials in admin_credentials, cluster_username and cluster_password as you have them in your secret - otherwise you will start getting permission issues in the logs and it will not work.
One thing to which I bumped also is that I had too small value for default_query_timeout so my dump was interrupted so I had to increase it and then it worked.

If you already have the cluster running you will need to specify all current options plus the new ones and apply it that way (at least that worked for me) or the alternative it to connect to proxysql admin interface and execute something like:

set mysql-set_query_lock_on_hostgroup=0;
load mysql variables to runtime;
save mysql variables to disk;
1 Like