MariaDB wait_timeout

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 1, the maximum value for other as 31536000, and maximum value for Windows as 2147483 and it has the command line format written as –wait_timeout=#.

Syntax:

Let us discuss the usage with syntax of 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 keyword SET GLOBAL written as below:

SET GLOBAL wait_timeout =130;

The session value of this MariaDB system variable wait_timeout also can be dynamically set at runtime by running the query with keyword SET SESSION written as below:

SET SESSION wait_timeout =130;

But if the value of system variable wait_timeout is set at runtime dynamically then the value may be reset the next time whenever we restart the server. We can make the value of the function wait_timeout to persist on the server restart, by setting it in a configuration file excessively.

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 global interactive_timeout function or from the value of global wait_timeout function, the value of session wait_timeout is prepared on the basis of 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 which is a system variable is responsible to set the time in seconds which the server is waiting for an indolent collaborating connection for becoming active before terminating it.

The MariaDB system variable wait_timeout function needs the SUPER privilege on the server to set the value of its global scope dynamically at runtime.

Examples

Let us view the effect of the Interactive_timeout and MariaDB wait_timeout function working in the server:

The MariaDB system variable interactive_timeout function purposes by overriding the value of the MariaDB system variable wait_timeout function for building up 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 to the server along with an interactive client, at the time the user will view that their system variable wait_timeout’s session value is essentially set to the interactive_timeout’s global value.

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 30

Canceled Connections

If any connection has been idle for a lengthier time than the configured system variable wait_timeout value, then the server may eradicate the connection. But if the system variable log_warnings is set or configured to 2 or more, thereafter a warning along with the error code as ER_ABORTING_CONNECTION may be displayed to the respective error log or its file as shown below:

We can also reset the Global value of 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 reset to the value of session to the value of Global for 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 the MariaDB system variable wait_timeout function value is provided to an invalid one by any user then, the particular behavior be influenced by the system variable sql_mode value. 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 operation query will succeed and at the same time a warning will also be delivered with the error code ER_TRUNCATED_WRONG_VALUE.

Thus, this warning defines that the value was abbreviated which states that the value set was spontaneous to the contiguous inacceptable value as 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 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 which are useful for controlling and managing the serve operations. The wait_timeout function in MariaDB refers to the number of seconds that the server is waiting for any task or operation on an established connection before it gets closed. Hence, the function works dynamically and supports the configuration file with data type BIGINT UNSIGNED.

Recommended Articles

This is a guide to MariaDB wait_timeout. Here we discuss the Definition, How wait_timeout function works in MariaDB? and examples with code implementation. You may also have a look at the following articles to learn more –

1. MariaDB MaxScale
2. MariaDB UPDATE
3. MariaDB Foreign Key
4. MariaDB Commands