Rechercher dans le manuel MySQL
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. 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:
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.23 GTID Functions
Name | Description |
---|---|
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. |
Given two sets of global transaction identifiers
set1
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:
- -> '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
- -> '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
- -> '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
Given two sets of global transaction identifiers
set1
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:
- -> '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
- -> '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
- -> '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
WAIT_FOR_EXECUTED_GTID_SET(
gtid_set
[,timeout
])Wait until the server has applied all of the transactions whose global transaction identifiers are contained in
gtid_set
; that is, until the condition GTID_SUBSET(gtid_subset
,@@GLOBAL.gtid_executed
) holds. See Section 17.1.3.1, “GTID Format and Storage” for a definition of GTID sets.If a timeout is specified, and
timeout
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.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, 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:
- -> 0
For a syntax description for GTID sets, see Section 17.1.3.1, “GTID Format and Storage”.
For
WAIT_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.The
channel
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. See Section 17.2.3, “Replication Channels” for more information on replication channels.NoteBecause
WAIT_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 with
WAIT_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 slave 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.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-gtid-functions.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.