247 lines
9.9 KiB
Plaintext
247 lines
9.9 KiB
Plaintext
RESET MASTER;
|
|
==== Conditions for gtid_executed ====
|
|
# Readable.
|
|
SELECT @@GLOBAL.GTID_EXECUTED;
|
|
@@GLOBAL.GTID_EXECUTED
|
|
|
|
SELECT @@SESSION.GTID_EXECUTED;
|
|
ERROR HY000: Variable 'gtid_executed' is a GLOBAL variable
|
|
# 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, SYSTEM_VARIABLES_ADMIN, SESSION_VARIABLES_ADMIN or REPLICATION_APPLIER 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;
|