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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
# Tests for the performance schema
# =====================================================
# HELPER include/transaction_nested_events_verifier.inc
# =====================================================
--connection default
--disable_query_log
# Poll till the activity of the actor connection con1 caused by the last
# statement issued has finished.
let $wait_timeout= 10;
let $wait_condition=
SELECT COUNT(*) > 0 FROM performance_schema.threads
WHERE THREAD_ID = @con1_thread_id
AND PROCESSLIST_COMMAND = 'Sleep';
--source include/wait_condition.inc
if(!$success)
{
--echo # The activity of connection con1 did not finish.
SELECT thread_id , PROCESSLIST_id, PROCESSLIST_STATE, PROCESSLIST_INFO
FROM performance_schema.threads
WHERE thread_id = @con1_thread_id;
--echo # Abort.
exit;
}
--echo #========================================================================
--echo # Verify
--echo #========================================================================
SELECT event_id into @base_tx_event_id
from performance_schema.events_transactions_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
SELECT event_id into @base_stmt_event_id from performance_schema.events_statements_history_long
where (THREAD_ID = @con1_thread_id)
order by event_id limit 1;
select if(@base_tx_event_id < @base_stmt_event_id,
@base_tx_event_id - 1,
@base_stmt_event_id - 1)
into @base_event_id;
# Debug helpers
# set @base_event_id = 0;
# select @base_tx_event_id, @base_stmt_event_id, @base_event_id;
--echo #
--echo # EVENTS_TRANSACTIONS_CURRENT
--echo #
--replace_column 1 thread_id
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_current
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
--echo #
--echo # EVENTS_TRANSACTIONS_HISTORY_LONG
--echo #
--replace_column 1 thread_id
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 11, ' ') 'EVENT_NAME ',
RPAD(STATE, 11, ' ') 'STATE ',
RPAD(ACCESS_MODE, 11, ' ') ACCESS_MODE,
RPAD(ISOLATION_LEVEL, 16, ' ') 'ISOLATION_LEVEL ',
RPAD(AUTOCOMMIT, 4, ' ') AUTO,
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE
FROM performance_schema.events_transactions_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
--echo #
--echo # EVENTS_STATEMENTS_HISTORY_LONG
--echo #
--replace_column 1 thread_id
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 30, ' ') 'EVENT_NAME ',
RPAD(IFNULL(object_name, 'NULL'), 12, ' ') 'OBJECT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
LPAD(NESTING_EVENT_LEVEL, 5, ' ') LEVEL,
SQL_TEXT
FROM performance_schema.events_statements_history_long
WHERE ((THREAD_ID = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, event_id;
--echo #
--echo ### Combined statement and transaction event history ordered by event id
--echo #
--echo #EVENTS_STATEMENTS_HISTORY_LONG + EVENTS_TRANSACTIONS_HISTORY_LONG
--echo #
--replace_column 1 thread_id
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
LPAD(NESTING_EVENT_ID - @base_event_id, 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
'<transaction started>' AS SQL_TXT
FROM performance_schema.events_transactions_history_long t
WHERE (t.thread_id = @con1_thread_id)
UNION
SELECT THREAD_ID,
LPAD(EVENT_ID - @base_event_id, 11, ' ') as R_EVENT_ID,
LPAD(END_EVENT_ID - @base_event_id, 11, ' ') as R_END_EVENT_ID,
RPAD(EVENT_NAME, 25, ' ') 'EVENT_NAME ',
LPAD(IFNULL(NESTING_EVENT_ID - @base_event_id, 'NULL'), 19, ' ') as R_NESTING_EVENT_ID,
RPAD(IFNULL(NESTING_EVENT_TYPE, 'NULL'), 18, ' ') NESTING_EVENT_TYPE,
SQL_TEXT
FROM performance_schema.events_statements_history_long s
WHERE ((s.thread_id = @con1_thread_id) OR (@all_threads = 1))
ORDER BY thread_id, r_event_id;
--echo #
--echo ### Clear statement and transaction history
--echo # CALL test.clear_history();
CALL test.clear_history();
--enable_query_log
--echo ## Reset db.t1
DELETE FROM db.t1;
--echo #
|
