Debian Codegraph @

mysql-test/suite/perfschema/include/stage_setup.inc

raw
1 # Tests for the performance schema
2
3 # =============
4 # DOCUMENTATION
5 # =============
6
7 # Verify critical stages of a statement
8 #
9 # The tests are written with the following helpers:
10 # - include/stage_setup.inc
11 # - include/stage_cleanup.inc
12 #
13 # Helpers are intended to be used as follows.
14 #
15 # A Typical test t/stage_xxx.test will consist of:
16 # --source ../include/stage_setup.inc
17 # ... test specific payload ...
18 # --source ../include/stage_cleanup.inc
19 # and a t/stage_xxx-master.opt file
20 #
21 # ==============================
22 # HELPER include/stage_setup.inc
23 # ==============================
24
25 --source include/not_embedded.inc
26 --source include/have_perfschema.inc
27 --source include/no_protocol.inc
28
29 --disable_query_log
30
31 create user user1@localhost;
32 grant ALL on *.* to user1@localhost;
33 create user user2@localhost;
34 grant ALL on *.* to user2@localhost;
35 create user user3@localhost;
36 grant ALL on *.* to user3@localhost;
37 create user user4@localhost;
38 grant ALL on *.* to user4@localhost;
39
40 flush privileges;
41
42 # Save the setup
43
44 --disable_warnings
45 drop table if exists test.setup_actors;
46 drop table if exists test.t1;
47 --enable_warnings
48
49 create table test.t1(a varchar(64));
50
51 create table test.setup_actors as
52 select * from performance_schema.setup_actors;
53
54 # Only instrument the user connections
55 truncate table performance_schema.setup_actors;
56 insert into performance_schema.setup_actors
57 set host= 'localhost', user= 'user1', role= '%';
58 insert into performance_schema.setup_actors
59 set host= 'localhost', user= 'user2', role= '%';
60 insert into performance_schema.setup_actors
61 set host= 'localhost', user= 'user3', role= '%';
62 insert into performance_schema.setup_actors
63 set host= 'localhost', user= 'user4', role= '%';
64
65 update performance_schema.threads set instrumented='NO';
66
67 # Only instrument a few events of each kind
68 update performance_schema.setup_instruments set enabled='YES', timed='YES';
69
70 # Start from a known clean state, to avoid noise from previous tests
71 flush tables;
72 flush global status;
73 truncate performance_schema.events_stages_summary_by_thread_by_event_name;
74 truncate performance_schema.events_stages_summary_global_by_event_name;
75 truncate performance_schema.events_stages_history;
76 truncate performance_schema.events_stages_history_long;
77 truncate performance_schema.events_statements_summary_by_thread_by_event_name;
78 truncate performance_schema.events_statements_summary_global_by_event_name;
79 truncate performance_schema.events_statements_history;
80 truncate performance_schema.events_statements_history_long;
81
82 --disable_warnings
83 drop procedure if exists dump_thread;
84 drop procedure if exists dump_one_thread;
85 --enable_warnings
86
87 delimiter $$;
88
89 create procedure dump_thread()
90 begin
91 call dump_one_thread('user1');
92 call dump_one_thread('user2');
93 call dump_one_thread('user3');
94 call dump_one_thread('user4');
95 end
96 $$
97
98 create procedure dump_one_thread(in username varchar(64))
99 begin
100 declare my_thread_id int;
101 declare my_statement_id int;
102
103 set my_thread_id = (select thread_id from performance_schema.threads
104 where processlist_user=username);
105
106 if (my_thread_id is not null) then
107 begin
108 # Dump the current statement for this thread
109 select username, event_name, sql_text
110 from performance_schema.events_statements_current
111 where thread_id = my_thread_id;
112
113 # Get the current statement
114 set my_statement_id = (select event_id from
115 performance_schema.events_statements_current
116 where thread_id = my_thread_id);
117
118 # Dump the stages for this statement
119 select username, event_name, nesting_event_type
120 from performance_schema.events_stages_current
121 where thread_id = my_thread_id
122 and nesting_event_id = my_statement_id
123 order by event_id asc;
124 # 1. Ignore query cache as it may not be enabled
125 # 2. MDL wait consists of short 1 second waits (this is not configurable)
126 # repeated until lock_wait_timeout is reached. The stage is changed
127 # to Waiting and back every second. To have predictable result here
128 # the query filters all sequences of X, "Waiting for MDL", X,
129 # leaving just X.
130 with h as (
131 select event_id, username, event_name, nesting_event_type,
132 lag(event_name) over (order by event_id) = lead(event_name) over (order by event_id)
133 and event_name = "stage/sql/Waiting for stored function metadata lock" as v1,
134 lag(event_name, 2) over (order by event_id) = event_name
135 and lag(event_name, 1) over (order by event_id) = "stage/sql/Waiting for stored function metadata lock" as v2
136 from performance_schema.events_stages_history
137 where thread_id = my_thread_id
138 and nesting_event_id = my_statement_id and
139 event_name not like "%query cache%"
140 ) select username, event_name, nesting_event_type from h where v1 is not true and v2 is not true
141 order by event_id asc;
142 end;
143 else
144 select username, "not found" as status;
145 end if;
146 end
147 $$
148
149 delimiter ;$$
150
151 --enable_query_log