This could possibly be an improvement to the replication lag feature currently in use. It would be simpler and would not require MaxScale to create its own tables.

Can also be used to track old master status after a crash in case of slave promotion. For a failback or the old master reintroduction as slave in the cluster it is possible if the max gtid on old master is found on the new master before promotion. If not found on new master old master is mark desync in maxscale , and can be re provisioned using 10.2 new feature load data from master . https://mariadb.atlassian.net/browse/MDEV-7502

maybe master_gtid_wait function can be implemented in hint, because add a query before every user query will be expensive, it will introduce a round trip latency;

Enabling MULTI_STATEMENTS and MULTI_RESULTS for the backend connections would allow this query:

INSERT INTO my_table VALUES (1);

To be converted into this:

INSERT INTO my_table VALUES (1); SELECT @@gtid_binlog_pos;

This is how the GTID could be retrieved for later use.

The SELECTS can then use this information to read from a consistent point. Here's an example:

SELECT * FROM my_table;

To be converted into this:

SELECT MASTER_GTID_WAIT('0-1-1234'); SELECT * FROM my_table;

The only problem with this is that we would need to discard the extra results returned by each query. This would also add extra overhead to the implementation but not a large one.

Ideally, we would get the latest GTID in the OK packet that the server sends. With this, we would only need to modify SELECT statements (or even use some server-native mechanism to tell which GTID we want to use).

In reality, we don't even need to have MULTI_STATEMENTS and MULTI_RESULTS enabled to allow multiple commands to be sent before any responses are received. This is due to the fact that the MariaDB server allows new queries to be sent even if previous ones have not yet been processed.

Optimistic fast prototype?

SELECT * FROM (select * from city ) as C WHERE MASTER_GTID_WAIT('0-1-1234',1)=0 

if empty query the master

It is not work if table `city` is empty

That's why it's optimistic, a long running empty query is something that can most of the time be fixed by proper indexing and for other cases should not stay in DBMS like a search engine ! so so 2 round trip for empty result can be fine for a fast prototype!

Maybe use MULTI_STATEMENTS is a good idea, but still have another problem:

SELECT MASTER_GTID_WAIT('0-1-1234', 1); SELECT * FROM my_table;

No matter what the result of MASTER_GTID_WAIT is; the statements behind will be executed anyway; so if timeout do happened, we need discard all the results, it is so complicate and expensive.

There is a tricky way to solve this problem:

SELECT CASE WHEN MASTER_GTID_WAIT('0-1-1234', 1) = 0 THEN 1 ELSE (SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.CHARACTER_SETS) END; SELECT * FROM my_table;

When timeout, it will return error "ERROR 1242 (21000): Subquery returns more than 1 row", then we can redirect the query to master;

Implemented by the pull request by dapeng. Thank you for contributing!