Debian Codegraph @

mysql-test/suite/sysschema/t/fn_ps_thread_trx_info.test

raw
1 # Tests for sys schema
2 # Verify the sys.ps_thread_trx_info() function perfoms as expected
3
4 # Performance schema tracks prepared statements separately, and does not
5 # yet have a summary view that we can use for this view.
6 # Until then, we disable this test with --ps-protocol
7 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
8 -- source include/no_protocol.inc
9
10 # Passing unknown connection id should return NULL
11 --disable_warnings
12 SELECT sys.ps_thread_trx_info(234623462376);
13 --enable_warnings
14
15 # Make sure current thread returns a valid JSON object
16 SELECT JSON_VALID(sys.ps_thread_trx_info(ps_current_thread_id()));
17
18 # Dummy up some transactions and inspect
19 CREATE DATABASE trx;
20 CREATE TABLE trx.info (id INT PRIMARY KEY, info VARCHAR(20));
21
22 --echo # Warm-up data-dictionary cache.
23 --disable_query_log
24 --disable_result_log
25 SELECT * FROM trx.info;
26 --enable_result_log
27 --enable_query_log
28
29 --connect(con1,localhost,root,,)
30 connection con1;
31 let $NEW_TRX_ID=`SELECT thread_id FROM performance_schema.threads
32 WHERE PROCESSLIST_ID = connection_id()`;
33
34 USE trx;
35 START TRANSACTION;
36 INSERT INTO info VALUES (1, 'foo');
37 COMMIT;
38 START TRANSACTION;
39 INSERT INTO info VALUES (2, 'bar');
40 COMMIT;
41
42 connection default;
43 --disable_query_log ONCE
44 eval SET @ps_thread_id = $NEW_TRX_ID;
45
46 # Get the JSON dump of the transaction info
47 SET @json_doc := sys.ps_thread_trx_info(@ps_thread_id);
48
49 # JSON should be valid
50 SELECT JSON_VALID(@json_doc);
51
52 # Should have two transactions in the array
53 SELECT JSON_LENGTH(@json_doc);
54
55 # Expected keys are returned for first transaction details
56 SELECT JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0]'));
57
58 # Expected values are returned for the transaction details
59 SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].time');
60 SELECT JSON_CONTAINS(@json_doc, '"COMMITTED"', '$[0].state');
61 SELECT JSON_CONTAINS(@json_doc, '"READ WRITE"', '$[0].mode');
62 SELECT JSON_CONTAINS(@json_doc, '"NO"', '$[0].autocommitted');
63 # gtid can be "AUTOMATIC", "ANONYMOUS", or "<server_uuid>:<number>",
64 # depending on whether binlog and/or GTIDs are enabled
65 --let $server_uuid= `SELECT @@global.server_uuid`
66 --let $replace_pattern= /$server_uuid:[0-9][0-9]*|AUTOMATIC|ANONYMOUS/GTID/
67 --replace_regex $replace_pattern
68 SELECT JSON_EXTRACT(@json_doc, '$[0].gtid');
69 SELECT JSON_CONTAINS(@json_doc, '"REPEATABLE READ"', '$[0].isolation');
70
71 # Expected keys are returned for first transaction statements_executed details
72 SELECT JSON_KEYS(JSON_EXTRACT(@json_doc, '$[0].statements_executed[0]'));
73
74 # Confirm statement details values
75 SELECT JSON_CONTAINS_PATH(@json_doc, 'one', '$[0].statements_executed[0].time');
76 SELECT JSON_CONTAINS(@json_doc, '"INSERT INTO info VALUES (1, \'foo\')"', '$[0].statements_executed[0].sql_text');
77 SELECT JSON_CONTAINS(@json_doc, '"trx"', '$[0].statements_executed[0].schema');
78 SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_examined');
79 SELECT JSON_CONTAINS(@json_doc, '1', '$[0].statements_executed[0].rows_affected');
80 SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].rows_sent');
81 SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_tables');
82 SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].tmp_disk_tables');
83 SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_rows');
84 SELECT JSON_CONTAINS(@json_doc, '0', '$[0].statements_executed[0].sort_merge_passes');
85
86 # Second statement in transaction should be a COMMIT
87 SELECT JSON_CONTAINS(@json_doc, '"COMMIT"', '$[0].statements_executed[1].sql_text');
88
89 # Simulate a truncated set of output by lowering the @sys.ps_thread_trx_info.max_length user variable
90 # This also tests the user variable works appropriately, incidentally
91
92 SET @sys.ps_thread_trx_info.max_length = 100;
93
94 # Should return an error JSON object
95 SELECT sys.ps_thread_trx_info(@ps_thread_id);
96
97 # Setting the user variable back to NULL should reset to 65535 from sys_config, and no truncation
98 SET @sys.ps_thread_trx_info.max_length = NULL;
99 SELECT JSON_VALID(sys.ps_thread_trx_info(@ps_thread_id));
100
101 # Clean up
102
103 disconnect con1;
104 DROP DATABASE trx;