Receiving delayed data when doing a SELECT over a table which has received an INSERT in MariaDB

Question

I am using MariaDB in a PHP application. The problem is the following: using Doctrine DBAL with the MySQL adaptor I do an insert from one page and then redirect to another one, in which a SELECT is done. Both are very basic queries.

The problem is that the SELECT does not reflect the actual data, but older one. I am hosting this application on a shared hosting, so please consider that I won't have all DB configuration options/permissions available.

I have tried to flush after the first INSERT, but it does not work either, and it still shows outdated data. I believed that the Query Caches are invalidated if the data changes, and that they do not apply because, in fact, it is a different query.

I do not use transactions either, so the commit is supposedly done after the insert. Any idea on how to get the most recent data possible?

Answer 1

It sounds like you are doing Replication and the proxy for directing queries is oblivious to "Critical Reads".

In a replication setup (MariaDB or MySQL), there is one Master server and one Slave (at least). INSERTs (and other writes) must occur on the Master. Then they are replicated to the Slave(s). SELECTs, on the other hand, may be done on either server, but, for sharing the load, it is better to do them on the Slave(s).

Replication is "asynchronous". That is, the write is eventually sent to the Slave and performed there. Normally, the delay is sub-second. But, for a number of reasons, the delay could be arbitrarily large. One should not depend on how quickly writes are replicated.

But... There is a thing called a "Critical Read". This is when the SELECT needs to "see" the thing that was just written.

You have a "critical read".

I don't know what is deciding to direct your SELECT to a Slave.

If you are using the Galera clustering option of MariaDB, then you can protect yourself from the critical read problem by changing your select to

SET SESSION wsrep_sync_wait = 1;
SELECT ... (as before)
SET SESSION wsrep_sync_wait = 0;

However; the SETs must go to the same 'node' as the SELECT. Without knowing what kind of proxying is going on, I cannot be more specific.

I hope you are not reconnecting before each statement. That would be really bad.

More on Galera issues for developers

If you are using replication and Doctrine DBAL has nothing for critical reads, complain to them!