interactive_timeout

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

This page is part of MariaDB's Enterprise Documentation.

The parent of this page is: System Variables for MariaDB Enterprise Server

Topics on this page:

USAGE
DETAILS
EXAMPLES
CHANGE HISTORY
EXTERNAL REFERENCES

USAGE

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	HA (Primary/Replica)	10.4	3600 (choices: 300, 600, 3600, 28800, 86400)
AWS	HA (Primary/Replica)	10.5	3600 (choices: 300, 600, 3600, 28800, 86400)
AWS	Transactional Standalone	10.4	3600 (choices: 300, 600, 3600, 28800, 86400)
AWS	Transactional Standalone	10.5	3600 (choices: 300, 600, 3600, 28800, 86400)
AWS	Distributed Transactions	10.5	28800 (choices: 300, 600, 3600, 28800, 86400)
GCP	ColumnStore	10.5	28800 (choices: 300, 600, 3600, 28800, 86400)
GCP	ColumnStore Multi	10.5	28800 (choices: 300, 600, 3600, 28800, 86400)
GCP	Galera	10.4	3600 (choices: 300, 600, 3600, 28800, 86400)
GCP	Galera	10.5	3600 (choices: 300, 600, 3600, 28800, 86400)
GCP	HA (Primary/Replica)	10.4	3600 (choices: 300, 600, 3600, 28800, 86400)
GCP	HA (Primary/Replica)	10.5	3600 (choices: 300, 600, 3600, 28800, 86400)
GCP	HTAP	10.5	28800 (choices: 300, 600, 3600, 28800, 86400)
GCP	Transactional Standalone	10.4	3600 (choices: 300, 600, 3600, 28800, 86400)
GCP	Transactional Standalone	10.5	3600 (choices: 300, 600, 3600, 28800, 86400)
GCP	Distributed Transactions	10.5	28800 (choices: 300, 600, 3600, 28800, 86400)

See MariaDB SkySQL documentation to learn more about the MariaDB SkySQL database as a service.

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.

EXTERNAL REFERENCES

Information specific to MariaDB SkySQL can be found on the interactive_timeout page in the SkySQL Documentation.