wait_timeout

USAGE

The wait_timeout system variable can have its default value configured via SkySQL Configuration Manager.

The wait_timeout system variable can be set in a configuration file:

[mariadb]
wait_timeout=120

The global value of the wait_timeout system variable can also be set dynamically at runtime by executing SET GLOBAL:

SET GLOBAL wait_timeout=120;

The session value of the wait_timeout system variable can also be set dynamically at runtime by executing SET SESSION:

SET SESSION wait_timeout=120;

When the wait_timeout system variable is set dynamically at runtime, its value will be reset the next time the server restarts. To make the value persist on restart, set it in a configuration file too.

DETAILS

The wait_timeout system variable sets the time in seconds that the server waits for an idle interactive connection to become active before closing it.

SYNONYMS

SCHEMA

PARAMETERS

Command-line

--wait_timeout=#

Configuration file

Supported

Dynamic

Yes

Scope

Global, Session

Data Type

BIGINT UNSIGNED

Minimum Value

1

Maximum Value

31536000

Product Default Value

28800

SKYSQL

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

Multi-Node Analytics

10.6

28800 (choices: 300, 600, 3600, 28800, 86400)

Single Node Analytics

10.6

28800 (choices: 300, 600, 3600, 28800, 86400)

Replicated Transactions

10.4

600 (choices: 300, 600, 3600, 28800, 86400)

10.5

600 (choices: 300, 600, 3600, 28800, 86400)

10.6

600 (choices: 300, 600, 3600, 28800, 86400)

Single Node Transactions

10.4

600 (choices: 300, 600, 3600, 28800, 86400)

10.5

600 (choices: 300, 600, 3600, 28800, 86400)

10.6

600 (choices: 300, 600, 3600, 28800, 86400)

GCP

Multi-Node Analytics

10.6

28800 (choices: 300, 600, 3600, 28800, 86400)

Single Node Analytics

10.6

28800 (choices: 300, 600, 3600, 28800, 86400)

Replicated Transactions

10.4

600 (choices: 300, 600, 3600, 28800, 86400)

10.5

600 (choices: 300, 600, 3600, 28800, 86400)

10.6

600 (choices: 300, 600, 3600, 28800, 86400)

Single Node Transactions

10.4

600 (choices: 300, 600, 3600, 28800, 86400)

10.5

600 (choices: 300, 600, 3600, 28800, 86400)

10.6

600 (choices: 300, 600, 3600, 28800, 86400)

Cloud

SkySQL Topology

ES Ver

Service Default

AWS

ColumnStore Data Warehouse

10.6

28800 (choices: 300, 600, 3600, 28800, 86400)

Enterprise Server With Replica(s)

10.4

600 (choices: 300, 600, 3600, 28800, 86400)

10.5

600 (choices: 300, 600, 3600, 28800, 86400)

10.6

600 (choices: 300, 600, 3600, 28800, 86400)

Enterprise Server Single Node

10.4

600 (choices: 300, 600, 3600, 28800, 86400)

10.5

600 (choices: 300, 600, 3600, 28800, 86400)

10.6

600 (choices: 300, 600, 3600, 28800, 86400)

GCP

ColumnStore Data Warehouse

10.6

28800 (choices: 300, 600, 3600, 28800, 86400)

Enterprise Server With Replica(s)

10.4

600 (choices: 300, 600, 3600, 28800, 86400)

10.5

600 (choices: 300, 600, 3600, 28800, 86400)

10.6

600 (choices: 300, 600, 3600, 28800, 86400)

Enterprise Server Single Node

10.4

600 (choices: 300, 600, 3600, 28800, 86400)

10.5

600 (choices: 300, 600, 3600, 28800, 86400)

10.6

600 (choices: 300, 600, 3600, 28800, 86400)

PRIVILEGES

The wait_timeout system variable requires the SUPER privilege to dynamically set its global value at runtime.

The wait_timeout system variable does not require any special privilege to dynamically set its session value at runtime.

EXAMPLES

Effect from Interactive Timeout

The interactive_timeout system variable works by overriding the value of the wait_timeout system variable for interactive connections. This can be demonstrated by setting interactive_timeout and wait_timeout to different values:

SET GLOBAL interactive_timeout=60;

SET GLOBAL wait_timeout=30;

With this configuration, if a user connects to the server with an interactive client, then they will see that their session's value of the wait_timeout system variable is actually set to the global value of interactive_timeout. The mysql command-line client functions as an interactive client by default, so this client can be used to demonstrate the behavior of interactive clients:

$ sudo mysql

SHOW SESSION VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 60    |
+---------------+-------+

However, the mysql command-line client functions as a non-interactive client if the --batch option is provided, so this client can be used with this option to demonstrate the behavior of non-interactive clients:

$ sudo mysql --batch --execute="SHOW SESSION VARIABLES LIKE 'wait_timeout'"
Variable_name   Value
wait_timeout    30

Aborted Connections

If a connection has been idle for longer than the configured value of the wait_timeout system variable, then the server will kill the connection. If the log_warnings system variable is set to 2 or more, then a warning with the ER_ABORTING_CONNECTION error code will be written to the error log:

Feb 20 02:16:58 ip-172-30-0-123 mysqld: 2020-02-20  2:16:58 16 [Warning] Aborted connection 16 to db: 'unconnected' user: 'root' host: 'localhost' (Got timeout reading communication packets)

Dynamically Resetting Global Value to Default

The global value of the wait_timeout system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL wait_timeout=180;

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 180   |
+---------------+-------+

SET GLOBAL wait_timeout=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

Resetting Session Value to Global Value

The session value of the wait_timeout system variable can be reset to the global value by setting it to DEFAULT:

SET SESSION wait_timeout=180;

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

SHOW SESSION VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 180   |
+---------------+-------+

SET SESSION wait_timeout=DEFAULT;

SHOW SESSION VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

Invalid Value for Variable

If a user tries to set the wait_timeout system variable to an invalid value, then the specific behavior depends on the value of the sql_mode system variable.

If the sql_mode system variable does not contain STRICT_ALL_TABLES and a user tries to set the wait_timeout system variable to an invalid value, then the operation will appear to succeed, but a warning will be raised with the ER_TRUNCATED_WRONG_VALUE error code. The warning will point out that the value was truncated, which means that the value was automatically set to the closest invalid value:

SET GLOBAL wait_timeout=-1;

SHOW WARNINGS;

+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect wait_timeout value: '-1' |
+---------+------+----------------------------------------------+

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 1     |
+---------------+-------+

SET GLOBAL wait_timeout=31536001;

SHOW WARNINGS;

+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1292 | Truncated incorrect wait_timeout value: '31536001' |
+---------+------+----------------------------------------------------+

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| wait_timeout  | 31536000 |
+---------------+----------+

If the sql_mode system variable does contain STRICT_ALL_TABLES and a user tries to set the wait_timeout system variable to an invalid value, then the operation will fail with the ER_WRONG_VALUE_FOR_VAR error code:

SHOW SESSION VARIABLES LIKE 'sql_mode';

+---------------+-------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                     |
+---------------+-------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------+

SET SESSION sql_mode=CONCAT(@@session.sql_mode, ',STRICT_ALL_TABLES');

SET GLOBAL wait_timeout=-1;

ERROR 1231 (42000): Variable 'wait_timeout' can't be set to the value of '-1'

Invalid Type for Variable

If a user tries to set the wait_timeout system variable to an invalid argument, then the operation will fail with the ER_WRONG_TYPE_FOR_VAR error code:

SET GLOBAL wait_timeout='';

ERROR 1232 (42000): Incorrect argument type to variable 'wait_timeout'

SET GLOBAL wait_timeout='infinity';

ERROR 1232 (42000): Incorrect argument type to variable 'wait_timeout'

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

11.4 Enterprise

  • Not present.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES