| Server IP : 104.21.38.3 / Your IP : 172.68.164.24 Web Server : Apache System : Linux krdc-ubuntu-s-2vcpu-4gb-amd-blr1-01.localdomain 5.15.0-142-generic #152-Ubuntu SMP Mon May 19 10:54:31 UTC 2025 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : ON Directory : /www/server/mysql/src/mysql-test/suite/perfschema/r/ |
Upload File : |
##
## Test the Performance Schema-based implementation of SHOW PROCESSLIST.
##
## Verify handling of the SELECT and PROCESS privileges.
##
## Test cases:
## - Execute SHOW PROCESSLIST (new and legacy) with all privileges
## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with all privileges
## - Execute SHOW PROCESSLIST (new and legacy) with no privileges
## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with no privileges
##
## Results must be manually verified.
### Setup ###
select @@global.performance_schema_show_processlist into @save_processlist;
# Control users
create user user_00@localhost, user_01@localhost;
grant ALL on *.* to user_00@localhost;
grant ALL on *.* to user_01@localhost;
# Test users
create user user_all@localhost, user_none@localhost;
grant ALL on *.* to user_all@localhost;
grant USAGE on *.* to user_none@localhost;
flush privileges;
show grants for user_all@localhost;
Grants for user_all@localhost
GRANT ALL PRIVILEGES ON *.* TO 'user_all'@'localhost'
show grants for user_none@localhost;
Grants for user_none@localhost
GRANT USAGE ON *.* TO 'user_none'@'localhost'
use test;
create table test.t1 (s1 int, s2 int, s3 int, s4 int);
# Connect (con_00, localhost, user_00, , )
# Connect (con_01, localhost, user_01, , )
insert into test.t1 values(1, 1, 1, 1);
insert into test.t1 values(2, 2, 2, 2);
insert into test.t1 values(3, 3, 3, 3);
insert into test.t1 values(4, 4, 4, 4);
# Lock test.t1, insert/update/deletes will block
lock tables t1 read;
# Establish 2 connections for user_all
# Connect (con_all_1, localhost, user_all, , )
# Connect (con_all_2, localhost, user_all, , )
insert into test.t1 values (0, 0, 0, 0);
# Establish 4 connections for user_none
# Connect (con_none_1, localhost, user_none, , )
# Connect (con_none_2, localhost, user_none, , )
# Connect (con_none_3, localhost, user_none, , )
# Connect (con_none_4, localhost, user_none, , )
update test.t1 set s1 = s1 + 1, s2 = s2 + 2;;
# Connection con_all_1
# Wait for queries to appear in the processlist table
### Execute SHOW PROCESSLIST with all privileges
### Expect all users
# 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> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# 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> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# 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> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# 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> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# 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> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# 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> user_00 <Host> test <Command> <Time> <State> NULL
<Id> user_01 <Host> test <Command> <Time> <State> NULL
<Id> user_all <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user_all <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0)
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
### Execute SHOW PROCESSLIST with no SELECT and no PROCESS privileges
### Expect processes only from user_none
# New SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = on;
# Connection con_none_1
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> user_none <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Information Schema processlist table
select * from information_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> user_none <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Confirm that only processes from user_none are visible
select count(*) as "Expect 0" from performance_schema.processlist
where user not in ('user_none');
Expect 0
0
# Legacy SHOW PROCESSLIST
set @@global.performance_schema_show_processlist = off;
# Connection con_none_1
SHOW FULL PROCESSLIST;
Id User Host db Command Time State Info
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> SHOW FULL PROCESSLIST
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Performance Schema processlist table
select * from performance_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> user_none <Host> test Query <Time> <State> select * from performance_schema.processlist order by user, id
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
# Information Schema processlist table
select * from information_schema.processlist order by user, id;
ID USER HOST DB COMMAND TIME STATE INFO
<Id> user_none <Host> test Query <Time> <State> select * from information_schema.processlist order by user, id
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test <Command> <Time> <State> NULL
<Id> user_none <Host> test Query <Time> <State> update test.t1 set s1 = s1 + 1, s2 = s2 + 2
### Clean up ###
# Disconnect con_00
# Connection con_01, unlock test.t1, disconnect
unlock tables;
# Disconnect con_all_1
# Reap con_all_2, disconnect
# Disconnect con_none_1
# Disconnect con_none_2
# Disconnect con_none_3
# Reap con_none_4, disconnect
# Connection default
drop table test.t1;
drop user user_00@localhost;
drop user user_01@localhost;
drop user user_all@localhost;
drop user user_none@localhost;
set @@global.performance_schema_show_processlist = @save_processlist;