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.


Rick James and danblack both give the right answer.

The easy way:

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

This is the method from the official: Achieving Read-After-Write Semantics With Galera.

But, **It is highly recommend that not to read after write. **In this situation, you should check your business logic if it is really have to do this way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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