proxysql 2.5.0+ can't work with mysql 8.x backend servers #43
Description
Used the proxysql plugin to configure proxysql 2.5.x with a mysql 8.x innodb cluster, but proxysql failed to connect to the backend servers:
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 2 after 10000ms
Checked /var/lib/proxysql/proxysql.log and found the following message:
2023-07-22 18:34:14 MySQL_Monitor.cpp:3745:gr_report_fetching_errs(): [ERROR] Got error. mmsd 0x7f661d860000 , MYSQL 0x7f661d8d0000 , FD 40 : SELECT command denied to user 'monitor'@'mysql1' for table 'replication_group_members'
After granting the access right on performance_schema.* to the monitor user, this issue was fixed.
This issue seems related to the code change since 2.5.0 that added queries to the performance_schema.replication_group_members table, see https://github.com/sysown/proxysql/blob/v2.5.0/lib/MySQL_Monitor.cpp#L498