List:Commits« Previous MessageNext Message »
From:Matthias Leich Date:July 29 2010 7:48pm
Subject:bzr commit into mysql-next-mr branch (matthias.leich:3165) WL#4674
View as plain text  
#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
Thread
bzr commit into mysql-next-mr branch (matthias.leich:3165) WL#4674Matthias Leich30 Jul
  • Re: bzr commit into mysql-next-mr branch (matthias.leich:3165) WL#4674Marc Alff31 Aug