2
DROP TABLE IF EXISTS t1, t2;
3
DROP VIEW IF EXISTS v1;
4
DROP PROCEDURE IF EXISTS stp_t;
7
# Create additional connections used through test
8
CONNECT (root1, localhost, root,,);
10
CONNECT (root2, localhost, root,,);
12
--echo connection default;
14
eval CREATE TABLE t1 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=$engine;
15
INSERT INTO t1 VALUES (1,123,1,123);
16
INSERT INTO t1 VALUES (2,124,2,124);
17
INSERT INTO t1 VALUES (3,125,3,125);
18
INSERT INTO t1 VALUES (4,126,4,126);
19
INSERT INTO t1 VALUES (5,127,5,127);
20
INSERT INTO t1 VALUES (6,128,6,128);
22
eval CREATE TABLE t2 (k INT NOT NULL PRIMARY KEY, i INT, j INT, l INT) ENGINE=$engine;
23
INSERT INTO t2 VALUES (1,123,1,123);
24
INSERT INTO t2 VALUES (2,124,2,124);
25
INSERT INTO t2 VALUES (3,125,3,125);
26
INSERT INTO t2 VALUES (4,126,4,126);
27
INSERT INTO t2 VALUES (5,127,5,127);
28
INSERT INTO t2 VALUES (6,128,6,128);
30
CREATE VIEW v1 AS SELECT t1.i from t1;
33
CREATE PROCEDURE stp_t (IN p1 int, IN p2 int) MODIFIES SQL DATA
35
UPDATE t2 SET i = p2 WHERE i = p1;
36
UPDATE v1 SET i = p2 WHERE i = p1;
37
SELECT * FROM v1 ORDER BY i;
38
SELECT * FROM t1 ORDER BY t1.k;
39
SELECT * FROM t2 ORDER BY t2.k;
46
SELECT @@global.tx_isolation;
49
--echo connection root1;
51
CALL stp_t (125, 225);
53
--echo connection root2;
55
CALL stp_t (127, 227);
57
--echo connection default;
59
CALL stp_t (123, 223);
61
--echo connection root1;
63
CALL stp_t (126, 226);
65
--echo connection root2;
67
CALL stp_t (128, 228);
69
--echo connection default;
71
CALL stp_t (124, 224);
73
--echo connection root1;
75
DELETE FROM t1 WHERE t1.i=226;
76
SELECT * FROM t1 ORDER BY t1.k;
77
SELECT * FROM t2 ORDER BY t2.k;
79
--echo connection root2;
81
DELETE FROM t1 WHERE t1.i=228;
82
SELECT * FROM t1 ORDER BY t1.k;
83
SELECT * FROM t2 ORDER BY t2.k;
85
--echo connection default;
87
DELETE FROM t1 WHERE t1.i=224;
88
SELECT * FROM t1 ORDER BY t1.k;
89
SELECT * FROM t2 ORDER BY t2.k;
92
--echo connection root1;
96
--echo connection root1;
100
--echo connection default;
102
SELECT * FROM v1 ORDER BY i;
103
SELECT * FROM t1 ORDER BY t1.k;
104
SELECT * FROM t2 ORDER BY t2.k;
106
--echo connection root1;
108
SELECT * FROM v1 ORDER BY i;
109
SELECT * FROM t1 ORDER BY t1.k;
110
SELECT * FROM t2 ORDER BY t2.k;
113
--echo connection root2;
115
SELECT * FROM v1 ORDER BY i;
116
SELECT * FROM t1 ORDER BY t1.k;
117
SELECT * FROM t2 ORDER BY t2.k;
120
--echo connection default;
124
DROP PROCEDURE stp_t;