diff options
Diffstat (limited to 'mysql-test/suite/perfschema')
31 files changed, 2896 insertions, 7 deletions
diff --git a/mysql-test/suite/perfschema/disabled.def b/mysql-test/suite/perfschema/disabled.def index fb97b011567..935548fce76 100644 --- a/mysql-test/suite/perfschema/disabled.def +++ b/mysql-test/suite/perfschema/disabled.def @@ -53,3 +53,13 @@ show_misc: todo show_plugin: todo status_reprepare:why P_S filters out Com_* variables? + +processlist: todo +processlist_acl: todo +processlist_port: todo +processlist_no_pfs: todo +ddl_processlist: todo +dml_processlist: todo +processlist_57: todo +processlist_anonymous: todo +processlist_reg_user: todo diff --git a/mysql-test/suite/perfschema/include/processlist_load.inc b/mysql-test/suite/perfschema/include/processlist_load.inc new file mode 100644 index 00000000000..5a14ea17d07 --- /dev/null +++ b/mysql-test/suite/perfschema/include/processlist_load.inc @@ -0,0 +1,39 @@ +# ../include/processlist_load.inc +# +# SUMMARY +# +# Execute PROCESSLIST queries, sorted by user +# +# USAGE +# +# Example: Using processlist_set.inc to set @@global.performance_schema_show_processlist +# +# let $pfs_spl = on/off +# --source ../include/processlist_set.inc +# --source ../include/processlist_load.inc +# +# Columns +# 1 <Id> 2 <User> 3 <Host> 4 <db> 5 <Command> 6 <Time> 7 <State> 8 <Info> + +# Sort SHOW PROCESSLIST by User instead of Id because Id is not zero-padded +# Unique usernames give best results +--echo +--replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> +--replace_regex /Daemon/<Command>/ /Connect/<Command>/ /Sleep/<Command>/ +--sorted_result +SHOW FULL PROCESSLIST; + +--echo +--echo # Performance Schema processlist table +--echo +--replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> +--replace_regex /Daemon/<Command>/ /Connect/<Command>/ /Sleep/<Command>/ +select * from performance_schema.processlist order by user, id; + +--echo +--echo # Information Schema processlist table +--echo +--replace_column 1 <Id> 3 <Host> 6 <Time> 7 <State> +--replace_regex /Daemon/<Command>/ /Connect/<Command>/ /Sleep/<Command>/ +select * from information_schema.processlist order by user, id; + diff --git a/mysql-test/suite/perfschema/include/processlist_set.inc b/mysql-test/suite/perfschema/include/processlist_set.inc new file mode 100644 index 00000000000..a15e09fd989 --- /dev/null +++ b/mysql-test/suite/perfschema/include/processlist_set.inc @@ -0,0 +1,17 @@ +# ../include/processlist_set.inc +# +# SUMMARY +# +# Set the value of performance_schema_show_proceslist then +# wait for the operation to complete +# +# USAGE +# +# let $pfs_spl = on; +# --source ../include/processlist_set.inc + +eval set @@global.performance_schema_show_processlist = $pfs_spl; + +let $wait_condition = show variables where variable_name like '%show_processlist%' and value = '$pfs_spl'; +--source include/wait_condition.inc + diff --git a/mysql-test/suite/perfschema/r/ddl_processlist.result b/mysql-test/suite/perfschema/r/ddl_processlist.result new file mode 100644 index 00000000000..3b1b8608725 --- /dev/null +++ b/mysql-test/suite/perfschema/r/ddl_processlist.result @@ -0,0 +1,20 @@ +select @@global.performance_schema_show_processlist into @save_processlist; +set @@global.performance_schema_show_processlist = 'on'; +alter table performance_schema.processlist +add column foo integer; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema' +truncate table performance_schema.processlist; +ERROR HY000: Invalid performance_schema usage. +alter table performance_schema.processlist +add index test_index(info); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema' +create unique index test_index +on performance_schema.processlist(host); +ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema' +drop index `PRIMARY` + on performance_schema.processlist; +ERROR 42000: Access denied for user 'root'@'localhost' to database 'performance_schema' +CREATE TABLE test.create_select +AS SELECT * from performance_schema.processlist; +DROP TABLE test.create_select; +set @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/r/dml_processlist.result b/mysql-test/suite/perfschema/r/dml_processlist.result new file mode 100644 index 00000000000..f3627807a49 --- /dev/null +++ b/mysql-test/suite/perfschema/r/dml_processlist.result @@ -0,0 +1,24 @@ +select @@global.performance_schema_show_processlist into @save_processlist; +set @@global.performance_schema_show_processlist = 'on'; +select * from performance_schema.processlist +where user like 'event_scheduler'; +select * from performance_schema.processlist +where user = 'FOO'; +insert into performance_schema.processlist +values (12, 'foo', 'bar', 'test', null, 1000, 'state', 'info'); +ERROR 42000: INSERT command denied to user 'root'@'localhost' for table 'processlist' +update performance_schema.processlist +set id=12, user='foo'; +ERROR 42000: UPDATE command denied to user 'root'@'localhost' for table 'processlist' +delete from performance_schema.processlist +where id <> 99; +ERROR 42000: DELETE command denied to user 'root'@'localhost' for table 'processlist' +delete from performance_schema.processlist; +ERROR 42000: DELETE command denied to user 'root'@'localhost' for table 'processlist' +LOCK TABLES performance_schema.processlist READ; +ERROR 42000: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'processlist' +UNLOCK TABLES; +LOCK TABLES performance_schema.processlist WRITE; +ERROR 42000: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'processlist' +UNLOCK TABLES; +set @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/r/misc_global_status.result b/mysql-test/suite/perfschema/r/misc_global_status.result new file mode 100644 index 00000000000..1863dde6103 --- /dev/null +++ b/mysql-test/suite/perfschema/r/misc_global_status.result @@ -0,0 +1,106 @@ +CREATE USER user1@localhost; +CREATE USER user2@localhost; +CREATE USER user3@localhost; +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@localhost; +TRUNCATE TABLE performance_schema.accounts; +FLUSH PRIVILEGES; +CREATE TABLE test.t_range(a int, b int, PRIMARY KEY(a)); +INSERT INTO test.t_range values (1, 1), (2,2), (3, 3), (4, 4), (5, 5); +INSERT INTO test.t_range values (6, 6), (7,7), (8, 8), (9, 9), (10, 10); +FLUSH STATUS; +SELECT * from test.t_range where (a >= 3) AND (a <= 5); +a b +3 3 +4 4 +5 5 +SELECT * from performance_schema.session_status +WHERE VARIABLE_NAME = 'Select_range'; +VARIABLE_NAME VARIABLE_VALUE +Select_range 1 +VARIABLE_NAME DELTA +Select_range 1 +connect con1, localhost, user1,,; +SELECT * from test.t_range where (a >= 3) AND (a <= 5); +a b +3 3 +4 4 +5 5 +SELECT * from performance_schema.session_status +WHERE VARIABLE_NAME = 'Select_range'; +VARIABLE_NAME VARIABLE_VALUE +Select_range 1 +VARIABLE_NAME DELTA +Select_range 2 +connect con2, localhost, user2,,; +SELECT * from test.t_range where (a >= 3) AND (a <= 5); +a b +3 3 +4 4 +5 5 +SELECT * from test.t_range where (a >= 4) AND (a <= 6); +a b +4 4 +5 5 +6 6 +SELECT * from performance_schema.session_status +WHERE VARIABLE_NAME = 'Select_range'; +VARIABLE_NAME VARIABLE_VALUE +Select_range 2 +VARIABLE_NAME DELTA +Select_range 4 +connect con3, localhost, user3,,; +SELECT * from test.t_range where (a >= 3) AND (a <= 5); +a b +3 3 +4 4 +5 5 +SELECT * from test.t_range where (a >= 4) AND (a <= 6); +a b +4 4 +5 5 +6 6 +SELECT * from test.t_range where (a >= 5) AND (a <= 7); +a b +5 5 +6 6 +7 7 +SELECT * from performance_schema.session_status +WHERE VARIABLE_NAME = 'Select_range'; +VARIABLE_NAME VARIABLE_VALUE +Select_range 3 +VARIABLE_NAME DELTA +Select_range 7 +connection default; +VARIABLE_NAME DELTA +Select_range 7 +SELECT `USER`, `HOST`, VARIABLE_NAME, VARIABLE_VALUE +FROM performance_schema.status_by_account WHERE VARIABLE_NAME = 'Select_range' + AND `USER` LIKE 'user%' + ORDER BY `USER`; +USER HOST VARIABLE_NAME VARIABLE_VALUE +user1 localhost Select_range 1 +user2 localhost Select_range 2 +user3 localhost Select_range 3 +disconnect con1; +disconnect con2; +VARIABLE_NAME DELTA +Select_range 7 +TRUNCATE TABLE performance_schema.accounts; +VARIABLE_NAME DELTA +Select_range 7 +disconnect con3; +VARIABLE_NAME DELTA +Select_range 7 +TRUNCATE TABLE performance_schema.accounts; +VARIABLE_NAME DELTA +Select_range 7 +DROP TABLE test.t_range; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost; +DROP USER user1@localhost; +DROP USER user2@localhost; +DROP USER user3@localhost; +FLUSH PRIVILEGES; diff --git a/mysql-test/suite/perfschema/r/processlist.result b/mysql-test/suite/perfschema/r/processlist.result new file mode 100644 index 00000000000..40c5a0f04a8 --- /dev/null +++ b/mysql-test/suite/perfschema/r/processlist.result @@ -0,0 +1,201 @@ +## +## Test the Performance Schema-based implementation of SHOW PROCESSLIST. +## +## Test cases: +## 1. Execute the new SHOW [FULL] PROCESSLIST and SELECT on performance_schema.processlist +## 2. Execute the legacy SHOW [FULL] PROCESSLIST and SELECT on information_schema.processlist +## 3. Verify that performance_schema_show_processlist = ON executes the new implementation +## 4. Verify that performance_schema_show_processlist = OFF executes the legacy code path +## +## Results must be manually verified. + +### Setup ### + +select @@global.performance_schema_show_processlist into @save_processlist; + +create user user1@localhost, user2@localhost, +user3@localhost, user4@localhost; + +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@localhost; +grant ALL on *.* to user4@localhost; + +flush privileges; + +use test; +create table test.t1 (s1 int, s2 int, s3 int, s4 int); + +# Switch to (con0, localhost, root, , ) + +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; + +# Connect (con1, localhost, user1, , ) +insert into test.t1 values (0, 0, 0, 0); + +# Connect (con2, localhost, user2, , ) +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;; + +# Connect (con3, localhost, user3, , ) +delete from test.t1 where s1 = 3; + +# Connect (con4, localhost, user4, , ) +insert into test.t1 values (4, 4, 4, 4); + +# Connection default + +# Wait for queries to appear in the processlist table + + +### Execute new SHOW [FULL] PROCESSLIST and SELECT on performance_schema.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> root <Host> test Query <Time> <State> SHOW FULL PROCESSLIST +<Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) +<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 +<Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3 +<Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4) + +# 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 Query <Time> <State> select * from performance_schema.processlist order by user, id +<Id> root <Host> test <Command> <Time> <State> NULL +<Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) +<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 +<Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3 +<Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4) + +# 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 Query <Time> <State> select * from information_schema.processlist order by user, id +<Id> root <Host> test <Command> <Time> <State> NULL +<Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) +<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 +<Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3 +<Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4) + + +### Execute legacy SHOW [FULL] PROCESSLIST and SELECT on information_schema.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> root <Host> test Query <Time> <State> SHOW FULL PROCESSLIST +<Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) +<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 +<Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3 +<Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4) + +# 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 Query <Time> <State> select * from performance_schema.processlist order by user, id +<Id> root <Host> test <Command> <Time> <State> NULL +<Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) +<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 +<Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3 +<Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4) + +# 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 Query <Time> <State> select * from information_schema.processlist order by user, id +<Id> root <Host> test <Command> <Time> <State> NULL +<Id> user1 <Host> test Query <Time> <State> insert into test.t1 values (0, 0, 0, 0) +<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 +<Id> user3 <Host> test Query <Time> <State> delete from test.t1 where s1 = 3 +<Id> user4 <Host> test Query <Time> <State> insert into test.t1 values (4, 4, 4, 4) + + +### Verify feature code path + +# Enable SHOW PROCESSLIST via the Performance Schema +set @@global.performance_schema_show_processlist = on; + +# Connection default, send SHOW PROCESSLIST +SET DEBUG_SYNC='pfs_show_processlist_performance_schema SIGNAL pfs_processlist_pfs WAIT_FOR continue'; +SHOW FULL PROCESSLIST; + +# Connection con0 +SET DEBUG_SYNC='now WAIT_FOR pfs_processlist_pfs'; +SET DEBUG_SYNC='now SIGNAL continue'; + +# Connection default, reap +Id User Host db Command Time State Info +<Id> event_scheduler <Host> NULL <Command> <Time> <State> <Info> +<Id> root <Host> test <Command> <Time> <State> <Info> +<Id> root <Host> test <Command> <Time> <State> <Info> +<Id> user1 <Host> test <Command> <Time> <State> <Info> +<Id> user2 <Host> test <Command> <Time> <State> <Info> +<Id> user3 <Host> test <Command> <Time> <State> <Info> +<Id> user4 <Host> test <Command> <Time> <State> <Info> + + +### Verify legacy code path + +# Enable the legacy SHOW PROCESSLIST +set @@global.performance_schema_show_processlist = off; + +# Connection default, send SHOW PROCESSLIST +SET DEBUG_SYNC='RESET'; +SET DEBUG_SYNC='pfs_show_processlist_legacy SIGNAL pfs_processlist_legacy WAIT_FOR continue'; +SHOW FULL PROCESSLIST; + +# Connection con0 +SET DEBUG_SYNC='now WAIT_FOR pfs_processlist_legacy'; +SET DEBUG_SYNC='now SIGNAL continue'; + +# Connection default, reap +Id User Host db Command Time State Info +<Id> event_scheduler <Host> NULL <Command> <Time> <State> <Info> +<Id> root <Host> test <Command> <Time> <State> <Info> +<Id> root <Host> test <Command> <Time> <State> <Info> +<Id> user1 <Host> test <Command> <Time> <State> <Info> +<Id> user2 <Host> test <Command> <Time> <State> <Info> +<Id> user3 <Host> test <Command> <Time> <State> <Info> +<Id> user4 <Host> test <Command> <Time> <State> <Info> + + +### Clean up ### + +# Connection con0, unlock test.t1, disconnect +unlock tables; + +# Connection con1, reap, disconnect +# Connection con2, reap, disconnect +# Connection con3, reap, disconnect +# Connection con4, reap, disconnect + +# Connection default + +drop table test.t1; +drop user user1@localhost; +drop user user2@localhost; +drop user user3@localhost; +drop user user4@localhost; + +set @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/r/processlist_57.result b/mysql-test/suite/perfschema/r/processlist_57.result new file mode 100644 index 00000000000..0068eb6c3cd --- /dev/null +++ b/mysql-test/suite/perfschema/r/processlist_57.result @@ -0,0 +1,345 @@ +call mtr.add_suppression("Optional native table 'performance_schema'.'processlist' has the wrong structure or is missing."); +call mtr.add_suppression("Column count of performance_schema.processlist is wrong. Expected 8, found 2. The table is probably corrupted"); +## +## Verify fresh 5.7 instance +## +SELECT @@global.performance_schema_show_processlist; +@@global.performance_schema_show_processlist +0 +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `ID` bigint(20) unsigned NOT NULL, + `USER` varchar(32) DEFAULT NULL, + `HOST` varchar(66) DEFAULT NULL, + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) DEFAULT NULL, + `TIME` bigint(20) DEFAULT NULL, + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 +## +## Simulate old 5.7 instance +## +DROP TABLE performance_schema.processlist; +SHOW CREATE TABLE performance_schema.processlist; +ERROR 42S02: Table 'performance_schema.processlist' doesn't exist +## +## Server shutdown +## +## +### Server restart +## +## +## Verify old 5.7 instance +## +SELECT @@global.performance_schema_show_processlist; +@@global.performance_schema_show_processlist +0 +SHOW CREATE TABLE performance_schema.processlist; +ERROR 42S02: Table 'performance_schema.processlist' doesn't exist +SELECT * FROM performance_schema.processlist; +ERROR 42S02: Table 'performance_schema.processlist' doesn't exist +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +ERROR 42S02: Table 'performance_schema.processlist' doesn't exist +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +ERROR 42S02: Table 'performance_schema.processlist' doesn't exist +## +## Perform broken upgrade (case 1) +## +CREATE TABLE performance_schema.processlist (a int, b int); +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +SET GLOBAL performance_schema_show_processlist = 'OFF'; +SELECT * FROM performance_schema.processlist; +a b +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +a b +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +ERROR 42S22: Unknown column 'ID' in 'field list' +## +## Server shutdown +## +## +### Server restart +## +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `a` int(11) DEFAULT NULL, + `b` int(11) DEFAULT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +SET GLOBAL performance_schema_show_processlist = 'OFF'; +SELECT * FROM performance_schema.processlist; +a b +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +a b +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +ERROR 42S22: Unknown column 'ID' in 'field list' +## +## Perform broken upgrade (case 2) +## +DROP TABLE performance_schema.processlist; +CREATE TABLE performance_schema.processlist ( +`ID` bigint(20) unsigned NOT NULL, +`USER` varchar(32) DEFAULT NULL, +`HOST` varchar(66) DEFAULT NULL, +`DB` varchar(64) DEFAULT NULL, +`COMMAND` varchar(16) DEFAULT NULL, +`TIME` bigint(20) DEFAULT NULL, +`STATE` varchar(64) DEFAULT NULL, +`INFO` longtext +); +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `ID` bigint(20) unsigned NOT NULL, + `USER` varchar(32) DEFAULT NULL, + `HOST` varchar(66) DEFAULT NULL, + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) DEFAULT NULL, + `TIME` bigint(20) DEFAULT NULL, + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +SET GLOBAL performance_schema_show_processlist = 'OFF'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +## +## Server shutdown +## +## +### Server restart +## +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `ID` bigint(20) unsigned NOT NULL, + `USER` varchar(32) DEFAULT NULL, + `HOST` varchar(66) DEFAULT NULL, + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) DEFAULT NULL, + `TIME` bigint(20) DEFAULT NULL, + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +SET GLOBAL performance_schema_show_processlist = 'OFF'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +## +## Perform broken upgrade (case 3) +## +DROP TABLE performance_schema.processlist; +CREATE TABLE performance_schema.processlist +LIKE INFORMATION_SCHEMA.PROCESSLIST; +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `ID` bigint(21) unsigned NOT NULL DEFAULT '0', + `USER` varchar(32) NOT NULL DEFAULT '', + `HOST` varchar(64) NOT NULL DEFAULT '', + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) NOT NULL DEFAULT '', + `TIME` int(7) NOT NULL DEFAULT '0', + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +SET GLOBAL performance_schema_show_processlist = 'OFF'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +## +## Server shutdown +## +## +### Server restart +## +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `ID` bigint(21) unsigned NOT NULL DEFAULT '0', + `USER` varchar(32) NOT NULL DEFAULT '', + `HOST` varchar(64) NOT NULL DEFAULT '', + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) NOT NULL DEFAULT '', + `TIME` int(7) NOT NULL DEFAULT '0', + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +) ENGINE=InnoDB DEFAULT CHARSET=utf8 +SET GLOBAL performance_schema_show_processlist = 'OFF'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +## +## Perform correct upgrade +## +DROP TABLE performance_schema.processlist; +CREATE TABLE performance_schema.processlist ( +`ID` bigint(20) unsigned NOT NULL, +`USER` varchar(32) DEFAULT NULL, +`HOST` varchar(66) DEFAULT NULL, +`DB` varchar(64) DEFAULT NULL, +`COMMAND` varchar(16) DEFAULT NULL, +`TIME` bigint(20) DEFAULT NULL, +`STATE` varchar(64) DEFAULT NULL, +`INFO` longtext +) ENGINE = 'PERFORMANCE_SCHEMA'; +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `ID` bigint(20) unsigned NOT NULL, + `USER` varchar(32) DEFAULT NULL, + `HOST` varchar(66) DEFAULT NULL, + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) DEFAULT NULL, + `TIME` bigint(20) DEFAULT NULL, + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 +SET GLOBAL performance_schema_show_processlist = 'OFF'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] Sending data SELECT * FROM performance_schema.processlist +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] Sending data SELECT * FROM performance_schema.processlist +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] Sending data SHOW PROCESSLIST +## +## Server shutdown +## +## +### Server restart +## +SHOW CREATE TABLE performance_schema.processlist; +Table Create Table +processlist CREATE TABLE `processlist` ( + `ID` bigint(20) unsigned NOT NULL, + `USER` varchar(32) DEFAULT NULL, + `HOST` varchar(66) DEFAULT NULL, + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) DEFAULT NULL, + `TIME` bigint(20) DEFAULT NULL, + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 +SET GLOBAL performance_schema_show_processlist = 'OFF'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] Sending data SELECT * FROM performance_schema.processlist +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] starting SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'ON'; +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] Sending data SELECT * FROM performance_schema.processlist +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +2 root [HOST:PORT] test Query [TIME] executing SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST +SHOW PROCESSLIST; +Id User Host db Command Time State Info +2 root [HOST:PORT] test Query [TIME] Sending data SHOW PROCESSLIST +SET GLOBAL performance_schema_show_processlist = 'OFF'; diff --git a/mysql-test/suite/perfschema/r/processlist_acl.result b/mysql-test/suite/perfschema/r/processlist_acl.result new file mode 100644 index 00000000000..2e60b403484 --- /dev/null +++ b/mysql-test/suite/perfschema/r/processlist_acl.result @@ -0,0 +1,255 @@ +## +## 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; diff --git a/mysql-test/suite/perfschema/r/processlist_anonymous.result b/mysql-test/suite/perfschema/r/processlist_anonymous.result new file mode 100644 index 00000000000..a27a6df2606 --- /dev/null +++ b/mysql-test/suite/perfschema/r/processlist_anonymous.result @@ -0,0 +1,66 @@ +## +## Test the Performance Schema-based implementation of SHOW PROCESSLIST. +## Verify behavior for anonymous users and PROCESS_ACL. +## +SELECT @@global.performance_schema_show_processlist INTO @save_processlist; +SET @@global.performance_schema_show_processlist = OFF; +SHOW GRANTS; +Grants for @localhost +GRANT USAGE ON *.* TO ''@'localhost' +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SHOW PROCESSLIST; +ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation +SET @@global.performance_schema_show_processlist = ON; +SHOW GRANTS; +Grants for @localhost +GRANT USAGE ON *.* TO ''@'localhost' +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; +ID USER HOST DB COMMAND TIME STATE INFO +SELECT * FROM performance_schema.processlist; +ID USER HOST DB COMMAND TIME STATE INFO +SHOW PROCESSLIST; +ERROR 42000: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation +GRANT PROCESS ON *.* TO ''@'localhost'; +SET @@global.performance_schema_show_processlist = OFF; +SHOW GRANTS; +Grants for @localhost +GRANT PROCESS ON *.* TO ''@'localhost' +SELECT count(*) >= 2 FROM INFORMATION_SCHEMA.PROCESSLIST; +count(*) >= 2 +1 +SELECT count(*) >= 2 FROM performance_schema.processlist; +count(*) >= 2 +1 +SHOW PROCESSLIST; +SELECT "Previous statement is now completed." as status; +status +Previous statement is now completed. +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT as BROKEN_ROWS_SENT +FROM performance_schema.events_statements_history +WHERE SQL_TEXT = "SHOW PROCESSLIST"; +EVENT_NAME SQL_TEXT BROKEN_ROWS_SENT +statement/sql/show_processlist SHOW PROCESSLIST 0 +TRUNCATE TABLE performance_schema.events_statements_history; +set @@global.performance_schema_show_processlist = ON; +SHOW GRANTS; +Grants for @localhost +GRANT PROCESS ON *.* TO ''@'localhost' +SELECT count(*) >= 2 FROM INFORMATION_SCHEMA.PROCESSLIST; +count(*) >= 2 +1 +SELECT count(*) >= 2 FROM performance_schema.processlist; +count(*) >= 2 +1 +SHOW PROCESSLIST; +SELECT "Previous statement is now completed." as status; +status +Previous statement is now completed. +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT >= 2 +FROM performance_schema.events_statements_history +WHERE SQL_TEXT = "SHOW PROCESSLIST"; +EVENT_NAME SQL_TEXT ROWS_SENT >= 2 +statement/sql/show_processlist SHOW PROCESSLIST 1 +SET @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/r/processlist_no_pfs.result b/mysql-test/suite/perfschema/r/processlist_no_pfs.result new file mode 100644 index 00000000000..16e5a13b52a --- /dev/null +++ b/mysql-test/suite/perfschema/r/processlist_no_pfs.result @@ -0,0 +1,181 @@ +show databases; +Database +information_schema +mtr +mysql +performance_schema +sys +test +select count(*) from performance_schema.performance_timers; +count(*) +0 +select count(*) from performance_schema.setup_consumers; +count(*) +0 +select count(*) > 4 from performance_schema.setup_instruments; +count(*) > 4 +0 +select count(*) from performance_schema.setup_timers; +count(*) +0 +Warnings: +Warning 1681 'performance_schema.setup_timers' is deprecated and will be removed in a future release. +select * from performance_schema.accounts; +select * from performance_schema.cond_instances; +select * from performance_schema.events_stages_current; +select * from performance_schema.events_stages_history; +select * from performance_schema.events_stages_history_long; +select * from performance_schema.events_stages_summary_by_account_by_event_name; +select * from performance_schema.events_stages_summary_by_host_by_event_name; +select * from performance_schema.events_stages_summary_by_thread_by_event_name; +select * from performance_schema.events_stages_summary_by_user_by_event_name; +select * from performance_schema.events_stages_summary_global_by_event_name; +select * from performance_schema.events_statements_current; +select * from performance_schema.events_statements_history; +select * from performance_schema.events_statements_history_long; +select * from performance_schema.events_statements_summary_by_account_by_event_name; +select * from performance_schema.events_statements_summary_by_digest; +select * from performance_schema.events_statements_summary_by_host_by_event_name; +select * from performance_schema.events_statements_summary_by_thread_by_event_name; +select * from performance_schema.events_statements_summary_by_user_by_event_name; +select * from performance_schema.events_statements_summary_global_by_event_name; +select * from performance_schema.events_transactions_current; +select * from performance_schema.events_transactions_history; +select * from performance_schema.events_transactions_history_long; +select * from performance_schema.events_transactions_summary_by_account_by_event_name; +select * from performance_schema.events_transactions_summary_by_host_by_event_name; +select * from performance_schema.events_transactions_summary_by_thread_by_event_name; +select * from performance_schema.events_transactions_summary_by_user_by_event_name; +select * from performance_schema.events_transactions_summary_global_by_event_name; +select * from performance_schema.events_waits_current; +select * from performance_schema.events_waits_history; +select * from performance_schema.events_waits_history_long; +select * from performance_schema.events_waits_summary_by_account_by_event_name; +select * from performance_schema.events_waits_summary_by_host_by_event_name; +select * from performance_schema.events_waits_summary_by_instance; +select * from performance_schema.events_waits_summary_by_thread_by_event_name; +select * from performance_schema.events_waits_summary_by_user_by_event_name; +select * from performance_schema.events_waits_summary_global_by_event_name; +select * from performance_schema.memory_summary_by_account_by_event_name; +select * from performance_schema.memory_summary_by_host_by_event_name; +select * from performance_schema.memory_summary_by_thread_by_event_name; +select * from performance_schema.memory_summary_by_user_by_event_name; +select * from performance_schema.memory_summary_global_by_event_name; +select * from performance_schema.file_instances; +select * from performance_schema.file_summary_by_event_name; +select * from performance_schema.file_summary_by_instance; +select * from performance_schema.host_cache; +select * from performance_schema.hosts; +select * from performance_schema.memory_summary_by_account_by_event_name; +select * from performance_schema.memory_summary_by_host_by_event_name; +select * from performance_schema.memory_summary_by_thread_by_event_name; +select * from performance_schema.memory_summary_by_user_by_event_name; +select * from performance_schema.memory_summary_global_by_event_name; +select * from performance_schema.metadata_locks; +select * from performance_schema.mutex_instances; +select * from performance_schema.objects_summary_global_by_type; +select * from performance_schema.performance_timers; +select * from performance_schema.rwlock_instances; +select * from performance_schema.session_account_connect_attrs; +select * from performance_schema.session_connect_attrs; +select * from performance_schema.setup_actors; +select * from performance_schema.setup_consumers; +select * from performance_schema.setup_instruments; +select * from performance_schema.setup_objects; +select * from performance_schema.setup_timers; +select * from performance_schema.socket_instances; +select * from performance_schema.socket_summary_by_instance; +select * from performance_schema.socket_summary_by_event_name; +select * from performance_schema.table_handles; +select * from performance_schema.table_io_waits_summary_by_index_usage; +select * from performance_schema.table_io_waits_summary_by_table; +select * from performance_schema.table_lock_waits_summary_by_table; +select * from performance_schema.threads; +select * from performance_schema.users; +select * from performance_schema.replication_connection_configuration; +select * from performance_schema.replication_connection_status; +select * from performance_schema.replication_applier_configuration; +select * from performance_schema.replication_applier_status; +select * from performance_schema.replication_applier_status_by_coordinator; +select * from performance_schema.replication_applier_status_by_worker; +select * from performance_schema.global_status; +select * from performance_schema.status_by_thread; +select * from performance_schema.status_by_user; +select * from performance_schema.status_by_host; +select * from performance_schema.status_by_account; +select * from performance_schema.session_status; +select * from performance_schema.global_variables; +select * from performance_schema.variables_by_thread; +select * from performance_schema.session_variables; +show global variables like "performance_schema%"; +Variable_name Value +performance_schema OFF +performance_schema_accounts_size 0 +performance_schema_digests_size 0 +performance_schema_events_stages_history_long_size 0 +performance_schema_events_stages_history_size 0 +performance_schema_events_statements_history_long_size 0 +performance_schema_events_statements_history_size 0 +performance_schema_events_transactions_history_long_size 0 +performance_schema_events_transactions_history_size 0 +performance_schema_events_waits_history_long_size 0 +performance_schema_events_waits_history_size 0 +performance_schema_hosts_size 0 +performance_schema_max_cond_classes 0 +performance_schema_max_cond_instances 0 +performance_schema_max_digest_length 0 +performance_schema_max_file_classes 0 +performance_schema_max_file_handles 0 +performance_schema_max_file_instances 0 +performance_schema_max_index_stat 0 +performance_schema_max_memory_classes 0 +performance_schema_max_metadata_locks 0 +performance_schema_max_mutex_classes 0 +performance_schema_max_mutex_instances 0 +performance_schema_max_prepared_statements_instances 0 +performance_schema_max_program_instances 0 +performance_schema_max_rwlock_classes 0 +performance_schema_max_rwlock_instances 0 +performance_schema_max_socket_classes 0 +performance_schema_max_socket_instances 0 +performance_schema_max_sql_text_length 0 +performance_schema_max_stage_classes 0 +performance_schema_max_statement_classes 0 +performance_schema_max_statement_stack 0 +performance_schema_max_table_handles 0 +performance_schema_max_table_instances 0 +performance_schema_max_table_lock_stat 0 +performance_schema_max_thread_classes 0 +performance_schema_max_thread_instances 0 +performance_schema_session_connect_attrs_size 0 +performance_schema_setup_actors_size 0 +performance_schema_setup_objects_size 0 +performance_schema_show_processlist ON +performance_schema_users_size 0 +show engine PERFORMANCE_SCHEMA status; +show global status like "performance_schema%"; + +select * from information_schema.engines +where engine = "PERFORMANCE_SCHEMA"; +ENGINE SUPPORT COMMENT TRANSACTIONS XA SAVEPOINTS +PERFORMANCE_SCHEMA YES Performance Schema NO NO NO + +# If the Performance Schema is disabled, then expect +# performance-schema-show-processlist = OFF +# regardless of its initial setting + +select @@global.performance_schema_show_processlist; +@@global.performance_schema_show_processlist +1 + +# If the Performance Schema is disabled, then setting +# performance-schema-show-processlist = ON +# succeeds, SHOW PROCESSLIST returns no data. + +set @@global.performance_schema_show_processlist = ON; +SHOW PROCESSLIST; +Id User Host db Command Time State Info + +show global variables like "performance_schema"; +Variable_name Value +performance_schema OFF diff --git a/mysql-test/suite/perfschema/r/processlist_port.result b/mysql-test/suite/perfschema/r/processlist_port.result new file mode 100644 index 00000000000..c11ff41f112 --- /dev/null +++ b/mysql-test/suite/perfschema/r/processlist_port.result @@ -0,0 +1,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; diff --git a/mysql-test/suite/perfschema/r/processlist_reg_user.result b/mysql-test/suite/perfschema/r/processlist_reg_user.result new file mode 100644 index 00000000000..72e879e4c09 --- /dev/null +++ b/mysql-test/suite/perfschema/r/processlist_reg_user.result @@ -0,0 +1,89 @@ +## +## Test the Performance Schema-based implementation of SHOW PROCESSLIST. +## Verify behavior for regular users and PROCESS_ACL. +## +SELECT @@global.performance_schema_show_processlist INTO @save_processlist; +SET @@global.performance_schema_show_processlist = OFF; +CREATE USER 'regular'@'localhost'; +SHOW GRANTS; +Grants for regular@localhost +GRANT USAGE ON *.* TO 'regular'@'localhost' +SELECT USER, INFO FROM INFORMATION_SCHEMA.PROCESSLIST; +USER INFO +regular SELECT USER, INFO FROM INFORMATION_SCHEMA.PROCESSLIST +SELECT USER, INFO FROM performance_schema.processlist; +USER INFO +regular SELECT USER, INFO FROM performance_schema.processlist +SHOW PROCESSLIST; +SELECT "Previous statement is now completed." as status; +status +Previous statement is now completed. +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT as BROKEN_ROWS_SENT +FROM performance_schema.events_statements_history +WHERE SQL_TEXT = "SHOW PROCESSLIST"; +EVENT_NAME SQL_TEXT BROKEN_ROWS_SENT +statement/sql/show_processlist SHOW PROCESSLIST 0 +TRUNCATE TABLE performance_schema.events_statements_history; +SET @@global.performance_schema_show_processlist = ON; +SHOW GRANTS; +Grants for regular@localhost +GRANT USAGE ON *.* TO 'regular'@'localhost' +SELECT USER, INFO FROM INFORMATION_SCHEMA.PROCESSLIST; +USER INFO +regular SELECT USER, INFO FROM INFORMATION_SCHEMA.PROCESSLIST +SELECT USER, INFO FROM performance_schema.processlist; +USER INFO +regular SELECT USER, INFO FROM performance_schema.processlist +SHOW PROCESSLIST; +SELECT "Previous statement is now completed." as status; +status +Previous statement is now completed. +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT +FROM performance_schema.events_statements_history +WHERE SQL_TEXT = "SHOW PROCESSLIST"; +EVENT_NAME SQL_TEXT ROWS_SENT +statement/sql/show_processlist SHOW PROCESSLIST 1 +TRUNCATE TABLE performance_schema.events_statements_history; +GRANT PROCESS ON *.* TO 'regular'@'localhost'; +SET @@global.performance_schema_show_processlist = OFF; +SHOW GRANTS; +Grants for regular@localhost +GRANT PROCESS ON *.* TO 'regular'@'localhost' +SELECT count(*) >= 2 FROM INFORMATION_SCHEMA.PROCESSLIST; +count(*) >= 2 +1 +SELECT count(*) >= 2 FROM performance_schema.processlist; +count(*) >= 2 +1 +SHOW PROCESSLIST; +SELECT "Previous statement is now completed." as status; +status +Previous statement is now completed. +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT as BROKEN_ROWS_SENT +FROM performance_schema.events_statements_history +WHERE SQL_TEXT = "SHOW PROCESSLIST"; +EVENT_NAME SQL_TEXT BROKEN_ROWS_SENT +statement/sql/show_processlist SHOW PROCESSLIST 0 +TRUNCATE TABLE performance_schema.events_statements_history; +SET @@global.performance_schema_show_processlist = ON; +SHOW GRANTS; +Grants for regular@localhost +GRANT PROCESS ON *.* TO 'regular'@'localhost' +SELECT count(*) >= 2 FROM INFORMATION_SCHEMA.PROCESSLIST; +count(*) >= 2 +1 +SELECT count(*) >= 2 FROM performance_schema.processlist; +count(*) >= 2 +1 +SHOW PROCESSLIST; +SELECT "Previous statement is now completed." as status; +status +Previous statement is now completed. +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT >= 2 +FROM performance_schema.events_statements_history +WHERE SQL_TEXT = "SHOW PROCESSLIST"; +EVENT_NAME SQL_TEXT ROWS_SENT >= 2 +statement/sql/show_processlist SHOW PROCESSLIST 1 +TRUNCATE TABLE performance_schema.events_statements_history; +SET @@global.performance_schema_show_processlist = @save_processlist; +DROP USER 'regular'@'localhost'; diff --git a/mysql-test/suite/perfschema/r/show_sanity.result b/mysql-test/suite/perfschema/r/show_sanity.result index dc326dd7f64..6fb33ea4d17 100644 --- a/mysql-test/suite/perfschema/r/show_sanity.result +++ b/mysql-test/suite/perfschema/r/show_sanity.result @@ -414,6 +414,7 @@ SHOW_MODE SOURCE VARIABLE_NAME 5.6 I_S.SESSION_VARIABLES INNODB_STATS_INCLUDE_DELETE_MARKED 5.6 I_S.SESSION_VARIABLES KEYRING_OPERATIONS 5.6 I_S.SESSION_VARIABLES LOG_STATEMENTS_UNSAFE_FOR_BINLOG +5.6 I_S.SESSION_VARIABLES PERFORMANCE_SCHEMA_SHOW_PROCESSLIST 5.6 I_S.SESSION_VARIABLES REPLICATION_OPTIMIZE_FOR_STATIC_PLUGIN_CONFIG 5.6 I_S.SESSION_VARIABLES REPLICATION_SENDER_OBSERVE_COMMIT_ONLY 5.6 I_S.SESSION_VARIABLES TLS_VERSION @@ -444,6 +445,7 @@ SHOW_MODE SOURCE VARIABLE_NAME 5.6 I_S.SESSION_VARIABLES INNODB_STATS_INCLUDE_DELETE_MARKED 5.6 I_S.SESSION_VARIABLES KEYRING_OPERATIONS 5.6 I_S.SESSION_VARIABLES LOG_STATEMENTS_UNSAFE_FOR_BINLOG +5.6 I_S.SESSION_VARIABLES PERFORMANCE_SCHEMA_SHOW_PROCESSLIST 5.6 I_S.SESSION_VARIABLES REPLICATION_OPTIMIZE_FOR_STATIC_PLUGIN_CONFIG 5.6 I_S.SESSION_VARIABLES REPLICATION_SENDER_OBSERVE_COMMIT_ONLY 5.6 I_S.SESSION_VARIABLES TLS_VERSION diff --git a/mysql-test/suite/perfschema/r/threads_mysql.result b/mysql-test/suite/perfschema/r/threads_mysql.result index d0748349af3..20c119da31f 100644 --- a/mysql-test/suite/perfschema/r/threads_mysql.result +++ b/mysql-test/suite/perfschema/r/threads_mysql.result @@ -3,7 +3,7 @@ SELECT name, type, processlist_user, processlist_host, processlist_db, processlist_command, processlist_info, connection_type, IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') AS unified_parent_thread_id, -role, instrumented +`role`, instrumented FROM performance_schema.threads WHERE name LIKE 'thread/sql%' ORDER BY name; @@ -39,7 +39,7 @@ processlist_info SELECT name, type, processlist_user, processlist_host, processl processlist_command, processlist_info, connection_type, IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') AS unified_parent_thread_id, -role, instrumented +`role`, instrumented FROM performance_schema.threads WHERE name LIKE 'thread/sql%' ORDER BY name @@ -66,7 +66,7 @@ SELECT name, type, processlist_user, processlist_host, processlist_db, processlist_command, processlist_info, IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') AS unified_parent_thread_id, -role, instrumented +`role`, instrumented FROM performance_schema.threads WHERE name LIKE 'thread/sql%' AND thread_id NOT IN (SELECT thread_id FROM t1) @@ -94,7 +94,7 @@ SELECT name, type, processlist_user, processlist_host, processlist_db, processlist_command, processlist_info, IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') AS unified_parent_thread_id, -role, instrumented +`role`, instrumented FROM performance_schema.threads WHERE name LIKE 'thread/sql%' AND thread_id NOT IN (SELECT thread_id FROM t1) diff --git a/mysql-test/suite/perfschema/t/ddl_processlist.test b/mysql-test/suite/perfschema/t/ddl_processlist.test new file mode 100644 index 00000000000..d960bf2df89 --- /dev/null +++ b/mysql-test/suite/perfschema/t/ddl_processlist.test @@ -0,0 +1,32 @@ +# Tests for PERFORMANCE_SCHEMA + +--source include/not_embedded.inc + +select @@global.performance_schema_show_processlist into @save_processlist; +set @@global.performance_schema_show_processlist = 'on'; + +--error ER_DBACCESS_DENIED_ERROR +alter table performance_schema.processlist + add column foo integer; + +--error ER_WRONG_PERFSCHEMA_USAGE +truncate table performance_schema.processlist; + +--error ER_DBACCESS_DENIED_ERROR +alter table performance_schema.processlist + add index test_index(info); + +--error ER_DBACCESS_DENIED_ERROR +create unique index test_index + on performance_schema.processlist(host); + +-- error ER_DBACCESS_DENIED_ERROR +drop index `PRIMARY` + on performance_schema.processlist; + +CREATE TABLE test.create_select + AS SELECT * from performance_schema.processlist; + +DROP TABLE test.create_select; + +set @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/t/dml_processlist.test b/mysql-test/suite/perfschema/t/dml_processlist.test new file mode 100644 index 00000000000..c075db3eabd --- /dev/null +++ b/mysql-test/suite/perfschema/t/dml_processlist.test @@ -0,0 +1,39 @@ +# Tests for PERFORMANCE_SCHEMA + +--source include/not_embedded.inc + +select @@global.performance_schema_show_processlist into @save_processlist; +set @@global.performance_schema_show_processlist = 'on'; + +--disable_result_log +select * from performance_schema.processlist + where user like 'event_scheduler'; + +select * from performance_schema.processlist + where user = 'FOO'; +--enable_result_log + +--error ER_TABLEACCESS_DENIED_ERROR +insert into performance_schema.processlist + values (12, 'foo', 'bar', 'test', null, 1000, 'state', 'info'); + +--error ER_TABLEACCESS_DENIED_ERROR +update performance_schema.processlist + set id=12, user='foo'; + +--error ER_TABLEACCESS_DENIED_ERROR +delete from performance_schema.processlist + where id <> 99; + +--error ER_TABLEACCESS_DENIED_ERROR +delete from performance_schema.processlist; + +--error ER_TABLEACCESS_DENIED_ERROR +LOCK TABLES performance_schema.processlist READ; +UNLOCK TABLES; + +--error ER_TABLEACCESS_DENIED_ERROR +LOCK TABLES performance_schema.processlist WRITE; +UNLOCK TABLES; + +set @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/t/misc_global_status-master.opt b/mysql-test/suite/perfschema/t/misc_global_status-master.opt new file mode 100644 index 00000000000..26b61f08d4e --- /dev/null +++ b/mysql-test/suite/perfschema/t/misc_global_status-master.opt @@ -0,0 +1 @@ +--loose-performance_schema_hosts_size=0 diff --git a/mysql-test/suite/perfschema/t/misc_global_status.test b/mysql-test/suite/perfschema/t/misc_global_status.test new file mode 100644 index 00000000000..be20835c66e --- /dev/null +++ b/mysql-test/suite/perfschema/t/misc_global_status.test @@ -0,0 +1,134 @@ +# Tests for PERFORMANCE_SCHEMA + +--source include/not_embedded.inc +--source include/have_perfschema.inc +--source include/have_query_cache_disabled.inc + +CREATE USER user1@localhost; +CREATE USER user2@localhost; +CREATE USER user3@localhost; + +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@localhost; + +# To aggregate to accounts +#SET GLOBAL show_compatibility_56=0; + +TRUNCATE TABLE performance_schema.accounts; + +FLUSH PRIVILEGES; + +CREATE TABLE test.t_range(a int, b int, PRIMARY KEY(a)); + +INSERT INTO test.t_range values (1, 1), (2,2), (3, 3), (4, 4), (5, 5); +INSERT INTO test.t_range values (6, 6), (7,7), (8, 8), (9, 9), (10, 10); + +FLUSH STATUS; + +let $initial= `SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'`; + +# Causes Select_range to increment (+1) +SELECT * from test.t_range where (a >= 3) AND (a <= 5); + +SELECT * from performance_schema.session_status + WHERE VARIABLE_NAME = 'Select_range'; + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +connect(con1, localhost, user1,,); +# Causes Select_range to increment (+1) +SELECT * from test.t_range where (a >= 3) AND (a <= 5); + +SELECT * from performance_schema.session_status + WHERE VARIABLE_NAME = 'Select_range'; + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +connect(con2, localhost, user2,,); +# Causes Select_range to increment (+2) +SELECT * from test.t_range where (a >= 3) AND (a <= 5); +SELECT * from test.t_range where (a >= 4) AND (a <= 6); + +SELECT * from performance_schema.session_status + WHERE VARIABLE_NAME = 'Select_range'; + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +connect(con3, localhost, user3,,); +# Causes Select_range to increment (+3) +SELECT * from test.t_range where (a >= 3) AND (a <= 5); +SELECT * from test.t_range where (a >= 4) AND (a <= 6); +SELECT * from test.t_range where (a >= 5) AND (a <= 7); + +SELECT * from performance_schema.session_status + WHERE VARIABLE_NAME = 'Select_range'; + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +--connection default + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +SELECT `USER`, `HOST`, VARIABLE_NAME, VARIABLE_VALUE + FROM performance_schema.status_by_account WHERE VARIABLE_NAME = 'Select_range' + AND `USER` LIKE 'user%' + ORDER BY `USER`; + +--disconnect con1 +--disconnect con2 + +# Wait till all disconnects are completed +let $count_sessions= 2; +--source include/wait_until_count_sessions.inc + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +TRUNCATE TABLE performance_schema.accounts; + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +--disconnect con3 + +# Wait till all disconnects are completed +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +# Make sure TRUNCATE on accounts does not add to global_status +TRUNCATE TABLE performance_schema.accounts; + +--disable_query_log +eval SELECT VARIABLE_NAME, (VARIABLE_VALUE - $initial) AS DELTA from performance_schema.global_status WHERE VARIABLE_NAME = 'Select_range'; +--enable_query_log + +DROP TABLE test.t_range; + +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user1@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user2@localhost; +REVOKE ALL PRIVILEGES, GRANT OPTION FROM user3@localhost; +DROP USER user1@localhost; +DROP USER user2@localhost; +DROP USER user3@localhost; + +#SET GLOBAL show_compatibility_56=1; + +FLUSH PRIVILEGES; + diff --git a/mysql-test/suite/perfschema/t/processlist-master.opt b/mysql-test/suite/perfschema/t/processlist-master.opt new file mode 100644 index 00000000000..f93413a61e5 --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist-master.opt @@ -0,0 +1 @@ +--event-scheduler diff --git a/mysql-test/suite/perfschema/t/processlist.test b/mysql-test/suite/perfschema/t/processlist.test new file mode 100644 index 00000000000..fed22153931 --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist.test @@ -0,0 +1,197 @@ +--echo ## +--echo ## Test the Performance Schema-based implementation of SHOW PROCESSLIST. +--echo ## +--echo ## Test cases: +--echo ## 1. Execute the new SHOW [FULL] PROCESSLIST and SELECT on performance_schema.processlist +--echo ## 2. Execute the legacy SHOW [FULL] PROCESSLIST and SELECT on information_schema.processlist +--echo ## 3. Verify that performance_schema_show_processlist = ON executes the new implementation +--echo ## 4. Verify that performance_schema_show_processlist = OFF executes the legacy code path +--echo ## +--echo ## Results must be manually verified. + +--source include/no_protocol.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/not_embedded.inc + +--echo +--echo ### Setup ### +--echo +select @@global.performance_schema_show_processlist into @save_processlist; + +--echo +create user user1@localhost, user2@localhost, + user3@localhost, user4@localhost; +--echo +grant ALL on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@localhost; +grant ALL on *.* to user4@localhost; +--echo +flush privileges; + +--echo +use test; +create table test.t1 (s1 int, s2 int, s3 int, s4 int); + +--echo +--echo # Switch to (con0, localhost, root, , ) +connect (con0, localhost, root, , ); +--echo +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); + +--echo +--echo # Lock test.t1, insert/update/deletes will block +lock tables t1 read; + +--echo +--echo # Connect (con1, localhost, user1, , ) +connect (con1, localhost, user1, , ); +--send insert into test.t1 values (0, 0, 0, 0) + +--echo +--echo # Connect (con2, localhost, user2, , ) +connect (con2, localhost, user2, , ); +# Create a query > 100 characters +--send 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; +--echo +--echo # Connect (con3, localhost, user3, , ) +connect (con3, localhost, user3, , ); +--send delete from test.t1 where s1 = 3 + +--echo +--echo # Connect (con4, localhost, user4, , ) +connect (con4, localhost, user4, , ); +--send insert into test.t1 values (4, 4, 4, 4) + +--echo +--echo # Connection default +--connection default + +--echo +--echo # Wait for queries to appear in the processlist table +let $wait_condition = select count(*) >= 4 from information_schema.processlist + where state like '%metadata%'; +--source include/wait_condition.inc + +--echo +--echo +--echo ### Execute new SHOW [FULL] PROCESSLIST and SELECT on performance_schema.processlist +--echo +let $pfs_spl = on; +--source ../include/processlist_set.inc +--source ../include/processlist_load.inc + +--echo +--echo +--echo ### Execute legacy SHOW [FULL] PROCESSLIST and SELECT on information_schema.processlist +--echo +let $pfs_spl = off; +--source ../include/processlist_set.inc +--source ../include/processlist_load.inc + +--echo +--echo +--echo ### Verify feature code path +--echo +--echo # Enable SHOW PROCESSLIST via the Performance Schema +let $pfs_spl = on; +--source ../include/processlist_set.inc + +--echo +--echo # Connection default, send SHOW PROCESSLIST +--connection default +SET DEBUG_SYNC='pfs_show_processlist_performance_schema SIGNAL pfs_processlist_pfs WAIT_FOR continue'; +--send SHOW FULL PROCESSLIST + +--echo +--echo # Connection con0 +--connection con0 +SET DEBUG_SYNC='now WAIT_FOR pfs_processlist_pfs'; +SET DEBUG_SYNC='now SIGNAL continue'; + +--echo +--echo # Connection default, reap +--connection default +--replace_column 1 <Id> 3 <Host> 5 <Command> 6 <Time> 7 <State> 8 <Info> +--sorted_result +--reap + +--echo +--echo +--echo ### Verify legacy code path +--echo +--echo # Enable the legacy SHOW PROCESSLIST +let $pfs_spl = off; +--source ../include/processlist_set.inc + +--echo +--echo # Connection default, send SHOW PROCESSLIST +--connection default +SET DEBUG_SYNC='RESET'; +SET DEBUG_SYNC='pfs_show_processlist_legacy SIGNAL pfs_processlist_legacy WAIT_FOR continue'; +--send SHOW FULL PROCESSLIST + +--echo +--echo # Connection con0 +--connection con0 +SET DEBUG_SYNC='now WAIT_FOR pfs_processlist_legacy'; +SET DEBUG_SYNC='now SIGNAL continue'; + +--echo +--echo # Connection default, reap +--connection default +--replace_column 1 <Id> 3 <Host> 5 <Command> 6 <Time> 7 <State> 8 <Info> +--sorted_result +--reap + +--echo +--echo +--echo ### Clean up ### +--echo + +--echo # Connection con0, unlock test.t1, disconnect +--connection con0 +unlock tables; +--disconnect con0 + +--disable_query_log +--disable_result_log +--echo +--echo # Connection con1, reap, disconnect +--connection con1 +--reap +--disconnect con1 + +--echo # Connection con2, reap, disconnect +--connection con2 +--reap +--disconnect con2 + +--echo # Connection con3, reap, disconnect +--connection con3 +--reap +--disconnect con3 + +--echo # Connection con4, reap, disconnect +--connection con4 +--reap +--disconnect con4 +--enable_result_log +--enable_query_log + +--echo +--echo # Connection default +--connection default + +--echo +drop table test.t1; +drop user user1@localhost; +drop user user2@localhost; +drop user user3@localhost; +drop user user4@localhost; +--echo +set @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/t/processlist_57.test b/mysql-test/suite/perfschema/t/processlist_57.test new file mode 100644 index 00000000000..748d8b7402b --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_57.test @@ -0,0 +1,349 @@ + +--source include/no_protocol.inc +--source include/not_embedded.inc + +call mtr.add_suppression("Optional native table 'performance_schema'.'processlist' has the wrong structure or is missing."); +call mtr.add_suppression("Column count of performance_schema.processlist is wrong. Expected 8, found 2. The table is probably corrupted"); + +--echo ## +--echo ## Verify fresh 5.7 instance +--echo ## + +SELECT @@global.performance_schema_show_processlist; + +SHOW CREATE TABLE performance_schema.processlist; + +--echo ## +--echo ## Simulate old 5.7 instance +--echo ## + +DROP TABLE performance_schema.processlist; + +--error ER_NO_SUCH_TABLE +SHOW CREATE TABLE performance_schema.processlist; + +let $restart_file= $MYSQLTEST_VARDIR/tmp/mysqld.1.expect; +--exec echo "wait" > $restart_file +--echo ## +--echo ## Server shutdown +--echo ## +--shutdown_server +--source include/wait_until_disconnected.inc +--echo ## +--echo ### Server restart +--echo ## +--exec echo "restart:">$restart_file +--enable_reconnect +--source include/wait_until_connected_again.inc + +--echo ## +--echo ## Verify old 5.7 instance +--echo ## + +SELECT @@global.performance_schema_show_processlist; + +--error ER_NO_SUCH_TABLE +SHOW CREATE TABLE performance_schema.processlist; + +--error ER_NO_SUCH_TABLE +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +--error ER_NO_SUCH_TABLE +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--error ER_NO_SUCH_TABLE +SHOW PROCESSLIST; + +--echo ## +--echo ## Perform broken upgrade (case 1) +--echo ## + +CREATE TABLE performance_schema.processlist (a int, b int); + +SHOW CREATE TABLE performance_schema.processlist; + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--error ER_BAD_FIELD_ERROR +SHOW PROCESSLIST; + +--exec echo "wait" > $restart_file +--echo ## +--echo ## Server shutdown +--echo ## +--shutdown_server +--source include/wait_until_disconnected.inc +--echo ## +--echo ### Server restart +--echo ## +--exec echo "restart:">$restart_file +--enable_reconnect +--source include/wait_until_connected_again.inc + +SHOW CREATE TABLE performance_schema.processlist; + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--error ER_BAD_FIELD_ERROR +SHOW PROCESSLIST; + +--echo ## +--echo ## Perform broken upgrade (case 2) +--echo ## + +DROP TABLE performance_schema.processlist; +CREATE TABLE performance_schema.processlist ( + `ID` bigint(20) unsigned NOT NULL, + `USER` varchar(32) DEFAULT NULL, + `HOST` varchar(66) DEFAULT NULL, + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) DEFAULT NULL, + `TIME` bigint(20) DEFAULT NULL, + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +); + +SHOW CREATE TABLE performance_schema.processlist; + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Works and returns no data, innodb table is empty. +SHOW PROCESSLIST; + +--exec echo "wait" > $restart_file +--echo ## +--echo ## Server shutdown +--echo ## +--shutdown_server +--source include/wait_until_disconnected.inc +--echo ## +--echo ### Server restart +--echo ## +--exec echo "restart:">$restart_file +--enable_reconnect +--source include/wait_until_connected_again.inc + +SHOW CREATE TABLE performance_schema.processlist; + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Works and returns no data, innodb table is empty. +SHOW PROCESSLIST; + +--echo ## +--echo ## Perform broken upgrade (case 3) +--echo ## + +DROP TABLE performance_schema.processlist; +CREATE TABLE performance_schema.processlist + LIKE INFORMATION_SCHEMA.PROCESSLIST; + +SHOW CREATE TABLE performance_schema.processlist; + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Works and returns no data, innodb table is empty. +SHOW PROCESSLIST; + +--exec echo "wait" > $restart_file +--echo ## +--echo ## Server shutdown +--echo ## +--shutdown_server +--source include/wait_until_disconnected.inc +--echo ## +--echo ### Server restart +--echo ## +--exec echo "restart:">$restart_file +--enable_reconnect +--source include/wait_until_connected_again.inc + +SHOW CREATE TABLE performance_schema.processlist; + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Works and returns no data, innodb table is empty. +SHOW PROCESSLIST; + +--echo ## +--echo ## Perform correct upgrade +--echo ## + +DROP TABLE performance_schema.processlist; +CREATE TABLE performance_schema.processlist ( + `ID` bigint(20) unsigned NOT NULL, + `USER` varchar(32) DEFAULT NULL, + `HOST` varchar(66) DEFAULT NULL, + `DB` varchar(64) DEFAULT NULL, + `COMMAND` varchar(16) DEFAULT NULL, + `TIME` bigint(20) DEFAULT NULL, + `STATE` varchar(64) DEFAULT NULL, + `INFO` longtext +) ENGINE = 'PERFORMANCE_SCHEMA'; + +SHOW CREATE TABLE performance_schema.processlist; + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +--exec echo "wait" > $restart_file +--echo ## +--echo ## Server shutdown +--echo ## +--shutdown_server +--source include/wait_until_disconnected.inc +--echo ## +--echo ### Server restart +--echo ## +--exec echo "restart:">$restart_file +--enable_reconnect +--source include/wait_until_connected_again.inc + +SHOW CREATE TABLE performance_schema.processlist; + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +SET GLOBAL performance_schema_show_processlist = 'ON'; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM performance_schema.processlist; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +--replace_column 3 [HOST:PORT] 6 [TIME] +SHOW PROCESSLIST; + +# Cleanup + +SET GLOBAL performance_schema_show_processlist = 'OFF'; + diff --git a/mysql-test/suite/perfschema/t/processlist_acl-master.opt b/mysql-test/suite/perfschema/t/processlist_acl-master.opt new file mode 100644 index 00000000000..f93413a61e5 --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_acl-master.opt @@ -0,0 +1 @@ +--event-scheduler diff --git a/mysql-test/suite/perfschema/t/processlist_acl.test b/mysql-test/suite/perfschema/t/processlist_acl.test new file mode 100644 index 00000000000..f0733865ebc --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_acl.test @@ -0,0 +1,196 @@ +--echo ## +--echo ## Test the Performance Schema-based implementation of SHOW PROCESSLIST. +--echo ## +--echo ## Verify handling of the SELECT and PROCESS privileges. +--echo ## +--echo ## Test cases: +--echo ## - Execute SHOW PROCESSLIST (new and legacy) with all privileges +--echo ## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with all privileges +--echo ## - Execute SHOW PROCESSLIST (new and legacy) with no privileges +--echo ## - Execute SELECT on the performance_schema.processlist and information_schema.processlist with no privileges +--echo ## +--echo ## Results must be manually verified. +--echo + +--source include/no_protocol.inc +--source include/not_embedded.inc + +--echo ### Setup ### +--echo +select @@global.performance_schema_show_processlist into @save_processlist; + +--echo +--echo # Control users +create user user_00@localhost, user_01@localhost; +grant ALL on *.* to user_00@localhost; +grant ALL on *.* to user_01@localhost; + +--echo +--echo # Test users +create user user_all@localhost, user_none@localhost; +grant ALL on *.* to user_all@localhost; +grant USAGE on *.* to user_none@localhost; +--echo +flush privileges; + +--echo +show grants for user_all@localhost; +--echo +show grants for user_none@localhost; + +--echo +use test; +create table test.t1 (s1 int, s2 int, s3 int, s4 int); + +--echo +--echo # Connect (con_00, localhost, user_00, , ) +connect (con_00, localhost, user_00, , ); + +--echo # Connect (con_01, localhost, user_01, , ) +connect (con_01, localhost, user_01, , ); +--echo +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); + +--echo +--echo # Lock test.t1, insert/update/deletes will block +lock tables t1 read; + +--echo +--echo # Establish 2 connections for user_all +--echo # Connect (con_all_1, localhost, user_all, , ) +connect (con_all_1, localhost, user_all, , ); + +--echo # Connect (con_all_2, localhost, user_all, , ) +connect (con_all_2, localhost, user_all, , ); +--send insert into test.t1 values (0, 0, 0, 0) + +--echo +--echo # Establish 4 connections for user_none +--echo # Connect (con_none_1, localhost, user_none, , ) +connect (con_none_1, localhost, user_none, , ); + +--echo # Connect (con_none_2, localhost, user_none, , ) +connect (con_none_2, localhost, user_none, , ); + +--echo # Connect (con_none_3, localhost, user_none, , ) +connect (con_none_3, localhost, user_none, , ); + +--echo # Connect (con_none_4, localhost, user_none, , ) +connect (con_none_4, localhost, user_none, , ); +--send update test.t1 set s1 = s1 + 1, s2 = s2 + 2; + +--echo +--echo # Connection con_all_1 +--connection con_all_1 + +# Note: When expecting processlist results for all users, the wait_condition +# and subsequent execution of SHOW PROCESSLIST should be conducted from the +# same connection to ensure consistent values in the Command and Info columns. + +--echo +--echo # Wait for queries to appear in the processlist table +let $wait_condition = select count(*) >= 2 from information_schema.processlist + where state like '%metadata%'; +--source include/wait_condition.inc + +--echo +--echo ### Execute SHOW PROCESSLIST with all privileges +--echo ### Expect all users +--echo +--echo # New SHOW PROCESSLIST +let $pfs_spl = on; +--source ../include/processlist_set.inc +--source ../include/processlist_load.inc + +--echo +--echo # Legacy SHOW PROCESSLIST +let $pfs_spl = off; +--source ../include/processlist_set.inc +--source ../include/processlist_load.inc + +--echo +--echo +--echo ### Execute SHOW PROCESSLIST with no SELECT and no PROCESS privileges +--echo ### Expect processes only from user_none +--echo +--echo # New SHOW PROCESSLIST +let $pfs_spl = on; +--source ../include/processlist_set.inc + +--echo +--echo # Connection con_none_1 +--connection con_none_1 +--source ../include/processlist_load.inc + +--echo +--echo # Confirm that only processes from user_none are visible +--echo +select count(*) as "Expect 0" from performance_schema.processlist + where user not in ('user_none'); + +--echo +--echo # Legacy SHOW PROCESSLIST +--connection con_00 +let $pfs_spl = off; +--source ../include/processlist_set.inc + +--echo +--echo # Connection con_none_1 +--connection con_none_1 +--source ../include/processlist_load.inc + +--echo +--echo +--echo ### Clean up ### +--echo + +--echo # Disconnect con_00 +--connection con_00 +--disconnect con_00 + +--echo # Connection con_01, unlock test.t1, disconnect +--connection con_01 +unlock tables; +--disconnect con_01 + +--echo # Disconnect con_all_1 +--connection con_all_1 +--disconnect con_all_1 + +--echo # Reap con_all_2, disconnect +--connection con_all_2 +--reap +--disconnect con_all_2 + +--echo # Disconnect con_none_1 +--connection con_none_1 +--disconnect con_none_1 + +--echo # Disconnect con_none_2 +--connection con_none_2 +--disconnect con_none_2 + +--echo # Disconnect con_none_3 +--connection con_none_3 +--disconnect con_none_3 + +--echo # Reap con_none_4, disconnect +--connection con_none_4 +--reap +--disconnect con_none_4 + +--echo +--echo # Connection default +--connection default + +--echo +drop table test.t1; +drop user user_00@localhost; +drop user user_01@localhost; +drop user user_all@localhost; +drop user user_none@localhost; +--echo +set @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/t/processlist_anonymous.test b/mysql-test/suite/perfschema/t/processlist_anonymous.test new file mode 100644 index 00000000000..4b8e7ec2e1f --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_anonymous.test @@ -0,0 +1,111 @@ +--echo ## +--echo ## Test the Performance Schema-based implementation of SHOW PROCESSLIST. +--echo ## Verify behavior for anonymous users and PROCESS_ACL. +--echo ## + +--source include/no_protocol.inc +--source include/not_embedded.inc + +SELECT @@global.performance_schema_show_processlist INTO @save_processlist; + +--source include/add_anonymous_users.inc + +SET @@global.performance_schema_show_processlist = OFF; + +connect (anon,localhost,test,,test,$MASTER_MYPORT,$MASTER_MYSOCK); + +SHOW GRANTS; + +# Empty +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Empty +SELECT * FROM performance_schema.processlist; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +SHOW PROCESSLIST; + +connection default; + +SET @@global.performance_schema_show_processlist = ON; + +connection anon; + +SHOW GRANTS; + +# Empty +SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Empty +SELECT * FROM performance_schema.processlist; + +--error ER_SPECIFIC_ACCESS_DENIED_ERROR +SHOW PROCESSLIST; + +disconnect anon; +connection default; + +GRANT PROCESS ON *.* TO ''@'localhost'; + +SET @@global.performance_schema_show_processlist = OFF; + +connect (anon2,localhost,test,,test,$MASTER_MYPORT,$MASTER_MYSOCK); + +SHOW GRANTS; + +# Full rows +SELECT count(*) >= 2 FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Full rows +SELECT count(*) >= 2 FROM performance_schema.processlist; + +# Full rows +--disable_result_log +SHOW PROCESSLIST; +--enable_result_log + +SELECT "Previous statement is now completed." as status; + +connection default; +# Returns ROWS_SENT = 0 even when SHOW PROCESSLIST has rows +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT as BROKEN_ROWS_SENT + FROM performance_schema.events_statements_history + WHERE SQL_TEXT = "SHOW PROCESSLIST"; +connection anon2; + + +connection default; + +TRUNCATE TABLE performance_schema.events_statements_history; +set @@global.performance_schema_show_processlist = ON; + + +connection anon2; + +SHOW GRANTS; + +# Full rows +SELECT count(*) >= 2 FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Full rows +SELECT count(*) >= 2 FROM performance_schema.processlist; + +# Full rows +--disable_result_log +SHOW PROCESSLIST; +--enable_result_log + +SELECT "Previous statement is now completed." as status; + +connection default; +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT >= 2 + FROM performance_schema.events_statements_history + WHERE SQL_TEXT = "SHOW PROCESSLIST"; + +disconnect anon2; +connection default; + +--source include/delete_anonymous_users.inc + +SET @@global.performance_schema_show_processlist = @save_processlist; + diff --git a/mysql-test/suite/perfschema/t/processlist_no_pfs-master.opt b/mysql-test/suite/perfschema/t/processlist_no_pfs-master.opt new file mode 100644 index 00000000000..6fb075f60bc --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_no_pfs-master.opt @@ -0,0 +1,2 @@ +--loose-disable-performance-schema +--loose-performance-schema-show-processlist=ON diff --git a/mysql-test/suite/perfschema/t/processlist_no_pfs.test b/mysql-test/suite/perfschema/t/processlist_no_pfs.test new file mode 100644 index 00000000000..273e180a18a --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_no_pfs.test @@ -0,0 +1,29 @@ +# Tests for PERFORMANCE_SCHEMA + +--source include/not_embedded.inc + +--source ../include/start_server_common.inc + +--echo +select * from information_schema.engines + where engine = "PERFORMANCE_SCHEMA"; + +--echo +--echo # If the Performance Schema is disabled, then expect +--echo # performance-schema-show-processlist = OFF +--echo # regardless of its initial setting +--echo +select @@global.performance_schema_show_processlist; + +--echo +--echo # If the Performance Schema is disabled, then setting +--echo # performance-schema-show-processlist = ON +--echo # succeeds, SHOW PROCESSLIST returns no data. +--echo +set @@global.performance_schema_show_processlist = ON; + +SHOW PROCESSLIST; + +--echo +show global variables like "performance_schema"; + diff --git a/mysql-test/suite/perfschema/t/processlist_port-master.opt b/mysql-test/suite/perfschema/t/processlist_port-master.opt new file mode 100644 index 00000000000..f93413a61e5 --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_port-master.opt @@ -0,0 +1 @@ +--event-scheduler diff --git a/mysql-test/suite/perfschema/t/processlist_port.test b/mysql-test/suite/perfschema/t/processlist_port.test new file mode 100644 index 00000000000..8403b0c793b --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_port.test @@ -0,0 +1,162 @@ +--echo ## +--echo ## Test the Performance Schema-based implementation of SHOW PROCESSLIST. +--echo ## +--echo ## Verify the Host field (hostname:port) against the legacy implementation. +--echo ## + +--source include/no_protocol.inc +--source include/not_embedded.inc + +--echo +--echo ### Setup ### +--echo +select @@global.performance_schema_show_processlist into @save_processlist; + +--echo +--echo # Control user +create user user0@localhost; +grant ALL on *.* to user0@localhost; + +--echo # Test users +--echo +create user user1@localhost, user2@localhost, + user3@localhost, user4@localhost; +--echo +# Grant no privileges to user1 to ensure only one process +grant USAGE on *.* to user1@localhost; +grant ALL on *.* to user2@localhost; +grant ALL on *.* to user3@localhost; +grant ALL on *.* to user4@localhost; +--echo +flush privileges; +--echo +show grants for user1@localhost; + +--echo +--echo # Connect (con_user0, 127.0.0.1, user0, , , MASTER_MYPORT, ) +connect (con_user0, 127.0.0.1, user0, , , $MASTER_MYPORT, ); +--echo +select connection_id() into @con_user0_id; + +--echo # Connect (con_user1, 127.0.0.1, user1, , , MASTER_MYPORT, ) +connect (con_user1, 127.0.0.1, user1, , , $MASTER_MYPORT, ); + +--echo # Connect (con_user2, 127.0.0.1, user2, , , MASTER_MYPORT, ) +connect (con_user2, 127.0.0.1, user2, , , $MASTER_MYPORT, ); + +--echo # Connect (con_user3, 127.0.0.1, user3, , , MASTER_MYPORT, ) +connect (con_user3, 127.0.0.1, user3, , , $MASTER_MYPORT, ); + +--echo # Connect (con_user4, 127.0.0.1, user4, , , MASTER_MYPORT, ) +connect (con_user4, 127.0.0.1, user4, , , $MASTER_MYPORT, ); + +--echo # Connection user0 +--connection con_user0 +let $wait_condition = select connection_id() = @con_user0_id; +--source include/wait_condition.inc + +--echo +--echo ### Compare the SHOW PROCESSLIST Host column between the new and old implementations +--echo +--echo ## New SHOW PROCESSLIST +let $pfs_spl = on; +--source ../include/processlist_set.inc +--source ../include/processlist_load.inc + +--echo +--echo # Connection user1 +--connection con_user1 + +--echo # Get Host:Port, new +let $host_new = query_get_value(SHOW FULL PROCESSLIST, Host, 1); + +--echo +--echo ## Legacy SHOW PROCESSLIST +--connection con_user0 +let $pfs_spl = off; +--source ../include/processlist_set.inc +--source ../include/processlist_load.inc + +--echo +--echo # Connection user1 +--connection con_user1 + +--echo # Get Host:Port, legacy +let $host_old = query_get_value(SHOW FULL PROCESSLIST, Host, 1); + +--echo + +## DEBUG ONLY +## --echo DEBUG: New: $host_new Old: $host_old + +if ($host_new == $host_old) +{ + --echo ***SUCCESS*** The SHOW PROCESSLIST Host fields match +} + +if ($host_new != $host_old) +{ + --echo ***ERROR*** SHOW PROCESSLIST Host fields do not match. New: $host_new Old: $host_old +} + +--echo +--echo ### Compare the processlist Host column between Performance Schema and the Information Schema +--echo +--echo # Connection con_user0 +--connection con_user0 +let $count_new = `select count(*) from performance_schema.processlist`; +let $count_old = `select count(*) from information_schema.processlist`; +let $count_join = `select count(*) from performance_schema.processlist pspl + left join information_schema.processlist ispl on pspl.host = ispl.host and pspl.id = ispl.id`; + +--echo + +if ($count_old == $count_join) +{ + --echo ***SUCCESS*** The processlist Host fields match between the Performance Schema and the Information Schema +} + +if ($count_old != $count_join) +{ + --echo ***ERROR*** The processlist Host fields do not match + --echo Count new: $count_new Count old: $count_old Count join: $count_join + --echo + select * from performance_schema.processlist order by host, id; + --echo + select * from information_schema.processlist order by host, id; + --echo + select * from performance_schema.processlist pspl + left join information_schema.processlist ispl on pspl.host = ispl.host and pspl.id = ispl.id; +} + +--echo +--echo +--echo ### Clean up ### +--echo + +--echo # Disconnect con_user0 +--connection con_user0 +--disconnect con_user0 +--echo # Disconnect con_user1 +--connection con_user1 +--disconnect con_user1 +--echo # Disconnect con_user2 +--connection con_user2 +--disconnect con_user2 +--echo # Disconnect con_user3 +--connection con_user3 +--disconnect con_user3 +--echo # Disconnect con_user4 +--connection con_user4 +--disconnect con_user4 + +--echo # Connection default +--connection default +--echo +drop user user0@localhost; +drop user user1@localhost; +drop user user2@localhost; +drop user user3@localhost; +drop user user4@localhost; +--echo +set @@global.performance_schema_show_processlist = @save_processlist; diff --git a/mysql-test/suite/perfschema/t/processlist_reg_user.test b/mysql-test/suite/perfschema/t/processlist_reg_user.test new file mode 100644 index 00000000000..53dc8abfa63 --- /dev/null +++ b/mysql-test/suite/perfschema/t/processlist_reg_user.test @@ -0,0 +1,134 @@ +--echo ## +--echo ## Test the Performance Schema-based implementation of SHOW PROCESSLIST. +--echo ## Verify behavior for regular users and PROCESS_ACL. +--echo ## + +--source include/no_protocol.inc +--source include/not_embedded.inc + +SELECT @@global.performance_schema_show_processlist INTO @save_processlist; + +--source include/add_anonymous_users.inc + +SET @@global.performance_schema_show_processlist = OFF; + +CREATE USER 'regular'@'localhost'; + +connect (reg,localhost,regular,,test,$MASTER_MYPORT,$MASTER_MYSOCK); + +SHOW GRANTS; + +# Self rows only +SELECT USER, INFO FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Self rows only +SELECT USER, INFO FROM performance_schema.processlist; + +# Self rows only +--disable_result_log +SHOW PROCESSLIST; +--enable_result_log + +SELECT "Previous statement is now completed." as status; + +connection default; + +# Returns ROWS_SENT = 0 even when SHOW PROCESSLIST has rows +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT as BROKEN_ROWS_SENT + FROM performance_schema.events_statements_history + WHERE SQL_TEXT = "SHOW PROCESSLIST"; + +TRUNCATE TABLE performance_schema.events_statements_history; +SET @@global.performance_schema_show_processlist = ON; + +connection reg; + +SHOW GRANTS; + +# Self rows only +SELECT USER, INFO FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Self rows only +SELECT USER, INFO FROM performance_schema.processlist; + +# Self rows only +--disable_result_log +SHOW PROCESSLIST; +--enable_result_log + +SELECT "Previous statement is now completed." as status; + +connection default; + +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT + FROM performance_schema.events_statements_history + WHERE SQL_TEXT = "SHOW PROCESSLIST"; + +TRUNCATE TABLE performance_schema.events_statements_history; + +disconnect reg; + +GRANT PROCESS ON *.* TO 'regular'@'localhost'; + +SET @@global.performance_schema_show_processlist = OFF; + +connect (reg2,localhost,regular,,test,$MASTER_MYPORT,$MASTER_MYSOCK); + +SHOW GRANTS; + +# Full rows +SELECT count(*) >= 2 FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Full rows +SELECT count(*) >= 2 FROM performance_schema.processlist; + +# Full rows +--disable_result_log +SHOW PROCESSLIST; +--enable_result_log + +SELECT "Previous statement is now completed." as status; + +connection default; + +# Returns ROWS_SENT = 0 even when SHOW PROCESSLIST has rows +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT as BROKEN_ROWS_SENT + FROM performance_schema.events_statements_history + WHERE SQL_TEXT = "SHOW PROCESSLIST"; + +TRUNCATE TABLE performance_schema.events_statements_history; +SET @@global.performance_schema_show_processlist = ON; + +connection reg2; + +SHOW GRANTS; + +# Full rows +SELECT count(*) >= 2 FROM INFORMATION_SCHEMA.PROCESSLIST; + +# Full rows +SELECT count(*) >= 2 FROM performance_schema.processlist; + +# Full rows +--disable_result_log +SHOW PROCESSLIST; +--enable_result_log + +SELECT "Previous statement is now completed." as status; + +connection default; + +SELECT EVENT_NAME, SQL_TEXT, ROWS_SENT >= 2 + FROM performance_schema.events_statements_history + WHERE SQL_TEXT = "SHOW PROCESSLIST"; + +TRUNCATE TABLE performance_schema.events_statements_history; + +disconnect reg2; + +--source include/delete_anonymous_users.inc + +SET @@global.performance_schema_show_processlist = @save_processlist; + +DROP USER 'regular'@'localhost'; + diff --git a/mysql-test/suite/perfschema/t/threads_mysql.test b/mysql-test/suite/perfschema/t/threads_mysql.test index 8a021055d44..e19b3664f21 100644 --- a/mysql-test/suite/perfschema/t/threads_mysql.test +++ b/mysql-test/suite/perfschema/t/threads_mysql.test @@ -35,7 +35,7 @@ SELECT name, type, processlist_user, processlist_host, processlist_db, processlist_command, processlist_info, connection_type, IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') AS unified_parent_thread_id, - role, instrumented + `role`, instrumented FROM performance_schema.threads WHERE name LIKE 'thread/sql%' ORDER BY name; @@ -54,7 +54,7 @@ SELECT name, type, processlist_user, processlist_host, processlist_db, processlist_command, processlist_info, IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') AS unified_parent_thread_id, - role, instrumented + `role`, instrumented FROM performance_schema.threads WHERE name LIKE 'thread/sql%' AND thread_id NOT IN (SELECT thread_id FROM t1) @@ -94,7 +94,7 @@ SELECT name, type, processlist_user, processlist_host, processlist_db, processlist_command, processlist_info, IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') AS unified_parent_thread_id, - role, instrumented + `role`, instrumented FROM performance_schema.threads WHERE name LIKE 'thread/sql%' AND thread_id NOT IN (SELECT thread_id FROM t1) |