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
|
# Tests for sys schema
# Verify the sys.ps_thread_trx_info() function perfoms as expected
-- source include/not_embedded.inc
# Performance schema tracks prepared statements separately, and does not
# yet have a summary view that we can use for this view.
# Until then, we disable this test with --ps-protocol
-- source include/no_protocol.inc
# Passing unknown connection id should return NULL
SELECT sys.ps_thread_trx_info(234623462376);
# Make sure current thread returns a valid JSON object
SELECT JSON_VALID(sys.ps_thread_trx_info(sys.ps_thread_id(NULL)));
# Dummy up some transactions and inspect
CREATE DATABASE trx;
CREATE TABLE trx.info (id INT PRIMARY KEY, info VARCHAR(20));
--connect(con1,localhost,root,,)
connection con1;
let $NEW_TRX_ID=`SELECT thread_id FROM performance_schema.threads
WHERE PROCESSLIST_ID = connection_id()`;
USE trx;
START TRANSACTION;
INSERT INTO info VALUES (1, 'foo');
COMMIT;
START TRANSACTION;
INSERT INTO info VALUES (2, 'bar');
COMMIT;
connection default;
--disable_query_log ONCE
eval SET @ps_thread_id = $NEW_TRX_ID;
# Get the JSON dump of the transaction info
SET @json_doc := sys.ps_thread_trx_info(@ps_thread_id);
# JSON should be valid
SELECT JSON_VALID(@json_doc);
# Should have two transactions in the array
SELECT JSON_LENGTH(@json_doc);
# Expected keys are returned for first transaction details
SELECT JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0]'));
# Expected values are returned for the transaction details
SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].time');
SELECT JSON_CONTAINS(@json_doc, '"COMMITTED"', '$[0].state');
SELECT JSON_CONTAINS(@json_doc, '"READ WRITE"', '$[0].mode');
SELECT JSON_CONTAINS(@json_doc, '"NO"', '$[0].autocommitted');
SELECT JSON_CONTAINS(@json_doc, '"AUTOMATIC"', '$[0].gtid');
SELECT JSON_CONTAINS(@json_doc, '"REPEATABLE READ"', '$[0].isolation');
# Expected keys are returned for first transaction statements_executed details
SELECT JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0].statements_executed[0]'));
# Confirm statement details values
SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].statements_executed[0].time');
SELECT JSON_CONTAINS(@json_doc, '"INSERT INTO info VALUES (1, \'foo\')"', '$[0].statements_executed[0].sql_text');
SELECT JSON_CONTAINS(@json_doc, '"trx"', '$[0].statements_executed[0].schema');
SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_examined');
SELECT JSON_CONTAINS(@json_doc, '1', '$[0].statements_executed[0].rows_affected');
SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_sent');
SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_tables');
SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_disk_tables');
SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_rows');
SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_merge_passes');
# Second statement in transaction should be a COMMIT
SELECT JSON_CONTAINS(@json_doc, '"COMMIT"', '$[0].statements_executed[1].sql_text');
# Simulate a truncated set of output by lowering the @sys.ps_thread_trx_info.max_length user variable
# This also tests the user variable works appropriately, incidentally
SET @sys.ps_thread_trx_info.max_length = 100;
# Should return an error JSON object
--replace_regex /Row 1[1-2] was/Row 1X was/
SELECT sys.ps_thread_trx_info(@ps_thread_id);
# Setting the user variable back to NULL should reset to 65535 from sys_config, and no truncation
SET @sys.ps_thread_trx_info.max_length = NULL;
SELECT JSON_VALID(sys.ps_thread_trx_info(@ps_thread_id));
# Clean up
disconnect con1;
DROP DATABASE trx;
|
