Rechercher dans le manuel MySQL

27.4.2.1 The sys_config Table

Inhaltsverzeichnis

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;

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-sys-sys-config.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

  1. Zeigen Sie - html-Dokument Sprache des Dokuments:en Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.

Inhaltsverzeichnis Haut