Debian Codegraph @

mysql-test/suite/perfschema/r/processlist.result

raw
1 ##
2 ## Test the Performance Schema-based implementation of SHOW PROCESSLIST.
3 ##
4 ## Test cases:
5 ## 1. Execute the new SHOW [FULL] PROCESSLIST and SELECT on performance_schema.processlist
6 ## 2. Execute the legacy SHOW [FULL] PROCESSLIST and SELECT on information_schema.processlist
7 ## 3. Verify that performance_schema_show_processlist = ON executes the new implementation
8 ## 4. Verify that performance_schema_show_processlist = OFF executes the legacy code path
9 ##
10 ## Results must be manually verified.
11
12 ### Setup ###
13
14 select @@global.performance_schema_show_processlist into @save_processlist;
15
16 create user user1@localhost, user2@localhost,
17 user3@localhost, user4@localhost;
18
19 grant ALL on *.* to user1@localhost;
20 grant ALL on *.* to user2@localhost;
21 grant ALL on *.* to user3@localhost;
22 grant ALL on *.* to user4@localhost;
23
24 flush privileges;
25
26 use test;
27 create table test.t1 (s1 int, s2 int, s3 int, s4 int);
28
29 # Switch to (con0, localhost, root, , )
30
31 insert into test.t1 values(1, 1, 1, 1);
32 insert into test.t1 values(2, 2, 2, 2);
33 insert into test.t1 values(3, 3, 3, 3);
34 insert into test.t1 values(4, 4, 4, 4);
35
36 # Lock test.t1, insert/update/deletes will block
37 lock tables t1 read;
38
39 # Connect (con1, localhost, user1, , )
40 insert into test.t1 values (0, 0, 0, 0);
41
42 # Connect (con2, localhost, user2, , )
43 update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999;;
44
45 # Connect (con3, localhost, user3, , )
46 delete from test.t1 where s1 = 3;
47
48 # Connect (con4, localhost, user4, , )
49 insert into test.t1 values (4, 4, 4, 4);
50
51 # Connection default
52
53 # Wait for queries to appear in the processlist table
54
55
56 ### Execute new SHOW [FULL] PROCESSLIST and SELECT on performance_schema.processlist
57
58 set @@global.performance_schema_show_processlist = on;
59 Warnings:
60 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.
61
62 SHOW FULL PROCESSLIST;
63 Id User Host db Command Time State Info
64 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
65 <Id> root <Host> test <Command> <Time> <State> NULL
66 <Id> root <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
67 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
68 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
69 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
70 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
71
72 # Performance Schema processlist table
73
74 select * from performance_schema.processlist order by user, id;
75 ID USER HOST DB COMMAND TIME STATE INFO EXECUTION_ENGINE
76 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL PRIMARY
77 <Id> root <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id PRIMARY
78 <Id> root <Host> test <Command> <Time> <State> NULL PRIMARY
79 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) PRIMARY
80 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999 PRIMARY
81 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3 PRIMARY
82 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4) PRIMARY
83
84 # Information Schema processlist table
85
86 select * from information_schema.processlist order by user, id;
87 ID USER HOST DB COMMAND TIME STATE INFO
88 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
89 <Id> root <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
90 <Id> root <Host> test <Command> <Time> <State> NULL
91 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
92 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
93 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
94 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
95 Warnings:
96 <Id> 1287 <Host>
97
98
99 ### Execute legacy SHOW [FULL] PROCESSLIST and SELECT on information_schema.processlist
100
101 set @@global.performance_schema_show_processlist = off;
102 Warnings:
103 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.
104
105 SHOW FULL PROCESSLIST;
106 Id User Host db Command Time State Info
107 <Id> 1287 <Host>
108 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
109 <Id> root <Host> test <Command> <Time> <State> NULL
110 <Id> root <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
111 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
112 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
113 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
114 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
115 Warnings:
116
117 # Performance Schema processlist table
118
119 select * from performance_schema.processlist order by user, id;
120 ID USER HOST DB COMMAND TIME STATE INFO EXECUTION_ENGINE
121 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL PRIMARY
122 <Id> root <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id PRIMARY
123 <Id> root <Host> test <Command> <Time> <State> NULL PRIMARY
124 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) PRIMARY
125 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999 PRIMARY
126 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3 PRIMARY
127 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4) PRIMARY
128
129 # Information Schema processlist table
130
131 select * from information_schema.processlist order by user, id;
132 ID USER HOST DB COMMAND TIME STATE INFO
133 <Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
134 <Id> root <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
135 <Id> root <Host> test <Command> <Time> <State> NULL
136 <Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
137 <Id> user2 <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2, s3 = s3 + 3, s4 = ((s4 + 4) * (s4 + 5)) + 12345 + 67890 + 11111 + 22222 + 33333 + 44444 + 55555 + 99999
138 <Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3
139 <Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4)
140 Warnings:
141 <Id> 1287 <Host>
142
143
144 ### Verify feature code path
145
146 # Enable SHOW PROCESSLIST via the Performance Schema
147 set @@global.performance_schema_show_processlist = on;
148 Warnings:
149 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.
150
151 # Connection default, send SHOW PROCESSLIST
152 SET DEBUG_SYNC='pfs_show_processlist_performance_schema SIGNAL pfs_processlist_pfs WAIT_FOR continue';
153 SHOW FULL PROCESSLIST;
154
155 # Connection con0
156 SET DEBUG_SYNC='now WAIT_FOR pfs_processlist_pfs';
157 SET DEBUG_SYNC='now SIGNAL continue';
158
159 # Connection default, reap
160 Id User Host db Command Time State Info
161 <Id> event_scheduler <Host> NULL <Command> <Time> <State> <Info>
162 <Id> root <Host> test <Command> <Time> <State> <Info>
163 <Id> root <Host> test <Command> <Time> <State> <Info>
164 <Id> user1 <Host> test <Command> <Time> <State> <Info>
165 <Id> user2 <Host> test <Command> <Time> <State> <Info>
166 <Id> user3 <Host> test <Command> <Time> <State> <Info>
167 <Id> user4 <Host> test <Command> <Time> <State> <Info>
168
169
170 ### Verify legacy code path
171
172 # Enable the legacy SHOW PROCESSLIST
173 set @@global.performance_schema_show_processlist = off;
174 Warnings:
175 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.
176
177 # Connection default, send SHOW PROCESSLIST
178 SET DEBUG_SYNC='RESET';
179 SET DEBUG_SYNC='pfs_show_processlist_legacy SIGNAL pfs_processlist_legacy WAIT_FOR continue';
180 SHOW FULL PROCESSLIST;
181
182 # Connection con0
183 SET DEBUG_SYNC='now WAIT_FOR pfs_processlist_legacy';
184 SET DEBUG_SYNC='now SIGNAL continue';
185
186 # Connection default, reap
187 Id User Host db Command Time State Info
188 <Id> 1287 <Host>
189 <Id> event_scheduler <Host> NULL <Command> <Time> <State> <Info>
190 <Id> root <Host> test <Command> <Time> <State> <Info>
191 <Id> root <Host> test <Command> <Time> <State> <Info>
192 <Id> user1 <Host> test <Command> <Time> <State> <Info>
193 <Id> user2 <Host> test <Command> <Time> <State> <Info>
194 <Id> user3 <Host> test <Command> <Time> <State> <Info>
195 <Id> user4 <Host> test <Command> <Time> <State> <Info>
196 Warnings:
197
198
199 ### Clean up ###
200
201 # Connection con0, unlock test.t1, disconnect
202 unlock tables;
203
204 # Connection con1, reap, disconnect
205 # Connection con2, reap, disconnect
206 # Connection con3, reap, disconnect
207 # Connection con4, reap, disconnect
208
209 # Connection default
210
211 drop table test.t1;
212 drop user user1@localhost;
213 drop user user2@localhost;
214 drop user user3@localhost;
215 drop user user4@localhost;
216
217 set @@global.performance_schema_show_processlist = @save_processlist;
218 Warnings:
219 Warning 4166 '@@performance_schema_show_processlist' is deprecated and will be removed in a future release. When it is removed, SHOW PROCESSLIST will always use the performance schema implementation.