#At file:///work2/repo/mysql-next-mr-wl4674-1/ based on revid:marc.alff@stripped
3165 Matthias Leich 2010-07-29
Improved and additional tests for
WL#4674 PERFORMANCE_SCHEMA Setup For Actors
Attention:
schema_lc1.result and schema_lc2.result are "guesses" because I couldn't
check them within my environment.
@ mysql-test/suite/perfschema/include/schema.inc
- make test more robust against databases to be added in future
- add the missing show create
@ mysql-test/suite/perfschema/t/schema_lc0.test
Improved comment
@ mysql-test/suite/perfschema/t/schema_lc1.test
Improved comment
@ mysql-test/suite/perfschema/t/schema_lc2.test
Improved comment
@ mysql-test/suite/perfschema/t/setup_actors.test
- Improved comments
- add several subtests for properties mentioned within the WL
- minor changes in formatting which improve the synchronization in graphical diff tools
@ mysql-test/suite/perfschema/t/threads_innodb.test
Show InnoDB related content in performance_schema.THREADS
@ mysql-test/suite/perfschema/t/threads_insert_delayed.test
Show the insert delayed thread in performance_schema.THREADS
@ mysql-test/suite/perfschema/t/threads_mysql.test
Show MySQL server related content in performance_schema.THREADS
- event scheduler, event worker, signal handler, current connection, main
- parent child relationship of threads
but no storage engine related stuff.
added:
mysql-test/suite/perfschema/r/threads_events.result
mysql-test/suite/perfschema/r/threads_innodb.result
mysql-test/suite/perfschema/r/threads_insert_delayed.result
mysql-test/suite/perfschema/r/threads_mysql.result
mysql-test/suite/perfschema/t/threads_innodb.test
mysql-test/suite/perfschema/t/threads_insert_delayed.test
mysql-test/suite/perfschema/t/threads_mysql-master.opt
mysql-test/suite/perfschema/t/threads_mysql.test
modified:
mysql-test/suite/perfschema/include/schema.inc
mysql-test/suite/perfschema/r/schema_lc0.result
mysql-test/suite/perfschema/r/schema_lc1.result
mysql-test/suite/perfschema/r/schema_lc2.result
mysql-test/suite/perfschema/r/setup_actors.result
mysql-test/suite/perfschema/t/schema_lc0.test
mysql-test/suite/perfschema/t/schema_lc1.test
mysql-test/suite/perfschema/t/schema_lc2.test
mysql-test/suite/perfschema/t/setup_actors.test
=== modified file 'mysql-test/suite/perfschema/include/schema.inc'
--- a/mysql-test/suite/perfschema/include/schema.inc 2010-07-02 16:36:20 +0000
+++ b/mysql-test/suite/perfschema/include/schema.inc 2010-07-29 19:48:05 +0000
@@ -14,8 +14,10 @@
# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
# Tests for PERFORMANCE_SCHEMA
+# Show existing objects and information about their structure
-show databases;
+show databases like 'performance_schema';
+show create database performance_schema;
use performance_schema;
=== modified file 'mysql-test/suite/perfschema/r/schema_lc0.result'
--- a/mysql-test/suite/perfschema/r/schema_lc0.result 2010-05-12 14:04:32 +0000
+++ b/mysql-test/suite/perfschema/r/schema_lc0.result 2010-07-29 19:48:05 +0000
@@ -1,10 +1,9 @@
-show databases;
-Database
-information_schema
-mtr
-mysql
+show databases like 'performance_schema';
+Database (performance_schema)
performance_schema
-test
+show create database performance_schema;
+Database Create Database
+performance_schema CREATE DATABASE `performance_schema` /*!40100 DEFAULT CHARACTER SET utf8 */
use performance_schema;
show tables;
Tables_in_performance_schema
=== modified file 'mysql-test/suite/perfschema/r/schema_lc1.result'
--- a/mysql-test/suite/perfschema/r/schema_lc1.result 2010-05-14 14:21:56 +0000
+++ b/mysql-test/suite/perfschema/r/schema_lc1.result 2010-07-29 19:48:05 +0000
@@ -1,10 +1,9 @@
-show databases;
-Database
-information_schema
-mtr
-mysql
+show databases like 'performance_schema';
+Database (performance_schema)
performance_schema
-test
+show create database performance_schema;
+Database Create Database
+performance_schema CREATE DATABASE `performance_schema` /*!40100 DEFAULT CHARACTER SET utf8 */
use performance_schema;
show tables;
Tables_in_performance_schema
=== modified file 'mysql-test/suite/perfschema/r/schema_lc2.result'
--- a/mysql-test/suite/perfschema/r/schema_lc2.result 2010-05-12 14:04:32 +0000
+++ b/mysql-test/suite/perfschema/r/schema_lc2.result 2010-07-29 19:48:05 +0000
@@ -1,10 +1,9 @@
-show databases;
-Database
-information_schema
-mtr
-mysql
+show databases like 'performance_schema';
+Database (performance_schema)
performance_schema
-test
+show create database performance_schema;
+Database Create Database
+performance_schema CREATE DATABASE `performance_schema` /*!40100 DEFAULT CHARACTER SET utf8 */
use performance_schema;
show tables;
Tables_in_performance_schema
=== modified file 'mysql-test/suite/perfschema/r/setup_actors.result'
--- a/mysql-test/suite/perfschema/r/setup_actors.result 2010-05-20 15:43:53 +0000
+++ b/mysql-test/suite/perfschema/r/setup_actors.result 2010-07-29 19:48:05 +0000
@@ -1,3 +1,6 @@
+select * from performance_schema.SETUP_ACTORS;
+HOST USER ROLE
+% % %
truncate table performance_schema.SETUP_ACTORS;
insert into performance_schema.SETUP_ACTORS
values ('hosta', 'user1', '%');
@@ -18,50 +21,112 @@ grant ALL on *.* to user1@localhost;
grant ALL on *.* to user2@localhost;
grant ALL on *.* to user3@localhost;
grant ALL on *.* to user4@localhost;
+grant select on test.* to user5@localhost;
flush privileges;
+# Switch to (con1, localhost, user1, , )
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.THREADS
-where PROCESSLIST_ID=connection_id();
+where PROCESSLIST_ID = connection_id();
+NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
+thread/sql/one_connection FOREGROUND NO user1 localhost
+# Switch to connection default
+insert into performance_schema.SETUP_ACTORS
+values ('%', 'user1', '%');
+# Switch to connection con1
+select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
+from performance_schema.THREADS
+where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO user1 localhost
+# Disconnect con1
+# Switch to (con2, localhost, user2, , )
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.THREADS
where PROCESSLIST_ID=connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES user2 localhost
+# Disconnect con2
+# Switch to connection default
+drop table if exists test.t1;
+create table test.t1 (col1 bigint);
+lock table test.t1 write;
+# Switch to (con3, localhost, user3, , )
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.THREADS
-where PROCESSLIST_ID=connection_id();
+where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND YES user3 localhost
+# Send a statement to the server, but do not wait till the result
+# comes back. We will pull this later.
+insert into test.t1 set col1 = 1;
+# Switch to (con4, localhost, user4, , )
+# Poll till INFO is no more NULL and State = 'Waiting for table'.
+select count(*) = 1
+from performance_schema.THREADS T inner join information_schema.PROCESSLIST P
+on T.PROCESSLIST_ID = P.ID and T.PROCESSLIST_USER = P.USER and
+T.PROCESSLIST_HOST = P.HOST and T.PROCESSLIST_DB = P.DB and
+T.PROCESSLIST_STATE = P.STATE and
+T.PROCESSLIST_COMMAND = P.COMMAND and T.PROCESSLIST_INFO = P.INFO
+where T.PROCESSLIST_USER = 'user3' and T.NAME = 'thread/sql/one_connection';
+count(*) = 1
+1
+# Switch to connection default
+unlock tables;
+# Switch to connection con3 and reap the result of the no more blocked insert
+# Switch to connection default
+drop table test.t1;
+# Disconnect con3
+# Switch to connection con4
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.THREADS
-where PROCESSLIST_ID=connection_id();
+where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
thread/sql/one_connection FOREGROUND NO user4 localhost
+# Disconnect con4
+# Switch to connection default
insert into performance_schema.SETUP_ACTORS
-values ('%', 'localhost', '%');
+values ('localhost', '%', '%');
select * from performance_schema.SETUP_ACTORS
order by USER, HOST, ROLE;
HOST USER ROLE
hostb % %
-% localhost %
+localhost % %
+% user1 %
hosta user1 %
% user2 %
localhost user3 %
+# Switch to (con4b, localhost, user4, , )
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
from performance_schema.THREADS
-where PROCESSLIST_ID=connection_id();
+where PROCESSLIST_ID = connection_id();
NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
-thread/sql/one_connection FOREGROUND NO user4 localhost
+thread/sql/one_connection FOREGROUND YES user4 localhost
+# Disconnect con4b
+# Switch to connection default
+insert into performance_schema.SETUP_ACTORS
+values ('%', 'user5', '%');
+create sql security definer view test.v1 as select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
+from performance_schema.THREADS
+where PROCESSLIST_ID = connection_id();
+# Switch to (con5, localhost, user5, , )
+select * from performance_schema.THREADS;
+ERROR 42000: SELECT command denied to user 'user5'@'localhost' for table 'THREADS'
+select * from test.v1;
+NAME TYPE INSTRUMENTED PROCESSLIST_USER PROCESSLIST_HOST
+thread/sql/one_connection FOREGROUND YES user5 localhost
+# Disconnect con5
+# Switch to connection default and cleanup
+drop view test.v1;
revoke all privileges, grant option from user1@localhost;
revoke all privileges, grant option from user2@localhost;
revoke all privileges, grant option from user3@localhost;
revoke all privileges, grant option from user4@localhost;
+revoke all privileges, grant option from user5@localhost;
drop user user1@localhost;
drop user user2@localhost;
drop user user3@localhost;
drop user user4@localhost;
+drop user user5@localhost;
flush privileges;
truncate table performance_schema.SETUP_ACTORS;
insert into performance_schema.SETUP_ACTORS
=== added file 'mysql-test/suite/perfschema/r/threads_events.result'
--- a/mysql-test/suite/perfschema/r/threads_events.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/threads_events.result 2010-07-29 19:48:05 +0000
@@ -0,0 +1,17 @@
+DROP TABLE IF EXISTS t_event;
+DROP EVENT IF EXISTS t_ps_event;
+CREATE TABLE t_event AS
+SELECT EVENT_ID FROM performance_schema.EVENTS_WAITS_CURRENT
+WHERE 1 = 2;
+CREATE EVENT t_ps_event
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
+DO INSERT INTO t_event
+SELECT DISTINCT EVENT_ID
+FROM performance_schema.EVENTS_WAITS_CURRENT
+JOIN performance_schema.EVENTS_WAITS_HISTORY USING (EVENT_ID)
+ORDER BY EVENT_ID
+LIMIT 1;
+SELECT * FROM t_event;
+EVENT_ID
+[EVENT_ID]
+DROP TABLE t_event;
=== added file 'mysql-test/suite/perfschema/r/threads_innodb.result'
--- a/mysql-test/suite/perfschema/r/threads_innodb.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/threads_innodb.result 2010-07-29 19:48:05 +0000
@@ -0,0 +1,12 @@
+SELECT name, type, processlist_user, processlist_host, processlist_db,
+processlist_command, processlist_time, processlist_state, processlist_info,
+parent_thread_id, role, instrumented
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/innodb/%'
+GROUP BY name;
+name type processlist_user processlist_host processlist_db processlist_command processlist_time processlist_state processlist_info parent_thread_id role instrumented
+thread/innodb/io_handler_thread BACKGROUND NULL NULL NULL NULL NULL NULL NULL NULL NULL YES
+thread/innodb/srv_error_monitor_thread BACKGROUND NULL NULL NULL NULL NULL NULL NULL NULL NULL YES
+thread/innodb/srv_lock_timeout_thread BACKGROUND NULL NULL NULL NULL NULL NULL NULL NULL NULL YES
+thread/innodb/srv_master_thread BACKGROUND NULL NULL NULL NULL NULL NULL NULL NULL NULL YES
+thread/innodb/srv_monitor_thread BACKGROUND NULL NULL NULL NULL NULL NULL NULL NULL NULL YES
=== added file 'mysql-test/suite/perfschema/r/threads_insert_delayed.result'
--- a/mysql-test/suite/perfschema/r/threads_insert_delayed.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/threads_insert_delayed.result 2010-07-29 19:48:05 +0000
@@ -0,0 +1,19 @@
+DROP TABLE IF EXISTS t1;
+DROP TEMPORARY TABLE IF EXISTS t2;
+CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM;
+CREATE TEMPORARY TABLE t2 AS
+SELECT thread_id FROM performance_schema.THREADS WHERE 1 = 2;
+SELECT COUNT(*) = 0 AS expect_1 FROM performance_schema.THREADS
+WHERE name = 'thread/sql/delayed_insert';
+expect_1
+1
+INSERT INTO t2 SELECT thread_id
+FROM performance_schema.THREADS;
+INSERT DELAYED INTO t1 SET f1 = SLEEP(3);
+SELECT name, type, instrumented, processlist_user, processlist_host
+FROM performance_schema.THREADS
+WHERE thread_id NOT IN (SELECT thread_id FROM t2);
+name type instrumented processlist_user processlist_host
+thread/sql/delayed_insert FOREGROUND YES root localhost
+DROP TABLE t1;
+DROP TEMPORARY TABLE t2;
=== added file 'mysql-test/suite/perfschema/r/threads_mysql.result'
--- a/mysql-test/suite/perfschema/r/threads_mysql.result 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/r/threads_mysql.result 2010-07-29 19:48:05 +0000
@@ -0,0 +1,108 @@
+SET GLOBAL event_scheduler = OFF;
+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
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%'
+ORDER BY name;
+name thread/sql/main
+type BACKGROUND
+processlist_user NULL
+processlist_host NULL
+processlist_db NULL
+processlist_command NULL
+processlist_info NULL
+unified_parent_thread_id NULL
+role NULL
+instrumented YES
+name thread/sql/one_connection
+type FOREGROUND
+processlist_user root
+processlist_host localhost
+processlist_db test
+processlist_command Query
+processlist_info 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
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%'
+ORDER BY name
+unified_parent_thread_id unified parent_thread_id
+role NULL
+instrumented YES
+name thread/sql/signal_handler
+type BACKGROUND
+processlist_user NULL
+processlist_host NULL
+processlist_db NULL
+processlist_command NULL
+processlist_info NULL
+unified_parent_thread_id unified parent_thread_id
+role NULL
+instrumented YES
+CREATE TEMPORARY TABLE t1 AS
+SELECT thread_id FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%';
+SET GLOBAL event_scheduler = ON;
+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
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%'
+ AND thread_id NOT IN (SELECT thread_id FROM t1)
+ORDER BY name;
+name thread/sql/event_scheduler
+type FOREGROUND
+processlist_user root
+processlist_host localhost
+processlist_db NULL
+processlist_command Sleep
+processlist_info NULL
+unified_parent_thread_id unified parent_thread_id
+role NULL
+instrumented YES
+TRUNCATE t1;
+INSERT INTO t1
+SELECT thread_id FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%';
+SELECT COUNT(*) INTO @aux FROM t1;
+DROP EVENT IF EXISTS t_ps_event;
+CREATE EVENT t_ps_event
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
+DO SELECT SLEEP(3);
+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
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%'
+ AND thread_id NOT IN (SELECT thread_id FROM t1)
+ORDER BY name;
+name thread/sql/event_worker
+type FOREGROUND
+processlist_user root
+processlist_host localhost
+processlist_db NULL
+processlist_command Sleep
+processlist_info SELECT SLEEP(3)
+unified_parent_thread_id unified parent_thread_id
+role NULL
+instrumented YES
+SELECT t2.name AS parent_thread_name, t1.name AS child_thread_name
+FROM performance_schema.THREADS t1 INNER JOIN performance_schema.THREADS t2
+ON t1.parent_thread_id = t2.thread_id
+WHERE t1.name LIKE 'thread/sql%'
+ AND t1.parent_thread_id IS NOT NULL
+ORDER BY parent_thread_name, child_thread_name;
+parent_thread_name child_thread_name
+thread/sql/event_scheduler thread/sql/event_worker
+thread/sql/main thread/sql/one_connection
+thread/sql/main thread/sql/signal_handler
+thread/sql/one_connection thread/sql/event_scheduler
=== modified file 'mysql-test/suite/perfschema/t/schema_lc0.test'
--- a/mysql-test/suite/perfschema/t/schema_lc0.test 2010-07-02 16:36:20 +0000
+++ b/mysql-test/suite/perfschema/t/schema_lc0.test 2010-07-29 19:48:05 +0000
@@ -14,6 +14,7 @@
# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
# Tests for PERFORMANCE_SCHEMA
+# Show existing objects and information about their structure
--source include/not_embedded.inc
--source include/have_perfschema.inc
=== modified file 'mysql-test/suite/perfschema/t/schema_lc1.test'
--- a/mysql-test/suite/perfschema/t/schema_lc1.test 2010-07-02 16:36:20 +0000
+++ b/mysql-test/suite/perfschema/t/schema_lc1.test 2010-07-29 19:48:05 +0000
@@ -14,6 +14,7 @@
# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
# Tests for PERFORMANCE_SCHEMA
+# Show existing objects and information about their structure
--source include/not_embedded.inc
--source include/have_perfschema.inc
=== modified file 'mysql-test/suite/perfschema/t/schema_lc2.test'
--- a/mysql-test/suite/perfschema/t/schema_lc2.test 2010-07-02 16:36:20 +0000
+++ b/mysql-test/suite/perfschema/t/schema_lc2.test 2010-07-29 19:48:05 +0000
@@ -14,6 +14,7 @@
# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
# Tests for PERFORMANCE_SCHEMA
+# Show existing objects and information about their structure
--source include/not_embedded.inc
--source include/have_perfschema.inc
=== modified file 'mysql-test/suite/perfschema/t/setup_actors.test'
--- a/mysql-test/suite/perfschema/t/setup_actors.test 2010-07-02 16:36:20 +0000
+++ b/mysql-test/suite/perfschema/t/setup_actors.test 2010-07-29 19:48:05 +0000
@@ -13,97 +13,248 @@
# along with this program; if not, write to the Free Software Foundation,
# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
+# Check the impact of different entries in performance_schema.SETUP_ACTORS
+# on when and how activity of users is recordeed in performance_schema.THREADS.
+# The checks for indirect activity caused by users, system threads etc.
+# are within setup_actors1.test.
+
--source include/not_embedded.inc
--source include/have_perfschema.inc
+# The initial number of rows is 1. The initial row always looks like this:
+# mysql> select * from performance_schema.SETUP_ACTORS;
+# +------+------+------+
+# | Host | User | ROLE |
+# +------+------+------+
+# | % | % | % |
+# +------+------+------+
+select * from performance_schema.SETUP_ACTORS;
+
truncate table performance_schema.SETUP_ACTORS;
insert into performance_schema.SETUP_ACTORS
- values ('hosta', 'user1', '%');
+values ('hosta', 'user1', '%');
insert into performance_schema.SETUP_ACTORS
- values ('%', 'user2', '%');
+values ('%', 'user2', '%');
insert into performance_schema.SETUP_ACTORS
- values ('localhost', 'user3', '%');
+values ('localhost', 'user3', '%');
insert into performance_schema.SETUP_ACTORS
- values ('hostb', '%', '%');
+values ('hostb', '%', '%');
select * from performance_schema.SETUP_ACTORS
- order by USER, HOST, ROLE;
+order by USER, HOST, ROLE;
grant ALL on *.* to user1@localhost;
grant ALL on *.* to user2@localhost;
grant ALL on *.* to user3@localhost;
grant ALL on *.* to user4@localhost;
+grant select on test.* to user5@localhost;
flush privileges;
+--echo # Switch to (con1, localhost, user1, , )
connect (con1, localhost, user1, , );
+# INSTRUMENTED must be NO because there is no match in performance_schema.SETUP_ACTORS
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
- from performance_schema.THREADS
- where PROCESSLIST_ID=connection_id();
+from performance_schema.THREADS
+where PROCESSLIST_ID = connection_id();
+let $con1_thread_id= `select THREAD_ID from performance_schema.THREADS
+ where PROCESSLIST_ID = connection_id()`;
+
+--echo # Switch to connection default
+--connection default
+insert into performance_schema.SETUP_ACTORS
+values ('%', 'user1', '%');
+--echo # Switch to connection con1
+--connection con1
+# INSTRUMENTED must be NO because there was no match in performance_schema.SETUP_ACTORS
+# when our current session made its connect. Later changes in SETUP_ACTORS have no
+# impact.
+select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
+from performance_schema.THREADS
+where PROCESSLIST_ID = connection_id();
+
+--echo # Disconnect con1
--disconnect con1
+--echo # Switch to (con2, localhost, user2, , )
connect (con2, localhost, user2, , );
+# INSTRUMENTED must be YES because there is a match via
+# (HOST,USER,ROLE) = ('%', 'user2', '%') in performance_schema.SETUP_ACTORS.
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
- from performance_schema.THREADS
- where PROCESSLIST_ID=connection_id();
+from performance_schema.THREADS
+where PROCESSLIST_ID=connection_id();
+let $con2_thread_id= `select THREAD_ID from performance_schema.THREADS
+ where PROCESSLIST_ID = connection_id()`;
+--echo # Disconnect con2
--disconnect con2
+--echo # Switch to connection default
+--connection default
+# If a thread dies, we don't expect its THREAD_ID value will be re-used.
+if (`SELECT $con2_thread_id <= $con1_thread_id`)
+{
+ --echo ERROR: THREAD_ID of con2 is not bigger than THREAD_ID of con1
+ eval SELECT $con2_thread_id as THREAD_ID_con2, $con1_thread_id THREAD_ID_con1;
+}
+
+--disable_warnings
+drop table if exists test.t1;
+--enable_warnings
+create table test.t1 (col1 bigint);
+lock table test.t1 write;
+
+--echo # Switch to (con3, localhost, user3, , )
connect (con3, localhost, user3, , );
+# INSTRUMENTED must be YES because there is a match via
+# (HOST,USER,ROLE) = ('localhost', 'user3', '%') in performance_schema.SETUP_ACTORS.
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
- from performance_schema.THREADS
- where PROCESSLIST_ID=connection_id();
-
---disconnect con3
+from performance_schema.THREADS
+where PROCESSLIST_ID = connection_id();
+# PROCESSLIST_ columns are:
+# (if name like '%OneConnection') all the same as what you'd get if you
+# run a select on INFORMATION_SCHEMA.PROCESSLIST for the corresponding thread.
+# Check at least once that this is fulfilled.
+# Note(mleich):
+# A join between INFORMATION_SCHEMA.PROCESSLIST and performance_schema.THREADS
+# Example:
+# select count(*) = 1
+# from performance_schema.THREADS T inner join information_schema.PROCESSLIST P
+# on T.PROCESSLIST_ID = P.ID and T.PROCESSLIST_USER = P.USER and
+# T.PROCESSLIST_HOST = P.HOST and T.PROCESSLIST_DB = P.DB and
+# T.PROCESSLIST_COMMAND = P.COMMAND and T.PROCESSLIST_INFO = P.INFO
+# where T.PROCESSLIST_ID = connection_id() and T.NAME = 'thread/sql/one_connection'
+# executed by the current connection looks like some of the most elegant solutions
+# for revealing this. But such a join suffers from sporadic differences like
+# column | observation
+# -------|-------------
+# state | "Sending data" vs. "executing"
+# time | 0 vs. 1 (high load on the testing box)
+# info | <full statement> vs. NULL (use of "--ps-protocol")
+# IMHO the differences are harmless.
+# Therefore we use here a different solution.
+#
+--echo # Send a statement to the server, but do not wait till the result
+--echo # comes back. We will pull this later.
+send
+insert into test.t1 set col1 = 1;
+--echo # Switch to (con4, localhost, user4, , )
connect (con4, localhost, user4, , );
+--echo # Poll till INFO is no more NULL and State = 'Waiting for table'.
+let $wait_condition= select count(*) from information_schema.processlist
+ where user = 'user3' and info is not null and state = 'Waiting for table';
+--source include/wait_condition.inc
+# Expect to get 1 now
+select count(*) = 1
+from performance_schema.THREADS T inner join information_schema.PROCESSLIST P
+ on T.PROCESSLIST_ID = P.ID and T.PROCESSLIST_USER = P.USER and
+ T.PROCESSLIST_HOST = P.HOST and T.PROCESSLIST_DB = P.DB and
+ T.PROCESSLIST_STATE = P.STATE and
+ T.PROCESSLIST_COMMAND = P.COMMAND and T.PROCESSLIST_INFO = P.INFO
+where T.PROCESSLIST_USER = 'user3' and T.NAME = 'thread/sql/one_connection';
+
+# Resolve the situation + some cleanup
+--echo # Switch to connection default
+--connection default
+unlock tables;
+--echo # Switch to connection con3 and reap the result of the no more blocked insert
+--connection con3
+--reap
+--echo # Switch to connection default
+--connection default
+drop table test.t1;
+--echo # Disconnect con3
+--disconnect con3
+--echo # Switch to connection con4
+--connection con4
+# INSTRUMENTED must be NO because there is no match in performance_schema.SETUP_ACTORS
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
- from performance_schema.THREADS
- where PROCESSLIST_ID=connection_id();
+from performance_schema.THREADS
+where PROCESSLIST_ID = connection_id();
+--echo # Disconnect con4
--disconnect con4
+--echo # Switch to connection default
--connection default
insert into performance_schema.SETUP_ACTORS
- values ('%', 'localhost', '%');
+values ('localhost', '%', '%');
select * from performance_schema.SETUP_ACTORS
- order by USER, HOST, ROLE;
+order by USER, HOST, ROLE;
+--echo # Switch to (con4b, localhost, user4, , )
connect (con4b, localhost, user4, , );
+# INSTRUMENTED must be YES because there is a match via
+# (HOST,USER,ROLE) = ('localhost', '%', '%') in performance_schema.SETUP_ACTORS.
select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
- from performance_schema.THREADS
- where PROCESSLIST_ID=connection_id();
+from performance_schema.THREADS
+where PROCESSLIST_ID = connection_id();
+--echo # Disconnect con4b
--disconnect con4b
+--echo # Switch to connection default
+--connection default
+insert into performance_schema.SETUP_ACTORS
+values ('%', 'user5', '%');
+
+create sql security definer view test.v1 as select NAME, TYPE, INSTRUMENTED, PROCESSLIST_USER, PROCESSLIST_HOST
+from performance_schema.THREADS
+where PROCESSLIST_ID = connection_id();
+
+--echo # Switch to (con5, localhost, user5, , )
+connect (con5, localhost, user5, , );
+
+--error ER_TABLEACCESS_DENIED_ERROR
+select * from performance_schema.THREADS;
+# 1. INSTRUMENTED must be YES because there are two matches
+# (HOST,USER,ROLE) = ('localhost', '%', '%')
+# (HOST,USER,ROLE) = ('%', 'user5', '%')
+# in performance_schema.SETUP_ACTORS.
+# But the instrument will only count once which means we must get only one row.
+# 2. PROCESSLIST_USER refers to USER(), the user who connected,
+# not the user we might be temporarily acting as (with definer's rights).
+# Therefore PROCESSLIST_USER must be 'user5' though we run with right's of definer 'root'
+select * from test.v1;
+
+--echo # Disconnect con5
+--disconnect con5
+--source include/wait_until_disconnected.inc
+
+
+--echo # Switch to connection default and cleanup
--connection default
+drop view test.v1;
revoke all privileges, grant option from user1@localhost;
revoke all privileges, grant option from user2@localhost;
revoke all privileges, grant option from user3@localhost;
revoke all privileges, grant option from user4@localhost;
+revoke all privileges, grant option from user5@localhost;
drop user user1@localhost;
drop user user2@localhost;
drop user user3@localhost;
drop user user4@localhost;
+drop user user5@localhost;
flush privileges;
truncate table performance_schema.SETUP_ACTORS;
insert into performance_schema.SETUP_ACTORS
- values ('%', '%', '%');
+values ('%', '%', '%');
select * from performance_schema.SETUP_ACTORS;
=== added file 'mysql-test/suite/perfschema/t/threads_innodb.test'
--- a/mysql-test/suite/perfschema/t/threads_innodb.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/threads_innodb.test 2010-07-29 19:48:05 +0000
@@ -0,0 +1,35 @@
+# Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software Foundation,
+# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
+
+
+# Tests for special content of performance_schema.THREADS
+#
+# Show InnoDB related content in performance_schema.THREADS
+
+--source include/not_embedded.inc
+--source include/have_innodb.inc
+--source include/have_perfschema.inc
+
+# There are several InnoDB io_handler_threads servicing for read IO, write IO etc.
+# The number of these threads is at least for some types configurable.
+# We suppress here duplicates rows with the goal to avoid that the test fails
+# in case some defaults are changed.
+SELECT name, type, processlist_user, processlist_host, processlist_db,
+ processlist_command, processlist_time, processlist_state, processlist_info,
+ parent_thread_id, role, instrumented
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/innodb/%'
+GROUP BY name;
+
=== added file 'mysql-test/suite/perfschema/t/threads_insert_delayed.test'
--- a/mysql-test/suite/perfschema/t/threads_insert_delayed.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/threads_insert_delayed.test 2010-07-29 19:48:05 +0000
@@ -0,0 +1,55 @@
+# Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software Foundation,
+# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
+
+
+# Tests for special content of performance_schema.THREADS
+#
+# The feature INSERT DELAYED (supported by MyISAM,MEMORY,ARCHIVE,BLACKHOLE)
+# causes that a "delayed_insert" thread shows up as soon as the first
+# INSERT DELAYED was issued.
+
+--source include/not_embedded.inc
+--source include/have_perfschema.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TEMPORARY TABLE IF EXISTS t2;
+--enable_warnings
+CREATE TABLE t1 (f1 BIGINT) ENGINE = MyISAM;
+CREATE TEMPORARY TABLE t2 AS
+SELECT thread_id FROM performance_schema.THREADS WHERE 1 = 2;
+
+# Reveal that the delayed_insert thread does not exist.
+# Note(mleich): This expectation is probably not all time true.
+SELECT COUNT(*) = 0 AS expect_1 FROM performance_schema.THREADS
+WHERE name = 'thread/sql/delayed_insert';
+
+INSERT INTO t2 SELECT thread_id
+FROM performance_schema.THREADS;
+
+let $wait_condition= SELECT COUNT(*) = 1 AS expect_0 FROM performance_schema.THREADS
+WHERE name = 'thread/sql/delayed_insert';
+INSERT DELAYED INTO t1 SET f1 = SLEEP(3);
+--source include/wait_condition.inc
+
+# Expect to get
+# thread/sql/delayed_insert FOREGROUND YES root localhost
+SELECT name, type, instrumented, processlist_user, processlist_host
+FROM performance_schema.THREADS
+WHERE thread_id NOT IN (SELECT thread_id FROM t2);
+
+DROP TABLE t1;
+DROP TEMPORARY TABLE t2;
+
=== added file 'mysql-test/suite/perfschema/t/threads_mysql-master.opt'
--- a/mysql-test/suite/perfschema/t/threads_mysql-master.opt 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/threads_mysql-master.opt 2010-07-29 19:48:05 +0000
@@ -0,0 +1 @@
+--event-scheduler
=== added file 'mysql-test/suite/perfschema/t/threads_mysql.test'
--- a/mysql-test/suite/perfschema/t/threads_mysql.test 1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/perfschema/t/threads_mysql.test 2010-07-29 19:48:05 +0000
@@ -0,0 +1,130 @@
+# Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
+#
+# This program is free software; you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation; version 2 of the License.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program; if not, write to the Free Software Foundation,
+# 51 Franklin Street, Suite 500, Boston, MA 02110-1335 USA
+
+
+# Tests for special content of performance_schema.THREADS
+#
+# Show MySQL server related content in performance_schema.THREADS
+
+--source include/not_embedded.inc
+--source include/have_perfschema.inc
+
+# Ensure that the event scheduler (started via threads_events-master.opt)
+# is really running.
+--source include/running_event_scheduler.inc
+
+SET GLOBAL event_scheduler = OFF;
+--source include/no_running_event_scheduler.inc
+
+--vertical_results
+
+# Show all "system" threads except the event scheduler
+# ---------------------------------------------------
+# 0. The values within the columns "thread_id" and "processlist_id" depend
+# on server history. Therefore they are unstable and need to be omitted.
+# 1. The columns "time" and "state" are omitted because it is thinkable that
+# they contain slightly unstable though correct values.
+# 2. The unification of the column "parent_thread_id" is in the moment most
+# probably not required because I observed all time that the parent is
+# "thread/sql/main" with the thread_id = 1.
+# But there might be more kinds of parents with most probably unstable
+# "thread_id" values in future.
+# 3. In case the test is started with the option "--ps-protocol" we will
+# find a correcet row for our current thread but the content will differ.
+# Therefore we have to disable this protocol for the next statement.
+--disable_ps_protocol
+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
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%'
+ORDER BY name;
+--enable_ps_protocol
+
+CREATE TEMPORARY TABLE t1 AS
+SELECT thread_id FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%';
+
+
+SET GLOBAL event_scheduler = ON;
+--source include/running_event_scheduler.inc
+
+# Show entries belonging to the just started event scheduler
+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
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%'
+ AND thread_id NOT IN (SELECT thread_id FROM t1)
+ORDER BY name;
+
+TRUNCATE t1;
+INSERT INTO t1
+SELECT thread_id FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%';
+SELECT COUNT(*) INTO @aux FROM t1;
+
+# Attention:
+# Just waiting for some new thread showing up is not sufficient because
+# because the successing SELECT showing the thread might catch this thread
+# in a very early and short phase.
+# process_info si quite often
+# CREATE PROCEDURE `t_ps_event`() SQL SECURITY INVOKER SELECT SLEEP(3)
+#
+let $wait_condition=
+SELECT COUNT(*) = 1 FROM information_schema.processlist
+WHERE info = 'SELECT SLEEP(3)';
+
+--disable_warnings
+DROP EVENT IF EXISTS t_ps_event;
+--enable_warnings
+CREATE EVENT t_ps_event
+ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND
+DO SELECT SLEEP(3);
+
+# Wait till one more thread comes up.
+# This must be the event worker thread.
+let $wait_timeout= 20;
+--source include/wait_condition.inc
+
+# Show entries belonging to the just started event worker
+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
+FROM performance_schema.THREADS
+WHERE name LIKE 'thread/sql%'
+ AND thread_id NOT IN (SELECT thread_id FROM t1)
+ORDER BY name;
+
+# Show parent - child relations between "system" threads
+# ------------------------------------------------------
+--horizontal_results
+SELECT t2.name AS parent_thread_name, t1.name AS child_thread_name
+FROM performance_schema.THREADS t1 INNER JOIN performance_schema.THREADS t2
+ON t1.parent_thread_id = t2.thread_id
+WHERE t1.name LIKE 'thread/sql%'
+ AND t1.parent_thread_id IS NOT NULL
+ORDER BY parent_thread_name, child_thread_name;
+
+# Cleanup
+# Wait till the event worker disappeared
+--source include/no_running_events.inc
+
Attachment: [text/bzr-bundle] bzr/matthias.leich@oracle.com-20100729194805-degjbski0vkv1o4u.bundle
