Rechercher dans le manuel MySQL

27.4.2.1 The sys_config Table

Inhoudsopgave

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;

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-sys-sys-config.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut