Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Explore sys_schema stored functions in MariaDB Server. These functions simplify querying performance and configuration data, offering a user-friendly interface for database diagnostics.
Convert bytes to a human-readable string. This function formats a numeric byte count into units like KiB, MiB, GiB, up to EiB.
sys.format_bytes(double)format_bytes is a available with the .
Given a byte count, returns a string consisting of a value and the units in a human-readable format. The units will be in bytes, KiB (kibibytes), MiB (mebibytes), GiB (gibibytes), TiB (tebibytes), or PiB (pebibytes).
The binary prefixes (kibi, mebi, gibi, tebi and pebi) were created in December 1998 by the International Electrotechnical Commission to avoid possible ambiguity, as the widely-used prefixes kilo, mega, giga, tera and peta can be used to refer to both the power-of-10 decimal system multipliers and the power-of-two binary system multipliers.
From , the built-in function can instead be used. The functions are similar, except that FORMAT_BYTES also displays exbibytes.
This page is licensed: CC BY-SA / Gnu FDL
This function takes a file path and returns the schema name it belongs to, assuming the file is located within the schema's directory.
sys.extract_schema_from_file_name(path)extract_schema_from_file_name is a available with the .
Given a file path, it returns the schema (database) name. The file name is assumed to be within the schema directory, and therefore the function will not return the expected result with partitions, or when tables are defined using the DATA_DIRECTORY table option.
The function does not examine anything on disk. The return value, a VARCHAR(64), is determined solely from the provided path.
This page is licensed: CC BY-SA / Gnu FDL
Given a file path, it returns the table name.
The function does not examine anything on disk. The return value, a VARCHAR(64), is determined solely from the provided path.
This page is licensed: CC BY-SA / Gnu FDL
sys.extract_table_from_file_name(path)SELECT sys.extract_schema_from_file_name('/usr/local/mysql/data/db/t1.ibd');
+----------------------------------------------------------------------+
| sys.extract_schema_from_file_name('/usr/local/mysql/data/db/t1.ibd') |
+----------------------------------------------------------------------+
| db |
+----------------------------------------------------------------------+This function returns the user account (username@hostname) associated with a specific Performance Schema thread ID.
This function checks if a specific Performance Schema instrument is timed by default, returning YES or NO based on the server's default configuration.
sys.ps_is_instrument_default_timed(instrument)ps_is_instrument_default_timed is a available with the .
It returns YES if the given Performance Schema instrument is timed by default, and NO if it is not, does not exist, or is a NULL value.
This page is licensed: CC BY-SA / Gnu FDL
This function appends a value to a comma-separated list of values, which is useful for modifying system variables that take list values.
sys.list_add(list,value)list_add is a available with the .
It takes a list to be modified and a value to be added to the list, returning the resulting value. This can be used, for example, to add a value to a system variable taking a comma-delimited list of options, such as .
The related function can be used to drop a value from a list.
This page is licensed: CC BY-SA / Gnu FDL
This function checks whether a specific user account is enabled for instrumentation in the Performance Schema setup_actors table.
sys.ps_is_account_enabled(host,user)ps_is_account_enabled is a available with the .
It takes host and user arguments, and returns an ENUM('YES','NO') depending on whether Performance Schema instrumentation for the given account is enabled.
This page is licensed: CC BY-SA / Gnu FDL
The ps_thread_trx_info function returns a JSON object detailing the current transaction and executed statements for a specific thread.
sys.ps_thread_trx_info(thread_id)ps_thread_trx_info is a available with the .
It returns a JSON object with information about the thread specified by the given thread_id. This information includes:
the current transaction;
executed statements (derived from the and the (full data will only returned if the consumers for those tables are enabled).
The maximum length of the returned JSON object is determined by the value of the (by default 65535). If the returned value exceeds this length, a JSON object error is returned.
This page is licensed: CC BY-SA / Gnu FDL
This function formats and truncates an SQL statement string to a specified length, making it suitable for display in summary views.
sys.format_statement(statement)Returns a reduced length string. The length is specified by the statement_truncate_len configuration option (default 64), and the removed part of the string (if any) is replaced with an ellipsis (three dots).
The function is intended for use in formatting lengthy SQL statements to a fixed length.
Default truncation length 64:
Reducing the truncation length to 48:
This page is licensed: CC BY-SA / Gnu FDL
This function checks if a given Performance Schema instrument is enabled by default, returning YES or NO.
sys.ps_is_instrument_default_enabled(instrument)ps_is_instrument_default_enabled is a available with the .
It returns YES if the given Performance Schema instrument is enabled by default, and NO if it is not, does not exist, or is a NULL value.
This page is licensed: CC BY-SA / Gnu FDL
The ps_is_thread_instrumented function checks whether a specific thread ID is currently instrumented by the Performance Schema.
sys.ps_is_thread_instrumented(connection_id)ps_is_thread_instrumented is a available with the that returns whether or not Performance Schema instrumentation for the given connection_id is enabled.
YES - instrumentation is enabled
NO - instrumentation is not enabled
UNKNOWN - the connection ID is unknown
This page is licensed: CC BY-SA / Gnu FDL
The sys_get_config function retrieves the value of a specified configuration option from the sys_config table, or a user variable if set.
sys.sys_get_config(name,default)sys_get_config is a available with the .
The function returns a configuration option value from the . It takes two arguments; name, a configuration option name, and default, which is returned if the given option does not exist in the table.
Both arguments are VARCHAR(128) and can be NULL. Returns NULL if name is NULL, or if the given option is not found and default is NULL.
This page is licensed: CC BY-SA / Gnu FDL
SELECT sys.extract_table_from_file_name('/usr/local/mysql/data/db/t1.ibd');
+---------------------------------------------------------------------+
| sys.extract_table_from_file_name('/usr/local/mysql/data/db/t1.ibd') |
+---------------------------------------------------------------------+
| t1 |
+---------------------------------------------------------------------+SELECT sys.format_bytes(1000),sys.format_bytes(1024);
+------------------------+------------------------+
| sys.format_bytes(1000) | sys.format_bytes(1024) |
+------------------------+------------------------+
| 1000 bytes | 1.00 KiB |
+------------------------+------------------------+
SELECT sys.format_bytes(1000000),sys.format_bytes(1048576);
+---------------------------+---------------------------+
| sys.format_bytes(1000000) | sys.format_bytes(1048576) |
+---------------------------+---------------------------+
| 976.56 KiB | 1.00 MiB |
+---------------------------+---------------------------+
SELECT sys.format_bytes(1000000000),sys.format_bytes(1073741874);
+------------------------------+------------------------------+
| sys.format_bytes(1000000000) | sys.format_bytes(1073741874) |
+------------------------------+------------------------------+
| 953.67 MiB | 1.00 GiB |
+------------------------------+------------------------------+
SELECT sys.format_bytes(1000000000000),sys.format_bytes(1099511627776);
+---------------------------------+---------------------------------+
| sys.format_bytes(1000000000000) | sys.format_bytes(1099511627776) |
+---------------------------------+---------------------------------+
| 931.32 GiB | 1.00 TiB |
+---------------------------------+---------------------------------+
SELECT sys.format_bytes(1000000000000000),sys.format_bytes(1125899906842624);
+------------------------------------+------------------------------------+
| sys.format_bytes(1000000000000000) | sys.format_bytes(1125899906842624) |
+------------------------------------+------------------------------------+
| 909.49 TiB | 1.00 PiB |
+------------------------------------+------------------------------------+SELECT sys.ps_is_account_enabled('localhost', 'root');
+------------------------------------------------+
| sys.ps_is_account_enabled('localhost', 'root') |
+------------------------------------------------+
| YES |
+------------------------------------------------+SELECT sys.format_statement(
'SELECT field1, field2, field3, field4, field5, field6 FROM table1'
) AS formatted_statement;
+-------------------------------------------------------------------+
| formatted_statement |
+-------------------------------------------------------------------+
| SELECT field1, field2, field3, ... d4, field5, field6 FROM table1 |
+-------------------------------------------------------------------+NULL - NULL valueThe boolean verbose argument specifies whether or not to include file:lineno information in the events.
This page is licensed: CC BY-SA / Gnu FDL
sys.ps_thread_stack(thread_id, verbose)It takes a list to be modified and a value to be dropped from the list, returning the resulting value. This can be used, for example, to remove a value from a system variable taking a comma-delimited list of options, such as sql_mode.
The related function list_add can be used to add a value to a list.
This page is licensed: CC BY-SA / Gnu FDL
NULL, returns the thread_id for the current connection.This page is licensed: CC BY-SA / Gnu FDL
sys.ps_thread_id(connection_id)SELECT sys.ps_thread_account(sys.ps_thread_id(CONNECTION_ID()));
+----------------------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(CONNECTION_ID())) |
+----------------------------------------------------------+
| msandbox@localhost |
+----------------------------------------------------------+
SELECT sys.ps_thread_account(sys.ps_thread_id(2042));
+-----------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(2042)) |
+-----------------------------------------------+
| NULL |
+-----------------------------------------------+
SELECT sys.ps_thread_account(sys.ps_thread_id(NULL));
+-----------------------------------------------+
| sys.ps_thread_account(sys.ps_thread_id(NULL)) |
+-----------------------------------------------+
| msandbox@localhost |
+-----------------------------------------------+SELECT sys.ps_is_instrument_default_timed('statement/sql/select');
+------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('statement/sql/select') |
+------------------------------------------------------------+
| YES |
+------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed('memory/sql/udf_mem');
+----------------------------------------------------------+
| sys.ps_is_instrument_default_timed('memory/sql/udf_mem') |
+----------------------------------------------------------+
| NO |
+----------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed('memory/sql/nonexistent');
+-------------------------------------------------------------+
| sys.ps_is_instrument_default_timed('memory/sql/udf_memsds') |
+-------------------------------------------------------------+
| NO |
+-------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_timed(NULL);
+------------------------------------------+
| sys.ps_is_instrument_default_timed(NULL) |
+------------------------------------------+
| NO |
+------------------------------------------+SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
SET @@sql_mode = sys.list_add(@@sql_mode, 'NO_ZERO_DATE');
SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+SET @sys.statement_truncate_len = 48;
SELECT sys.format_statement(
'SELECT field1, field2, field3, field4, field5, field6 FROM table1'
) AS formatted_statement;
+---------------------------------------------------+
| formatted_statement |
+---------------------------------------------------+
| SELECT field1, field2, ... d5, field6 FROM table1 |
+---------------------------------------------------+SELECT sys.ps_is_instrument_default_enabled('statement/sql/select');
+--------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('statement/sql/select') |
+--------------------------------------------------------------+
| YES |
+--------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled('memory/sql/udf_mem');
+------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('memory/sql/udf_mem') |
+------------------------------------------------------------+
| NO |
+------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled('memory/sql/nonexistent');
+----------------------------------------------------------------+
| sys.ps_is_instrument_default_enabled('memory/sql/nonexistent') |
+----------------------------------------------------------------+
| NO |
+----------------------------------------------------------------+
SELECT sys.ps_is_instrument_default_enabled(NULL);
+--------------------------------------------+
| sys.ps_is_instrument_default_enabled(NULL) |
+--------------------------------------------+
| NO |
+--------------------------------------------+SELECT sys.ps_is_thread_instrumented(CONNECTION_ID());
+------------------------------------------------+
| sys.ps_is_thread_instrumented(CONNECTION_ID()) |
+------------------------------------------------+
| YES |
+------------------------------------------------+
SELECT sys.ps_is_thread_instrumented(2042);
+-------------------------------------+
| sys.ps_is_thread_instrumented(2042) |
+-------------------------------------+
| UNKNOWN |
+-------------------------------------+
SELECT sys.ps_is_thread_instrumented(NULL);
+-------------------------------------+
| sys.ps_is_thread_instrumented(NULL) |
+-------------------------------------+
| NULL |
+-------------------------------------+SELECT sys.sys_get_config('ps_thread_trx_info.max_length',NULL);
+----------------------------------------------------------+
| sys.sys_get_config('ps_thread_trx_info.max_length',NULL) |
+----------------------------------------------------------+
| 65535 |
+----------------------------------------------------------+SELECT sys.ps_thread_stack(13, FALSE) AS thread_stack\G
*************************** 1. row ***************************
thread_stack: {"rankdir": "LR","nodesep": "0.10",
"stack_created": "2022-03-28 16:01:06",
"mysql_version": "10.8.2-MariaDB",
"mysql_user": "msandbox@localhost",
"events": []}sys.list_drop(list,value)SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------+
SET @@sql_mode = sys.list_drop(@@sql_mode, 'NO_ENGINE_SUBSTITUTION');
SELECT @@sql_mode;
+-----------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,
NO_AUTO_CREATE_USER |
+-----------------------------------------------------------------------+SELECT * FROM performance_schema.threads\G
*************************** 13. row ***************************
THREAD_ID: 13
NAME: thread/sql/one_connection
TYPE: FOREGROUND
PROCESSLIST_ID: 3
PROCESSLIST_USER: msandbox
PROCESSLIST_HOST: localhost
PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
PROCESSLIST_TIME: 0
PROCESSLIST_STATE: Sending data
PROCESSLIST_INFO: SELECT * FROM performance_schema.threads
PARENT_THREAD_ID: 1
ROLE: NULL
INSTRUMENTED: YES
HISTORY: YES
CONNECTION_TYPE: Socket
THREAD_OS_ID: 24379
SELECT sys.ps_thread_id(3);
+---------------------+
| sys.ps_thread_id(3) |
+---------------------+
| 13 |
+---------------------+
SELECT sys.ps_thread_id(NULL);
+------------------------+
| sys.ps_thread_id(NULL) |
+------------------------+
| 13 |
+------------------------+The format_time function converts a time value in picoseconds into a human-readable string with appropriate units (e.g., ms, s, min).
format_time is a available with the . Given a time in picoseconds, returns a human-readable time value and unit indicator. Unit can be:
ps - picoseconds
ns - nanoseconds
us - microseconds
ms - milliseconds
This function is very similar to the function introduced in , but with the following differences:
Represents minutes as m rather than min.
Represent weeks.
This page is licensed: CC BY-SA / Gnu FDL
This function returns YES or NO indicating whether a specific consumer is currently enabled in the Performance Schema.
sys.ps_is_consumer_enabled(consumer)ps_is_consumer_enabled is a available with the .
It returns an ENUM('YES','NO') depending on whether Performance Schema instrumentation for the given consumer is enabled, and NULL if not given a valid consumer name.
This page is licensed: CC BY-SA / Gnu FDL
This function quotes a given string with backticks to ensure it can be safely used as an identifier in SQL statements.
sys.quote_identifier(str)quote_identifier is a available with the .
It quotes a string to produce a result that can be used as an identifier in an SQL statement. The string is returned enclosed by backticks ("") and with each instance of backtick ("") doubled. If the argument is NULL, the return value is the word "NULL" without enclosing backticks.
This page is licensed: CC BY-SA / Gnu FDL
sys.format_time(picoseconds)SELECT sys.ps_is_consumer_enabled('global_instrumentation');
+------------------------------------------------------+
| sys.ps_is_consumer_enabled('global_instrumentation') |
+------------------------------------------------------+
| YES |
+------------------------------------------------------+
SELECT sys.ps_is_consumer_enabled('events_stages_current');
+-----------------------------------------------------+
| sys.ps_is_consumer_enabled('events_stages_current') |
+-----------------------------------------------------+
| NO |
+-----------------------------------------------------+
SELECT sys.ps_is_consumer_enabled('nonexistent_consumer');
+----------------------------------------------------+
| sys.ps_is_consumer_enabled('nonexistent_consumer') |
+----------------------------------------------------+
| NULL |
+----------------------------------------------------+SELECT sys.quote_identifier("Identifier with spaces");
+------------------------------------------------+
| sys.quote_identifier("Identifier with spaces") |
+------------------------------------------------+
| `Identifier with spaces` |
+------------------------------------------------+
SELECT sys.quote_identifier("Identifier` containing `backticks");
+-----------------------------------------------------------+
| sys.quote_identifier("Identifier` containing `backticks") |
+-----------------------------------------------------------+
| `Identifier`` containing ``backticks` |
+-----------------------------------------------------------+m - minutes
h - hours
d - days
w - weeks
SELECT
sys.format_time(43) AS ps,
sys.format_time(4321) AS ns,
sys.format_time(43211234) AS us,
sys.format_time(432112344321) AS ms,
sys.format_time(43211234432123) AS s,
sys.format_time(432112344321234) AS m,
sys.format_time(4321123443212345) AS h,
sys.format_time(432112344321234545) AS d,
sys.format_time(43211234432123444543) AS w;
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+
| ps | ns | us | ms | s | m | h | d | w |
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+
| 43 ps | 4.32 ns | 43.21 us | 432.11 ms | 43.21 s | 7.20 m | 1.20 h | 5.00 d | 71.45 w |
+-------+---------+----------+-----------+---------+--------+--------+--------+---------+This function returns the minor version number of the MariaDB Server instance, such as the 6 in 10.6.4.
sys.version_minor()version_minor is a stored function available with the .
It returns the MariaDB Server minor release version.
This page is licensed: CC BY-SA / Gnu FDL
SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+The system variables that are matched are, in order:
This page is licensed: CC BY-SA / Gnu FDL
sys.format_path(path)SELECT @@tmpdir;
+------------------------------------+
| @@tmpdir |
+------------------------------------+
| /home/ian/sandboxes/msb_10_8_2/tmp |
+------------------------------------+
SELECT sys.format_path('/home/ian/sandboxes/msb_10_8_2/tmp/testdb.ibd');
+------------------------------------------------------------------+
| sys.format_path('/home/ian/sandboxes/msb_10_8_2/tmp/testdb.ibd') |
+------------------------------------------------------------------+
| @@tmpdir/testdb.ibd |
+------------------------------------------------------------------+This function returns the patch release version number of the MariaDB Server instance, such as the 4 in 10.6.4.
sys.version_patch()version_patch is a stored function available with the .
It returns the MariaDB Server patch release version.
This page is licensed: CC BY-SA / Gnu FDL
SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+This function returns the major version number of the MariaDB Server instance (e.g., 10 from 10.6.4).
sys.version_major()version_major is a stored function available with the .
It returns the MariaDB Server major release version.
This page is licensed: CC BY-SA / Gnu FDL
SELECT VERSION(),
sys.version_major() AS major,
sys.version_minor() AS minor,
sys.version_patch() AS patch;
+----------------+-------+-------+-------+
| VERSION() | major | minor | patch |
+----------------+-------+-------+-------+
| 10.8.2-MariaDB | 10 | 8 | 2 |
+----------------+-------+-------+-------+