Debian Codegraph @

mysql-test/suite/galera/t/ev51914.test

raw
1 # Disable SAVEPOINT and ROLLBACK TO SAVEPOINT in SP, SF, TR.
2
3 --source include/galera_cluster.inc
4 --source include/have_innodb.inc
5
6
7 --connection node_1
8 --echo SAVEPOINT in a stored function should be forbidden
9 --delimiter |
10 CREATE FUNCTION f1 () RETURNS INT BEGIN
11 SAVEPOINT s;
12 RETURN 1;
13 END|
14 --delimiter ;
15
16 SELECT f1();
17
18 DROP FUNCTION f1;
19
20 --echo ROLLBACK TO SAVEPOINT in a stored function should be forbidden
21 --delimiter |
22 CREATE FUNCTION f2 () RETURNS INT BEGIN
23 ROLLBACK TO SAVEPOINT s;
24 RETURN 1;
25 END|
26 --delimiter ;
27
28 BEGIN;
29 SAVEPOINT s;
30 --error ER_SP_DOES_NOT_EXIST
31 SELECT f2();
32 COMMIT;
33
34 DROP FUNCTION f2;
35
36 BEGIN;
37 SAVEPOINT S;
38 ROLLBACK TO SAVEPOINT S;
39 COMMIT;
40
41
42 CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
43 CREATE TABLE t2 (a INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
44 INSERT INTO t1 values (110), (111), (112), (113), (114);
45
46 --echo Direct SAVEPOINT in a trigger should be forbidden
47 --connection node_2
48 CREATE TRIGGER i1_t1 BEFORE INSERT ON t1 FOR EACH ROW SAVEPOINT s;
49
50 --connection node_1
51 INSERT INTO t1 VALUES (1);
52 DROP TRIGGER i1_t1;
53
54 CREATE TRIGGER i2_t1 AFTER INSERT ON t1 FOR EACH ROW SAVEPOINT s;
55 INSERT INTO t1 VALUES (2);
56 DROP TRIGGER i2_t1;
57
58 INSERT INTO t1 VALUES (3);
59 CREATE TRIGGER u1_t1 BEFORE UPDATE ON t1 FOR EACH ROW SAVEPOINT s;
60 UPDATE t1 SET a=4 WHERE a=3;
61 DROP TRIGGER u1_t1;
62
63 CREATE TRIGGER u2_t1 AFTER UPDATE ON t1 FOR EACH ROW SAVEPOINT s;
64 UPDATE t1 SET a=4 WHERE a=3;
65 DROP TRIGGER u2_t1;
66
67 CREATE TRIGGER d1_t1 BEFORE DELETE ON t1 FOR EACH ROW SAVEPOINT s;
68 DELETE FROM t1;
69 DROP TRIGGER d1_t1;
70
71 CREATE TRIGGER d1_t1 AFTER DELETE ON t1 FOR EACH ROW SAVEPOINT s;
72 DELETE FROM t1;
73 DROP TRIGGER d1_t1;
74
75 --echo SAVEPOINT in a compound statement in a trigger should be forbidden
76 --delimiter |
77 CREATE TRIGGER i3_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
78 SAVEPOINT s;
79 END|
80 --delimiter ;
81 INSERT INTO t1 VALUES (5);
82 DROP TRIGGER i3_t1;
83
84 --echo SAVEPOINT in a PS call in a trigger should be forbidden
85 # echo handled by SAVEPOINT forbidden in PS
86 --delimiter |
87 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
88 CREATE TRIGGER i4_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
89 PREPARE set_savepoint FROM "SAVEPOINT s";
90 EXECUTE set_savepoint;
91 DEALLOCATE PREPARE set_savepoint;
92 END|
93 --delimiter ;
94
95 --connection node_2
96 --echo SAVEPOINT in SP called from a trigger should be forbidden
97 --delimiter |
98 CREATE PROCEDURE p1() BEGIN
99 SAVEPOINT s;
100 END|
101 --delimiter ;
102 --connection node_1
103 CREATE TRIGGER i5_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1;
104 INSERT INTO t1 VALUES (6);
105 DROP TRIGGER i5_t1;
106
107 --echo SAVEPOINT in a SP called from a PS called from a trigger be forbidden
108 # echo handled by SAVEPOINT forbidden in PS
109 PREPARE call_p1 FROM "CALL p1";
110 --error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
111 CREATE TRIGGER i6_t1 BEFORE INSERT ON t1 FOR EACH ROW EXECUTE call_p1;
112
113 --echo SAVEPOINT in a function called from a trigger should be forbidden
114 --delimiter |
115 CREATE FUNCTION f1 () RETURNS INT BEGIN
116 SAVEPOINT s;
117 RETURN 1;
118 END|
119 --delimiter ;
120 CREATE TRIGGER i7_t1 BEFORE INSERT ON t1 FOR EACH ROW SET @foo = f1();
121 INSERT INTO t1 VALUES (7);
122 DROP TRIGGER i7_t1;
123
124 --echo SAVEPOINT in a SP called from a SP called from a trigger should be forbidden
125 --delimiter |
126 CREATE PROCEDURE p2() BEGIN
127 CALL p1();
128 END|
129 --delimiter ;
130 CREATE TRIGGER i8_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p2;
131 INSERT INTO t1 VALUES (8);
132 DROP TRIGGER i8_t1;
133
134 --echo SAVEPOINT in a SP called from a trigger called from a SP should be forbidden
135 CREATE TRIGGER i9_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1;
136 --delimiter |
137 CREATE PROCEDURE p3() BEGIN
138 INSERT INTO t1 VALUES (9);
139 END|
140 --delimiter ;
141 CALL p3();
142 DROP TRIGGER i9_t1;
143
144 --echo ROLLBACK TO SAVEPOINT in trigger as a trivial statement should be forbidden
145 # Trigger activation creates a new savepoint level, making the earlier levels
146 # inaccessible. Thus forbidding SAVEPOINT should be enough as then there is
147 # no valid savepoint to pass to ROLLBACK TO SAVEPOINT, but we forbid it once
148 # more just in case.
149 CREATE TRIGGER i4_t1 BEFORE INSERT ON t1 FOR EACH ROW ROLLBACK TO SAVEPOINT s;
150 BEGIN;
151 SAVEPOINT s;
152 --error ER_SP_DOES_NOT_EXIST
153 INSERT INTO t1 VALUES (5);
154 COMMIT;
155 DROP TRIGGER i4_t1;
156
157 --echo ROLLBACK TO SAVEPOINT in a trigger in a SP call should be forbidden
158 --delimiter |
159 CREATE PROCEDURE p4() BEGIN
160 ROLLBACK TO SAVEPOINT s;
161 END|
162 --delimiter ;
163 CREATE TRIGGER i5_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p4;
164 BEGIN;
165 SAVEPOINT s;
166 --error ER_SP_DOES_NOT_EXIST
167 INSERT INTO t1 VALUES (6);
168 COMMIT;
169 DROP TRIGGER i5_t1;
170
171 --echo SAVEPOINT in a SP next to a trigger should work
172 CREATE TRIGGER i6_t1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = NEW.a + 1;
173 --delimiter |
174 CREATE PROCEDURE p5() BEGIN
175 SAVEPOINT s;
176 INSERT INTO t1 VALUES (10);
177 ROLLBACK TO SAVEPOINT s;
178 END|
179 --delimiter ;
180 BEGIN;
181 CALL p5();
182 COMMIT;
183 DROP TRIGGER i6_t1;
184
185 --connection node_2
186 delimiter |;
187 create trigger t1 before insert on t1 for each row
188 begin
189 insert into t2 values (NULL);
190 end|
191 delimiter ;|
192
193 --connection node_1
194 INSERT INTO t1 VALUES (201), (202), (203);
195
196 --connection node_1
197 SELECT * FROM t1;
198 SELECT COUNT(*) FROM t2;
199
200 --connection node_2
201 SELECT * FROM t1;
202 SELECT COUNT(*) FROM t2;
203
204 --connection node_1
205 DEALLOCATE PREPARE call_p1;
206
207 --connection node_2
208 DROP TABLE t1, t2;
209 DROP PROCEDURE p1;
210 DROP PROCEDURE p2;
211 DROP PROCEDURE p3;
212 DROP PROCEDURE p4;
213 DROP PROCEDURE p5;
214 DROP FUNCTION f1;