With the introduction of Global Transaction Identifiers(GTID) in MySQL from mysql-5.6 GA a whole lot of different functionality have been developed around it so that GTID could be used in a much simpler and efficient way.

One of the interesting functionality with GTID in use is the function to sync a slave with its
master server using the WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS. This is used to do a timed or an indefinite wait till the servers in contention are in sync with respect to the GTID executed set.

This function is used to address specific use cases in which transactions are applied using SQL/worker thread. In order to give more flexibility to the user a new function of syncing the servers with GTID has been introduced in mysql-5.7.5 called WAIT_FOR_EXECUTED_GTID_SET.

Limitations of WAIT_UNTIL_SQL_THREAD_AFTER_GITDS

The already existing WAIT_UNTIL_SQL_THREAD_AFTER_GITDS functions had a very specific use case.

This function waits for GTIDs that are applied by the SQL thread. If the slave thread is not
running (or if is being stopped while the function is executed), then the function returns an
error.The user may be interested to wait for transactions that are applied from various
sources, e.g., user sessions and SQL threads. The existing function would not allow that and we are now extending this functionality to make that possible.

New WAIT_FOR_EXECUTED_GTID_SET option for MySQL

The new function implemented is more generic in its behavior and provides greater flexibility to the user. WAIT_FOR_EXECUTED_GTID_SET(GTID_SET [, TIMEOUT])

Wait until all transactions whose global transaction identifiers are included in GTID_SET have committed. ie. After this function returns with success, it must hold that

GTID_SUBSET(GTID_SET, @@global.gtid_executed) returns true.

The TIMEOUT parameter is used for the maximum duration for which we have to wait for the GTIDS to be synced. If it is not able to get to that GTID value in this duration we return timeout error. (current unit for timeout is second and it uses integer values for timeout)

This function is independent of the slave threads and has logical return values of :

0 – success (i.e. the GTID got in sync within the timeout(if specified))
1 – timeout (a timeout occurred)

For all other cases we will throw corresponding error messages eg :-

ER_MALFORMED_GTID_SET_SPECIFICATION
ER_QUERY_INTERRUPTED

USE CASE

Given below is a use case which is easy to be handled using this new wait function.

One of the possible use case where this new function can be used is when the user does not want to execute a transaction on the slave.

• Master executes transactions with GTID as UUID:1 and UUID:2 and the UUID:2 is bad for the slave and the user wants to prevent that from getting executed on the slave.
• There is an application waiting for UUID:1 and UUID:2 on the slave (WAIT_FOR_EXECUTED_GTID_SET (UUID:1-2))
• The slave has already committed UUID:1 and has still not processed the transaction with GTID UUID:2.
• User can go to the slave and commit and empty transaction with GTID as UUID:2 which unblocks the application.

USING THE NEW FUNCTION

Case I – The GTID was found and the sync function returns success (no timeout was specified)

Case II – The GTID was not found and the sync function returns failure (here timeout was specified)

If a timeout is not specified the function will wait indefinitely till the specified GTID set becomes a subset of gtid_executed.

CONCLUSION

The existing function serves a specific use case (wait for the sql thread to apply a set of GTIDs) and we are now extending the GTID functionality to cover more use cases like applying through ” user session “.

Use this new function and if  any questions or feedback regarding this project, please post them here.