interactive_timeout
This page is part of MariaDB's Documentation.
The parent of this page is: System Variables for MariaDB Enterprise Server
Topics on this page:
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 withmysql_real_connect()
.Some clients may be considered interactive clients when executed with some options, and non-interactive clients when executed with other options.
SYNONYMS
SCHEMA
PARAMETERS
Command-line | --interactive_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 | 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) | |
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 | Service Default |
---|---|---|---|
AWS | ColumnStore Data Warehouse | 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) |
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) |
See SkySQL details in MariaDB SkySQL previous release
See SkySQL details in MariaDB SkySQL new release
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_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_
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_
If the sql_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_
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_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_
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_
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'
ERROR HANDLING
FEATURE INTERACTION
RESPONSES
DIAGNOSIS
ISO 9075:2016
CHANGE HISTORY
Release Series | History |
---|---|
11.4 Enterprise |
|
10.6 Enterprise |
|
10.6 Community |
|
10.5 Enterprise |
|
10.5 Community |
|
10.4 Enterprise |
|
10.4 Community |
|
10.3 Enterprise |
|
10.3 Community |
|
10.2 Enterprise |
|
10.2 Community |
|
Release Series | History |
---|---|
11.4 Enterprise |
|
10.6 Enterprise |
|
10.5 Enterprise |
|
10.4 Enterprise |
|