Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.2Mb
PDF (A4) - 31.2Mb
PDF (RPM) - 30.4Mb
EPUB - 7.7Mb
HTML Download (TGZ) - 7.5Mb
HTML Download (Zip) - 7.6Mb
HTML Download (RPM) - 6.5Mb
Eclipse Doc Plugin (TGZ) - 8.3Mb
Eclipse Doc Plugin (Zip) - 10.1Mb
Man Pages (TGZ) - 183.8Kb
Man Pages (Zip) - 295.2Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

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

12.16 Functions Used with Global Transaction IDs

The functions described in this section are used with GTID-based replication (available in MySQL 5.6.5 and later). 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.

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 17.1.3, “Replication with Global Transaction Identifiers”.

Table 12.20 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.
SQL_THREAD_WAIT_AFTER_GTIDS() (deprecated 5.6.9) OBSOLETE: Replaced by WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS()
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 (1) if all GTIDs in subset are also in set. Returns false (0) 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)
    

    This function was added in MySQL 5.6.5.

  • 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)
    

    This function was added in MySQL 5.6.5.

  • SQL_THREAD_WAIT_AFTER_GTIDS(gtid_set[, timeout])

    SQL_THREAD_WAIT_AFTER_GTIDS() was added in MySQL 5.6.5, and replaced by WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() in MySQL 5.6.9. (Bug #14775984)

    For more information, see Section 17.1.3, “Replication with Global Transaction Identifiers”.

  • WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(gtid_set[, timeout])

    Wait until the slave SQL thread has executed all of the transactions whose global transaction identifiers are contained in gtid_set (see Section 17.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 master simply waits until all of the transactions in the GTID set have been executed.

    Prior to MySQL 5.6.9, WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS() was named SQL_THREAD_WAIT_AFTER_GTIDS(). (Bug #14775984)

    For more information, see Section 17.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. Prior to MySQL 5.6.8, this function behaved unpredictably if no timeout was set and it was invoked while GTID-based replication was not active; in MySQL 5.6.8 and later, the function returns NULL whenever gtid_mode is OFF. (Bug #14640065)


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