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
139
140
141
142
143
144
145
|
##
## Test the Performance Schema-based implementation of SHOW PROCESSLIST.
##
## Verify the Host field (hostname:port) against the legacy implementation.
##
### Setup ###
select @@global.performance_schema_show_processlist into @save_processlist;
# Control user
create user user0@localhost;
grant ALL on *.* to user0@localhost;
# Test users
create user user1@localhost, user2@localhost,
user3@localhost, user4@localhost;
grant USAGE on *.* to user1@localhost;
grant ALL on *.* to user2@localhost;
grant ALL on *.* to user3@localhost;
grant ALL on *.* to user4@localhost;
flush privileges;
show grants for user1@localhost;
Grants for user1@localhost
GRANT USAGE ON *.* TO 'user1'@'localhost'
# Connect (con_user0, 127.0.0.1, user0, , , MASTER_MYPORT, )
select connection_id() into @con_user0_id;
# Connect (con_user1, 127.0.0.1, user1, , , MASTER_MYPORT, )
# Connect (con_user2, 127.0.0.1, user2, , , MASTER_MYPORT, )
# Connect (con_user3, 127.0.0.1, user3, , , MASTER_MYPORT, )
# Connect (con_user4, 127.0.0.1, user4, , , MASTER_MYPORT, )
# Connection user0
### Compare the SHOW PROCESSLIST Host column between the new and old implementations
## New SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = on;
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user0 <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user1 <Host> test <Command> <Time> <State> NULL
<Id> user2 <Host> test <Command> <Time> <State> NULL
<Id> user3 <Host> test <Command> <Time> <State> NULL
<Id> user4 <Host> test <Command> <Time> <State> NULL
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user0 <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user1 <Host> test <Command> <Time> <State> NULL
<Id> user2 <Host> test <Command> <Time> <State> NULL
<Id> user3 <Host> test <Command> <Time> <State> NULL
<Id> user4 <Host> test <Command> <Time> <State> NULL
# Information Schema processlist table
select * from information_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user0 <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user1 <Host> test <Command> <Time> <State> NULL
<Id> user2 <Host> test <Command> <Time> <State> NULL
<Id> user3 <Host> test <Command> <Time> <State> NULL
<Id> user4 <Host> test <Command> <Time> <State> NULL
# Connection user1
# Get Host:Port, new
## Legacy SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = off;
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user0 <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user1 <Host> test <Command> <Time> <State> NULL
<Id> user2 <Host> test <Command> <Time> <State> NULL
<Id> user3 <Host> test <Command> <Time> <State> NULL
<Id> user4 <Host> test <Command> <Time> <State> NULL
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user0 <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user1 <Host> test <Command> <Time> <State> NULL
<Id> user2 <Host> test <Command> <Time> <State> NULL
<Id> user3 <Host> test <Command> <Time> <State> NULL
<Id> user4 <Host> test <Command> <Time> <State> NULL
# Information Schema processlist table
select * from information_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL
<Id> root <Host> test <Command> <Time> <State> NULL
<Id> user0 <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user1 <Host> test <Command> <Time> <State> NULL
<Id> user2 <Host> test <Command> <Time> <State> NULL
<Id> user3 <Host> test <Command> <Time> <State> NULL
<Id> user4 <Host> test <Command> <Time> <State> NULL
# Connection user1
# Get Host:Port, legacy
***SUCCESS*** The SHOW PROCESSLIST Host fields match
### Compare the processlist Host column between Performance Schema and the Information Schema
# Connection con_user0
***SUCCESS*** The processlist Host fields match between the Performance Schema and the Information Schema
### Clean up ###
# Disconnect con_user0
# Disconnect con_user1
# Disconnect con_user2
# Disconnect con_user3
# Disconnect con_user4
# Connection default
drop user user0@localhost;
drop user user1@localhost;
drop user user2@localhost;
drop user user3@localhost;
drop user user4@localhost;
set @@global.performance_schema_show_processlist = @save_processlist;
|
