Rechercher dans le manuel MySQL

27.4.2.1 The sys_config Table

Contents

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:

  1. mysql> SET @stmt = 'SELECT variable, value, set_time, set_by FROM sys_config';
  2. mysql> SELECT sys.format_statement(@stmt);
  3. +----------------------------------------------------------+
  4. | sys.format_statement(@stmt)                              |
  5. +----------------------------------------------------------+
  6. | SELECT variable, value, set_time, set_by FROM sys_config |
  7. +----------------------------------------------------------+
  8. mysql> SET @sys.statement_truncate_len = 32;
  9. mysql> SELECT sys.format_statement(@stmt);
  10. +-----------------------------------+
  11. | sys.format_statement(@stmt)       |
  12. +-----------------------------------+
  13. | SELECT variabl ... ROM sys_config |
  14. +-----------------------------------+

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:

  1. mysql> SET @sys.statement_truncate_len = NULL;
  2. mysql> SELECT sys.format_statement(@stmt);
  3. +----------------------------------------------------------+
  4. | sys.format_statement(@stmt)                              |
  5. +----------------------------------------------------------+
  6. | SELECT variable, value, set_time, set_by FROM sys_config |
  7. +----------------------------------------------------------+

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 will not be used in that session as long as the user-defined variable exists with a non-NULL value. (The changed table value will be used in other sessions that do not have the user-defined variable 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:

  1. 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:

  1. mysql> UPDATE sys.sys_config
  2.        SET value = 'OFF'
  3.        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:

  1. mysql> SET @sys.debug = NULL;

Find a PHP function

Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-sys-sys-config.html

The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.

References

  1. View the html document Language of the document:en Manuel MySQL : https://dev.mysql.com/

These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.

Contents Haut