interactive_timeout

Overview

The number of seconds the server waits for activity on an interactive connection before closing it

USAGE

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

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

[mariadb]
interactive_timeout=120

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

SET GLOBAL interactive_timeout=120;

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

SET SESSION interactive_timeout=120;

When the interactive_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 interactive_timeout system variable sets the time in seconds that the server waits for an idle interactive connection to become active before closing it.

  • An interactive client is one that sets the CLIENT_INTERACTIVE option when it connects to the server with mysql_real_connect().

  • Some clients may be considered interactive clients when executed with some options, and non-interactive clients when executed with other options.

PARAMETERS

Command-line

--interactive_timeout=#

Configuration file

Supported

Dynamic

Yes

Scope

Global, Session

Data Type

BIGINT UNSIGNED

Minimum Value

1

Maximum Value

31536000

Default Value

28800

SKYSQL

Cloud

SkySQL Topology

ES Ver

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)

Distributed Transactions

10.5

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

10.6

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

Replicated Transactions

10.4

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

10.5

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

10.6

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

Single Node Transactions

10.4

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

10.5

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

10.6

3600 (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)

Distributed Transactions

10.5

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

10.6

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

Replicated Transactions

10.4

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

10.5

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

10.6

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

Single Node Transactions

10.4

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

10.5

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

10.6

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

Cloud

SkySQL Topology

ES Ver

Default

AWS

ColumnStore Data Warehouse

10.6

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

Xpand Distributed SQL

10.5

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

10.6

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

Enterprise Server With Replica(s)

10.4

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

10.5

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

10.6

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

Enterprise Server Single Node

10.4

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

10.5

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

10.6

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

GCP

ColumnStore Data Warehouse

10.6

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

Xpand Distributed SQL

10.5

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

10.6

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

Enterprise Server With Replica(s)

10.4

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

10.5

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

10.6

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

Enterprise Server Single Node

10.4

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

10.5

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

10.6

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

PRIVILEGES

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

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

EXAMPLES

Effect on Wait 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 an interactive connection has been idle for longer than the configured value of the interactive_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 interactive_timeout system variable can be dynamically reset to the default value by setting it to DEFAULT:

SET GLOBAL interactive_timeout=180;

SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 180   |
+---------------------+-------+
SET GLOBAL interactive_timeout=DEFAULT;

SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+

Resetting Session Value to Global Value

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

SET SESSION interactive_timeout=180;

SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
SHOW SESSION VARIABLES LIKE 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 180   |
+---------------------+-------+
SET SESSION interactive_timeout=DEFAULT;

SHOW SESSION VARIABLES LIKE 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+

Invalid Value for Variable

If a user tries to set the interactive_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 interactive_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 interactive_timeout=-1;

SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1292 | Truncated incorrect interactive_timeout value: '-1' |
+---------+------+-----------------------------------------------------+
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 1     |
+---------------------+-------+
SET GLOBAL interactive_timeout=31536001;

SHOW WARNINGS;
+---------+------+-----------------------------------------------------------+
| Level   | Code | Message                                                   |
+---------+------+-----------------------------------------------------------+
| Warning | 1292 | Truncated incorrect interactive_timeout value: '31536001' |
+---------+------+-----------------------------------------------------------+
SHOW GLOBAL VARIABLES LIKE 'interactive_timeout';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| interactive_timeout | 31536000 |
+---------------------+----------+

If the sql_mode system variable does contain STRICT_ALL_TABLES and a user tries to set the interactive_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 interactive_timeout=-1;
ERROR 1231 (42000): Variable 'interactive_timeout' can't be set to the value of '-1'

Invalid Type for Variable

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

SET GLOBAL interactive_timeout='';
ERROR 1232 (42000): Incorrect argument type to variable 'interactive_timeout'
SET GLOBAL interactive_timeout='infinity';
ERROR 1232 (42000): Incorrect argument type to variable 'interactive_timeout'

CHANGE HISTORY

Release Series

History

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

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.