mirror of
https://github.com/MariaDB/server.git
synced 2025-11-04 12:56:14 +01:00
c2b6916393
* it isn't "pfs" function, don't call it Item_func_pfs, don't use item_pfsfunc.* * tests don't depend on performance schema, put in the main suite * inherit from Item_str_ascii_func * use connection collation, not utf8mb3_general_ci * set result length in fix_length_and_dec * do not set maybe_null * use my_snprintf() where possible * don't set m_value.ptr on every invocation * update sys schema to use the format_pico_time() * len must be size_t (compilation error on Windows) * the correct function name for double->double is fabs() * drop volatile hack
233 lines
10 KiB
SQL
233 lines
10 KiB
SQL
-- Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
|
|
--
|
|
-- This program is free software; you can redistribute it and/or modify
|
|
-- it under the terms of the GNU General Public License as published by
|
|
-- the Free Software Foundation; version 2 of the License.
|
|
--
|
|
-- This program is distributed in the hope that it will be useful,
|
|
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
-- GNU General Public License for more details.
|
|
--
|
|
-- You should have received a copy of the GNU General Public License
|
|
-- along with this program; if not, write to the Free Software
|
|
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
|
|
|
|
DROP FUNCTION IF EXISTS ps_thread_trx_info;
|
|
|
|
DELIMITER $$
|
|
|
|
CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_thread_trx_info (
|
|
in_thread_id BIGINT UNSIGNED
|
|
) RETURNS LONGTEXT
|
|
COMMENT '
|
|
Description
|
|
-----------
|
|
|
|
Returns a JSON object with info on the given threads current transaction,
|
|
and the statements it has already executed, derived from the
|
|
performance_schema.events_transactions_current and
|
|
performance_schema.events_statements_history tables (so the consumers
|
|
for these also have to be enabled within Performance Schema to get full
|
|
data in the object).
|
|
|
|
When the output exceeds the default truncation length (65535), a JSON error
|
|
object is returned, such as:
|
|
|
|
{ "error": "Trx info truncated: Row 6 was cut by GROUP_CONCAT()" }
|
|
|
|
Similar error objects are returned for other warnings/and exceptions raised
|
|
when calling the function.
|
|
|
|
The max length of the output of this function can be controlled with the
|
|
ps_thread_trx_info.max_length variable set via sys_config, or the
|
|
@sys.ps_thread_trx_info.max_length user variable, as appropriate.
|
|
|
|
Parameters
|
|
-----------
|
|
|
|
in_thread_id (BIGINT UNSIGNED):
|
|
The id of the thread to return the transaction info for.
|
|
|
|
Example
|
|
-----------
|
|
|
|
SELECT sys.ps_thread_trx_info(48)\\G
|
|
*************************** 1. row ***************************
|
|
sys.ps_thread_trx_info(48): [
|
|
{
|
|
"time": "790.70 us",
|
|
"state": "COMMITTED",
|
|
"mode": "READ WRITE",
|
|
"autocommitted": "NO",
|
|
"gtid": "AUTOMATIC",
|
|
"isolation": "REPEATABLE READ",
|
|
"statements_executed": [
|
|
{
|
|
"sql_text": "INSERT INTO info VALUES (1, \'foo\')",
|
|
"time": "471.02 us",
|
|
"schema": "trx",
|
|
"rows_examined": 0,
|
|
"rows_affected": 1,
|
|
"rows_sent": 0,
|
|
"tmp_tables": 0,
|
|
"tmp_disk_tables": 0,
|
|
"sort_rows": 0,
|
|
"sort_merge_passes": 0
|
|
},
|
|
{
|
|
"sql_text": "COMMIT",
|
|
"time": "254.42 us",
|
|
"schema": "trx",
|
|
"rows_examined": 0,
|
|
"rows_affected": 0,
|
|
"rows_sent": 0,
|
|
"tmp_tables": 0,
|
|
"tmp_disk_tables": 0,
|
|
"sort_rows": 0,
|
|
"sort_merge_passes": 0
|
|
}
|
|
]
|
|
},
|
|
{
|
|
"time": "426.20 us",
|
|
"state": "COMMITTED",
|
|
"mode": "READ WRITE",
|
|
"autocommitted": "NO",
|
|
"gtid": "AUTOMATIC",
|
|
"isolation": "REPEATABLE READ",
|
|
"statements_executed": [
|
|
{
|
|
"sql_text": "INSERT INTO info VALUES (2, \'bar\')",
|
|
"time": "107.33 us",
|
|
"schema": "trx",
|
|
"rows_examined": 0,
|
|
"rows_affected": 1,
|
|
"rows_sent": 0,
|
|
"tmp_tables": 0,
|
|
"tmp_disk_tables": 0,
|
|
"sort_rows": 0,
|
|
"sort_merge_passes": 0
|
|
},
|
|
{
|
|
"sql_text": "COMMIT",
|
|
"time": "213.23 us",
|
|
"schema": "trx",
|
|
"rows_examined": 0,
|
|
"rows_affected": 0,
|
|
"rows_sent": 0,
|
|
"tmp_tables": 0,
|
|
"tmp_disk_tables": 0,
|
|
"sort_rows": 0,
|
|
"sort_merge_passes": 0
|
|
}
|
|
]
|
|
}
|
|
]
|
|
1 row in set (0.03 sec)
|
|
'
|
|
|
|
SQL SECURITY INVOKER
|
|
NOT DETERMINISTIC
|
|
READS SQL DATA
|
|
BEGIN
|
|
DECLARE v_output LONGTEXT DEFAULT '{}';
|
|
DECLARE v_msg_text TEXT DEFAULT '';
|
|
DECLARE v_signal_msg TEXT DEFAULT '';
|
|
DECLARE v_mysql_errno INT;
|
|
DECLARE v_max_output_len BIGINT;
|
|
-- Capture warnings/errors such as group_concat truncation
|
|
-- and report as JSON error objects
|
|
DECLARE EXIT HANDLER FOR SQLWARNING, SQLEXCEPTION
|
|
BEGIN
|
|
GET DIAGNOSTICS CONDITION 1
|
|
v_msg_text = MESSAGE_TEXT,
|
|
v_mysql_errno = MYSQL_ERRNO;
|
|
|
|
IF v_mysql_errno = 1260 THEN
|
|
SET v_signal_msg = CONCAT('{ "error": "Trx info truncated: ', v_msg_text, '" }');
|
|
ELSE
|
|
SET v_signal_msg = CONCAT('{ "error": "', v_msg_text, '" }');
|
|
END IF;
|
|
|
|
RETURN v_signal_msg;
|
|
END;
|
|
|
|
-- Set configuration options
|
|
IF (@sys.ps_thread_trx_info.max_length IS NULL) THEN
|
|
SET @sys.ps_thread_trx_info.max_length = sys.sys_get_config('ps_thread_trx_info.max_length', 65535);
|
|
END IF;
|
|
|
|
IF (@sys.ps_thread_trx_info.max_length != @@session.group_concat_max_len) THEN
|
|
SET @old_group_concat_max_len = @@session.group_concat_max_len;
|
|
-- Convert to int value for the SET, and give some surrounding space
|
|
SET v_max_output_len = (@sys.ps_thread_trx_info.max_length - 5);
|
|
SET SESSION group_concat_max_len = v_max_output_len;
|
|
END IF;
|
|
|
|
SET v_output = (
|
|
SELECT CONCAT('[', IFNULL(GROUP_CONCAT(trx_info ORDER BY event_id), ''), '\n]') AS trx_info
|
|
FROM (SELECT trxi.thread_id,
|
|
trxi.event_id,
|
|
GROUP_CONCAT(
|
|
IFNULL(
|
|
CONCAT('\n {\n',
|
|
' "time": "', IFNULL(format_pico_time(trxi.timer_wait), ''), '",\n',
|
|
' "state": "', IFNULL(trxi.state, ''), '",\n',
|
|
' "mode": "', IFNULL(trxi.access_mode, ''), '",\n',
|
|
' "autocommitted": "', IFNULL(trxi.autocommit, ''), '",\n',
|
|
' "gtid": "', IFNULL(trxi.gtid, ''), '",\n',
|
|
' "isolation": "', IFNULL(trxi.isolation_level, ''), '",\n',
|
|
' "statements_executed": [', IFNULL(s.stmts, ''), IF(s.stmts IS NULL, ' ]\n', '\n ]\n'),
|
|
' }'
|
|
),
|
|
'')
|
|
ORDER BY event_id) AS trx_info
|
|
|
|
FROM (
|
|
(SELECT thread_id, event_id, timer_wait, state,access_mode, autocommit, gtid, isolation_level
|
|
FROM performance_schema.events_transactions_current
|
|
WHERE thread_id = in_thread_id
|
|
AND end_event_id IS NULL)
|
|
UNION
|
|
(SELECT thread_id, event_id, timer_wait, state,access_mode, autocommit, gtid, isolation_level
|
|
FROM performance_schema.events_transactions_history
|
|
WHERE thread_id = in_thread_id)
|
|
) AS trxi
|
|
LEFT JOIN (SELECT thread_id,
|
|
nesting_event_id,
|
|
GROUP_CONCAT(
|
|
IFNULL(
|
|
CONCAT('\n {\n',
|
|
' "sql_text": "', IFNULL(sys.format_statement(REPLACE(sql_text, '\\', '\\\\')), ''), '",\n',
|
|
' "time": "', IFNULL(format_pico_time(timer_wait), ''), '",\n',
|
|
' "schema": "', IFNULL(current_schema, ''), '",\n',
|
|
' "rows_examined": ', IFNULL(rows_examined, ''), ',\n',
|
|
' "rows_affected": ', IFNULL(rows_affected, ''), ',\n',
|
|
' "rows_sent": ', IFNULL(rows_sent, ''), ',\n',
|
|
' "tmp_tables": ', IFNULL(created_tmp_tables, ''), ',\n',
|
|
' "tmp_disk_tables": ', IFNULL(created_tmp_disk_tables, ''), ',\n',
|
|
' "sort_rows": ', IFNULL(sort_rows, ''), ',\n',
|
|
' "sort_merge_passes": ', IFNULL(sort_merge_passes, ''), '\n',
|
|
' }'), '') ORDER BY event_id) AS stmts
|
|
FROM performance_schema.events_statements_history
|
|
WHERE sql_text IS NOT NULL
|
|
AND thread_id = in_thread_id
|
|
GROUP BY thread_id, nesting_event_id
|
|
) AS s
|
|
ON trxi.thread_id = s.thread_id
|
|
AND trxi.event_id = s.nesting_event_id
|
|
WHERE trxi.thread_id = in_thread_id
|
|
GROUP BY trxi.thread_id, trxi.event_id
|
|
) trxs
|
|
GROUP BY thread_id
|
|
);
|
|
|
|
IF (@old_group_concat_max_len IS NOT NULL) THEN
|
|
SET SESSION group_concat_max_len = @old_group_concat_max_len;
|
|
END IF;
|
|
|
|
RETURN v_output;
|
|
END$$
|
|
|
|
DELIMITER ;
|