1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
|
#----------------------------------------------------------
# Concurrency check for instrumentation of stored programs
#----------------------------------------------------------
--source include/big_test.inc
--source include/not_embedded.inc
--source include/have_perfschema.inc
--source include/no_protocol.inc
TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
TRUNCATE TABLE performance_schema.events_statements_history;
--echo # concurrency check through multi connections
# Save the initial number of concurrent sessions
--source include/count_sessions.inc
CREATE DATABASE db1;
CREATE DATABASE db2;
CREATE DATABASE db3;
# connection 1
connect (con1,localhost,root,,db1);
USE db1;
CREATE TABLE t1(
i INT NOT NULL
);
LOAD DATA INFILE '../../std_data/wl5766_data.txt' INTO TABLE t1;
DELIMITER |;
CREATE PROCEDURE proc()
BEGIN
INSERT INTO t1 SELECT * FROM t1;
END|
DELIMITER ;|
--send CALL proc();
# connection 2
connect (con2,localhost,root,,db2);
USE db2;
CREATE TABLE t2(
i INT NOT NULL
);
LOAD DATA INFILE '../../std_data/wl5766_data.txt' INTO TABLE t2;
DELIMITER |;
CREATE FUNCTION addition(x INT, y INT) RETURNS INT
BEGIN
INSERT INTO t2 SELECT * FROM t2;
RETURN x+y;
END|
DELIMITER ;|
--send SELECT addition(1234,9876);
# connection 3
connect (con3,localhost,root,,db3);
USE db3;
CREATE TABLE t(
i INT NOT NULL,
j INT
);
CREATE TABLE t3(
i INT NOT NULL
);
LOAD DATA INFILE '../../std_data/wl5766_data.txt' INTO TABLE t3;
INSERT INTO t VALUES ( 10,1000 );
CREATE TRIGGER trg AFTER INSERT ON t FOR EACH ROW
INSERT INTO t3 SELECT * FROM t3;
--send INSERT INTO t VALUES ( 20,2000);
connection con1;
--reap;
connection con2;
--reap;
connection con3;
--reap;
connection default;
--let $wait_condition=select count(*)=3 from information_schema.processlist where command='sleep';
--source include/wait_condition.inc
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
FROM performance_schema.events_statements_summary_by_program
WHERE OBJECT_SCHEMA='db1' OR OBJECT_SCHEMA='db2' OR OBJECT_SCHEMA='db3'
ORDER BY OBJECT_SCHEMA, OBJECT_NAME;
SELECT EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA,
OBJECT_NAME, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL FROM
performance_schema.events_statements_history WHERE CURRENT_SCHEMA='db1'
OR CURRENT_SCHEMA='db2' OR CURRENT_SCHEMA='db3'
ORDER BY CURRENT_SCHEMA, OBJECT_NAME;
TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
TRUNCATE TABLE performance_schema.events_statements_history;
--echo # Clean-up
DROP PROCEDURE db1.proc;
DROP FUNCTION db2.addition;
DROP TRIGGER db3.trg;
DROP TABLE db1.t1;
DROP TABLE db2.t2;
DROP TABLE db3.t3;
DROP TABLE db3.t;
DROP DATABASE db1;
DROP DATABASE db2;
DROP DATABASE db3;
disconnect con1;
disconnect con2;
disconnect con3;
# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc
|
