Proxysql setup for Percona PXC Multimaster cluster

Question

I have a clean Ubuntu lab setup for PXC Cluster and Proxysql (each a separate VM)and i'm seeing this issue (error below). Is there a specific installation that needs to be followed for PXC cluster? I was using a few different guides (all are slightly different, but so far I reinstalled proxy 3-4 times and never managed to get it past this error message).

https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration

https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/proxysql.html

https://www.percona.com/doc/percona-xtradb-cluster/LATEST/howtos/virt_sandbox.html

• OS Ubuntu 20.04.1 LTS Release: 20.04 Codename: focal

  ProxySQL version 2.0.15-percona-1.1, codename Truls (i tried proxy 2.0.14 as well)

  mysql Ver 14.14 Distrib 5.7.32-35, for debian-linux-gnu (x86_64) using 8.0

Cluster is set up to be Multimaster - i need all 3 nodes as writers.

Bug 1994 is closed and i opened a new one for it (https://github.com/sysown/proxysql/issues/3284) The logs i have are the same as the logs that were already submitted for bug #1994 - this bug is linked to several other bugs and i read through most of them - i didn't find a workaround. This was supposed to be fixed in the latest release (proxysql_2.0.14) - i tried version before, this one and the one that came after with the same result.

2021-02-03 13:54:09 MySQL_Monitor.cpp:1319:monitor_group_replication_thread(): [ERROR] mysql_fetch_fields returns NULL, or mysql_num_fields is incorrect. Server 192.168.100.63:3306 . See bug #1994 
2021-02-03 13:54:09 MySQL_Monitor.cpp:1319:monitor_group_replication_thread(): [ERROR] mysql_fetch_fields returns NULL, or mysql_num_fields is incorrect. Server 192.168.100.64:3306 . See bug #1994 
2021-02-03 13:54:11 MySQL_Monitor.cpp:1319:monitor_group_replication_thread(): [ERROR] mysql_fetch_fields returns NULL, or mysql_num_fields is incorrect. Server 192.168.100.62:3306 . See bug #1994 

2021-02-03 16:35:18 [INFO] Creating new server in HG 3 : 192.168.100.64:3306 , gtid_port=0, weight=1, status=0 
2021-02-03 16:35:18 [INFO] Creating new server in HG 3 : 192.168.100.62:3306 , gtid_port=0, weight=1, status=0 
2021-02-03 16:35:18 [INFO] New mysql_group_replication_hostgroups table 
2021-02-03 16:35:18 [INFO] Loading MySQL Group Replication info for (1,2,3,5,on,3,1,100,"(null)") 2021-02-03 16:35:18 [INFO] New mysql_galera_hostgroups table 
2021-02-03 16:35:18 [INFO] New mysql_aws_aurora_hostgroups table 
2021-02-03 16:35:18 [INFO] MySQL_HostGroups_Manager::commit() locked for 10ms Standard Query Processor rev. 2.0.6.0805 -- Query_Processor.cpp -- Fri Nov 13 11:13:22 2020 In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Fri Nov 13 11:13:22 2020 Standard MySQL Monitor (StdMyMon) rev. 2.0.1226 -- MySQL_Monitor.cpp -- Fri Nov 13 11:13:22 
2020 2021-02-03 16:35:18 MySQL_Monitor.cpp:1319:monitor_group_replication_thread(): [ERROR] mysql_fetch_fields returns NULL, or mysql_num_fields is incorrect. Server 192.168.100.62:3306 . See bug #1994 
2021-02-03 16:35:18 MySQL_Monitor.cpp:1473:monitor_group_replication_thread(): [ERROR] Got error. mmsd 0x7fdee72bd000 , MYSQL 0x7fdee9209000 , FD 26 : Unknown error 
2021-02-03 16:35:18 MySQL_Monitor.cpp:1319:monitor_group_replication_thread(): [ERROR] mysql_fetch_fields returns NULL, or mysql_num_fields is incorrect. Server 192.168.100.63:3306 . See bug #1994
2021-02-03 16:35:18 MySQL_Monitor.cpp:1473:monitor_group_replication_thread(): [ERROR] Got error. mmsd 0x7fdee72bd0c0 , MYSQL 0x7fdee9209000 , FD 26 : Unknown error
2021-02-03 16:35:18 MySQL_Monitor.cpp:1319:monitor_group_replication_thread(): [ERROR] mysql_fetch_fields returns NULL, or mysql_num_fields is incorrect. Server 192.168.100.64:3306 . See bug #1994 
2021-02-03 16:35:18 MySQL_Monitor.cpp:1473:monitor_group_replication_thread(): [ERROR] Got error. mmsd 0x7fdee72bd180 , MYSQL 0x7fdee72c9500 , FD 29 : Unknown error 
2021-02-03 16:35:18 [INFO] Latest ProxySQL version available: 2.1.0-544-g17a4b4a7

Proxysql is able to see all the nodes, but the monitoring is not accurate. When i reboot nodes in cluster (restart service, reboot VM) it is having difficulties recognizing the node as online - it just stays Shunned or Offline.

At this point i'm thinking about alternatives for Proxysql - we need the query filtering that it provides, but if i'm running into a bug so early on, i'm afraid to think what would happen if we had this in production. The odds are that i'm not doing something right regarding the setup - but i'm not finding what's missing.

Does anyone have a guide that could help with the setup, or did anyone encounter a similar issue and find a fix/workaround? And finally - what would be a recommended alternative for Proxysql?

Answer 1

The first thing that jumps out to me is that the error message is talking about the mysql_group_replication_hostgroups table. However, you mentioned that you are using a PXC Galera cluster. Therefore, you will need to add your hosts to the mysql_galera_hostgroups table. This tells ProxySQL to use it's Galera monitoring checks, which are different to async / group replication checks. This is explained in the ProxySQL documentation

Unlike regular async replication, Galera hostgroup are NOT defined in mysql_replication_hostgroups table. Instead there is a separate table that is specifically designed for Galera hostgroups. The reason for this is that more advanced topology support is needed in order to accommodate the deployment options available for Galera (e.g. controlled number of writers, cluster level replication thresholds etc.)

This ProxySQL blogpost goes through the different steps to configure a Galera cluster in ProxySQL.