Debian Codegraph @

mysql-test/suite/binlog/t/binlog_transaction_write_set_savepoint_level.test

raw
1 ###############################################################################
2 # This test validates that a new level is created when a stored function or
3 # a trigger is called. The savepoint on previous level become unavailable
4 # and thus do not cause conflict with savepoints on new level. When function
5 # or trigger terminates, any savepoints it created are released and the previous
6 # level is restored.
7 #
8 # Test:
9 # 0. Create four tables (t1, t2, t3, t4) to insert data
10 # 1. Create a trigger t1_tr1, will create a SAVEPOINT S1 and insert values on t2
11 # 2. Create a function f1, will insert on t2, create a SAVEPOINT S1, insert on
12 # t1 and execute ROLLBACK TO S1
13 # 3. Create a transaction, define a SAVEPOINT S1 insert values on T1 and execute
14 # a rollback to S1
15 # 4. Call twice the function f1
16 # 5. Create trigger t3_tr1 with exit handler which will rollback to savepoint
17 # created inside the trigger.
18 # 6. Print contents of tables
19 # 7. Execute asserts to confirm table data
20 # 8. Clean up
21 #
22 ###############################################################################
23
24 --source include/have_debug.inc
25 --source include/have_binlog_format_row.inc
26
27 SET @save_session_debug= @@SESSION.debug;
28
29 # transaction_write_set_savepoint_level guarantees that when a SAVEPOINT is
30 # created the level is empty
31 SET @@SESSION.debug = "+d,transaction_write_set_savepoint_level";
32
33 CREATE TABLE t1 (c1 INT NOT NULL PRIMARY KEY);
34 CREATE TABLE t2 (c1 INT NOT NULL PRIMARY KEY);
35 CREATE TABLE t3 (c1 INT NOT NULL PRIMARY KEY);
36 CREATE TABLE t4 (c1 INT NOT NULL PRIMARY KEY);
37
38 DELIMITER |;
39
40 #
41 # When executing a trigger is created a new empty savepoint level
42 #
43 CREATE TRIGGER t1_tr1 BEFORE INSERT ON t1 FOR EACH ROW
44 BEGIN
45 SAVEPOINT S1;
46 INSERT INTO t2 VALUES (NEW.c1);
47 END|
48
49 #
50 # When executing a function is created a new empty savepoint level
51 #
52 CREATE FUNCTION f1(x INT) RETURNS INT
53 BEGIN
54 INSERT INTO t2 VALUES (x);
55 SAVEPOINT S1;
56 INSERT INTO t1 VALUES (100);
57 ROLLBACK TO S1;
58
59 RETURN (1);
60 END|
61
62 DELIMITER ;|
63
64 BEGIN;
65
66 # S1 is the only savepoint on this level
67 SAVEPOINT S1;
68 INSERT INTO t1 VALUES (1);
69 # This will trigger t1_tr1 that will create a new empty savepoint level and
70 # there it will add a SAVEPOINT
71 ROLLBACK TO S1;
72
73 # The call to f1 will create a new empty savepoint level and there will add a
74 # SAVEPOINT
75 SELECT f1(102);
76
77 # Validate that when executing a rollback, it will be executed in the context
78 # of the function, the write set have size 2
79 SET @@SESSION.debug = "+d,transaction_write_set_size_2";
80
81 # This call to f1 will create a new empty savepoint level and there will add
82 # a SAVEPOINT
83 SELECT f1(101);
84
85 COMMIT;
86
87 SET @@SESSION.debug= @save_session_debug;
88
89 # Create trigger t3_tr1 with exit handler which will rollback to a savepoint
90 # created within the trigger.
91
92 DELIMITER |;
93 CREATE TRIGGER t3_tr1 AFTER INSERT ON t3 FOR EACH ROW
94 BEGIN
95 DECLARE EXIT HANDLER FOR SQLEXCEPTION
96 BEGIN
97 ROLLBACK TO SAVEPOINT inside_trigger;
98 INSERT INTO t4 VALUES (NEW.c1*20);
99 END;
100
101 SAVEPOINT inside_trigger;
102
103 INSERT INTO t4 VALUES (NEW.c1);
104
105 RELEASE SAVEPOINT inside_trigger;
106
107 END|
108 DELIMITER ;|
109
110 # Invoke t3_tr1 such that it will not invoke exit handler.
111 INSERT INTO t3 values (1);
112
113 # Invoke t3_tr1 such that it will invoke exit handler.
114 INSERT INTO t4 values (2);
115 INSERT INTO t3 values (2);
116
117 --query_vertical SELECT * FROM t1
118 --query_vertical SELECT * FROM t2
119 --query_vertical SELECT * FROM t3
120 --query_vertical SELECT * FROM t4
121
122 --let $assert_text= 'There are no values in table t1'
123 --let $assert_cond= [SELECT COUNT(*) AS count FROM t1, count, 1] = 0
124 --source include/assert.inc
125
126 --let $assert_text= 'There are 2 values in table t2'
127 --let $assert_cond= [SELECT COUNT(*) AS count FROM t2, count, 1] = 2
128 --source include/assert.inc
129
130 --let $assert_text= 'There are 2 values in table t3'
131 --let $assert_cond= [SELECT COUNT(*) AS count FROM t3, count, 1] = 2
132 --source include/assert.inc
133
134 --let $assert_text= 'There are 3 values in table t4'
135 --let $assert_cond= [SELECT COUNT(*) AS count FROM t4, count, 1] = 3
136 --source include/assert.inc
137
138 # Cleanup
139 DROP FUNCTION f1;
140 DROP TABLE t1;
141 DROP TABLE t2;
142 DROP TABLE t3;
143 DROP TABLE t4;