Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.1Mb
PDF (A4) - 35.2Mb
Man Pages (TGZ) - 256.4Kb
Man Pages (Zip) - 361.2Kb
Info (Gzip) - 3.4Mb
Info (Zip) - 3.4Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  / Functions and Operators  /  Functions Used with Global Transaction Identifiers (GTIDs)

12.18 Functions Used with Global Transaction Identifiers (GTIDs)

The functions described in this section are used with GTID-based replication. It is important to keep in mind that all of these functions take string representations of GTID sets as arguments. As such, the GTID sets must always be quoted when used with them. SeeGTID Sets for more information.

The union of two GTID sets is simply their representations as strings, joined together with an interposed comma. In other words, you can define a very simple function for obtaining the union of two GTID sets, similar to that created here:

CREATE FUNCTION GTID_UNION(g1 TEXT, g2 TEXT)    RETURNS TEXT DETERMINISTIC    RETURN CONCAT(g1,',',g2);

For more information about GTIDs and how these GTID functions are used in practice, seeSection 16.1.3, “Replication with Global Transaction Identifiers”.

Table 12.24 GTID Functions

NameDescription
GTID_SUBSET() Return true if all GTIDs in subset are also in set; otherwise false.
GTID_SUBTRACT() Return all GTIDs in set that are not in subset.
WAIT_FOR_EXECUTED_GTID_SET() Wait until the given GTIDs have executed on the replica.
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() UseWAIT_FOR_EXECUTED_GTID_SET().

  • GTID_SUBSET(set1,set2)

    Given two sets of global transaction identifiersset1 andset2, returns true if all GTIDs inset1 are also inset2. Returns false otherwise.

    The GTID sets used with this function are represented as strings, as shown in the following examples:

    mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23',    ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G*************************** 1. row ***************************GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23',    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 11 row in set (0.00 sec)mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23-25',    ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G*************************** 1. row ***************************GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:23-25',    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 11 row in set (0.00 sec)mysql> SELECT GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25',    ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G*************************** 1. row ***************************GTID_SUBSET('3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25',    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 01 row in set (0.00 sec)

  • GTID_SUBTRACT(set1,set2)

    Given two sets of global transaction identifiersset1 andset2, returns only those GTIDs fromset1 that are not inset2.

    All GTID sets used with this function are represented as strings and must be quoted, as shown in these examples:

    mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',    ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21')\G*************************** 1. row ***************************GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:22-571 row in set (0.00 sec)mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',    ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25')\G*************************** 1. row ***************************GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',    '3E11FA47-71CA-11E1-9E33-C80AA9429562:20-25'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:26-571 row in set (0.00 sec)mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',    ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:23-24')\G*************************** 1. row ***************************GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',    '3E11FA47-71CA-11E1-9E33-C80AA9429562:23-24'): 3e11fa47-71ca-11e1-9e33-c80aa9429562:21-22:25-571 row in set (0.01 sec)

    Subtracting a GTID set from itself produces an empty set, as shown here:

    mysql> SELECT GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',    ->     '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57')\G*************************** 1. row ***************************GTID_SUBTRACT('3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57',    '3E11FA47-71CA-11E1-9E33-C80AA9429562:21-57'): 1 row in set (0.00 sec)

  • WAIT_FOR_EXECUTED_GTID_SET(gtid_set[,timeout])

    Wait until the server has applied all of the transactions whose global transaction identifiers are contained ingtid_set; that is, until the condition GTID_SUBSET(gtid_subset,@@GLOBAL.gtid_executed) holds. SeeSection 16.1.3.1, “GTID Format and Storage” for a definition of GTID sets.

    If a timeout is specified, andtimeout seconds elapse before all of the transactions in the GTID set have been applied, the function stops waiting.timeout is optional, and the default timeout is 0 seconds, in which case the function always waits until all of the transactions in the GTID set have been applied.timeout must be greater than or equal to 0; as of MySQL 5.7.18, when running in strict SQL mode, a negativetimeout value is immediately rejected with an error; otherwise the function returnsNULL, and raises a warning.

    WAIT_FOR_EXECUTED_GTID_SET() monitors all the GTIDs that are applied on the server, including transactions that arrive from all replication channels and user clients. It does not take into account whether replication channels have been started or stopped.

    For more information, seeSection 16.1.3, “Replication with Global Transaction Identifiers”.

    GTID sets used with this function are represented as strings and so must be quoted as shown in the following example:

    mysql> SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5');        -> 0

    For a syntax description for GTID sets, seeSection 16.1.3.1, “GTID Format and Storage”.

    ForWAIT_FOR_EXECUTED_GTID_SET(), the return value is the state of the query, where 0 represents success, and 1 represents timeout. Any other failures generate an error.

    gtid_mode cannot be changed to OFF while any client is using this function to wait for GTIDs to be applied.

  • WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(gtid_set[,timeout][,channel])

    WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() is similar toWAIT_FOR_EXECUTED_GTID_SET() in that it waits until all of the transactions whose global transaction identifiers are contained ingtid_set have been applied, or untiltimeout seconds have elapsed, whichever occurs first. However,WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() applies to a specific replication channel, and stops only after the transactions have been applied on the specified channel, for which the applier must be running. In contrast,WAIT_FOR_EXECUTED_GTID_SET() stops after the transactions have been applied, regardless of where they were applied (on any replication channel or any user client), and whether or not any replication channels are running.

    timeout must be greater than or equal to 0; as of MySQL 5.7.18, when running in strict SQL mode, a negativetimeout value is immediately rejected with an error (ER_WRONG_ARGUMENTS); otherwiseWAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() returnsNULL, and raises a warning.

    Thechannel option names which replication channel the function applies to. If no channel is named and no channels other than the default replication channel exist, the function applies to the default replication channel. If multiple replication channels exist, you must specify a channel as otherwise it is not known which replication channel the function applies to. SeeSection 16.2.2, “Replication Channels” for more information on replication channels.

    Note

    BecauseWAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() applies to a specific replication channel, if an expected transaction arrives on a different replication channel or from a user client, for example in a failover or manual recovery situation, the function can hang indefinitely if no timeout is set. UseWAIT_FOR_EXECUTED_GTID_SET() instead to ensure correct handling of transactions in these situations.

    GTID sets used withWAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() are represented as strings and must be quoted in the same way as forWAIT_FOR_EXECUTED_GTID_SET(). ForWAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(), the return value for the function is an arbitrary positive number. If GTID-based replication is not active (that is, if the value of thegtid_mode variable is OFF), then this value is undefined andWAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() returns NULL. If the replica is not running then the function also returns NULL.

    gtid_mode cannot be changed to OFF while any client is using this function to wait for GTIDs to be applied.