30.4.2.1 The sys_config Table

This table contains sys schema configuration options, one row per option. Configuration changes made by updating this table persist across client sessions and server restarts.

The sys_config table has these columns:

• variable

  The configuration option name.

• value

  The configuration option value.

• set_time

  The timestamp of the most recent modification to the row.

• set_by

  The account that made the most recent modification to the row. The value is NULL if the row has not been changed since the sys schema was installed.

As an efficiency measure to minimize the number of direct reads from the sys_config table, sys schema functions that use a value from this table check for a user-defined variable with a corresponding name, which is the user-defined variable having the same name plus a @sys. prefix. (For example, the variable corresponding to the diagnostics.include_raw option is @sys.diagnostics.include_raw.) If the user-defined variable exists in the current session and is non-NULL, the function uses its value in preference to the value in the sys_config table. Otherwise, the function reads and uses the value from the table. In the latter case, the calling function conventionally also sets the corresponding user-defined variable to the table value so that further references to the configuration option within the same session use the variable and need not read the table again.

For example, the statement_truncate_len option controls the maximum length of statements returned by the format_statement() function. The default is 64. To temporarily change the value to 32 for your current session, set the corresponding @sys.statement_truncate_len user-defined variable:

mysql> SET @stmt = 'SELECT variable, value, set_time, set_by FROM sys_config';
mysql> SELECT sys.format_statement(@stmt);
+----------------------------------------------------------+
| sys.format_statement(@stmt)                              |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+
mysql> SET @sys.statement_truncate_len = 32;
mysql> SELECT sys.format_statement(@stmt);
+-----------------------------------+
| sys.format_statement(@stmt)       |
+-----------------------------------+
| SELECT variabl ... ROM sys_config |
+-----------------------------------+

Subsequent invocations of format_statement() within the session continue to use the user-defined variable value (32), rather than the value stored in the table (64).

To stop using the user-defined variable and revert to using the value in the table, set the variable to NULL within your session:

mysql> SET @sys.statement_truncate_len = NULL;
mysql> SELECT sys.format_statement(@stmt);
+----------------------------------------------------------+
| sys.format_statement(@stmt)                              |
+----------------------------------------------------------+
| SELECT variable, value, set_time, set_by FROM sys_config |
+----------------------------------------------------------+

Alternatively, end your current session (causing the user-defined variable to no longer exist) and begin a new session.

The conventional relationship just described between options in the sys_config table and user-defined variables can be exploited to make temporary configuration changes that end when your session ends. However, if you set a user-defined variable and then subsequently change the corresponding table value within the same session, the changed table value is not used in that session as long as the user-defined variable exists with a non-NULL value. (The changed table value is used in other sessions in which the user-defined variable is not assigned.)

The following list describes the options in the sys_config table and the corresponding user-defined variables:

• diagnostics.allow_i_s_tables, @sys.diagnostics.allow_i_s_tables

  If this option is ON, the diagnostics() procedure is permitted to perform table scans on the Information Schema TABLES table. This can be expensive if there are many tables. The default is OFF.

• diagnostics.include_raw, @sys.diagnostics.include_raw

  If this option is ON, the diagnostics() procedure includes the raw output from querying the metrics view. The default is OFF.

• ps_thread_trx_info.max_length, @sys.ps_thread_trx_info.max_length

  The maximum length for JSON output produced by the ps_thread_trx_info() function. The default is 65535.

• statement_performance_analyzer.limit, @sys.statement_performance_analyzer.limit

  The maximum number of rows to return for views that have no built-in limit. (For example, the statements_with_runtimes_in_95th_percentile view has a built-in limit in the sense that it returns only statements with average execution time in the 95th percentile.) The default is 100.

• statement_performance_analyzer.view, @sys.statement_performance_analyzer.view

  The custom query or view to be used by the statement_performance_analyzer() procedure (which is itself invoked by the diagnostics() procedure). If the option value contains a space, it is interpreted as a query. Otherwise, it must be the name of an existing view that queries the Performance Schema events_statements_summary_by_digest table. There cannot be any LIMIT clause in the query or view definition if the statement_performance_analyzer.limit configuration option is greater than 0. The default is NULL (no custom view defined).

• statement_truncate_len, @sys.statement_truncate_len

  The maximum length of statements returned by the format_statement() function. Longer statements are truncated to this length. The default is 64.

Other options can be added to the sys_config table. For example, the diagnostics() and execute_prepared_stmt() procedures use the debug option if it exists, but this option is not part of the sys_config table by default because debug output normally is enabled only temporarily, by setting the corresponding @sys.debug user-defined variable. To enable debug output without having to set that variable in individual sessions, add the option to the table:

mysql> INSERT INTO sys.sys_config (variable, value) VALUES('debug', 'ON');

To change the debug setting in the table, do two things. First, modify the value in the table itself:

mysql> UPDATE sys.sys_config
       SET value = 'OFF'
       WHERE variable = 'debug';

Second, to also ensure that procedure invocations within the current session use the changed value from the table, set the corresponding user-defined variable to NULL:

mysql> SET @sys.debug = NULL;