Updated June 3, 2023
Definition of MariaDB wait_timeout Function
MariaDB wait_timeout function is defined as a task of the server where it waits for a number of seconds for any action on a no collaborating connection beforehand closing it. This wait_timeout function works as a system variable with global as well as session scopes. MariaDB wait_timeout function is a dynamic variable in the server which is of integer type with default value as 28800 and without SET_VAR Hint Applies. Also, the wait_timeout function has a minimum value of 1, the maximum value for others as 31536000, and the maximum value for Windows as 2147483, and it has the command line format written as –wait_timeout=#.
Syntax:
Let us discuss the usage with the syntax of the MariaDB wait_timeout function as follows:
This wait_timeout function represents a system variable that can be set within a configuration file as:
[mariadb]
Wait_timeout=130;The global value of this MariaDB system variable wait_timeout also can be dynamically set at runtime by running the query with the keyword SET GLOBAL written as below:
SET GLOBAL wait_timeout =130;You can dynamically set the session value of the MariaDB system variable wait_timeout at runtime by executing a query with the keyword SET SESSION, as shown below:
SET SESSION wait_timeout =130;But if you dynamically set the value of the system variable wait_timeout at runtime, then the value may be reset the next time the server is restarted. We can make the value of the function wait_timeout persist on the server restart by excessively setting it in a configuration file.
How wait_timeout function works in MariaDB?
As MariaDB wait_timeout function has global scope and session also so, on the thread startup, either from the value of the global interactive_timeout function or from the value of the global wait_timeout function, the value of session wait_timeout is prepared based on the kind of client which may be stated by the CLIENT_INTERACTIVE associate option to mysql_real_connect().
In detail, the MariaDB wait_timeout function, a system variable, is responsible for setting the time in seconds when the server is waiting for an indolent collaborating connection to become active before terminating it.
The MariaDB system variable wait_timeout function needs the SUPER privilege on the server to dynamically set the value of its global scope at runtime.
Examples
Let us view the effect of the Interactive_timeout and MariaDB wait_timeout function working on the server:
The MariaDB system variable interactive_timeout function purposes by overriding the value of the variable wait_timeout function for building interactive connections. Suppose we can illustrate this by using the interactive_timeout function and wait_timeout function settings to diverse values as shown below:
SET GLOBAL interactive_timeout=90;
SET GLOBAL wait_timeout =60;Output:
Within this structure, when a user associates the server with an interactive client, they will observe that their session sets the value of the system variable wait_timeout to essentially match the global value of interactive_timeout.
By default, the MySQL command-line client works as an interactive client; therefore, this client may be implemented to determine the behavior of this interactive clients as follows:
$ sudo mysql
SHOW SESSION VARIABLES LIKE 'wait_timeout';Output:
Nevertheless, the MySQL server command line client works as a non-interactive client when the option –batch is delivered, thus this client now will be applied with this possibility to determine the behavior of non-interactive clients:
$sudo mysql -- batch --execute ="SHOW SESSION VARIABLES LIKE 'wait_timeout'"
Variable_name Value
Wait_timeout 30Canceled Connections
If any connection has been idle for a longer time than the configured system variable wait_timeout value, the server may eradicate the connection. If you set or configure the system variable log_warnings to 2 or more, it may display a warning message, accompanied by the error code ER_ABORTING_CONNECTION, to the respective error log or its file.
We can also reset the Global value of the wait_timeout function dynamically to default by just adding the keyword DEFAULT as below:
SET GLOBAL wait_timeout=150;
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';Output:
SET GLOBAL wait_timeout=DEFAULT;
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';Output:
We can also perform a reset to the value of session to the value of Global for the wait_timeout function similarly by setting to DEFAULT as:
SET SESSION wait_timeout=150;
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';Output:
SHOW SESSION VARIABLES LIKE 'wait_timeout';Output:
SET SESSION wait_timeout=DEFAULT;
SHOW SESSION VARIABLES LIKE 'wait_timeout';Output:
Invalid system variable value:
When a user provides an invalid value for the wait_timeout function in the MariaDB system variable, the value of the system variable sql_mode actively influences the behavior of the function. But if this sql_mode variable in MariaDB does not include STRICT_ALL_TABLES and also if a user effort to place an invalid value to the wait_timeout function, then this execution of the operation query will succeed. At the same time, a warning will also be delivered with the error code ER_TRUNCATED_WRONG_VALUE.
Therefore, this warning indicates that the value abbreviation signifies that the system automatically adjusted the set value to the nearest acceptable value, as shown below:
SET GLOBAL wait_timeout=-1;
SHOW WARNINGS;Output:
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';Output:
SET GLOBAL wait_timeout=305350100;
SHOW WARNINGS;Output:
SHOW GLOBAL VARIABLES LIKE 'wait_timeout';Output:
If this sql_mode variable in MariaDB includes STRICT_ALL_TABLES and also if a user effort to place an invalid value to the wait_timeout function, then this execution of the operation query will succeed, and at the same time, a warning will also be delivered with the error code ER_WRONG_VALUE_FOR_VAR as:
SHOW SESSION VARIABLES LIKE 'sql_mode';Output:
Invalid variable type:
Suppose any user attempts to make the wait_timeout function to an invalid argument value then this operation will be failed, generating an error code as ER_WRONG_TYPE_FOR_VAR as:
SET GLOBAL wait_timeout=' ';Output:
SET GLOBAL wait_timeout='infinity';Output:
Conclusion
MySQL and MariaDB databases include various timeout variable functions useful for controlling and managing the serve operations. The wait_timeout function in MariaDB actively refers to the duration that the server waits for any task or operation on an established connection before actively closing it. Hence, the function works dynamically and supports the configuration file with data type BIGINT UNSIGNED.
Recommended Articles
We hope that this EDUCBA information on “MariaDB wait_timeout” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
