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