1 2# Tests for special content of performance_schema.threads 3# 4# Show MySQL server related content in performance_schema.threads 5 6--source include/not_windows.inc 7--source include/not_embedded.inc 8--source include/have_perfschema.inc 9 10# Ensure that the event scheduler (started via threads_mysql-master.opt) 11# is really running. 12--source include/pfs_running_event_scheduler.inc 13 14SET GLOBAL event_scheduler = OFF; 15--source include/pfs_no_running_event_scheduler.inc 16 17--vertical_results 18 19# Show all "system" threads except the event scheduler 20# --------------------------------------------------- 21# 0. The values within the columns "thread_id" and "processlist_id" depend 22# on server history. Therefore they are unstable and need to be omitted. 23# 1. The columns "time" and "state" are omitted because it is thinkable that 24# they contain slightly unstable though correct values. 25# 2. The unification of the column "parent_thread_id" is in the moment most 26# probably not required because I observed all time that the parent is 27# "thread/sql/main" with the thread_id = 1. 28# But there might be more kinds of parents with most probably unstable 29# "thread_id" values in future. 30# 3. In case the test is started with the option "--ps-protocol" we will 31# find a correcet row for our current thread but the content will differ. 32# Therefore we have to disable this protocol for the next statement. 33--disable_ps_protocol 34SELECT name, type, processlist_user, processlist_host, processlist_db, 35 processlist_command, processlist_info, 36 IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') 37 AS unified_parent_thread_id, 38 role, instrumented 39FROM performance_schema.threads 40WHERE name LIKE 'thread/sql%' 41ORDER BY name; 42--enable_ps_protocol 43 44CREATE TEMPORARY TABLE t1 AS 45SELECT thread_id FROM performance_schema.threads 46WHERE name LIKE 'thread/sql%'; 47 48 49SET GLOBAL event_scheduler = ON; 50--source include/pfs_running_event_scheduler.inc 51 52# Show entries belonging to the just started event scheduler 53SELECT name, type, processlist_user, processlist_host, processlist_db, 54 processlist_command, processlist_info, 55 IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') 56 AS unified_parent_thread_id, 57 role, instrumented 58FROM performance_schema.threads 59WHERE name LIKE 'thread/sql%' 60 AND thread_id NOT IN (SELECT thread_id FROM t1) 61ORDER BY name; 62 63TRUNCATE t1; 64INSERT INTO t1 65SELECT thread_id FROM performance_schema.threads 66WHERE name LIKE 'thread/sql%'; 67SELECT COUNT(*) INTO @aux FROM t1; 68 69# Attention: 70# Just waiting for some new thread showing up is not sufficient because 71# because the successing SELECT showing the thread might catch this thread 72# in a very early and short phase. 73# process_info si quite often 74# CREATE PROCEDURE `t_ps_event`() SQL SECURITY INVOKER SELECT SLEEP(3) 75# 76let $wait_condition= 77SELECT COUNT(*) = 1 FROM information_schema.processlist 78WHERE info = 'SELECT SLEEP(3)'; 79 80--disable_warnings 81DROP EVENT IF EXISTS t_ps_event; 82--enable_warnings 83CREATE EVENT t_ps_event 84ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 SECOND 85DO SELECT SLEEP(3); 86 87# Wait till one more thread comes up. 88# This must be the event worker thread. 89let $wait_timeout= 20; 90--source include/wait_condition.inc 91 92# Show entries belonging to the just started event worker 93SELECT name, type, processlist_user, processlist_host, processlist_db, 94 processlist_command, processlist_info, 95 IF(parent_thread_id IS NULL, parent_thread_id, 'unified parent_thread_id') 96 AS unified_parent_thread_id, 97 role, instrumented 98FROM performance_schema.threads 99WHERE name LIKE 'thread/sql%' 100 AND thread_id NOT IN (SELECT thread_id FROM t1) 101ORDER BY name; 102 103# Show parent - child relations between "system" threads 104# ------------------------------------------------------ 105--horizontal_results 106SELECT t2.name AS parent_thread_name, t1.name AS child_thread_name 107FROM performance_schema.threads t1 INNER JOIN performance_schema.threads t2 108ON t1.parent_thread_id = t2.thread_id 109WHERE t1.name LIKE 'thread/sql%' 110 AND t1.parent_thread_id IS NOT NULL 111ORDER BY parent_thread_name, child_thread_name; 112 113# Cleanup 114# Wait till the event worker disappeared 115--source include/no_running_events.inc 116 117