Skip to content

Commit 4a68815

Browse files
AliSQLAliSQL
AliSQL
authored and
AliSQL
committed
[Feature] Issue#5 RELAX GTID LIMITATION FOR SOME STATEMENTS
Description: ------------ When gtid_mode is on, MySQL has a limitation on cts(create table ... select) and the statements mixed with transaction/non-transaction storage engines. It doesn't allow these statements to be executed. Such a limitation brings many inconveniences to users. In order to remove that limitation, we implemented such a feature which can make those statments run under rbr(row based replication) mode and gtid mode. We introduced one global system variable 'rds_allow_unsafe_stmt_with_gtid' to enable such a feature. When the feature is enabled, cts will be divided into two individual transactions. The first is CREATE TABLE. The second is inserting data into the table. For the second transaction, it will write into binlog directly, which means not to wait for any transaction commit once the first transaction is succesfull. The same logicfor the statements mixing nontransactional storage engine with transactional ones. Note if you want to use this feature, you must set gtid_next automatically.
1 parent aa5e331 commit 4a68815

21 files changed

Lines changed: 1657 additions & 12 deletions

‎mysql-test/r/mysqld--help-notwin.result‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -740,6 +740,9 @@ The following options may be given as the first argument:
740740
--rds-threads-running-high-watermark=#
741741
When threads_running exceeds this limit, query that isn't
742742
in active transaction should quit.
743+
--rds-allow-unsafe-stmt-with-gtid
744+
Allow executing CREATE TABLE AS SELECT or mixed engine
745+
transactions if enabled.
743746
--rds-gtid-precommit
744747
Add gtid into gtid_executed before flushing binlog from
745748
cache to file.
@@ -1287,6 +1290,7 @@ rds-sql-update-filter (No default value)
12871290
rds-tablestat FALSE
12881291
rds-threads-running-ctl-mode SELECTS
12891292
rds-threads-running-high-watermark 151
1293+
rds-allow-unsafe-stmt-with-gtid FALSE
12901294
rds-gtid-precommit FALSE
12911295
read-buffer-size 131072
12921296
read-only FALSE

‎mysql-test/suite/binlog/r/binlog_enforce_gtid_consistency_relax.result‎

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,104 @@
1+
include/rpl_init.inc [topology=none]
2+
include/rpl_default_connections.inc
3+
==== Variable is read-only and default is off ====
4+
include/assert.inc [ENFORCE_GTID_CONSISTENCY should default to 0]
5+
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1;
6+
ERROR HY000: Variable 'enforce_gtid_consistency' is a read only variable
7+
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 0;
8+
ERROR HY000: Variable 'enforce_gtid_consistency' is a read only variable
9+
==== Restart server ====
10+
include/rpl_restart_server.inc [server_number=1 parameters: --enforce-gtid-consistency=1]
11+
include/assert.inc [ENFORCE_GTID_CONSISTENCY should be set to 1 at restart]
12+
SET @old_rds_allow_unsafe_stmt_with_gtid = @@global.rds_allow_unsafe_stmt_with_gtid;
13+
SET GLOBAL rds_allow_unsafe_stmt_with_gtid = ON;
14+
CREATE TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
15+
DROP TABLE t1;
16+
CREATE TABLE t1 (a INT) ENGINE = MyISAM SELECT 1;
17+
DROP TABLE t1;
18+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
19+
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = InnoDB SELECT 1;
20+
DROP TEMPORARY TABLE t1, t2;
21+
SET SQL_LOG_BIN = 0;
22+
CREATE TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
23+
CREATE TABLE t2 (a INT) ENGINE = MyISAM SELECT 1;
24+
DROP TABLE t1;
25+
DROP TABLE t2;
26+
SET SQL_LOG_BIN = 1;
27+
CREATE TABLE t1 (a INT) ENGINE = MyISAM;
28+
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
29+
INSERT INTO t1 VALUES (1);
30+
INSERT INTO t2 VALUES (1);
31+
CREATE TABLE t3 (a INT) ENGINE = InnoDB;
32+
CREATE TRIGGER trig BEFORE INSERT ON t3 FOR EACH ROW BEGIN INSERT INTO t1 VALUES (1); END;
33+
INSERT INTO t3 VALUES (1);
34+
SET SQL_LOG_BIN = 0;
35+
INSERT INTO t1 VALUES (1);
36+
INSERT INTO t2 VALUES (1);
37+
INSERT INTO t3 VALUES (1);
38+
SET SQL_LOG_BIN = 1;
39+
ALTER TABLE t1 ADD COLUMN (b INT);
40+
ALTER TABLE t2 ADD COLUMN (b INT);
41+
DROP TABLE t1, t2, t3;
42+
==== [CREATE|DROP] TEMPORARY TABLE inside a transaction ====
43+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
44+
ALTER TABLE t1 ADD COLUMN (b INT);
45+
DROP TABLE t1;
46+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
47+
DROP TEMPORARY TABLE t1;
48+
BEGIN;
49+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
50+
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. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
51+
COMMIT;
52+
SET AUTOCOMMIT = 0;
53+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
54+
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. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
55+
SET AUTOCOMMIT = 1;
56+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
57+
BEGIN;
58+
ALTER TABLE t1 ADD COLUMN (b INT);
59+
COMMIT;
60+
SET AUTOCOMMIT = 0;
61+
ALTER TABLE t1 ADD COLUMN (c INT);
62+
SET AUTOCOMMIT = 1;
63+
ALTER TABLE t1 ADD COLUMN (d INT);
64+
BEGIN;
65+
DROP TEMPORARY TABLE t1;
66+
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. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
67+
COMMIT;
68+
SET AUTOCOMMIT = 0;
69+
DROP TEMPORARY TABLE t1;
70+
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. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.
71+
COMMIT;
72+
SET AUTOCOMMIT = 1;
73+
DROP TEMPORARY TABLE t1;
74+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
75+
BEGIN;
76+
DROP TABLE t1;
77+
COMMIT;
78+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
79+
SET AUTOCOMMIT = 0;
80+
DROP TABLE t1;
81+
SET AUTOCOMMIT = 1;
82+
SET SQL_LOG_BIN = 0;
83+
BEGIN;
84+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
85+
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
86+
COMMIT;
87+
BEGIN;
88+
DROP TEMPORARY TABLE t1;
89+
DROP TEMPORARY TABLE t2;
90+
COMMIT;
91+
SET AUTOCOMMIT = 0;
92+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
93+
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
94+
COMMIT;
95+
DROP TEMPORARY TABLE t1;
96+
DROP TEMPORARY TABLE t2;
97+
COMMIT;
98+
SET AUTOCOMMIT = 1;
99+
SET SQL_LOG_BIN = 1;
100+
set global rds_allow_unsafe_stmt_with_gtid = @old_rds_allow_unsafe_stmt_with_gtid;
101+
==== Restart server ====
102+
include/rpl_restart_server.inc [server_number=1 parameters: --enforce-gtid-consistency=0]
103+
include/assert.inc [ENFORCE_GTID_CONSISTENCY should be set to 0 at restart]
104+
include/rpl_end.inc

‎mysql-test/suite/binlog/t/binlog_enforce_gtid_consistency_relax.test‎

Lines changed: 168 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,168 @@
1+
# This test will switch ENFORCE_GTID_CONSISTENCY to 1 during execution.
2+
--source include/not_gtid_enabled.inc
3+
4+
# Use this so that we can use rpl_restart_server.inc later.
5+
--let $rpl_server_count= 1
6+
--let $rpl_topology= none
7+
--source include/rpl_init.inc
8+
--source include/rpl_default_connections.inc
9+
--source include/have_binlog_format_row.inc
10+
11+
--echo ==== Variable is read-only and default is off ====
12+
13+
--let $assert_text= ENFORCE_GTID_CONSISTENCY should default to 0
14+
--let $assert_cond= [SELECT @@GLOBAL.ENFORCE_GTID_CONSISTENCY] = 0
15+
--source include/assert.inc
16+
17+
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
18+
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1;
19+
20+
--error ER_INCORRECT_GLOBAL_LOCAL_VAR
21+
SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 0;
22+
23+
--echo ==== Restart server ====
24+
25+
--let $rpl_server_number= 1
26+
--let $rpl_server_parameters= --enforce-gtid-consistency=1
27+
--source include/rpl_restart_server.inc
28+
29+
--let $assert_text= ENFORCE_GTID_CONSISTENCY should be set to 1 at restart
30+
--let $assert_cond= [SELECT @@GLOBAL.ENFORCE_GTID_CONSISTENCY] = 1
31+
--source include/assert.inc
32+
33+
SET @old_rds_allow_unsafe_stmt_with_gtid = @@global.rds_allow_unsafe_stmt_with_gtid;
34+
SET GLOBAL rds_allow_unsafe_stmt_with_gtid = ON;
35+
36+
CREATE TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
37+
DROP TABLE t1;
38+
CREATE TABLE t1 (a INT) ENGINE = MyISAM SELECT 1;
39+
DROP TABLE t1;
40+
41+
# Temporary tables should be ok
42+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
43+
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = InnoDB SELECT 1;
44+
DROP TEMPORARY TABLE t1, t2;
45+
46+
# No problem if we turn off binlogging.
47+
SET SQL_LOG_BIN = 0;
48+
CREATE TABLE t1 (a INT) ENGINE = InnoDB SELECT 1;
49+
CREATE TABLE t2 (a INT) ENGINE = MyISAM SELECT 1;
50+
DROP TABLE t1;
51+
DROP TABLE t2;
52+
SET SQL_LOG_BIN = 1;
53+
54+
CREATE TABLE t1 (a INT) ENGINE = MyISAM;
55+
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
56+
57+
INSERT INTO t1 VALUES (1);
58+
INSERT INTO t2 VALUES (1);
59+
60+
CREATE TABLE t3 (a INT) ENGINE = InnoDB;
61+
--eval CREATE TRIGGER trig BEFORE INSERT ON t3 FOR EACH ROW BEGIN INSERT INTO t1 VALUES (1); END
62+
63+
INSERT INTO t3 VALUES (1);
64+
65+
# No problem if we turn off binlogging.
66+
SET SQL_LOG_BIN = 0;
67+
INSERT INTO t1 VALUES (1);
68+
INSERT INTO t2 VALUES (1);
69+
INSERT INTO t3 VALUES (1);
70+
SET SQL_LOG_BIN = 1;
71+
72+
ALTER TABLE t1 ADD COLUMN (b INT);
73+
ALTER TABLE t2 ADD COLUMN (b INT);
74+
DROP TABLE t1, t2, t3;
75+
76+
--echo ==== [CREATE|DROP] TEMPORARY TABLE inside a transaction ====
77+
78+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
79+
ALTER TABLE t1 ADD COLUMN (b INT);
80+
DROP TABLE t1;
81+
82+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
83+
DROP TEMPORARY TABLE t1;
84+
85+
# CREATE TEMPORARY TABLE is not allowed in a transaction.
86+
BEGIN;
87+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION
88+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
89+
COMMIT;
90+
91+
SET AUTOCOMMIT = 0;
92+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION
93+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
94+
95+
SET AUTOCOMMIT = 1;
96+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
97+
98+
# ALTER TABLE is not affected because it always does implicit commit.
99+
BEGIN;
100+
ALTER TABLE t1 ADD COLUMN (b INT);
101+
COMMIT;
102+
103+
SET AUTOCOMMIT = 0;
104+
ALTER TABLE t1 ADD COLUMN (c INT);
105+
106+
SET AUTOCOMMIT = 1;
107+
ALTER TABLE t1 ADD COLUMN (d INT);
108+
109+
# DROP TEMPORARY TABLE is not allowed inside transaction.
110+
BEGIN;
111+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION
112+
DROP TEMPORARY TABLE t1;
113+
COMMIT;
114+
115+
SET AUTOCOMMIT = 0;
116+
--error ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION
117+
DROP TEMPORARY TABLE t1;
118+
COMMIT;
119+
120+
# DROP TEMPORARY TABLE is allowed outside transactions.
121+
SET AUTOCOMMIT = 1;
122+
DROP TEMPORARY TABLE t1;
123+
124+
# DROP TABLE is not affected because it always does an implicit commit.
125+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
126+
BEGIN;
127+
DROP TABLE t1;
128+
COMMIT;
129+
130+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
131+
SET AUTOCOMMIT = 0;
132+
DROP TABLE t1;
133+
SET AUTOCOMMIT = 1;
134+
135+
# No problem if we turn off binlogging.
136+
SET SQL_LOG_BIN = 0;
137+
BEGIN;
138+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
139+
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
140+
COMMIT;
141+
BEGIN;
142+
DROP TEMPORARY TABLE t1;
143+
DROP TEMPORARY TABLE t2;
144+
COMMIT;
145+
SET AUTOCOMMIT = 0;
146+
CREATE TEMPORARY TABLE t1 (a INT) ENGINE = InnoDB;
147+
CREATE TEMPORARY TABLE t2 (a INT) ENGINE = MyISAM;
148+
COMMIT;
149+
DROP TEMPORARY TABLE t1;
150+
DROP TEMPORARY TABLE t2;
151+
COMMIT;
152+
SET AUTOCOMMIT = 1;
153+
SET SQL_LOG_BIN = 1;
154+
155+
set global rds_allow_unsafe_stmt_with_gtid = @old_rds_allow_unsafe_stmt_with_gtid;
156+
157+
# Revert to default enforce-gtid-consistency value
158+
--echo ==== Restart server ====
159+
--let $rpl_server_number= 1
160+
--let $rpl_server_parameters= --enforce-gtid-consistency=0
161+
--source include/rpl_restart_server.inc
162+
163+
--let $assert_text= ENFORCE_GTID_CONSISTENCY should be set to 0 at restart
164+
--let $assert_cond= [SELECT @@GLOBAL.ENFORCE_GTID_CONSISTENCY] = 0
165+
--source include/assert.inc
166+
167+
168+
--source include/rpl_end.inc

‎mysql-test/suite/rds/r/feature_relax_gtid_limit_procedure.result‎

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
include/master-slave.inc
2+
Warnings:
3+
Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4+
Note #### Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
5+
[connection master]
6+
CREATE PROCEDURE p1()
7+
BEGIN
8+
DECLARE id INT;
9+
DROP TABLE IF EXISTS t1;
10+
CREATE TABLE t1
11+
SELECT
12+
id AS t_id;
13+
END//
14+
CREATE PROCEDURE p2()
15+
BEGIN
16+
DECLARE id INT;
17+
DROP TABLE IF EXISTS tmp_value;
18+
CREATE TEMPORARY TABLE tmp_value(a INT);
19+
INSERT INTO tmp_value VALUES(100);
20+
DROP TABLE IF EXISTS t2;
21+
CREATE TABLE t2
22+
SELECT
23+
id AS t_id,
24+
a as value
25+
FROM tmp_value;
26+
DROP TABLE IF EXISTS tmp_value;
27+
END//
28+
CALL p1();
29+
INSERT INTO t1 VALUES(12);
30+
CALL p2();
31+
DROP TABLE t1;
32+
DROP TABLE t2;
33+
DROP PROCEDURE p1;
34+
DROP PROCEDURE p2;
35+
include/rpl_end.inc

‎mysql-test/suite/rds/t/feature_relax_gtid_limit_procedure-master.opt‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
--gtid-mode=on --enforce-gtid-consistency --log-slave-updates --log-bin --binlog-format=row --rds_allow_unsafe_stmt_with_gtid=on
2+

‎mysql-test/suite/rds/t/feature_relax_gtid_limit_procedure-slave.opt‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,2 @@
1+
--gtid-mode=on --enforce-gtid-consistency --log-slave-updates --log-bin --binlog-format=row --rds_allow_unsafe_stmt_with_gtid=on
2+

‎mysql-test/suite/rds/t/feature_relax_gtid_limit_procedure.test‎

Lines changed: 52 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,52 @@
1+
--source include/master-slave.inc
2+
--source include/have_gtid.inc
3+
--source include/have_binlog_format_row.inc
4+
connection master;
5+
6+
# create procedure use variables in CTS
7+
delimiter //;
8+
CREATE PROCEDURE p1()
9+
BEGIN
10+
11+
DECLARE id INT;
12+
13+
DROP TABLE IF EXISTS t1;
14+
15+
CREATE TABLE t1
16+
SELECT
17+
id AS t_id;
18+
END//
19+
20+
# create procedure use temporary table in CTS
21+
CREATE PROCEDURE p2()
22+
BEGIN
23+
24+
DECLARE id INT;
25+
26+
DROP TABLE IF EXISTS tmp_value;
27+
CREATE TEMPORARY TABLE tmp_value(a INT);
28+
INSERT INTO tmp_value VALUES(100);
29+
30+
DROP TABLE IF EXISTS t2;
31+
CREATE TABLE t2
32+
SELECT
33+
id AS t_id,
34+
a as value
35+
FROM tmp_value;
36+
37+
DROP TABLE IF EXISTS tmp_value;
38+
END//
39+
40+
delimiter ;//
41+
42+
CALL p1();
43+
INSERT INTO t1 VALUES(12);
44+
45+
CALL p2();
46+
47+
DROP TABLE t1;
48+
DROP TABLE t2;
49+
DROP PROCEDURE p1;
50+
DROP PROCEDURE p2;
51+
52+
--source include/rpl_end.inc

0 commit comments

Comments
 (0)