Today is my last day in Asia (China and Taiwan), and between the sessions I had the time to code a small proof-of-concept for people that are asking how they could read their writes on all nodes and ensure the read consistency with their last write ?
The usual (and easier) answer to this particular question is to read on the same host you just wrote. But currently MySQL provides you all the elements to force a consistent read across all the nodes of a group.
Since MySQL 5.7.5, we introduced session_track_gtidsand in 5.7.6 we also introduced GTIDs context to the OK packet (session tracker) (WL#6128 and WL#6972). So we can use this implementation in correlation with WAIT_FOR_EXECUTED_GTID_SET to force a consistent read.
Currently this solution must be implemented in your application but a tool like ProxySQL might be able to implement this too.
I wrote a small C program using Connector/C to illustrate this: gr_ryow.c
Let’s see this small C program in action:
Of course as we are aware of this request and we might come one day with an easier solution implemented only in the server or the router, but for the moment this already an option for those willing to read their own writes across all members.
[…] I wrote a small C program using Connector/C to illustrate this: gr_ryow.c […]
[…] execute a read on any slave where that GTID is already executed. Lefred described the process in a blog post with an example: the server needs to have session_track_gtids enabled, and the client can execute […]
[…] during reads have been a small concern from the adopters of MySQL InnoDB Cluster (see this post and this […]