We are using MySQL Cluster with proxySQL and have the following setup:
The problem is this: User on server 2 triggers SQL UPDATE, UPDATE client SET name= 'New Name' WHERE id = 123. (This update is sent to server 1, then server 2 updates)
In the same code block, if we select the record (SELECT name FROM customers WHERE id =123), the query will be sent to the local server and the result will be "old name" unless we pause the code for 1 second (for testing) .
One solution we thought of was to change the connection (PHP) and go directly to server 1 to get the rest of the code block, but that kind of defeats the purpose and is far from ideal (quietly large code base needs updating).
Is there a solution involving proxySQL?
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
To prevent queries from being routed to slave servers with high replication latency, you can set a threshold maximum replication latency in the table mysql_server column Maximum replication delay. Reference: max_replication_lag
max_replication_lag – If greater than 0, ProxySQL will Regularly monitor replication latency and whether it exceeds configured limits threshold, it will temporarily avoid the host until replication catches up.
You can also create query rules to split query reading and writing. ref: ProxySQL split read/write
Important: proxysql-gtid-causal-reads If you are using Mysql version 5.7.5 or higher, this may mainly solve your problem.