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. 

