Quelle  processlist_port.result   Sprache: Lisp

##
## 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;