RESET MASTER;
==== Conditions for gtid_executed ====
# Readable.
SELECT @@GLOBAL.GTID_EXECUTED;
@@GLOBAL.GTID_EXECUTED

SELECT @@SESSION.GTID_EXECUTED;
@@SESSION.GTID_EXECUTED

Warnings:
Warning	1681	'@@SESSION.GTID_EXECUTED' is deprecated and will be removed in a future release.
# Read-only.
SET @@GLOBAL.GTID_EXECUTED= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1';
ERROR HY000: Variable 'gtid_executed' is a read only variable
SET @@SESSION.GTID_EXECUTED= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:2';
ERROR HY000: Variable 'gtid_executed' is a read only variable
==== Conditions for gtid_purged ====
# Readable global variable
SELECT @@GLOBAL.GTID_PURGED;
@@GLOBAL.GTID_PURGED

# Not a session variable
SELECT @@SESSION.GTID_PURGED;
ERROR HY000: Variable 'gtid_purged' is a GLOBAL variable
# Read-only.
SET @@SESSION.GTID_PURGED= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3';
ERROR HY000: Variable 'gtid_purged' is a GLOBAL variable and should be set with SET GLOBAL
==== Conditions for gtid_owned ====
# Readable.
SELECT @@GLOBAL.GTID_OWNED;
@@GLOBAL.GTID_OWNED

SELECT @@SESSION.GTID_OWNED;
@@SESSION.GTID_OWNED

# Read-only.
SET @@SESSION.GTID_OWNED= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:5';
ERROR HY000: Variable 'gtid_owned' is a read only variable
SET @@GLOBAL.GTID_OWNED= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:6#1';
ERROR HY000: Variable 'gtid_owned' is a read only variable
==== Valid and invalid values for gtid_next ====
# Default should be AUTOMATIC
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'AUTOMATIC';
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:10';
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:10
ROLLBACK;
SET @@SESSION.GTID_NEXT= '
    aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa 	 : 	 10
    ';
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:10
ROLLBACK;
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:0x10';
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:16
ROLLBACK;
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:010';
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:8
ROLLBACK;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC';
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:10#';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:10#'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1-10'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1:3';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1:3'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:3'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= NULL;
ERROR 42000: Variable 'gtid_next' can't be set to the value of 'NULL'
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 10;
ERROR 42000: Incorrect argument type to variable 'gtid_next'
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= '10';
ERROR HY000: Malformed GTID specification '10'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:0';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:0'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:0x8000000000000000';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:0x8000000000000000'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:-33';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:-33'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:66-33';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:66-33'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:33 33';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:33 33'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa-33';
ERROR HY000: Malformed GTID specification 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa-33'.
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
==== Conditions for gtid_next ====
# Initialize
CREATE FUNCTION f() RETURNS INT BEGIN RETURN 1; END;
CREATE TABLE t1 (a INT) ENGINE = InnoDB;
CREATE USER nonsuper@localhost;
GRANT SELECT ON *.* TO nonsuper@localhost;
# Read/write session
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:7';
ROLLBACK;
# not global
SELECT @@GLOBAL.GTID_NEXT;
ERROR HY000: Variable 'gtid_next' is a SESSION variable
SET @@GLOBAL.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:8';
ERROR HY000: Variable 'gtid_next' is a SESSION variable and can't be used with SET GLOBAL
ROLLBACK;
SET GTID_NEXT = 'AUTOMATIC';
# Can't set value from stored function
CREATE FUNCTION set_var() RETURNS INT BEGIN SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:9'; RETURN 1; END;
SELECT set_var();
ERROR HY000: The system variable gtid_next cannot be set in stored functions or triggers.
DROP FUNCTION set_var;
# Can't set value from trigger
CREATE TRIGGER t BEFORE INSERT ON t1 FOR EACH ROW SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:10';
INSERT INTO t1 VALUES (1);
ERROR HY000: The system variable gtid_next cannot be set in stored functions or triggers.
DROP TRIGGER t;
# Can't set value when invoking SF
SET @@SESSION.GTID_NEXT = f();
ERROR HY000: The statement 'SET gtid_next' cannot invoke a stored function.
# must be SUPER
SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:11';
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
# can read even if non-SUPER
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
AUTOMATIC
# can't set inside transaction
BEGIN;
SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:12';
ERROR HY000: The system variable gtid_next cannot be set when there is an ongoing transaction.
INSERT INTO t1 VALUES (4);
ROLLBACK;
# can't set while owning a GTID
SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13';
SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:14';
ERROR HY000: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13. Ownership is released on COMMIT or ROLLBACK.
SET @@SESSION.GTID_NEXT = 'AUTOMATIC';
ROLLBACK;
# can't set while owning a GTID
SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13';
SET @@SESSION.GTID_NEXT = 'AUTOMATIC';
ERROR HY000: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:13. Ownership is released on COMMIT or ROLLBACK.
ROLLBACK;
# Can't set while owning ANONYMOUS
include/rpl_set_gtid_mode.inc [OFF on servers 1]
SET GTID_NEXT = 'ANONYMOUS';
SET GTID_NEXT = 'ANONYMOUS';
ERROR HY000: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
SET GTID_NEXT = 'ANONYMOUS';
SET GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:1';
ERROR HY000: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
SET GTID_NEXT = 'ANONYMOUS';
SET GTID_NEXT = 'AUTOMATIC';
ERROR HY000: @@SESSION.GTID_NEXT cannot be changed by a client that owns a GTID. The client owns ANONYMOUS. Ownership is released on COMMIT or ROLLBACK.
SET GTID_NEXT = 'AUTOMATIC';
include/rpl_set_gtid_mode.inc [ON on servers 1]
SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:14';
# can't do implicit commit while gtid_next=SID:GNO
BEGIN;
CREATE TABLE t2 (a INT);
ERROR HY000: Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
ROLLBACK;
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:15';
BEGIN;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (a INT);
ERROR HY000: Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
COMMIT;
SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:16';
CREATE TABLE t2 AS SELECT * FROM t1;
ERROR HY000: Statement violates GTID consistency: CREATE TABLE ... SELECT.
COMMIT;
SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:17';
# can't do implicit commit while gtid_next=SID:GNO
SET AUTOCOMMIT = 0;
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (a INT);
ERROR HY000: Cannot execute statements with implicit commit inside a transaction when @@SESSION.GTID_NEXT == 'UUID:NUMBER'.
ROLLBACK;
SELECT @@SESSION.GTID_NEXT;
@@SESSION.GTID_NEXT
aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:17
SET @@SESSION.GTID_NEXT = 'AUTOMATIC';
DROP TABLE t1;
DROP FUNCTION f;
DROP USER nonsuper@localhost;
CREATE PROCEDURE p ()
BEGIN
SET @@SESSION.GTID_NEXT = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa:16';
ROLLBACK;
SET @@SESSION.GTID_NEXT = 'AUTOMATIC';
END|
CALL p();
DROP PROCEDURE p;