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