Debian Codegraph @

mysql-test/suite/group_replication/t/gr_serializable_isolation.test

raw
1 ################################################################################
2 # Aim of the testcase is to check that GR successfully blocks the DMLs run with
3 # 'SERIALIZABLE' isolation mode.
4 # Test:
5 # 0. Start a server with GR.
6 # 1. Create table t1, temporary table temp1
7 # 2. Set isolation level to 'SERIALIZABLE'
8 # 3. Verify that DDLs are not blocked irrespective of trasaction isolation.
9 # 4. Verify that DMLs are blocked if isolation level is 'SERIALIZABLE'
10 # 5. Set isolation level to 'REPEATABLE-READ' and check that DMLs are allowed.
11 # 6. Clean-up
12 ################################################################################
13
14 --source include/set_privilege_checks_user_as_system_user.inc
15 --source include/have_group_replication_plugin.inc
16 --source include/group_replication.inc
17 --echo #
18 --echo # Bug#21225075 GROUP REPLICATION MAY CAUSE INCONSISTENCIES IF SERIALIZABLE IS ENABLED
19 --echo #
20 # Check that Group Replication does not allow any DML transactions if they are run with
21 # serializable isolation mode.
22
23 --let $rpl_connection_name= server1
24 --source include/rpl_connection.inc
25
26 SET SESSION sql_log_bin= 0;
27 CALL mtr.add_suppression("Transaction isolation level \\(tx_isolation\\) is set to SERIALIZABLE, which is not compatible with Group Replication");
28 SET SESSION sql_log_bin= 1;
29
30 --echo
31 --echo ## 1. Create table and insert some sample data to do some DML operations
32 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
33 INSERT INTO t1 VALUE (1);
34 CREATE TEMPORARY TABLE temp1 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
35 INSERT INTO temp1 VALUE (1);
36
37 --echo
38 --echo ## 2. Change Isolation level to SERIALIZABLE.
39 SET @transaction_isolation_orig = @@session.transaction_isolation;
40 SET SESSION transaction_isolation='SERIALIZABLE';
41
42 --echo
43 --echo ## 3. DDLs are allowed irrespective of transaction isolation value
44 CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
45
46 --echo
47 --echo ## 4. DMLs should be blocked if isolation level is SERIALIZABLE.
48 --error ER_BEFORE_DML_VALIDATION_ERROR
49 INSERT INTO t1 VALUES (2);
50
51 --error ER_BEFORE_DML_VALIDATION_ERROR
52 UPDATE t1 SET c1=c1+1;
53
54 --error ER_BEFORE_DML_VALIDATION_ERROR
55 DELETE FROM t1;
56
57 # Check that even DML operations on temporary tables are disallowed
58 # when isolation level is SERIALIZABLE
59 --error ER_BEFORE_DML_VALIDATION_ERROR
60 INSERT INTO temp1 VALUES (2);
61
62 --error ER_BEFORE_DML_VALIDATION_ERROR
63 UPDATE temp1 SET c1=c1+1;
64
65 --error ER_BEFORE_DML_VALIDATION_ERROR
66 DELETE FROM temp1;
67
68 DELIMITER |;
69
70 # Verify that if a procedure/function contains restricted DML operation, it
71 # will also throw error
72 CREATE PROCEDURE dml_procedure()
73 BEGIN
74 INSERT INTO t1 VALUES (2);
75 END |
76
77 CREATE FUNCTION dml_function() RETURNS INTEGER MODIFIES SQL DATA
78 BEGIN
79 INSERT INTO t1 VALUES (2);
80 RETURN 1;
81 END|
82
83 DELIMITER ;|
84
85 --error ER_BEFORE_DML_VALIDATION_ERROR
86 CALL dml_procedure();
87
88 --error ER_BEFORE_DML_VALIDATION_ERROR
89 SELECT dml_function();
90
91
92 START TRANSACTION;
93 SET SESSION transaction_isolation='REPEATABLE-READ';
94 # Isolation value change is effective only from the next transaction.
95 # So this insert should be disallowed as the current transaction's
96 # isolation level is 'SERIALIZABLE';
97 --error ER_BEFORE_DML_VALIDATION_ERROR
98 INSERT INTO t1 VALUES (2);
99 COMMIT;
100
101 --let $assert_text= 'transaction_isolation should be REPEATABLE-READ'
102 --let $assert_cond= "[SELECT @@SESSION.transaction_isolation]" = "REPEATABLE-READ";
103 --source include/assert.inc
104
105 START TRANSACTION;
106 SET SESSION transaction_isolation='SERIALIZABLE';
107 --echo
108 --echo ## 5. Isolation value change is effective only from the next transaction.
109 --echo ## So this insert should be allowed as the current transaction's
110 --echo ## isolation level is 'REPEATABLE-READ';
111 INSERT INTO t1 VALUES (2);
112 COMMIT;
113
114 --echo
115 --echo ## 6. Cleaning up
116 DROP TABLE t1;
117 DROP TABLE t2;
118 DROP TABLE temp1;
119 DROP PROCEDURE dml_procedure;
120 DROP FUNCTION dml_function;
121 SET SESSION transaction_isolation=@transaction_isolation_orig;
122
123 --source include/group_replication_end.inc