I am facing a issue with 3 node galera cluster where the read doesn't return any records even when the write has been commited. Below are the steps which can happen from multiple servers.

1>Server A writes a record at 12:00:00 AM

2>Server B reads the same record after 10 seconds at 12:00:10 AM but doesn't get any record.

The bin log on all 3 server confirms the commit at 12:00:00 AM.

What could be the issue here ?

  • commit binlog at 12:00:05 AM. – Akina Sep 14 '18 at 11:43
  • didn't get you. the commit statement was written in bin log at 12:00:00. – Ajay Sep 14 '18 at 12:48
  • That says that all the other nodes can comment when they get around to it. – Rick James Oct 6 '18 at 20:39
  • What version of MySQL/MariaDB/PXC are you running? – Rick James Oct 6 '18 at 20:40

If you are reading from a node other than then node where the transaction was commit, there is a finite response time on the other nodes from the time it is certified, to the time it is applied on the other node.

In theory you should be able to wait to ensure its committed.

  • in practice if you load balance between db servers, try to make it sticky based on the user connection somehow. – danblack Sep 17 '18 at 6:54

This handles the "critical read" problem you describe:

SET SESSION wsrep_sync_wait = 1;
SET SESSION wsrep_sync_wait = 0;

More discussion.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.