C C++ CMake CSS Cypress date-fns Django Django REST Framework ESLint Git GNU Make HTML htmx HTTP JavaScript MariaDB NumPy pandas PHP PostgreSQL Python PyTorch Qt SQLite Tailwind CSS

Exploring SQL Statements and Structure for Sys Schema sys_config in MariaDB


Introduction

The sys_config table, introduced in MariaDB 10.6.0 (and backported to MariaDB 10.5 Enterprise), resides within the sys schema. It serves as a mechanism to manage configuration options specifically for the Sys Schema itself.

Structure

The table is comprised of the following columns:

  • set_by (VARCHAR(128) DEFAULT NULL): This column optionally stores the user or process that performed the setting or updating of the configuration variable.
  • set_time (TIMESTAMP NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()): This column automatically captures the timestamp of when the configuration variable was last set or updated.
  • value (VARCHAR(128) DEFAULT NULL): This column stores the actual value assigned to the corresponding configuration variable.
  • variable (VARCHAR(128) NOT NULL): This column acts as the primary key and uniquely identifies each configuration variable.

Functionality

The sys_config table allows you to:

  • Access and retrieve the current values of these settings using SQL statements.
  • Centrally store and modify these settings, potentially simplifying management compared to scattered configuration files.
  • Define and manage configuration settings for the Sys Schema.

Relationship with User-Defined Variables

It's important to note that MariaDB also supports user-defined variables within a session. These variables take precedence over values stored in the sys_config table if they exist and have a non-NULL value. This provides a way to temporarily override configuration settings for specific sessions.

Triggers

The sys_config table is associated with two triggers:

  • sys_config_update_set_user: This trigger automatically updates the set_by column to the user who modifies an existing configuration variable.
  • sys_config_insert_set_user: This trigger automatically sets the set_by column to the user who inserts a new configuration variable.

These triggers help in maintaining an audit trail of configuration changes.

Example Usage

-- Check the current value of a hypothetical configuration variable named 'enable_logging'
SELECT * FROM sys.sys_config WHERE variable = 'enable_logging';

-- Update the value to 'ON' if necessary
UPDATE sys.sys_config SET value = 'ON' WHERE variable = 'enable_logging';
  • Exercise caution when changing configuration values, as it can potentially affect the behavior of the Sys Schema or MariaDB itself.
  • Modifying the sys_config table directly requires appropriate privileges.

Checking Current Configuration

-- Check the current value of the 'diagnostics.allow_i_s_tables' setting
SELECT * FROM sys.sys_config WHERE variable = 'diagnostics.allow_i_s_tables';

This query retrieves all columns (variable, value, set_time, set_by) for the configuration variable named diagnostics.allow_i_s_tables. This variable typically controls whether information about internal storage engine tables is exposed.

Updating a Configuration

-- Update the 'ps_thread_trx_info.max_length' setting to 2048 bytes
UPDATE sys.sys_config SET value = '2048' WHERE variable = 'ps_thread_trx_info.max_length';

This query updates the value column for the ps_thread_trx_info.max_length variable to 2048. This variable might control the maximum length of information displayed about ongoing transactions.

Inserting a New Configuration (if not already present)

-- Insert a new configuration named 'custom_view_limit' with a value of 500
INSERT IGNORE INTO sys.sys_config (variable, value) VALUES ('custom_view_limit', 500);

The INSERT IGNORE statement attempts to insert a new row. If a row with the same variable already exists, it's ignored to prevent duplicate entries. This example sets a hypothetical custom_view_limit for some Sys Schema view.

  • Refer to the MariaDB documentation for a complete list of available configuration options within the sys_config table.
  • Carefully consider the potential impact on your MariaDB setup before changing configuration values.
  • Modifying these settings directly requires appropriate privileges (usually SUPER or PROCESS).

System Variables

  • However, system variables are often global, affecting all connections to the server. This might not be ideal if you need configuration specific to the Sys Schema or individual sessions.
  • You can view and modify system variables using SHOW VARIABLES and SET statements.
  • MariaDB offers system variables that control various aspects of server behavior. While not specifically dedicated to the Sys Schema, some system variables might overlap in functionality with sys_config options.

User-Defined Variables

  • However, user-defined variables lack persistence and require setting them in each session where needed.
  • They can temporarily override settings from sys_config or system variables, providing flexibility for specific operations.
  • MariaDB allows defining variables within a session using the SET statement. These variables are temporary and exist for the duration of the session only.

Custom Configuration Files (External)

  • Be mindful of potential security implications if storing sensitive configuration data in external files.
  • This approach requires writing code that interacts with these files to manage settings.
  • If the available configuration options are insufficient, you could explore creating external configuration files for the Sys Schema functionality you want to customize.

Feature Flags (if applicable)

  • This method depends on your specific MariaDB setup and might not be universally available.
  • Some advanced MariaDB features might expose configuration options as feature flags. These could be managed through a separate control plane or configuration tool provided by your MariaDB distribution.

Choosing the Right Approach

The best alternative depends on your specific requirements:

  • For complex or persistent configuration outside sys_config, explore external configuration files or feature flags (if available).
  • If existing system variables suit your needs, leverage those for broader server-level control.
  • For temporary overrides within sessions, consider user-defined variables.