16.1.4.5. Global Transaction ID Options and Variables

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 16.1.3, "Replication with Global Transaction Identifiers".

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

--disable-gtid-unsafe-statements

Introduced	5.6.5
Removed	5.6.9
Command-Line Format	`--disable-gtid-unsafe-statements[=value]`
Option-File Format	`disable-gtid-unsafe-statements`
System Variable Name	`disable_gtid_unsafe_statements`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`boolean`
	Default	`false`

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

--enforce-gtid-consistency

Introduced	5.6.9
Command-Line Format	`--enforce-gtid-consistency[=value]`
Option-File Format	`enforce-gtid-consistency`
System Variable Name	`enforce_gtid_consistency`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`boolean`
	Default	`false`

When set, this option allows execution of only those statements that can be logged in a transactionally safe manner. This means that the following operations cannot be used when this option is enabled:

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

Introduced	5.6.5
Command-Line Format	`--gtid-mode=MODE`
Option-File Format	`gtid-mode`
System Variable Name	`gtid_mode`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`enumeration`
	Default	`OFF`
	Valid Values	`OFF`
		`UPGRADE_STEP_1`
		`UPGRADE_STEP_2`
		`ON`

This option specifies whether GTIDs are enabled. Starting the server with --gtid-mode=ON requires that the server also be started with the --log-bin and --log-slave-updates options as well. (In addition, you should also use --enforce-gtid-consistency.)

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.

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 it may make changes to MySQL system tables that use the MyISAM storage engine, which is nontransactional.

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 on replication masters. The following system variables are used with GTID-based replication:

disable_gtid_unsafe_statements

Introduced	5.6.5
Removed	5.6.9
Command-Line Format	`--disable-gtid-unsafe-statements[=value]`
Option-File Format	`disable_gtid_unsafe_statements`
System Variable Name	`disable_gtid_unsafe_statements`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`boolean`
	Default	`false`

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

gtid_done

Introduced	5.6.5
Removed	5.6.9
System Variable Name	`gtid_done`
Variable Scope	Global, Session
Dynamic Variable	No
	Permitted Values
	Type	`string`

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

enforce_gtid_consistency

Introduced	5.6.9
Command-Line Format	`--enforce-gtid-consistency[=value]`
Option-File Format	`enforce_gtid_consistency`
System Variable Name	`enforce_gtid_consistency`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`boolean`
	Default	`false`

When this variable is true, execution is allowed of only those statements that can be logged in a transactionally safe manner, which means that the following operations cannot be used:

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

Introduced	5.6.9
System Variable Name	`gtid_executed`
Variable Scope	Global, Session
Dynamic Variable	No
	Permitted Values
	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. When used with session scope, it contains a representation of the set of transactions that are written to the cache in the current session.

Issuing RESET MASTER causes the global value (but not the session value) of this variable to be reset to an empty string.

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

gtid_lost

Introduced	5.6.5
Removed	5.6.9
System Variable Name	`gtid_lost`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`string`

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

gtid_mode

Introduced	5.6.5
System Variable Name	`gtid_mode`
Variable Scope	Global
Dynamic Variable	No
	Permitted Values
	Type	`enumeration`
	Default	`OFF`
	Valid Values	`OFF`
		`UPGRADE_STEP_1`
		`UPGRADE_STEP_2`
		`ON`

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

gtid_next

Introduced	5.6.5
System Variable Name	`gtid_next`
Variable Scope	Session
Dynamic Variable	Yes
	Permitted Values
	Type	`enumeration`
	Default	`AUTOMATIC`
	Valid Values	`AUTOMATIC`
		`ANONYMOUS`
		`UUID:NUMBER`

This variable is used to specify whether and how the next GTID is obtained. 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.

You must have the SUPER privilege to set this variable. Setting this variable has no effect if gtid_mode is OFF.

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

Introduced	5.6.5
System Variable Name	`gtid_owned`
Variable Scope	Global, Session
Dynamic Variable	No
	Permitted Values
	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

Introduced	5.6.9
System Variable Name	`gtid_purged`
Variable Scope	Global
Dynamic Variable	Yes
	Permitted Values
	Type	`string`

The set of all transactions that have been purged from the binary log.

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

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, but only by adding GTIDs to those already listed, and only when gtid_executed is unset—that is, on a new server. (Bug #14797808)