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
146
147
148
149
150
151
152
153
154
155
156
157
|
##
## 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;
Warnings:
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.
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 EXECUTION_ENGINE
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL PRIMARY
<Id> root <Host> test <Command> <Time> <State> NULL PRIMARY
<Id> user0 <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id PRIMARY
<Id> user1 <Host> test <Command> <Time> <State> NULL PRIMARY
<Id> user2 <Host> test <Command> <Time> <State> NULL PRIMARY
<Id> user3 <Host> test <Command> <Time> <State> NULL PRIMARY
<Id> user4 <Host> test <Command> <Time> <State> NULL PRIMARY
# 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
Warnings:
<Id> 1287 <Host>
# Connection user1
# Get Host:Port, new
## Legacy SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = off;
Warnings:
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.
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> 1287 <Host>
<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
Warnings:
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO EXECUTION_ENGINE
<Id> event_scheduler <Host> NULL <Command> <Time> <State> NULL PRIMARY
<Id> root <Host> test <Command> <Time> <State> NULL PRIMARY
<Id> user0 <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id PRIMARY
<Id> user1 <Host> test <Command> <Time> <State> NULL PRIMARY
<Id> user2 <Host> test <Command> <Time> <State> NULL PRIMARY
<Id> user3 <Host> test <Command> <Time> <State> NULL PRIMARY
<Id> user4 <Host> test <Command> <Time> <State> NULL PRIMARY
# 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
Warnings:
<Id> 1287 <Host>
# 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;
Warnings:
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.
|
