summaryrefslogtreecommitdiff
path: root/mysql-test/suite/perfschema/t/threads_mysql.test
blob: c33f421863e2b5a553c77a19cc991f62f7bc48e0 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

# Tests for special content of performance_schema.threads
#
# Show MySQL server related content in performance_schema.threads

--source include/not_windows.inc
--source include/not_embedded.inc
--source include/have_perfschema.inc

# Ensure that the event scheduler (started via threads_mysql-master.opt)
# is really running.
--source include/pfs_running_event_scheduler.inc

SET GLOBAL event_scheduler = OFF;
--source include/pfs_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/pfs_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