Debian Codegraph @

mysql-test/suite/perfschema/t/selects.test

raw
1 --source include/have_myisam.inc
2 --source include/force_myisam_default.inc
3 # Make some data that we can work on:
4
5 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
6
7 #
8 # Avoid noise collected from other threads running in the server
9 #
10 # Disable every thread
11 UPDATE performance_schema.setup_threads SET ENABLED='NO';
12 UPDATE performance_schema.threads SET INSTRUMENTED='NO';
13 # Except this session
14 UPDATE performance_schema.threads
15 SET INSTRUMENTED='YES' WHERE PROCESSLIST_ID = connection_id();
16
17 # Disable ALL table IO, to avoid generating events from the selects
18 # To be revised after WL#5342 PERFORMANCE SCHEMA SETUP OBJECTS
19
20 UPDATE performance_schema.setup_instruments SET enabled = 'NO', timed = 'NO'
21 where NAME='wait/io/table/sql/handler';
22
23 TRUNCATE TABLE performance_schema.events_waits_current;
24 TRUNCATE TABLE performance_schema.events_waits_history;
25 TRUNCATE TABLE performance_schema.events_waits_history_long;
26
27 # Now generate data with a known (predictable) payload
28
29 --disable_warnings
30 DROP TABLE IF EXISTS t1;
31 --enable_warnings
32 CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
33 ENGINE=MyISAM;
34 INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);
35
36 # ORDER BY, GROUP BY and HAVING
37
38 --replace_column 2 [NUM_BYTES]
39 SELECT OPERATION, SUM(NUMBER_OF_BYTES) AS TOTAL
40 FROM performance_schema.events_waits_history_long
41 GROUP BY OPERATION
42 HAVING TOTAL IS NOT NULL
43 ORDER BY OPERATION
44 LIMIT 1;
45
46 # Sub SELECT
47 --replace_column 1 [EVENT_ID]
48 SELECT EVENT_ID FROM performance_schema.events_waits_current
49 WHERE THREAD_ID IN
50 (SELECT THREAD_ID FROM performance_schema.threads)
51 AND EVENT_NAME IN
52 (SELECT NAME FROM performance_schema.setup_instruments
53 WHERE NAME LIKE "wait/synch/%")
54 LIMIT 1;
55
56 # JOIN
57
58 --skip_if_hypergraph # Does not call THD::LOCK_query_plan(), so no event.
59 --replace_column 1 [EVENT_ID]
60 SELECT DISTINCT EVENT_ID
61 FROM performance_schema.events_waits_current
62 JOIN performance_schema.events_waits_history USING (EVENT_ID)
63 JOIN performance_schema.events_waits_history_long USING (EVENT_ID)
64 ORDER BY EVENT_ID
65 LIMIT 1;
66
67 # Self JOIN
68
69 --replace_column 1 [THREAD_ID] 2 [EVENT_ID] 3 [EVENT_NAME] 4 [TIMER_WAIT]
70 SELECT t1.THREAD_ID, t2.EVENT_ID, t3.EVENT_NAME, t4.TIMER_WAIT
71 FROM performance_schema.events_waits_history t1
72 JOIN performance_schema.events_waits_history t2 USING (EVENT_ID)
73 JOIN performance_schema.events_waits_history t3 ON (t2.THREAD_ID = t3.THREAD_ID)
74 JOIN performance_schema.events_waits_history t4 ON (t3.EVENT_NAME = t4.EVENT_NAME)
75 ORDER BY t1.EVENT_ID, t2.EVENT_ID
76 LIMIT 5;
77
78 # UNION
79 --replace_column 1 [THREAD_ID] 2 [EVENT_ID]
80 SELECT THREAD_ID, EVENT_ID FROM (
81 SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_current
82 UNION
83 SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_history
84 UNION
85 SELECT THREAD_ID, EVENT_ID FROM performance_schema.events_waits_history_long
86 ) t1 ORDER BY THREAD_ID, EVENT_ID
87 LIMIT 5;
88
89 # EVENT
90
91 # Check that the event_scheduler is really running
92 --source include/running_event_scheduler.inc
93
94 --disable_warnings
95 DROP TABLE IF EXISTS t_event;
96 DROP EVENT IF EXISTS t_ps_event;
97 --enable_warnings
98 CREATE TABLE t_event AS
99 SELECT EVENT_ID FROM performance_schema.events_waits_current
100 WHERE 1 = 2;
101 CREATE EVENT t_ps_event
102 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
103 ON COMPLETION PRESERVE
104 DO INSERT INTO t_event
105 SELECT DISTINCT EVENT_ID
106 FROM performance_schema.events_waits_current
107 ORDER BY EVENT_ID
108 LIMIT 1;
109
110 # TRIGGER
111
112 ALTER TABLE t1 ADD COLUMN c INT;
113
114 --disable_warnings
115 DROP TRIGGER IF EXISTS t_ps_trigger;
116 --enable_warnings
117 delimiter |;
118
119 CREATE TRIGGER t_ps_trigger BEFORE INSERT ON t1
120 FOR EACH ROW BEGIN
121 SET NEW.c = (SELECT MAX(EVENT_ID)
122 FROM performance_schema.events_waits_current);
123 END;
124 |
125
126 delimiter ;|
127
128 INSERT INTO t1 (id) VALUES (11), (12), (13);
129
130 --replace_column 2 [EVENT_ID]
131 SELECT id, c FROM t1 WHERE id > 10 ORDER BY id, c;
132
133 DROP TRIGGER t_ps_trigger;
134
135 # PROCEDURE
136
137 --disable_warnings
138 DROP PROCEDURE IF EXISTS t_ps_proc;
139 --enable_warnings
140 delimiter |;
141
142 CREATE PROCEDURE t_ps_proc(IN conid INT, OUT pid INT)
143 BEGIN
144 SELECT thread_id FROM performance_schema.threads
145 WHERE PROCESSLIST_ID = conid INTO pid;
146 END;
147
148 |
149
150 delimiter ;|
151
152 CALL t_ps_proc(connection_id(), @p_id);
153
154 # FUNCTION
155
156 --disable_warnings
157 DROP FUNCTION IF EXISTS t_ps_proc;
158 --enable_warnings
159 delimiter |;
160
161 CREATE FUNCTION t_ps_func(conid INT) RETURNS int
162 BEGIN
163 return (SELECT thread_id FROM performance_schema.threads
164 WHERE PROCESSLIST_ID = conid);
165 END;
166
167 |
168
169 delimiter ;|
170
171 SELECT t_ps_func(connection_id()) = @p_id;
172
173 # We might reach this point too early which means the event scheduler has not
174 # executed our "t_ps_event". Therefore we poll till the record was inserted
175 # and run our test statement afterwards.
176 let $wait_timeout= 20;
177 let $wait_condition= SELECT COUNT(*) = 1 FROM t_event;
178 --source include/wait_condition.inc
179 --replace_column 1 [EVENT_ID]
180 SELECT * FROM t_event;
181
182 # Clean up
183 DROP PROCEDURE t_ps_proc;
184 DROP FUNCTION t_ps_func;
185 DROP EVENT t_ps_event;
186 DROP TABLE t1;
187 DROP TABLE t_event;
188
189 UPDATE performance_schema.setup_threads SET ENABLED='YES';
190 UPDATE performance_schema.threads SET INSTRUMENTED='YES';
191 UPDATE performance_schema.setup_instruments SET enabled = 'YES', timed = 'YES';
192