summaryrefslogtreecommitdiffstats
path: root/scripts/sys_schema/functions
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 18:00:34 +0000
commit3f619478f796eddbba6e39502fe941b285dd97b1 (patch)
treee2c7b5777f728320e5b5542b6213fd3591ba51e2 /scripts/sys_schema/functions
parentInitial commit. (diff)
downloadmariadb-upstream.tar.xz
mariadb-upstream.zip
Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'scripts/sys_schema/functions')
-rw-r--r--scripts/sys_schema/functions/extract_schema_from_file_name.sql66
-rw-r--r--scripts/sys_schema/functions/extract_table_from_file_name.sql62
-rw-r--r--scripts/sys_schema/functions/format_bytes.sql84
-rw-r--r--scripts/sys_schema/functions/format_path.sql111
-rw-r--r--scripts/sys_schema/functions/format_path_57.sql136
-rw-r--r--scripts/sys_schema/functions/format_statement.sql81
-rw-r--r--scripts/sys_schema/functions/format_time.sql91
-rw-r--r--scripts/sys_schema/functions/list_add.sql90
-rw-r--r--scripts/sys_schema/functions/list_drop.sql91
-rw-r--r--scripts/sys_schema/functions/ps_is_account_enabled.sql69
-rw-r--r--scripts/sys_schema/functions/ps_is_account_enabled_57.sql70
-rw-r--r--scripts/sys_schema/functions/ps_is_consumer_enabled.sql76
-rw-r--r--scripts/sys_schema/functions/ps_is_instrument_default_enabled.sql76
-rw-r--r--scripts/sys_schema/functions/ps_is_instrument_default_timed.sql75
-rw-r--r--scripts/sys_schema/functions/ps_is_thread_instrumented.sql72
-rw-r--r--scripts/sys_schema/functions/ps_thread_account.sql71
-rw-r--r--scripts/sys_schema/functions/ps_thread_id.sql66
-rw-r--r--scripts/sys_schema/functions/ps_thread_stack.sql171
-rw-r--r--scripts/sys_schema/functions/ps_thread_trx_info.sql233
-rw-r--r--scripts/sys_schema/functions/quote_identifier.sql71
-rw-r--r--scripts/sys_schema/functions/sys_get_config.sql99
-rw-r--r--scripts/sys_schema/functions/version_major.sql51
-rw-r--r--scripts/sys_schema/functions/version_minor.sql51
-rw-r--r--scripts/sys_schema/functions/version_patch.sql51
24 files changed, 2114 insertions, 0 deletions
diff --git a/scripts/sys_schema/functions/extract_schema_from_file_name.sql b/scripts/sys_schema/functions/extract_schema_from_file_name.sql
new file mode 100644
index 00000000..92c127f9
--- /dev/null
+++ b/scripts/sys_schema/functions/extract_schema_from_file_name.sql
@@ -0,0 +1,66 @@
+-- Copyright (c) 2014, 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 extract_schema_from_file_name;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION extract_schema_from_file_name (
+ path VARCHAR(512)
+ )
+ RETURNS VARCHAR(64)
+ COMMENT '
+ Description
+ -----------
+
+ Takes a raw file path, and attempts to extract the schema name from it.
+
+ Useful for when interacting with Performance Schema data
+ concerning IO statistics, for example.
+
+ Currently relies on the fact that a table data file will be within a
+ specified database directory (will not work with partitions or tables
+ that specify an individual DATA_DIRECTORY).
+
+ Parameters
+ -----------
+
+ path (VARCHAR(512)):
+ The full file path to a data file to extract the schema name from.
+
+ Returns
+ -----------
+
+ VARCHAR(64)
+
+ Example
+ -----------
+
+ mysql> SELECT sys.extract_schema_from_file_name(\'/var/lib/mysql/employees/employee.ibd\');
+ +----------------------------------------------------------------------------+
+ | sys.extract_schema_from_file_name(\'/var/lib/mysql/employees/employee.ibd\') |
+ +----------------------------------------------------------------------------+
+ | employees |
+ +----------------------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ NO SQL
+BEGIN
+ RETURN LEFT(SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(path, '\\', '/'), '/', -2), '/', 1), 64);
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/extract_table_from_file_name.sql b/scripts/sys_schema/functions/extract_table_from_file_name.sql
new file mode 100644
index 00000000..54f92568
--- /dev/null
+++ b/scripts/sys_schema/functions/extract_table_from_file_name.sql
@@ -0,0 +1,62 @@
+-- Copyright (c) 2014, 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 extract_table_from_file_name;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION extract_table_from_file_name (
+ path VARCHAR(512)
+ )
+ RETURNS VARCHAR(64)
+ COMMENT '
+ Description
+ -----------
+
+ Takes a raw file path, and extracts the table name from it.
+
+ Useful for when interacting with Performance Schema data
+ concerning IO statistics, for example.
+
+ Parameters
+ -----------
+
+ path (VARCHAR(512)):
+ The full file path to a data file to extract the table name from.
+
+ Returns
+ -----------
+
+ VARCHAR(64)
+
+ Example
+ -----------
+
+ mysql> SELECT sys.extract_table_from_file_name(\'/var/lib/mysql/employees/employee.ibd\');
+ +---------------------------------------------------------------------------+
+ | sys.extract_table_from_file_name(\'/var/lib/mysql/employees/employee.ibd\') |
+ +---------------------------------------------------------------------------+
+ | employee |
+ +---------------------------------------------------------------------------+
+ 1 row in set (0.02 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ NO SQL
+BEGIN
+ RETURN LEFT(SUBSTRING_INDEX(REPLACE(SUBSTRING_INDEX(REPLACE(path, '\\', '/'), '/', -1), '@0024', '$'), '.', 1), 64);
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/format_bytes.sql b/scripts/sys_schema/functions/format_bytes.sql
new file mode 100644
index 00000000..361aa39a
--- /dev/null
+++ b/scripts/sys_schema/functions/format_bytes.sql
@@ -0,0 +1,84 @@
+-- Copyright (c) 2014, 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 format_bytes;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION format_bytes (
+ -- We feed in and return TEXT here, as aggregates of
+ -- bytes can return numbers larger than BIGINT UNSIGNED
+ bytes TEXT
+ )
+ RETURNS TEXT
+ COMMENT '
+ Description
+ -----------
+
+ Takes a raw bytes value, and converts it to a human readable format.
+
+ Parameters
+ -----------
+
+ bytes (TEXT):
+ A raw bytes value.
+
+ Returns
+ -----------
+
+ TEXT
+
+ Example
+ -----------
+
+ mysql> SELECT sys.format_bytes(2348723492723746) AS size;
+ +----------+
+ | size |
+ +----------+
+ | 2.09 PiB |
+ +----------+
+ 1 row in set (0.00 sec)
+
+ mysql> SELECT sys.format_bytes(2348723492723) AS size;
+ +----------+
+ | size |
+ +----------+
+ | 2.14 TiB |
+ +----------+
+ 1 row in set (0.00 sec)
+
+ mysql> SELECT sys.format_bytes(23487234) AS size;
+ +-----------+
+ | size |
+ +-----------+
+ | 22.40 MiB |
+ +-----------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ NO SQL
+BEGIN
+ IF bytes IS NULL THEN RETURN NULL;
+ ELSEIF bytes >= 1125899906842624 THEN RETURN CONCAT(ROUND(bytes / 1125899906842624, 2), ' PiB');
+ ELSEIF bytes >= 1099511627776 THEN RETURN CONCAT(ROUND(bytes / 1099511627776, 2), ' TiB');
+ ELSEIF bytes >= 1073741824 THEN RETURN CONCAT(ROUND(bytes / 1073741824, 2), ' GiB');
+ ELSEIF bytes >= 1048576 THEN RETURN CONCAT(ROUND(bytes / 1048576, 2), ' MiB');
+ ELSEIF bytes >= 1024 THEN RETURN CONCAT(ROUND(bytes / 1024, 2), ' KiB');
+ ELSE RETURN CONCAT(ROUND(bytes, 0), ' bytes');
+ END IF;
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/format_path.sql b/scripts/sys_schema/functions/format_path.sql
new file mode 100644
index 00000000..426bfb1d
--- /dev/null
+++ b/scripts/sys_schema/functions/format_path.sql
@@ -0,0 +1,111 @@
+-- Copyright (c) 2014, 2016, 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 format_path;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION format_path (
+ in_path VARCHAR(512)
+ )
+ RETURNS VARCHAR(512) CHARSET UTF8
+ COMMENT '
+ Description
+ -----------
+
+ Takes a raw path value, and strips out the datadir or tmpdir
+ replacing with @@datadir and @@tmpdir respectively.
+
+ Also normalizes the paths across operating systems, so backslashes
+ on Windows are converted to forward slashes
+
+ Parameters
+ -----------
+
+ path (VARCHAR(512)):
+ The raw file path value to format.
+
+ Returns
+ -----------
+
+ VARCHAR(512) CHARSET UTF8
+
+ Example
+ -----------
+
+ mysql> select @@datadir;
+ +-----------------------------------------------+
+ | @@datadir |
+ +-----------------------------------------------+
+ | /Users/mark/sandboxes/SmallTree/AMaster/data/ |
+ +-----------------------------------------------+
+ 1 row in set (0.06 sec)
+
+ mysql> select format_path(\'/Users/mark/sandboxes/SmallTree/AMaster/data/mysql/proc.MYD\') AS path;
+ +--------------------------+
+ | path |
+ +--------------------------+
+ | @@datadir/mysql/proc.MYD |
+ +--------------------------+
+ 1 row in set (0.03 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ NO SQL
+BEGIN
+ DECLARE v_path VARCHAR(512);
+ DECLARE v_undo_dir VARCHAR(1024);
+ DECLARE v_innodb_data_home_dir VARCHAR(1024);
+ DECLARE v_innodb_log_group_home_dir VARCHAR(1024);
+
+ DECLARE path_separator CHAR(1) DEFAULT '/';
+
+ IF @@global.version_compile_os LIKE 'win%' THEN
+ SET path_separator = '\\';
+ END IF;
+
+ -- OSX hides /private/ in variables, but Performance Schema does not
+ IF in_path LIKE '/private/%' THEN
+ SET v_path = REPLACE(in_path, '/private', '');
+ ELSE
+ SET v_path = in_path;
+ END IF;
+
+ -- @@global.innodb_undo_directory is only set when separate undo logs are used
+ SET v_undo_dir = IFNULL((SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'innodb_undo_directory'), '');
+ SET v_innodb_data_home_dir= IFNULL((SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'innodb_data_home_dir'), '');
+ SET v_innodb_log_group_home_dir= IFNULL((SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'innodb_log_group_home_dir'), '');
+ IF v_path IS NULL THEN
+ RETURN NULL;
+ ELSEIF v_path LIKE CONCAT(@@global.datadir, IF(SUBSTRING(@@global.datadir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, @@global.datadir, CONCAT('@@datadir', IF(SUBSTRING(@@global.datadir, -1) = path_separator, path_separator, '')));
+ ELSEIF v_path LIKE CONCAT(@@global.tmpdir, IF(SUBSTRING(@@global.tmpdir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, @@global.tmpdir, CONCAT('@@tmpdir', IF(SUBSTRING(@@global.tmpdir, -1) = path_separator, path_separator, '')));
+# ELSEIF v_path LIKE CONCAT(@@global.slave_load_tmpdir, IF(SUBSTRING(@@global.slave_load_tmpdir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+# SET v_path = REPLACE(v_path, @@global.slave_load_tmpdir, CONCAT('@@slave_load_tmpdir', IF(SUBSTRING(@@global.slave_load_tmpdir, -1) = path_separator, path_separator, '')));
+ ELSEIF v_path LIKE CONCAT(v_innodb_data_home_dir, IF(SUBSTRING(v_innodb_data_home_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, v_innodb_data_home_dir, CONCAT('@@innodb_data_home_dir', IF(SUBSTRING(v_innodb_data_home_dir, -1) = path_separator, path_separator, '')));
+ ELSEIF v_path LIKE CONCAT(v_innodb_log_group_home_dir, IF(SUBSTRING(v_innodb_log_group_home_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, v_innodb_log_group_home_dir, CONCAT('@@innodb_log_group_home_dir', IF(SUBSTRING(v_innodb_log_group_home_dir, -1) = path_separator, path_separator, '')));
+ ELSEIF v_path LIKE CONCAT(v_undo_dir, IF(SUBSTRING(v_undo_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, v_undo_dir, CONCAT('@@innodb_undo_directory', IF(SUBSTRING(v_undo_dir, -1) = path_separator, path_separator, '')));
+ ELSEIF v_path LIKE CONCAT(@@global.basedir, IF(SUBSTRING(@@global.basedir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, @@global.basedir, CONCAT('@@basedir', IF(SUBSTRING(@@global.basedir, -1) = path_separator, path_separator, '')));
+ END IF;
+
+ RETURN v_path;
+END$$
+
+DELIMITER ; \ No newline at end of file
diff --git a/scripts/sys_schema/functions/format_path_57.sql b/scripts/sys_schema/functions/format_path_57.sql
new file mode 100644
index 00000000..2bb8b723
--- /dev/null
+++ b/scripts/sys_schema/functions/format_path_57.sql
@@ -0,0 +1,136 @@
+-- Copyright (c) 2014, 2016, 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 format_path;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION format_path (
+ in_path VARCHAR(512)
+ )
+ RETURNS VARCHAR(512) CHARSET UTF8
+ COMMENT '
+ Description
+ -----------
+
+ Takes a raw path value, and strips out the datadir or tmpdir
+ replacing with @@datadir and @@tmpdir respectively.
+
+ Also normalizes the paths across operating systems, so backslashes
+ on Windows are converted to forward slashes
+
+ Parameters
+ -----------
+
+ path (VARCHAR(512)):
+ The raw file path value to format.
+
+ Returns
+ -----------
+
+ VARCHAR(512) CHARSET UTF8
+
+ Example
+ -----------
+
+ mysql> select @@datadir;
+ +-----------------------------------------------+
+ | @@datadir |
+ +-----------------------------------------------+
+ | /Users/mark/sandboxes/SmallTree/AMaster/data/ |
+ +-----------------------------------------------+
+ 1 row in set (0.06 sec)
+
+ mysql> select format_path(\'/Users/mark/sandboxes/SmallTree/AMaster/data/mysql/proc.MYD\') AS path;
+ +--------------------------+
+ | path |
+ +--------------------------+
+ | @@datadir/mysql/proc.MYD |
+ +--------------------------+
+ 1 row in set (0.03 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ NO SQL
+BEGIN
+ DECLARE v_dir VARCHAR(1024);
+ DECLARE v_path VARCHAR(512);
+
+ DECLARE path_separator CHAR(1) DEFAULT '/';
+
+ IF @@global.version_compile_os LIKE 'win%' THEN
+ SET path_separator = '\\';
+ END IF;
+
+ -- OSX hides /private/ in variables, but Performance Schema does not
+ IF in_path LIKE '/private/%' THEN
+ SET v_path = REPLACE(in_path, '/private', '');
+ ELSE
+ SET v_path = in_path;
+ END IF;
+
+ -- @@global.innodb_undo_directory is only set when separate undo logs are used
+
+ SET v_dir= IFNULL((SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'innodb_data_home_dir'), '');
+
+ IF v_path IS NULL THEN
+ RETURN NULL;
+ END IF;
+
+ IF v_path LIKE CONCAT(@@global.datadir, IF(SUBSTRING(@@global.datadir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, @@global.datadir, CONCAT('@@datadir', IF(SUBSTRING(@@global.datadir, -1) = path_separator, path_separator, '')));
+ RETURN v_path;
+ END IF;
+
+ IF v_path LIKE CONCAT(@@global.tmpdir, IF(SUBSTRING(@@global.tmpdir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, @@global.tmpdir, CONCAT('@@tmpdir', IF(SUBSTRING(@@global.tmpdir, -1) = path_separator, path_separator, '')));
+ RETURN v_path;
+ END IF;
+
+
+ SET v_dir= IFNULL((SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'innodb_data_home_dir'), '');
+ IF v_path LIKE CONCAT(v_dir, IF(SUBSTRING(v_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, v_dir, CONCAT('@@innodb_data_home_dir', IF(SUBSTRING(v_dir, -1) = path_separator, path_separator, '')));
+ RETURN v_path;
+ END IF;
+
+ SET v_dir= IFNULL((SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'innodb_log_group_home_dir'), '');
+ IF v_path LIKE CONCAT(v_dir, IF(SUBSTRING(v_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, v_dir, CONCAT('@@innodb_log_group_home_dir', IF(SUBSTRING(v_dir, -1) = path_separator, path_separator, '')));
+ RETURN v_path;
+ END IF;
+
+ SET v_dir= IFNULL((SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'slave_load_tmpdir'), '');
+ IF v_path LIKE CONCAT(v_dir, IF(SUBSTRING(v_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, v_dir, CONCAT('@@slave_load_tmpdir', IF(SUBSTRING(v_dir, -1) = path_separator, path_separator, '')));
+ RETURN v_path;
+ END IF;
+
+
+ SET v_dir = IFNULL((SELECT VARIABLE_VALUE FROM information_schema.global_variables WHERE VARIABLE_NAME = 'innodb_undo_directory'), '');
+ IF v_path LIKE CONCAT(v_dir, IF(SUBSTRING(v_dir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, v_dir, CONCAT('@@innodb_undo_directory', IF(SUBSTRING(v_dir, -1) = path_separator, path_separator, '')));
+ RETURN v_path;
+ END IF;
+
+ IF v_path LIKE CONCAT(@@global.basedir, IF(SUBSTRING(@@global.basedir, -1) = path_separator, '%', CONCAT(path_separator, '%'))) ESCAPE '|' THEN
+ SET v_path = REPLACE(v_path, @@global.basedir, CONCAT('@@basedir', IF(SUBSTRING(@@global.basedir, -1) = path_separator, path_separator, '')));
+ RETURN v_path;
+ END IF;
+
+ RETURN v_path;
+END$$
+
+DELIMITER ; \ No newline at end of file
diff --git a/scripts/sys_schema/functions/format_statement.sql b/scripts/sys_schema/functions/format_statement.sql
new file mode 100644
index 00000000..8e434b24
--- /dev/null
+++ b/scripts/sys_schema/functions/format_statement.sql
@@ -0,0 +1,81 @@
+-- Copyright (c) 2014, 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 format_statement;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION format_statement (
+ statement LONGTEXT
+ )
+ RETURNS LONGTEXT
+ COMMENT '
+ Description
+ -----------
+
+ Formats a normalized statement, truncating it if it is > 64 characters long by default.
+
+ To configure the length to truncate the statement to by default, update the `statement_truncate_len`
+ variable with `sys_config` table to a different value. Alternatively, to change it just for just
+ your particular session, use `SET @sys.statement_truncate_len := <some new value>`.
+
+ Useful for printing statement related data from Performance Schema from
+ the command line.
+
+ Parameters
+ -----------
+
+ statement (LONGTEXT):
+ The statement to format.
+
+ Returns
+ -----------
+
+ LONGTEXT
+
+ Example
+ -----------
+
+ mysql> SELECT sys.format_statement(digest_text)
+ -> FROM performance_schema.events_statements_summary_by_digest
+ -> ORDER by sum_timer_wait DESC limit 5;
+ +-------------------------------------------------------------------+
+ | sys.format_statement(digest_text) |
+ +-------------------------------------------------------------------+
+ | CREATE SQL SECURITY INVOKER VI ... KE ? AND `variable_value` > ? |
+ | CREATE SQL SECURITY INVOKER VI ... ait` IS NOT NULL , `esc` . ... |
+ | CREATE SQL SECURITY INVOKER VI ... ait` IS NOT NULL , `sys` . ... |
+ | CREATE SQL SECURITY INVOKER VI ... , `compressed_size` ) ) DESC |
+ | CREATE SQL SECURITY INVOKER VI ... LIKE ? ORDER BY `timer_start` |
+ +-------------------------------------------------------------------+
+ 5 rows in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ NO SQL
+BEGIN
+ -- Check if we have the configured length, if not, init it
+ IF @sys.statement_truncate_len IS NULL THEN
+ SET @sys.statement_truncate_len = sys_get_config('statement_truncate_len', 64);
+ END IF;
+
+ IF CHAR_LENGTH(statement) > @sys.statement_truncate_len THEN
+ RETURN REPLACE(CONCAT(LEFT(statement, (@sys.statement_truncate_len/2)-2), ' ... ', RIGHT(statement, (@sys.statement_truncate_len/2)-2)), '\n', ' ');
+ ELSE
+ RETURN REPLACE(statement, '\n', ' ');
+ END IF;
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/format_time.sql b/scripts/sys_schema/functions/format_time.sql
new file mode 100644
index 00000000..f828f791
--- /dev/null
+++ b/scripts/sys_schema/functions/format_time.sql
@@ -0,0 +1,91 @@
+-- Copyright (c) 2014, 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 format_time;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION format_time (
+ -- We feed in and return TEXT here, as aggregates of
+ -- picoseconds can return numbers larger than BIGINT UNSIGNED
+ picoseconds TEXT
+ )
+ RETURNS TEXT CHARSET UTF8
+ COMMENT '
+ Description
+ -----------
+
+ Takes a raw picoseconds value, and converts it to a human readable form.
+
+ Picoseconds are the precision that all latency values are printed in
+ within Performance Schema, however are not user friendly when wanting
+ to scan output from the command line.
+
+ Parameters
+ -----------
+
+ picoseconds (TEXT):
+ The raw picoseconds value to convert.
+
+ Returns
+ -----------
+
+ TEXT
+
+ Example
+ -----------
+
+ mysql> select format_time(342342342342345);
+ +------------------------------+
+ | format_time(342342342342345) |
+ +------------------------------+
+ | 00:05:42 |
+ +------------------------------+
+ 1 row in set (0.00 sec)
+
+ mysql> select format_time(342342342);
+ +------------------------+
+ | format_time(342342342) |
+ +------------------------+
+ | 342.34 us |
+ +------------------------+
+ 1 row in set (0.00 sec)
+
+ mysql> select format_time(34234);
+ +--------------------+
+ | format_time(34234) |
+ +--------------------+
+ | 34.23 ns |
+ +--------------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ NO SQL
+BEGIN
+ IF picoseconds IS NULL THEN RETURN NULL;
+ ELSEIF picoseconds >= 604800000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 604800000000000000, 2), ' w');
+ ELSEIF picoseconds >= 86400000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 86400000000000000, 2), ' d');
+ ELSEIF picoseconds >= 3600000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 3600000000000000, 2), ' h');
+ ELSEIF picoseconds >= 60000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 60000000000000, 2), ' m');
+ ELSEIF picoseconds >= 1000000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000000, 2), ' s');
+ ELSEIF picoseconds >= 1000000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000000, 2), ' ms');
+ ELSEIF picoseconds >= 1000000 THEN RETURN CONCAT(ROUND(picoseconds / 1000000, 2), ' us');
+ ELSEIF picoseconds >= 1000 THEN RETURN CONCAT(ROUND(picoseconds / 1000, 2), ' ns');
+ ELSE RETURN CONCAT(picoseconds, ' ps');
+ END IF;
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/list_add.sql b/scripts/sys_schema/functions/list_add.sql
new file mode 100644
index 00000000..6079fbc5
--- /dev/null
+++ b/scripts/sys_schema/functions/list_add.sql
@@ -0,0 +1,90 @@
+-- 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 list_add;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION list_add (
+ in_list TEXT,
+ in_add_value TEXT
+ )
+ RETURNS TEXT
+ COMMENT '
+ Description
+ -----------
+
+ Takes a list, and a value to add to the list, and returns the resulting list.
+
+ Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.
+
+ Parameters
+ -----------
+
+ in_list (TEXT):
+ The comma separated list to add a value to
+
+ in_add_value (TEXT):
+ The value to add to the input list
+
+ Returns
+ -----------
+
+ TEXT
+
+ Example
+ --------
+
+ mysql> select @@sql_mode;
+ +-----------------------------------------------------------------------------------+
+ | @@sql_mode |
+ +-----------------------------------------------------------------------------------+
+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+ +-----------------------------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+
+ mysql> set sql_mode = sys.list_add(@@sql_mode, ''ANSI_QUOTES'');
+ Query OK, 0 rows affected (0.06 sec)
+
+ mysql> select @@sql_mode;
+ +-----------------------------------------------------------------------------------------------+
+ | @@sql_mode |
+ +-----------------------------------------------------------------------------------------------+
+ | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+ +-----------------------------------------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ CONTAINS SQL
+BEGIN
+
+ IF (in_add_value IS NULL) THEN
+ SIGNAL SQLSTATE '02200'
+ SET MESSAGE_TEXT = 'Function sys.list_add: in_add_value input variable should not be NULL',
+ MYSQL_ERRNO = 1138;
+ END IF;
+
+ IF (in_list IS NULL OR LENGTH(in_list) = 0) THEN
+ -- return the new value as a single value list
+ RETURN in_add_value;
+ END IF;
+
+ RETURN (SELECT CONCAT(TRIM(BOTH ',' FROM TRIM(in_list)), ',', in_add_value));
+
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/list_drop.sql b/scripts/sys_schema/functions/list_drop.sql
new file mode 100644
index 00000000..eccce7ed
--- /dev/null
+++ b/scripts/sys_schema/functions/list_drop.sql
@@ -0,0 +1,91 @@
+-- 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 list_drop;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION list_drop (
+ in_list TEXT,
+ in_drop_value TEXT
+ )
+ RETURNS TEXT
+ COMMENT '
+ Description
+ -----------
+
+ Takes a list, and a value to attempt to remove from the list, and returns the resulting list.
+
+ Useful for altering certain session variables, like sql_mode or optimizer_switch for instance.
+
+ Parameters
+ -----------
+
+ in_list (TEXT):
+ The comma separated list to drop a value from
+
+ in_drop_value (TEXT):
+ The value to drop from the input list
+
+ Returns
+ -----------
+
+ TEXT
+
+ Example
+ --------
+
+ mysql> select @@sql_mode;
+ +-----------------------------------------------------------------------------------------------+
+ | @@sql_mode |
+ +-----------------------------------------------------------------------------------------------+
+ | ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+ +-----------------------------------------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+
+ mysql> set sql_mode = sys.list_drop(@@sql_mode, ''ONLY_FULL_GROUP_BY'');
+ Query OK, 0 rows affected (0.03 sec)
+
+ mysql> select @@sql_mode;
+ +----------------------------------------------------------------------------+
+ | @@sql_mode |
+ +----------------------------------------------------------------------------+
+ | ANSI_QUOTES,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+ +----------------------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ CONTAINS SQL
+BEGIN
+
+ IF (in_drop_value IS NULL) THEN
+ SIGNAL SQLSTATE '02200'
+ SET MESSAGE_TEXT = 'Function sys.list_drop: in_drop_value input variable should not be NULL',
+ MYSQL_ERRNO = 1138;
+ END IF;
+
+ IF (in_list IS NULL OR LENGTH(in_list) = 0) THEN
+ -- return the list as it was passed in
+ RETURN in_list;
+ END IF;
+
+ -- ensure that leading / trailing commas are remove, support values with either spaces or not between commas
+ RETURN (SELECT TRIM(BOTH ',' FROM REPLACE(REPLACE(CONCAT(',', in_list), CONCAT(',', in_drop_value), ''), CONCAT(', ', in_drop_value), '')));
+
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_is_account_enabled.sql b/scripts/sys_schema/functions/ps_is_account_enabled.sql
new file mode 100644
index 00000000..a4bcecc4
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_is_account_enabled.sql
@@ -0,0 +1,69 @@
+-- Copyright (c) 2014, 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_is_account_enabled;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_is_account_enabled (
+ in_host VARCHAR(60),
+ in_user VARCHAR(16)
+ )
+ RETURNS ENUM('YES', 'NO')
+ COMMENT '
+ Description
+ -----------
+
+ Determines whether instrumentation of an account is enabled
+ within Performance Schema.
+
+ Parameters
+ -----------
+
+ in_host VARCHAR(60):
+ The hostname of the account to check.
+ in_user (VARCHAR(16)):
+ The username of the account to check.
+
+ Returns
+ -----------
+
+ ENUM(\'YES\', \'NO\', \'PARTIAL\')
+
+ Example
+ -----------
+
+ mysql> SELECT sys.ps_is_account_enabled(\'localhost\', \'root\');
+ +------------------------------------------------+
+ | sys.ps_is_account_enabled(\'localhost\', \'root\') |
+ +------------------------------------------------+
+ | YES |
+ +------------------------------------------------+
+ 1 row in set (0.01 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ RETURN IF(EXISTS(SELECT 1
+ FROM performance_schema.setup_actors
+ WHERE (`HOST` = '%' OR in_host LIKE `HOST`)
+ AND (`USER` = '%' OR `USER` = in_user)
+ ),
+ 'YES', 'NO'
+ );
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_is_account_enabled_57.sql b/scripts/sys_schema/functions/ps_is_account_enabled_57.sql
new file mode 100644
index 00000000..12480716
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_is_account_enabled_57.sql
@@ -0,0 +1,70 @@
+-- Copyright (c) 2014, 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_is_account_enabled;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_is_account_enabled (
+ in_host VARCHAR(60),
+ in_user VARCHAR(32)
+ )
+ RETURNS ENUM('YES', 'NO')
+ COMMENT '
+ Description
+ -----------
+
+ Determines whether instrumentation of an account is enabled
+ within Performance Schema.
+
+ Parameters
+ -----------
+
+ in_host VARCHAR(60):
+ The hostname of the account to check.
+ in_user VARCHAR(32):
+ The username of the account to check.
+
+ Returns
+ -----------
+
+ ENUM(\'YES\', \'NO\', \'PARTIAL\')
+
+ Example
+ -----------
+
+ mysql> SELECT sys.ps_is_account_enabled(\'localhost\', \'root\');
+ +------------------------------------------------+
+ | sys.ps_is_account_enabled(\'localhost\', \'root\') |
+ +------------------------------------------------+
+ | YES |
+ +------------------------------------------------+
+ 1 row in set (0.01 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ RETURN IF(EXISTS(SELECT 1
+ FROM performance_schema.setup_actors
+ WHERE (`HOST` = '%' OR in_host LIKE `HOST`)
+ AND (`USER` = '%' OR `USER` = in_user)
+ AND (`ENABLED` = 'YES')
+ ),
+ 'YES', 'NO'
+ );
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_is_consumer_enabled.sql b/scripts/sys_schema/functions/ps_is_consumer_enabled.sql
new file mode 100644
index 00000000..c9290a9c
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_is_consumer_enabled.sql
@@ -0,0 +1,76 @@
+-- Copyright (c) 2014, 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_is_consumer_enabled;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_is_consumer_enabled (
+ in_consumer varchar(64)
+ )
+ RETURNS enum('YES', 'NO')
+ COMMENT '
+ Description
+ -----------
+
+ Determines whether a consumer is enabled (taking the consumer hierarchy into consideration)
+ within the Performance Schema.
+
+ Parameters
+ -----------
+
+ in_consumer VARCHAR(64):
+ The name of the consumer to check.
+
+ Returns
+ -----------
+
+ ENUM(\'YES\', \'NO\')
+
+ Example
+ -----------
+
+ mysql> SELECT sys.ps_is_consumer_enabled(\'events_stages_history\');
+ +-----------------------------------------------------+
+ | sys.ps_is_consumer_enabled(\'events_stages_history\') |
+ +-----------------------------------------------------+
+ | NO |
+ +-----------------------------------------------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ RETURN (
+ SELECT (CASE
+ WHEN c.NAME = 'global_instrumentation' THEN c.ENABLED
+ WHEN c.NAME = 'thread_instrumentation' THEN IF(cg.ENABLED = 'YES' AND c.ENABLED = 'YES', 'YES', 'NO')
+ WHEN c.NAME LIKE '%\_digest' THEN IF(cg.ENABLED = 'YES' AND c.ENABLED = 'YES', 'YES', 'NO')
+ WHEN c.NAME LIKE '%\_current' THEN IF(cg.ENABLED = 'YES' AND ct.ENABLED = 'YES' AND c.ENABLED = 'YES', 'YES', 'NO')
+ ELSE IF(cg.ENABLED = 'YES' AND ct.ENABLED = 'YES' AND c.ENABLED = 'YES'
+ AND ( SELECT cc.ENABLED FROM performance_schema.setup_consumers cc WHERE NAME = CONCAT(SUBSTRING_INDEX(c.NAME, '_', 2), '_current')
+ ) = 'YES', 'YES', 'NO')
+ END) AS IsEnabled
+ FROM performance_schema.setup_consumers c
+ INNER JOIN performance_schema.setup_consumers cg
+ INNER JOIN performance_schema.setup_consumers ct
+ WHERE cg.NAME = 'global_instrumentation'
+ AND ct.NAME = 'thread_instrumentation'
+ AND c.NAME = in_consumer
+ );
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_is_instrument_default_enabled.sql b/scripts/sys_schema/functions/ps_is_instrument_default_enabled.sql
new file mode 100644
index 00000000..2d79a161
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_is_instrument_default_enabled.sql
@@ -0,0 +1,76 @@
+-- Copyright (c) 2014, 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_is_instrument_default_enabled;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_is_instrument_default_enabled (
+ in_instrument VARCHAR(128)
+ )
+ RETURNS ENUM('YES', 'NO')
+ COMMENT '
+ Description
+ -----------
+
+ Returns whether an instrument is enabled by default in this version of MySQL.
+
+ Parameters
+ -----------
+
+ in_instrument VARCHAR(128):
+ The instrument to check.
+
+ Returns
+ -----------
+
+ ENUM(\'YES\', \'NO\')
+
+ Example
+ -----------
+
+ mysql> SELECT sys.ps_is_instrument_default_enabled(\'statement/sql/select\');
+ +--------------------------------------------------------------+
+ | sys.ps_is_instrument_default_enabled(\'statement/sql/select\') |
+ +--------------------------------------------------------------+
+ | YES |
+ +--------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ DECLARE v_enabled ENUM('YES', 'NO');
+
+ -- Currently the same in all versions
+ SET v_enabled = IF(in_instrument LIKE 'wait/io/file/%'
+ OR in_instrument LIKE 'wait/io/table/%'
+ OR in_instrument LIKE 'statement/%'
+ OR in_instrument LIKE 'memory/performance_schema/%'
+ OR in_instrument IN ('wait/lock/table/sql/handler', 'idle')
+ /*!50707
+ OR in_instrument LIKE 'stage/innodb/%'
+ OR in_instrument = 'stage/sql/copy to tmp table'
+ */
+ ,
+ 'YES',
+ 'NO'
+ );
+
+ RETURN v_enabled;
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_is_instrument_default_timed.sql b/scripts/sys_schema/functions/ps_is_instrument_default_timed.sql
new file mode 100644
index 00000000..d78e324d
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_is_instrument_default_timed.sql
@@ -0,0 +1,75 @@
+-- Copyright (c) 2014, 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_is_instrument_default_timed;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_is_instrument_default_timed (
+ in_instrument VARCHAR(128)
+ )
+ RETURNS ENUM('YES', 'NO')
+ COMMENT '
+ Description
+ -----------
+
+ Returns whether an instrument is timed by default in this version of MySQL.
+
+ Parameters
+ -----------
+
+ in_instrument VARCHAR(128):
+ The instrument to check.
+
+ Returns
+ -----------
+
+ ENUM(\'YES\', \'NO\')
+
+ Example
+ -----------
+
+ mysql> SELECT sys.ps_is_instrument_default_timed(\'statement/sql/select\');
+ +------------------------------------------------------------+
+ | sys.ps_is_instrument_default_timed(\'statement/sql/select\') |
+ +------------------------------------------------------------+
+ | YES |
+ +------------------------------------------------------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ DECLARE v_timed ENUM('YES', 'NO');
+
+ -- Currently the same in all versions
+ SET v_timed = IF(in_instrument LIKE 'wait/io/file/%'
+ OR in_instrument LIKE 'wait/io/table/%'
+ OR in_instrument LIKE 'statement/%'
+ OR in_instrument IN ('wait/lock/table/sql/handler', 'idle')
+ /*!50707
+ OR in_instrument LIKE 'stage/innodb/%'
+ OR in_instrument = 'stage/sql/copy to tmp table'
+ */
+ ,
+ 'YES',
+ 'NO'
+ );
+
+ RETURN v_timed;
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_is_thread_instrumented.sql b/scripts/sys_schema/functions/ps_is_thread_instrumented.sql
new file mode 100644
index 00000000..06388312
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_is_thread_instrumented.sql
@@ -0,0 +1,72 @@
+-- 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_is_thread_instrumented;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_is_thread_instrumented (
+ in_connection_id BIGINT UNSIGNED
+ ) RETURNS ENUM('YES', 'NO', 'UNKNOWN')
+ COMMENT '
+ Description
+ -----------
+
+ Checks whether the provided connection id is instrumented within Performance Schema.
+
+ Parameters
+ -----------
+
+ in_connection_id (BIGINT UNSIGNED):
+ The id of the connection to check.
+
+ Returns
+ -----------
+
+ ENUM(\'YES\', \'NO\', \'UNKNOWN\')
+
+ Example
+ -----------
+
+ mysql> SELECT sys.ps_is_thread_instrumented(CONNECTION_ID());
+ +------------------------------------------------+
+ | sys.ps_is_thread_instrumented(CONNECTION_ID()) |
+ +------------------------------------------------+
+ | YES |
+ +------------------------------------------------+
+ '
+
+ SQL SECURITY INVOKER
+ NOT DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ DECLARE v_enabled ENUM('YES', 'NO', 'UNKNOWN');
+
+ IF (in_connection_id IS NULL) THEN
+ RETURN NULL;
+ END IF;
+
+ SELECT INSTRUMENTED INTO v_enabled
+ FROM performance_schema.threads
+ WHERE PROCESSLIST_ID = in_connection_id;
+
+ IF (v_enabled IS NULL) THEN
+ RETURN 'UNKNOWN';
+ ELSE
+ RETURN v_enabled;
+ END IF;
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_thread_account.sql b/scripts/sys_schema/functions/ps_thread_account.sql
new file mode 100644
index 00000000..f91e8c66
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_thread_account.sql
@@ -0,0 +1,71 @@
+-- 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_account;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_thread_account (
+ in_thread_id BIGINT UNSIGNED
+ ) RETURNS TEXT
+ COMMENT '
+ Description
+ -----------
+
+ Return the user@host account for the given Performance Schema thread id.
+
+ Parameters
+ -----------
+
+ in_thread_id (BIGINT UNSIGNED):
+ The id of the thread to return the account for.
+
+ Example
+ -----------
+
+ mysql> select thread_id, processlist_user, processlist_host from performance_schema.threads where type = ''foreground'';
+ +-----------+------------------+------------------+
+ | thread_id | processlist_user | processlist_host |
+ +-----------+------------------+------------------+
+ | 23 | NULL | NULL |
+ | 30 | root | localhost |
+ | 31 | msandbox | localhost |
+ | 32 | msandbox | localhost |
+ +-----------+------------------+------------------+
+ 4 rows in set (0.00 sec)
+
+ mysql> select sys.ps_thread_account(31);
+ +---------------------------+
+ | sys.ps_thread_account(31) |
+ +---------------------------+
+ | msandbox@localhost |
+ +---------------------------+
+ 1 row in set (0.00 sec)
+ '
+
+ SQL SECURITY INVOKER
+ NOT DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ RETURN (SELECT IF(
+ type = 'FOREGROUND',
+ CONCAT(processlist_user, '@', processlist_host),
+ type
+ ) AS account
+ FROM `performance_schema`.`threads`
+ WHERE thread_id = in_thread_id);
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_thread_id.sql b/scripts/sys_schema/functions/ps_thread_id.sql
new file mode 100644
index 00000000..f344e6e1
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_thread_id.sql
@@ -0,0 +1,66 @@
+-- Copyright (c) 2014, 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_id;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_thread_id (
+ in_connection_id BIGINT UNSIGNED
+ ) RETURNS BIGINT UNSIGNED
+ COMMENT '
+ Description
+ -----------
+
+ Return the Performance Schema THREAD_ID for the specified connection ID.
+
+ Parameters
+ -----------
+
+ in_connection_id (BIGINT UNSIGNED):
+ The id of the connection to return the thread id for. If NULL, the current
+ connection thread id is returned.
+
+ Example
+ -----------
+
+ mysql> SELECT sys.ps_thread_id(79);
+ +----------------------+
+ | sys.ps_thread_id(79) |
+ +----------------------+
+ | 98 |
+ +----------------------+
+ 1 row in set (0.00 sec)
+
+ mysql> SELECT sys.ps_thread_id(CONNECTION_ID());
+ +-----------------------------------+
+ | sys.ps_thread_id(CONNECTION_ID()) |
+ +-----------------------------------+
+ | 98 |
+ +-----------------------------------+
+ 1 row in set (0.00 sec)
+ '
+
+ SQL SECURITY INVOKER
+ NOT DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ RETURN (SELECT THREAD_ID
+ FROM `performance_schema`.`threads`
+ WHERE PROCESSLIST_ID = IFNULL(in_connection_id, CONNECTION_ID())
+ );
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_thread_stack.sql b/scripts/sys_schema/functions/ps_thread_stack.sql
new file mode 100644
index 00000000..12b09bd6
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_thread_stack.sql
@@ -0,0 +1,171 @@
+-- Copyright (c) 2014, 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_stack;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION ps_thread_stack (
+ thd_id BIGINT UNSIGNED,
+ debug BOOLEAN
+ )
+RETURNS LONGTEXT CHARSET latin1
+ COMMENT '
+ Description
+ -----------
+
+ Outputs a JSON formatted stack of all statements, stages and events
+ within Performance Schema for the specified thread.
+
+ Parameters
+ -----------
+
+ thd_id (BIGINT UNSIGNED):
+ The id of the thread to trace. This should match the thread_id
+ column from the performance_schema.threads table.
+ in_verbose (BOOLEAN):
+ Include file:lineno information in the events.
+
+ Example
+ -----------
+
+ (line separation added for output)
+
+ mysql> SELECT sys.ps_thread_stack(37, FALSE) AS thread_stack\\G
+ *************************** 1. row ***************************
+ thread_stack: {"rankdir": "LR","nodesep": "0.10","stack_created": "2014-02-19 13:39:03",
+ "mysql_version": "5.7.3-m13","mysql_user": "root@localhost","events":
+ [{"nesting_event_id": "0", "event_id": "10", "timer_wait": 256.35, "event_info":
+ "sql/select", "wait_info": "select @@version_comment limit 1\\nerrors: 0\\nwarnings: 0\\nlock time:
+ ...
+ '
+SQL SECURITY INVOKER
+NOT DETERMINISTIC
+READS SQL DATA
+BEGIN
+
+ DECLARE json_objects LONGTEXT;
+
+ /*!50602
+ -- Do not track the current thread, it will kill the stack
+ UPDATE performance_schema.threads
+ SET instrumented = 'NO'
+ WHERE processlist_id = CONNECTION_ID();
+ */
+
+ SET SESSION group_concat_max_len=@@global.max_allowed_packet;
+
+ -- Select the entire stack of events
+ SELECT GROUP_CONCAT(CONCAT( '{'
+ , CONCAT_WS( ', '
+ , CONCAT('"nesting_event_id": "', IF(nesting_event_id IS NULL, '0', nesting_event_id), '"')
+ , CONCAT('"event_id": "', event_id, '"')
+ -- Convert from picoseconds to microseconds
+ , CONCAT( '"timer_wait": ', ROUND(timer_wait/1000000, 2))
+ , CONCAT( '"event_info": "'
+ , CASE
+ WHEN event_name NOT LIKE 'wait/io%' THEN REPLACE(SUBSTRING_INDEX(event_name, '/', -2), '\\', '\\\\')
+ WHEN event_name NOT LIKE 'wait/io/file%' OR event_name NOT LIKE 'wait/io/socket%' THEN REPLACE(SUBSTRING_INDEX(event_name, '/', -4), '\\', '\\\\')
+ ELSE event_name
+ END
+ , '"'
+ )
+ -- Always dump the extra wait information gathered for statements
+ , CONCAT( '"wait_info": "', IFNULL(wait_info, ''), '"')
+ -- If debug is enabled, add the file:lineno information for waits
+ , CONCAT( '"source": "', IF(true AND event_name LIKE 'wait%', IFNULL(wait_info, ''), ''), '"')
+ -- Depending on the type of event, name it appropriately
+ , CASE
+ WHEN event_name LIKE 'wait/io/file%' THEN '"event_type": "io/file"'
+ WHEN event_name LIKE 'wait/io/table%' THEN '"event_type": "io/table"'
+ WHEN event_name LIKE 'wait/io/socket%' THEN '"event_type": "io/socket"'
+ WHEN event_name LIKE 'wait/synch/mutex%' THEN '"event_type": "synch/mutex"'
+ WHEN event_name LIKE 'wait/synch/cond%' THEN '"event_type": "synch/cond"'
+ WHEN event_name LIKE 'wait/synch/rwlock%' THEN '"event_type": "synch/rwlock"'
+ WHEN event_name LIKE 'wait/lock%' THEN '"event_type": "lock"'
+ WHEN event_name LIKE 'statement/%' THEN '"event_type": "stmt"'
+ WHEN event_name LIKE 'stage/%' THEN '"event_type": "stage"'
+ WHEN event_name LIKE '%idle%' THEN '"event_type": "idle"'
+ ELSE ''
+ END
+ )
+ , '}'
+ )
+ ORDER BY event_id ASC SEPARATOR ',') event
+ INTO json_objects
+ FROM (
+ /*!50600
+ -- Select all statements, with the extra tracing information available
+ (SELECT thread_id, event_id, event_name, timer_wait, timer_start, nesting_event_id,
+ CONCAT(sql_text, '\\n',
+ 'errors: ', errors, '\\n',
+ 'warnings: ', warnings, '\\n',
+ 'lock time: ', ROUND(lock_time/1000000, 2),'us\\n',
+ 'rows affected: ', rows_affected, '\\n',
+ 'rows sent: ', rows_sent, '\\n',
+ 'rows examined: ', rows_examined, '\\n',
+ 'tmp tables: ', created_tmp_tables, '\\n',
+ 'tmp disk tables: ', created_tmp_disk_tables, '\\n',
+ 'select scan: ', select_scan, '\\n',
+ 'select full join: ', select_full_join, '\\n',
+ 'select full range join: ', select_full_range_join, '\\n',
+ 'select range: ', select_range, '\\n',
+ 'select range check: ', select_range_check, '\\n',
+ 'sort merge passes: ', sort_merge_passes, '\\n',
+ 'sort rows: ', sort_rows, '\\n',
+ 'sort range: ', sort_range, '\\n',
+ 'sort scan: ', sort_scan, '\\n',
+ 'no index used: ', IF(no_index_used, 'TRUE', 'FALSE'), '\\n',
+ 'no good index used: ', IF(no_good_index_used, 'TRUE', 'FALSE'), '\\n'
+ ) AS wait_info
+ FROM performance_schema.events_statements_history_long WHERE thread_id = thd_id)
+ UNION
+ -- Select all stages
+ (SELECT thread_id, event_id, event_name, timer_wait, timer_start, nesting_event_id, null AS wait_info
+ FROM performance_schema.events_stages_history_long WHERE thread_id = thd_id)
+ UNION */
+ -- Select all events, adding information appropriate to the event
+ (SELECT thread_id, event_id,
+ CONCAT(event_name ,
+ IF(event_name NOT LIKE 'wait/synch/mutex%', IFNULL(CONCAT(' - ', operation), ''), ''),
+ IF(number_of_bytes IS NOT NULL, CONCAT(' ', number_of_bytes, ' bytes'), ''),
+ IF(event_name LIKE 'wait/io/file%', '\\n', ''),
+ IF(object_schema IS NOT NULL, CONCAT('\\nObject: ', object_schema, '.'), ''),
+ IF(object_name IS NOT NULL,
+ IF (event_name LIKE 'wait/io/socket%',
+ -- Print the socket if used, else the IP:port as reported
+ CONCAT(IF (object_name LIKE ':0%', @@socket, object_name)),
+ object_name),
+ ''),
+ /*!50600 IF(index_name IS NOT NULL, CONCAT(' Index: ', index_name), ''),*/'\\n'
+ ) AS event_name,
+ timer_wait, timer_start, nesting_event_id, source AS wait_info
+ FROM performance_schema.events_waits_history_long WHERE thread_id = thd_id)) events
+ ORDER BY event_id;
+
+ RETURN CONCAT('{',
+ CONCAT_WS(',',
+ '"rankdir": "LR"',
+ '"nodesep": "0.10"',
+ CONCAT('"stack_created": "', NOW(), '"'),
+ CONCAT('"mysql_version": "', VERSION(), '"'),
+ CONCAT('"mysql_user": "', CURRENT_USER(), '"'),
+ CONCAT('"events": [', IFNULL(json_objects,''), ']')
+ ),
+ '}');
+
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/ps_thread_trx_info.sql b/scripts/sys_schema/functions/ps_thread_trx_info.sql
new file mode 100644
index 00000000..12ab6aef
--- /dev/null
+++ b/scripts/sys_schema/functions/ps_thread_trx_info.sql
@@ -0,0 +1,233 @@
+-- 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(sys.format_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(sys.format_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 ;
diff --git a/scripts/sys_schema/functions/quote_identifier.sql b/scripts/sys_schema/functions/quote_identifier.sql
new file mode 100644
index 00000000..b661e5c7
--- /dev/null
+++ b/scripts/sys_schema/functions/quote_identifier.sql
@@ -0,0 +1,71 @@
+-- Copyright (c) 2016, 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 quote_identifier;
+
+DELIMITER $$
+
+-- https://dev.mysql.com/doc/refman/5.7/en/identifiers.html
+-- Maximum supported length for any of the current identifiers in 5.7.5+ is 256 characters.
+-- Before that, user variables could have any length.
+--
+-- Based on Paul Dubois' suggestion in Bug #78823/Bug #22011361.
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION quote_identifier(in_identifier TEXT)
+ RETURNS TEXT CHARSET UTF8
+ COMMENT '
+ Description
+ -----------
+
+ Takes an unquoted identifier (schema name, table name, etc.) and
+ returns the identifier quoted with backticks.
+
+ Parameters
+ -----------
+
+ in_identifier (TEXT):
+ The identifier to quote.
+
+ Returns
+ -----------
+
+ TEXT
+
+ Example
+ -----------
+
+ mysql> SELECT sys.quote_identifier(''my_identifier'') AS Identifier;
+ +-----------------+
+ | Identifier |
+ +-----------------+
+ | `my_identifier` |
+ +-----------------+
+ 1 row in set (0.00 sec)
+
+ mysql> SELECT sys.quote_identifier(''my`idenfier'') AS Identifier;
+ +----------------+
+ | Identifier |
+ +----------------+
+ | `my``idenfier` |
+ +----------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ NO SQL
+BEGIN
+ RETURN CONCAT('`', REPLACE(in_identifier, '`', '``'), '`');
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/sys_get_config.sql b/scripts/sys_schema/functions/sys_get_config.sql
new file mode 100644
index 00000000..9c0c9011
--- /dev/null
+++ b/scripts/sys_schema/functions/sys_get_config.sql
@@ -0,0 +1,99 @@
+-- Copyright (c) 2014, 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 sys_get_config;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION sys_get_config (
+ in_variable_name VARCHAR(128),
+ in_default_value VARCHAR(128)
+ )
+ RETURNS VARCHAR(128)
+ COMMENT '
+ Description
+ -----------
+
+ Returns the value for the requested variable using the following logic:
+
+ 1. If the option exists in sys.sys_config return the value from there.
+ 2. Else fall back on the provided default value.
+
+ Notes for using sys_get_config():
+
+ * If the default value argument to sys_get_config() is NULL and case 2. is reached, NULL is returned.
+ It is then expected that the caller is able to handle NULL for the given configuration option.
+ * The convention is to name the user variables @sys.<name of variable>. It is <name of variable> that
+ is stored in the sys_config table and is what is expected as the argument to sys_get_config().
+ * If you want to check whether the configuration option has already been set and if not assign with
+ the return value of sys_get_config() you can use IFNULL(...) (see example below). However this should
+ not be done inside a loop (e.g. for each row in a result set) as for repeated calls where assignment
+ is only needed in the first iteration using IFNULL(...) is expected to be significantly slower than
+ using an IF (...) THEN ... END IF; block (see example below).
+
+ Parameters
+ -----------
+
+ in_variable_name (VARCHAR(128)):
+ The name of the config option to return the value for.
+
+ in_default_value (VARCHAR(128)):
+ The default value to return if the variable does not exist in sys.sys_config.
+
+ Returns
+ -----------
+
+ VARCHAR(128)
+
+ Example
+ -----------
+
+ -- Get the configuration value from sys.sys_config falling back on 128 if the option is not present in the table.
+ mysql> SELECT sys.sys_get_config(''statement_truncate_len'', 128) AS Value;
+ +-------+
+ | Value |
+ +-------+
+ | 64 |
+ +-------+
+ 1 row in set (0.00 sec)
+
+ -- Check whether the option is already set, if not assign - IFNULL(...) one liner example.
+ mysql> SET @sys.statement_truncate_len = IFNULL(@sys.statement_truncate_len, sys.sys_get_config(''statement_truncate_len'', 64));
+ Query OK, 0 rows affected (0.00 sec)
+
+ -- Check whether the option is already set, if not assign - IF ... THEN ... END IF example.
+ IF (@sys.statement_truncate_len IS NULL) THEN
+ SET @sys.statement_truncate_len = sys.sys_get_config(''statement_truncate_len'', 64);
+ END IF;
+ '
+ SQL SECURITY INVOKER
+ DETERMINISTIC
+ READS SQL DATA
+BEGIN
+ DECLARE v_value VARCHAR(128) DEFAULT NULL;
+ DECLARE old_val INTEGER DEFAULT NULL;
+
+ -- Check if we have the variable in the sys.sys_config table
+ SET v_value = (SELECT value FROM sys.sys_config WHERE variable = in_variable_name);
+
+ -- Protection against the variable not existing in sys_config
+ IF (v_value IS NULL) THEN
+ SET v_value = in_default_value;
+ END IF;
+
+ RETURN v_value;
+END $$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/version_major.sql b/scripts/sys_schema/functions/version_major.sql
new file mode 100644
index 00000000..8e44d8cf
--- /dev/null
+++ b/scripts/sys_schema/functions/version_major.sql
@@ -0,0 +1,51 @@
+-- Copyright (c) 2014, 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 version_major;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION version_major ()
+ RETURNS TINYINT UNSIGNED
+ COMMENT '
+ Description
+ -----------
+
+ Returns the major version of MySQL Server.
+
+ Returns
+ -----------
+
+ TINYINT UNSIGNED
+
+ Example
+ -----------
+
+ mysql> SELECT VERSION(), sys.version_major();
+ +--------------------------------------+---------------------+
+ | VERSION() | sys.version_major() |
+ +--------------------------------------+---------------------+
+ | 5.7.9-enterprise-commercial-advanced | 5 |
+ +--------------------------------------+---------------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ NOT DETERMINISTIC
+ NO SQL
+BEGIN
+ RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 1), '.', 1);
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/version_minor.sql b/scripts/sys_schema/functions/version_minor.sql
new file mode 100644
index 00000000..6d8f654d
--- /dev/null
+++ b/scripts/sys_schema/functions/version_minor.sql
@@ -0,0 +1,51 @@
+-- Copyright (c) 2014, 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 version_minor;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION version_minor ()
+ RETURNS TINYINT UNSIGNED
+ COMMENT '
+ Description
+ -----------
+
+ Returns the minor (release series) version of MySQL Server.
+
+ Returns
+ -----------
+
+ TINYINT UNSIGNED
+
+ Example
+ -----------
+
+ mysql> SELECT VERSION(), sys.server_minor();
+ +--------------------------------------+---------------------+
+ | VERSION() | sys.version_minor() |
+ +--------------------------------------+---------------------+
+ | 5.7.9-enterprise-commercial-advanced | 7 |
+ +--------------------------------------+---------------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ NOT DETERMINISTIC
+ NO SQL
+BEGIN
+ RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 1), '.', 2), '.', -1);
+END$$
+
+DELIMITER ;
diff --git a/scripts/sys_schema/functions/version_patch.sql b/scripts/sys_schema/functions/version_patch.sql
new file mode 100644
index 00000000..156b5678
--- /dev/null
+++ b/scripts/sys_schema/functions/version_patch.sql
@@ -0,0 +1,51 @@
+-- Copyright (c) 2014, 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 version_patch;
+
+DELIMITER $$
+
+CREATE DEFINER='mariadb.sys'@'localhost' FUNCTION version_patch ()
+ RETURNS TINYINT UNSIGNED
+ COMMENT '
+ Description
+ -----------
+
+ Returns the patch release version of MySQL Server.
+
+ Returns
+ -----------
+
+ TINYINT UNSIGNED
+
+ Example
+ -----------
+
+ mysql> SELECT VERSION(), sys.version_patch();
+ +--------------------------------------+---------------------+
+ | VERSION() | sys.version_patch() |
+ +--------------------------------------+---------------------+
+ | 5.7.9-enterprise-commercial-advanced | 9 |
+ +--------------------------------------+---------------------+
+ 1 row in set (0.00 sec)
+ '
+ SQL SECURITY INVOKER
+ NOT DETERMINISTIC
+ NO SQL
+BEGIN
+ RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(VERSION(), '-', 1), '.', -1);
+END$$
+
+DELIMITER ;