1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
include/rpl_init.inc [topology=none]
include/rpl_default_connections.inc
==== Variable is read-only and default is off ====
include/assert.inc [ENFORCE_GTID_CONSISTENCY should default to 0]
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1;
ERROR HY000: Variable 'enforce_gtid_consistency' is a read only variable
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 0;
ERROR HY000: Variable 'enforce_gtid_consistency' is a read only variable
==== Restart server ====
include/rpl_restart_server.inc [server_number=1 parameters: --enforce-gtid-consistency=1]
include/assert.inc [ENFORCE_GTID_CONSISTENCY should be set to 1 at restart]
==== CREATE TABLE .... SELECT not allowed ====
CREATE TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
ERROR HY000: CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.
CREATE TABLE t1 (a INT) ENGINE = MyISAM SELECT 1;
ERROR HY000: CREATE TABLE ... SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = InnoDB SELECT 1;
DROP TEMPORARY TABLE t1, t2;
SET SQL_LOG_BIN = 0;
CREATE TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
CREATE TABLE t2 (a INT) ENGINE = MyISAM SELECT 1;
DROP TABLE t1;
DROP TABLE t2;
SET SQL_LOG_BIN = 1;
==== Non-transactional updates not allowed ====
Non-transactional updates are allowed when row binlog format
is used and all non-transactional tables on a transaction are
temporary.
CREATE TABLE t1 (a INT) ENGINE = MyISAM;
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
CREATE TABLE t3 (a INT) ENGINE = InnoDB;
CREATE TRIGGER trig BEFORE INSERT ON t3 FOR EACH ROW BEGIN INSERT INTO t1 VALUES (1); END;
INSERT INTO t3 VALUES (1);
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.
SET SQL_LOG_BIN = 0;
INSERT INTO t1 VALUES (1);
INSERT INTO t2 VALUES (1);
INSERT INTO t3 VALUES (1);
SET SQL_LOG_BIN = 1;
ALTER TABLE t1 ADD COLUMN (b INT);
ALTER TABLE t2 ADD COLUMN (b INT);
DROP TABLE t1, t2, t3;
==== [CREATE|DROP] TEMPORARY TABLE inside a transaction ====
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
ALTER TABLE t1 ADD COLUMN (b INT);
DROP TABLE t1;
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
DROP TEMPORARY TABLE t1;
BEGIN;
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
COMMIT;
SET AUTOCOMMIT = 0;
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
SET AUTOCOMMIT = 1;
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
BEGIN;
ALTER TABLE t1 ADD COLUMN (b INT);
COMMIT;
SET AUTOCOMMIT = 0;
ALTER TABLE t1 ADD COLUMN (c INT);
SET AUTOCOMMIT = 1;
ALTER TABLE t1 ADD COLUMN (d INT);
BEGIN;
DROP TEMPORARY TABLE t1;
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
COMMIT;
SET AUTOCOMMIT = 0;
DROP TEMPORARY TABLE t1;
ERROR HY000: When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.
COMMIT;
SET AUTOCOMMIT = 1;
DROP TEMPORARY TABLE t1;
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
BEGIN;
DROP TABLE t1;
COMMIT;
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
SET AUTOCOMMIT = 0;
DROP TABLE t1;
SET AUTOCOMMIT = 1;
SET SQL_LOG_BIN = 0;
BEGIN;
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
COMMIT;
BEGIN;
DROP TEMPORARY TABLE t1;
DROP TEMPORARY TABLE t2;
COMMIT;
SET AUTOCOMMIT = 0;
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
COMMIT;
DROP TEMPORARY TABLE t1;
DROP TEMPORARY TABLE t2;
COMMIT;
SET AUTOCOMMIT = 1;
SET SQL_LOG_BIN = 1;
==== Restart server ====
include/rpl_restart_server.inc [server_number=1 parameters: --enforce-gtid-consistency=0]
include/assert.inc [ENFORCE_GTID_CONSISTENCY should be set to 0 at restart]
include/rpl_end.inc
|
