17.1.4.5 Global Transaction ID Options and Variables

• Startup Options Used with GTID Replication

• System Variables Used with GTID Replication

The MySQL Server options and system variables described in this section are used to monitor and control Global Transaction Identifiers (GTIDs), introduced in MySQL 5.6.5.

Note

Many of these options and variables were renamed in MySQL 5.6.9. See their descriptions in this section for more information.

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

Startup Options Used with GTID Replication

The followup server startup options are used with GTID-based replication:

• --disable-gtid-unsafe-statements

  Property	Value
  Command-Line Format	--disable-gtid-unsafe-statements[={OFF|ON}]
  Introduced	5.6.5
  Removed	5.6.9
  System Variable	disable_gtid_unsafe_statements
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  Obsolete: Replaced by --enforce-gtid-consistency in MySQL 5.6.9. (Bug #14775984)

• --enforce-gtid-consistency

  Property	Value
  Command-Line Format	--enforce-gtid-consistency[=value]
  Introduced	5.6.9
  System Variable	enforce_gtid_consistency
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  When enabled, this option enforces GTID consistency by allowing execution of only those statements that can be logged in a transactionally safe manner. You must enable --enforce-gtid-consistency before setting --gtid-mode to ON; otherwise, enabling GTID mode fails with an error. You can (and should) use this option prior to using --gtid-mode, in order to test whether the system is ready to use GTIDs.

  Since only transactionally safe statements can be logged when --enforce-gtid-consistency is enabled, it follows that the operations listed here cannot be used with this option:

  • CREATE TABLE ... SELECT statements

  • CREATE TEMPORARY TABLE statements inside transactions

  • Transactions or statements that update both transactional and nontransactional tables.

  Prior to MySQL 5.6.9, this option was named --disable-gtid-unsafe-statements. (Bug #14775984)

  Prior to MySQL 5.6.7, using this option caused nontransactional DML on temporary tables to fail, although changes to temporary tables are not logged when using row-based binary logging. In MySQL 5.6.7 and later, nontransactional DML statements are allowed on temporary tables with --disable-gtid-unsafe-statements (--enforce-gtid-consistency beginning with MySQL 5.6.9) as long as all affected tables are temporary tables (Bug #14272672).

  Prior to MySQL 5.6.7, mysql_upgrade could not be used with a MySQL Server running with this option enabled, unless mysql_upgrade was running with --write-binlog explicitly disabled. (Bug #13833710, Bug #14221043) In MySQL 5.6.7 and later, it is possible but not recommended to run mysql_upgrade on a server where --gtid-mode=ON, since the MySQL system tables use the MyISAM storage engine, which is nontransactional.

  In MySQL 5.6.8 and earlier, you could not use any statements affecting nontransactional tables when --enforce-gtid-consistency was used (the option was then called --disable-gtid-unsafe-statements). In MySQL 5.6.9 and later, this option allows single statements updating nontransactional tables. This is intended chiefly for use with programs such as mysql_install_db and mysql_upgrade. (Bug #14722659)

• --gtid-mode

  Property	Value
  Command-Line Format	--gtid-mode=MODE
  Introduced	5.6.5
  System Variable	gtid_mode
  Scope	Global
  Dynamic	No
  Type	Enumeration
  Default Value	OFF
  Valid Values	OFF UPGRADE_STEP_1 UPGRADE_STEP_2 ON

  This option specifies whether global transaction identifiers (GTIDs) are used to identify transactions. Starting the server with --gtid-mode=ON requires that the server also be started with the --log-bin, --log-slave-updates, and --enforce-gtid-consistency options.

  Setting this option to OFF when there are GTIDs in the binary log or in the relay log, or to ON when there remain anonymous transactions to be executed, causes an error.

  Important

  This option does not employ boolean values; its values are in fact enumerated. You should not attempt to use numeric values when setting this option, as these may lead to unexpected results. The values UPGRADE_STEP_1 and UPGRADE_STEP_2 are reserved for future use, but currently are not supported in production; if you use one of these two values with --gtid-mode, the server refuses to start.

  The values of gtid_purged and gtid_executed are not persistent while gtid_mode=off. Therefore, after changing gtid_mode to OFF, once all binary logs containing GTIDs are purged, the values of these variables are lost.

  Prior to MySQL 5.6.7, mysql_upgrade could not be used with a MySQL Server running with this option enabled, unless mysql_upgrade was running with --write-binlog explicitly disabled. (Bug #13833710, Bug #14221043)

  Prior to MySQL 5.6.10, setting the global value for the sql_slave_skip_counter variable to 1 had no effect when --gtid-mode was set to ON. (Bug #15833516) A workaround in MySQL 5.6.9 and earlier versions is to reset the slave's position using CHANGE MASTER TO ... MASTER_LOG_FILE = ... MASTER_LOG_POS = ..., including the MASTER_AUTO_POSITION = 0 option with this statement if needed.

System Variables Used with GTID Replication

The following system variables are used with GTID-based replication:

• binlog_gtid_simple_recovery

  Property	Value
  Command-Line Format	--binlog-gtid-simple-recovery[={OFF|ON}]
  Introduced	5.6.23
  System Variable	binlog_gtid_simple_recovery
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  This variable controls how binary log files are iterated during the search for GTIDs when MySQL starts or restarts. In MySQL version 5.6.21, this variable was added as simplified_binlog_gtid_recovery and in MySQL version 5.6.23 it was renamed to binlog_gtid_simple_recovery.

  When binlog_gtid_simple_recovery=FALSE, the iteration starts from the newest file to initialize gtid_executed, and starts from the oldest file to initialize gtid_purged. This process could take a long time if you had a large number of binary log files without GTID events, for example created when gtid_mode=OFF.

  When binlog_gtid_simple_recovery=TRUE, the server does not open more than two binary logs when iterating to populate gtid_purged and gtid_executed, either during server restart or when binary logs are being purged.

  Note

  If this option is enabled, gtid_executed and gtid_purged may be initialized incorrectly in the following situations:

  • Some binary logs were generated when gtid_mode was ON, but gtid_mode was OFF for the newest binary log.

  • A SET GTID_PURGED statement was issued after the oldest existing binary log was generated.

  If an incorrect GTID set is computed in either situation, it will remain incorrect even if the server is later restarted, regardless of the value of this option.

• disable_gtid_unsafe_statements

  Property	Value
  Command-Line Format	--disable-gtid-unsafe-statements[={OFF|ON}]
  Introduced	5.6.5
  Removed	5.6.9
  System Variable	disable_gtid_unsafe_statements
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  Obsolete: Replaced by enforce_gtid_consistency in MySQL 5.6.9. (Bug #14775984)

• gtid_done

  Property	Value
  Introduced	5.6.5
  Removed	5.6.9
  System Variable	gtid_done
  Scope	Global, Session
  Dynamic	No
  Type	String

  Obsolete: replaced in MySQL 5.6.9 by gtid_executed. (Bug #14775984)

• enforce_gtid_consistency

  Property	Value
  Command-Line Format	--enforce-gtid-consistency[=value]
  Introduced	5.6.9
  System Variable	enforce_gtid_consistency
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  When this variable is true, the server enforces GTID consistency by allowing execution of only those statements that can be logged in a transactionally safe manner. You must enable GTID consistency (by using --enforce-gtid-consistency) before you can start the server with --gtid-mode=ON; otherwise, enabling GTID mode fails with an error. You can (and should) enable GTID consistency prior to using --gtid-mode, in order to test whether the system is ready to use GTIDs.

  Since only transactionally safe statements can be logged when enforce_gtid_consistency is true, it follows that the operations listed here cannot be used when this is the case:

  • CREATE TABLE ... SELECT statements

  • CREATE TEMPORARY TABLE statements inside transactions

  • Transactions or statements that update both transactional and nontransactional tables.

  This variable is read-only. To set it, use the --enforce-gtid-consistency option on the command line or in an option file when starting the MySQL Server.

  Prior to MySQL 5.6.9, this variable was named disable_gtid_unsafe_statements. (Bug #14775984)

• gtid_executed

  Property	Value
  Introduced	5.6.9
  System Variable	gtid_executed
  Scope	Global, Session
  Dynamic	No
  Type	String

  When used with global scope, this variable contains a representation of the set of all transactions that are logged in the binary log. This is the same as the value of the Executed_Gtid_Set column in the output of SHOW MASTER STATUS and SHOW SLAVE STATUS.

  When used with session scope, this variable contains a representation of the set of transactions that are written to the cache in the current session.

  The set of transactions that can be found in the binary logs at any given time is equal to GTID_SUBTRACT(@@GLOBAL.gtid_executed, @@GLOBAL.gtid_purged); that is, to all transactions in the binary log that have not yet been purged.

  When the server starts, @@GLOBAL.gtid_executed is initialized to the union of the following two sets:

  • The GTIDs listed in the Previous_gtids_log_event of the newest binary log

  • The GTIDs found in every Gtid_log_event in the newest binary log.

  Thereafter, GTIDs are added to the set as transactions are executed.

  Issuing RESET MASTER causes the global value (but not the session value) of this variable to be reset to an empty string. GTIDs are not otherwise removed from this set other than when the set is cleared due to RESET MASTER. The set is also cleared if the server is shut down and all binary logs are removed.

  Prior to MySQL 5.6.9, this variable was known as gtid_done.

• gtid_lost

  Property	Value
  Introduced	5.6.5
  Removed	5.6.9
  System Variable	gtid_lost
  Scope	Global
  Dynamic	No
  Type	String

  Obsolete: Replaced by gtid_purged in MySQL 5.6.9. (Bug #14775984)

• gtid_mode

  Property	Value
  Introduced	5.6.5
  System Variable	gtid_mode
  Scope	Global
  Dynamic	No
  Type	Enumeration
  Default Value	OFF
  Valid Values	OFF UPGRADE_STEP_1 UPGRADE_STEP_2 ON

  Shows whether GTIDs are enabled. Read-only; set using --gtid-mode.

• gtid_next

  Property	Value
  Introduced	5.6.5
  System Variable	gtid_next
  Scope	Session
  Dynamic	Yes
  Type	Enumeration
  Default Value	AUTOMATIC
  Valid Values	AUTOMATIC ANONYMOUS UUID:NUMBER

  This variable is used to specify whether and how the next GTID is obtained.

  Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.8.1, “System Variable Privileges”.

  gtid_next can take any of the following values:

  • AUTOMATIC: Use the next automatically-generated global transaction ID.

  • ANONYMOUS: Transactions do not have global identifiers, and are identified by file and position only.

  • A global transaction ID in UUID:NUMBER format.

  Setting this variable has no effect if gtid_mode is OFF.

  Prior to MySQL 5.6.20, when GTIDs were enabled but gtid_next was not AUTOMATIC, DROP TABLE did not work correctly when used on a combination of nontemporary tables with temporary tables, or of temporary tables using transactional storage engines with temporary tables using nontransactional storage engines. In MySQL 5.6.20 and later, DROP TABLE or DROP TEMPORARY TABLE fails with an explicit error when used with either of these combinations of tables. (Bug #17620053)

  In MySQL 5.6.11 only, you cannot execute any of the statements CHANGE MASTER TO, START SLAVE, STOP SLAVE, REPAIR TABLE, OPTIMIZE TABLE, ANALYZE TABLE, CHECK TABLE, CREATE SERVER, ALTER SERVER, DROP SERVER, CACHE INDEX, LOAD INDEX INTO CACHE, FLUSH, or RESET when gtid_next is set to any value other than AUTOMATIC; in such cases, the statement fails with an error. Such statements are not disallowed in MySQL 5.6.12 and later. (Bug #16062608, Bug #16715809, Bug #69045)

• gtid_owned

  Property	Value
  Introduced	5.6.5
  System Variable	gtid_owned
  Scope	Global, Session
  Dynamic	No
  Type	String

  This read-only variable holds a list whose contents depend on its scope. When used with session scope, the list holds all GTIDs that are owned by this client; when used with global scope, it holds a list of all GTIDs along with their owners.

• gtid_purged

  Property	Value
  Introduced	5.6.9
  System Variable	gtid_purged
  Scope	Global
  Dynamic	Yes
  Type	String

  The set of all transactions that have been purged from the binary log. This is a subset of the set of transactions in gtid_executed.

  When the server starts, the global value of gtid_purged is initialized to the set of GTIDs contained by the Previous_gtid_log_event of the oldest binary log. When a binary log is purged, gtid_purged is re-read from the binary log that has now become the oldest one.

  Prior to MySQL 5.6.9, this variable was known as gtid_lost, and was read-only. In MySQL 5.6.9 and later, it is possible to update the value of this variable. (Bug #14797808)

  To update the value of this variable, gtid_mode must be ON, gtid_executed must be the empty string, and therefore gtid_purged will also be the empty string. This can occur either when replication has not been started previously, or when replication was not previously using GTIDs.

  After executing SET gtid_purged, you should note down the current binary log filename, which can be checked using SHOW MASTER STATUS. If the server is restarted before this file has been purged, then you should use binlog_gtid_simple_recovery=0 (the default in 5.6) to avoid gtid_purged or gtid_executed being computed incorrectly.

  Issuing RESET MASTER causes the value of this variable to be reset to an empty string.

• simplified_binlog_gtid_recovery

  Property	Value
  Command-Line Format	--simplified-binlog-gtid-recovery[={OFF|ON}]
  Introduced	5.6.21
  Deprecated	5.6.23
  System Variable	simplified_binlog_gtid_recovery
  Scope	Global
  Dynamic	No
  Type	Boolean
  Default Value	OFF

  This option is deprecated and will be removed in a future MySQL release. Use the renamed binlog_gtid_simple_recovery to control how MySQL iterates through binary log files after a crash.