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
133
134
135
136
137
138
|
# Tests for the performance schema
# =============
# DOCUMENTATION
# =============
# Verify critical stages of a statement
#
# The tests are written with the following helpers:
# - include/stage_setup.inc
# - include/stage_cleanup.inc
#
# Helpers are intended to be used as follows.
#
# A Typical test t/stage_xxx.test will consist of:
# --source ../include/stage_setup.inc
# ... test specific payload ...
# --source ../include/stage_cleanup.inc
# and a t/stage_xxx-master.opt file
#
# ==============================
# HELPER include/stage_setup.inc
# ==============================
--source include/not_embedded.inc
--source include/have_perfschema.inc
--source include/no_protocol.inc
--disable_query_log
create user user1@localhost;
grant ALL on *.* to user1@localhost;
create user user2@localhost;
grant ALL on *.* to user2@localhost;
create user user3@localhost;
grant ALL on *.* to user3@localhost;
create user user4@localhost;
grant ALL on *.* to user4@localhost;
flush privileges;
# Save the setup
--disable_warnings
drop table if exists test.setup_actors;
drop table if exists test.t1;
--enable_warnings
create table test.t1(a varchar(64));
create table test.setup_actors as
select * from performance_schema.setup_actors;
# Only instrument the user connections
truncate table performance_schema.setup_actors;
insert into performance_schema.setup_actors
set host= 'localhost', user= 'user1', role= '%';
insert into performance_schema.setup_actors
set host= 'localhost', user= 'user2', role= '%';
insert into performance_schema.setup_actors
set host= 'localhost', user= 'user3', role= '%';
insert into performance_schema.setup_actors
set host= 'localhost', user= 'user4', role= '%';
update performance_schema.threads set instrumented='NO';
# Only instrument a few events of each kind
update performance_schema.setup_instruments set enabled='YES', timed='YES';
# Start from a known clean state, to avoid noise from previous tests
flush tables;
flush status;
truncate performance_schema.events_stages_summary_by_thread_by_event_name;
truncate performance_schema.events_stages_summary_global_by_event_name;
truncate performance_schema.events_stages_history;
truncate performance_schema.events_stages_history_long;
truncate performance_schema.events_statements_summary_by_thread_by_event_name;
truncate performance_schema.events_statements_summary_global_by_event_name;
truncate performance_schema.events_statements_history;
truncate performance_schema.events_statements_history_long;
--disable_warnings
drop procedure if exists dump_thread;
drop procedure if exists dump_one_thread;
--enable_warnings
delimiter $$;
create procedure dump_thread()
begin
call dump_one_thread('user1');
call dump_one_thread('user2');
call dump_one_thread('user3');
call dump_one_thread('user4');
end
$$
create procedure dump_one_thread(in username varchar(64))
begin
declare my_thread_id int;
declare my_statement_id int;
set my_thread_id = (select thread_id from performance_schema.threads
where processlist_user=username);
if (my_thread_id is not null) then
begin
# Dump the current statement for this thread
select username, event_name, sql_text
from performance_schema.events_statements_current
where thread_id = my_thread_id;
# Get the current statement
set my_statement_id = (select event_id from
performance_schema.events_statements_current
where thread_id = my_thread_id);
# Dump the stages for this statement
select username, event_name, nesting_event_type
from performance_schema.events_stages_current
where thread_id = my_thread_id
and nesting_event_id = my_statement_id
order by event_id asc;
select username, event_name, nesting_event_type
from performance_schema.events_stages_history
where thread_id = my_thread_id
and nesting_event_id = my_statement_id
order by event_id asc;
end;
else
select username, "not found" as status;
end if;
end
$$
delimiter ;$$
--enable_query_log
|
