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

# Disable SAVEPOINT and ROLLBACK TO SAVEPOINT in SP, SF, TR.

--source include/galera_cluster.inc
--source include/have_innodb.inc


--connection node_1
--echo SAVEPOINT in a stored function should be forbidden
--delimiter |
CREATE FUNCTION f1 () RETURNS INT BEGIN
       SAVEPOINT s;
       RETURN 1;
END|
--delimiter ;

SELECT f1();

DROP FUNCTION f1;

--echo ROLLBACK TO SAVEPOINT in a stored function should be forbidden
--delimiter |
CREATE FUNCTION f2 () RETURNS INT BEGIN
       ROLLBACK TO SAVEPOINT s;
       RETURN 1;
END|
--delimiter ;

BEGIN;
SAVEPOINT s;
--error ER_SP_DOES_NOT_EXIST
SELECT f2();
COMMIT;

DROP FUNCTION f2;

BEGIN;
SAVEPOINT S;
ROLLBACK TO SAVEPOINT S;
COMMIT;


CREATE TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE t2 (a INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
INSERT INTO t1 values (110), (111), (112), (113), (114);

--echo Direct SAVEPOINT in a trigger should be forbidden
--connection node_2
CREATE TRIGGER i1_t1 BEFORE INSERT ON t1 FOR EACH ROW SAVEPOINT s;

--connection node_1
INSERT INTO t1 VALUES (1);
DROP TRIGGER i1_t1;

CREATE TRIGGER i2_t1 AFTER INSERT ON t1 FOR EACH ROW SAVEPOINT s;
INSERT INTO t1 VALUES (2);
DROP TRIGGER i2_t1;

INSERT INTO t1 VALUES (3);
CREATE TRIGGER u1_t1 BEFORE UPDATE ON t1 FOR EACH ROW SAVEPOINT s;
UPDATE t1 SET a=4 WHERE a=3;
DROP TRIGGER u1_t1;

CREATE TRIGGER u2_t1 AFTER UPDATE ON t1 FOR EACH ROW SAVEPOINT s;
UPDATE t1 SET a=4 WHERE a=3;
DROP TRIGGER u2_t1;

CREATE TRIGGER d1_t1 BEFORE DELETE ON t1 FOR EACH ROW SAVEPOINT s;
DELETE FROM t1;
DROP TRIGGER d1_t1;

CREATE TRIGGER d1_t1 AFTER DELETE ON t1 FOR EACH ROW SAVEPOINT s;
DELETE FROM t1;
DROP TRIGGER d1_t1;

--echo SAVEPOINT in a compound statement in a trigger should be forbidden
--delimiter |
CREATE TRIGGER i3_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
       SAVEPOINT s;
END|
--delimiter ;
INSERT INTO t1 VALUES (5);
DROP TRIGGER i3_t1;

--echo SAVEPOINT in a PS call in a trigger should be forbidden
# echo handled by SAVEPOINT forbidden in PS
--delimiter |
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER i4_t1 BEFORE INSERT ON t1 FOR EACH ROW BEGIN
       PREPARE set_savepoint FROM "SAVEPOINT s";
       EXECUTE set_savepoint;
       DEALLOCATE PREPARE set_savepoint;
END|
--delimiter ;

--connection node_2
--echo SAVEPOINT in SP called from a trigger should be forbidden
--delimiter |
CREATE PROCEDURE p1() BEGIN
       SAVEPOINT s;
END|
--delimiter ;
--connection node_1
CREATE TRIGGER i5_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1;
INSERT INTO t1 VALUES (6);
DROP TRIGGER i5_t1;

--echo SAVEPOINT in a SP called from a PS called from a trigger be forbidden
# echo handled by SAVEPOINT forbidden in PS
PREPARE call_p1 FROM "CALL p1";
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
CREATE TRIGGER i6_t1 BEFORE INSERT ON t1 FOR EACH ROW EXECUTE call_p1;

--echo SAVEPOINT in a function called from a trigger should be forbidden
--delimiter |
CREATE FUNCTION f1 () RETURNS INT BEGIN
       SAVEPOINT s;
       RETURN 1;
END|
--delimiter ;
CREATE TRIGGER i7_t1 BEFORE INSERT ON t1 FOR EACH ROW SET @foo = f1();
INSERT INTO t1 VALUES (7);
DROP TRIGGER i7_t1;

--echo SAVEPOINT in a SP called from a SP called from a trigger should be forbidden
--delimiter |
CREATE PROCEDURE p2() BEGIN
       CALL p1();
END|
--delimiter ;
CREATE TRIGGER i8_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p2;
INSERT INTO t1 VALUES (8);
DROP TRIGGER i8_t1;

--echo SAVEPOINT in a SP called from a trigger called from a SP should be forbidden
CREATE TRIGGER i9_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p1;
--delimiter |
CREATE PROCEDURE p3() BEGIN
       INSERT INTO t1 VALUES (9);
END|
--delimiter ;
CALL p3();
DROP TRIGGER i9_t1;

--echo ROLLBACK TO SAVEPOINT in trigger as a trivial statement should be forbidden
# Trigger activation creates a new savepoint level, making the earlier levels
# inaccessible. Thus forbidding SAVEPOINT should be enough as then there is
# no valid savepoint to pass to ROLLBACK TO SAVEPOINT, but we forbid it once
# more just in case.
CREATE TRIGGER i4_t1 BEFORE INSERT ON t1 FOR EACH ROW ROLLBACK TO SAVEPOINT s;
BEGIN;
SAVEPOINT s;
--error ER_SP_DOES_NOT_EXIST
INSERT INTO t1 VALUES (5);
COMMIT;
DROP TRIGGER i4_t1;

--echo ROLLBACK TO SAVEPOINT in a trigger in a SP call should be forbidden
--delimiter |
CREATE PROCEDURE p4() BEGIN
       ROLLBACK TO SAVEPOINT s;
END|
--delimiter ;
CREATE TRIGGER i5_t1 BEFORE INSERT ON t1 FOR EACH ROW CALL p4;
BEGIN;
SAVEPOINT s;
--error ER_SP_DOES_NOT_EXIST
INSERT INTO t1 VALUES (6);
COMMIT;
DROP TRIGGER i5_t1;

--echo SAVEPOINT in a SP next to a trigger should work
CREATE TRIGGER i6_t1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a = NEW.a + 1;
--delimiter |
CREATE PROCEDURE p5() BEGIN
       SAVEPOINT s;
       INSERT INTO t1 VALUES (10);
       ROLLBACK TO SAVEPOINT s;
END|
--delimiter ;
BEGIN;
CALL p5();
COMMIT;
DROP TRIGGER i6_t1;

--connection node_2
delimiter |;
create trigger t1 before insert on t1 for each row
begin
    insert into t2 values (NULL);
end|
delimiter ;|

--connection node_1 
INSERT INTO t1 VALUES (201), (202), (203);

--connection node_1 
SELECT * FROM t1;
SELECT COUNT(*) FROM t2;

--connection node_2
SELECT * FROM t1;
SELECT COUNT(*) FROM t2;

--connection node_1
DEALLOCATE PREPARE call_p1;

--connection node_2
DROP TABLE t1, t2;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP PROCEDURE p3;
DROP PROCEDURE p4;
DROP PROCEDURE p5;
DROP FUNCTION f1;