The idea was to split read and writes to a master-master cluster that has backend load balancer (ProxySQL), also there is load balancer for frontend (HAProxy).
Frontend part work flawless. Backend part is the one messing things up.
When I try to login into wordpress that as DB_HOST has proxysql, I sometimes get into it sometimes I dont, sometimes I can do stuff in Dashboard sometimes it disconnect me.
I narrow the issue to ProxySQL sending my connection to one DB and after it send other connection to second DB, as my master-master DB replication is async I fully understand that the issue is that the second DB is missing some ?session? data - but the benefits from master-master exclude changing it to sync replication.
The workaround that I found was to set each frontend server to have one direct DB connection, but that's introducing SPOF.
Is there a way to force ProxySQL to send queries that orginated from same source to same DB, or will I have to switch to HAProxy for SQL load balancing ?