diff options
Diffstat (limited to 'scripts/sys_schema/functions')
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 ; |