'Issue of small delay after insert / update using proxySQL and MySQl clusters

We are using MySQL clusters with proxySQL with the following set up:

  • Server 1 is the main server hosting the main dB, located in Europe
  • Server 2 is a server in Asia used to serve local users, this server contains a read only version of the DB that is updated by server 1 when changes are made.

Here is the issue: A user on server 2 triggers an SQL UPDATE, UPDATE customers SET name= 'New Name' WHERE id = 123. (this update is sent to server 1 which then update server 2)

In the same code block if we select the record (SELECT name FROM customers WHERE id =123), this query is sent to the local server and the result will be "Old Name" unless we pause the code for 1 second (to test).

One solution we thought about is to change the connection (PHP) and go directly to server 1 for the remainer of the code block but this defeats the purpose a little and is far from ideal (quiet large code base to update).

Are there solutions involving proxySQL maybe?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source