Documentation Home
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 35.3Mb
PDF (A4) - 35.3Mb
PDF (RPM) - 34.3Mb
EPUB - 8.6Mb
HTML Download (TGZ) - 8.4Mb
HTML Download (Zip) - 8.4Mb
HTML Download (RPM) - 7.2Mb
Eclipse Doc Plugin (TGZ) - 9.2Mb
Eclipse Doc Plugin (Zip) - 11.4Mb
Man Pages (TGZ) - 200.3Kb
Man Pages (Zip) - 305.6Kb
Info (Gzip) - 3.3Mb
Info (Zip) - 3.3Mb
Excerpts from this Manual

MySQL 5.7 Reference Manual  /  Functions and Operators  /  Functions Used with Global Transaction IDs

13.17 Functions Used with Global Transaction IDs

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. See GTID 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, see Section 18.1.3, “Replication with Global Transaction Identifiers”.

Table 13.22 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 slave.
WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() Wait until the given GTIDs have executed on slave.

  • GTID_SUBSET(subset,set)

    Given two sets of global transaction IDs subset and set, returns true if all GTIDs in subset are also in set. 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'): 1
    1 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'): 1
    1 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'): 0
    1 row in set (0.00 sec)
    
  • GTID_SUBTRACT(set,subset)

    Given two sets of global transaction IDs subset and set, returns only those GTIDs from set that are not in subset.

    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-57
    1 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-57
    1 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-57
    1 row in set (0.01 sec)
    
  • WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(gtid_set[, timeout][,channel])

    Wait until the slave SQL thread has executed all of the transactions whose global transaction identifiers are contained in gtid_set (see Section 18.1.3.1, “GTID Concepts”, for a definition of GTID sets), or until timeout seconds have elapsed, whichever occurs first. timeout is optional; the default timeout is 0 seconds, in which case the function waits until all of the transactions in the GTID set have been executed.

    For more information, see Section 18.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_UNTIL_SQL_THREAD_AFTER_GTIDS('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5');
            -> 5
    

    The return value is the number of transactional events that were executed. If GTID-based replication is not active (that is, if the value of the gtid_mode variable is OFF), then this value is undefined and thus WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() returns NULL. If the slave is not running then this function also returns NULL.

    The channel option, added in MySQL 5.7.6, enables you to choose which replication channel the function applies to. If no channel is set and no channels other than the default replication channel exist, the function applies to the default replication channel as found in versions of MySQL prior to 5.7.6. If you are using multiple replication channels you must specify a channel as otherwise it is not known which replication channel the function should act on. See Section 18.2.3, “Replication Channels” for more information on replication channels.

  • WAIT_FOR_EXECUTED_GTID_SET(gtid_set[, timeout])

    Introduced in MySQL 5.7.5, WAIT_FOR_EXECUTED_GTID_SET() is similar to WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() in that it waits until a server has executed all of the transactions whose global transaction identifiers are contained in gtid_set, or until timeout seconds have elapsed, whichever occurs first. Unlike WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(), WAIT_FOR_EXECUTED_GTID_SET() does not take into account whether the slave is running or not, and an error is returned if GTID-based replication is not enabled.

    In addition, WAIT_FOR_EXECUTED_GTID_SET() returns only the state of the query, where 0 represents success, 1 represents timeout, and any other failures return the error message.


User Comments
Sign Up Login You must be logged in to post a comment.