Web Applications do not receive all data in Galera Cluster
I have a real strange issue.
I have a web application that appears to not show the correct records.
I am using a load balancer for a web farm for a billing system. I am also using a load balancer for the Galera cluster.
When a trouble ticket is generated, sometimes that ticket is not available on all the web servers, but when I look at each individual MariaDB server, I see the record.
Data commits are synced and all looks good from the cluster side of things. I can perform a mysqldump to another database and point the billing system to that database and all is good.
If I reboot each MariaDB server one by one, things start working again. It almost seems like there is some caching where the database cluster at times only shows records that were generated by that web server.
I did check that all the web servers in the farm are using the same load balanced ip address for the MariaDB farm and they are.
I have replicated this on two different web applications (A CRM & Billing system) both on different web farms.
Answer Answered by Alex yurchenko in this comment.
Hi, This is a result of Galera synchrony being "virtual". If your application is picky about "stale" data and you want to randomly load-balance connections, you should set wsrep_causal_reads=ON. This will ensure that your application always gets what it has written - at some performance cost. Alternatively you may opt to reread the data if you can determine that it is stale, or use some sort of "sticky" balancing, routing related requests to the same node.