I’m facing an issue with ProxySQL in Percona as I have some scripts which SET some user defined variables which disables multiplexing and locks connection to the writer hostgroup and later during the DQL statements I get:
Error 9006: connection is locked to hostgroup 11 but trying to reach hostgroup 10
I’ve read Override query hostgroup and similar discussions and found the following 3 solutions
1)Wrap the entire script in a transaction since ProxySQL doesn’t break transactions. (Not ideal since we have multiple scripts and potentially more in future and it seems prone to break in future if someone misses this)
2)Enable Multiplexing for SET commands. (Doesn’t work since we use the session variables later and it throws NULL error)
3)Add a rule to send all queries to the same hostgroup. (This defeats the purpose of using ProxySQL since we lose read scalability)
I had a couple of questions here:
1)Is there a simpler way to solve this issue?
2)For modifying mysql_query rules we tried adding a custom one times k8s job which runs once percona setup is complete, I verified that proxy-admin script run by pxc-monit doesnot overwrite the rules since it only adds rules for new users, but I wanted to ask if this is the correct way to do it or if this method has any downsides?
Percona operator 1.17.0 and PXC version 8.0.32
Thanks.