bitscn.com
前言：
mysql中有两个关于连接超时的配置项。他们之间在某些条件下会互相继承，那究竟这两个参数会在什么情况下起作用呢？
本文将会通过一些测试实例来证明总结两者的相互关系。
参数介绍： interactive_timeout
the number of seconds the server waits for activity on an interactive connection before closing it. an interactive client is defined as a client that uses the client_interactive option to mysql_real_connect(). see alsowait_timeout.
wait_timeout 
the number of seconds the server waits for activity on a noninteractive connection before closing it. before mysql 5.1.41, this timeout applies only to tcp/ip connections, not to connections made through unix socket files, named pipes, or shared memory.
on thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the client_interactiveconnect option to mysql_real_connect()). see also interactive_timeout.
client_interactive
permit interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection. the client's sessionwait_timeout variable is set to the value of the session interactive_timeout variable.
简单的说 interactive就是交互式的终端，例如在shell里面直接执行mysql，出现 mysql> 后就是交互式的连接。而mysql -e 'select 1' 这样的直接返回结果的方式就是非交互式的连接。
 第二部分  测试 2.1 继承关系 q：通过socket连接 timeout会从哪个global timeout继承
a：由下例可见，通过socket登录，timeout 继承于global.interactive_timeout;
mysql> set global interactive_timeout = 11111;query ok, 0 rows affected (0.00 sec)mysql> set global wait_timeout = 22222;query ok, 0 rows affected (0.00 sec)mysql> show global variables like '%timeout%';+----------------------------+----------+| variable_name | value |+----------------------------+----------+| connect_timeout | 10 || delayed_insert_timeout | 300 || innodb_lock_wait_timeout | 50 || innodb_rollback_on_timeout | off || interactive_timeout | 11111 || lock_wait_timeout | 31536000 || net_read_timeout | 30 || net_write_timeout | 60 || slave_net_timeout | 3600 || wait_timeout | 22222 |+----------------------------+----------+10 rows in set (0.00 sec)mysql -uroot -ppassword -s /usr/local/mysql3310/mysql.sockwelcome to the mysql monitor. commands end with ; or /g.your mysql connection id is 4server version: 5.5.16-log mysql community server (gpl)copyright (c) 2000, 2011, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners.type 'help;' or '/h' for help. type '/c' to clear the current input statement.mysql> show session variables like '%timeout%';+----------------------------+----------+| variable_name | value |+----------------------------+----------+| connect_timeout | 10 || delayed_insert_timeout | 300 || innodb_lock_wait_timeout | 50 || innodb_rollback_on_timeout | off || interactive_timeout | 11111 || lock_wait_timeout | 31536000 || net_read_timeout | 30 || net_write_timeout | 60 || slave_net_timeout | 3600 || wait_timeout | 11111 |+----------------------------+----------+10 rows in set (0.00 sec)
q：通过tcp/ip client 连接， timeout会从哪个global timeout继承
a：由下例可见，通过tcp/ip client 连接后的wait_timeout 仍然继承于 global.interactive_timeout
mysql -uroot -ppassword -h 127.0.0.1 --port 3310welcome to the mysql monitor. commands end with ; or /g.your mysql connection id is 6server version: 5.5.16-log mysql community server (gpl)copyright (c) 2000, 2011, oracle and/or its affiliates. all rights reserved.oracle is a registered trademark of oracle corporation and/or itsaffiliates. other names may be trademarks of their respectiveowners.type 'help;' or '/h' for help. type '/c' to clear the current input statement.mysql> show session variables like '%timeout%';+----------------------------+----------+| variable_name | value |+----------------------------+----------+| connect_timeout | 10 || delayed_insert_timeout | 300 || innodb_lock_wait_timeout | 50 || innodb_rollback_on_timeout | off || interactive_timeout | 11111 || lock_wait_timeout | 31536000 || net_read_timeout | 30 || net_write_timeout | 60 || slave_net_timeout | 3600 || wait_timeout | 11111 |+----------------------------+----------+10 rows in set (0.00 sec)
2.2 起效关系q：timeout值，对于正在运行用的语句是否起效？
a：由下例可见sql正在执行状态的等待时间不计入timeout时间
mysql> set session wait_timeout=10;query ok, 0 rows affected (0.00 sec)mysql> set session interactive_timeout=10;query ok, 0 rows affected (0.00 sec)mysql> select 1,sleep(20) from dual;+---+-----------+| 1 | sleep(20) |+---+-----------+| 1 | 0 |+---+-----------+1 row in set (20.00 sec)mysql> mysql> show session variables like '%timeout%';+----------------------------+----------+| variable_name | value |+----------------------------+----------+| connect_timeout | 10 || delayed_insert_timeout | 300 || innodb_lock_wait_timeout | 50 || innodb_rollback_on_timeout | off || interactive_timeout | 10 || lock_wait_timeout | 31536000 || net_read_timeout | 30 || net_write_timeout | 60 || slave_net_timeout | 3600 || wait_timeout | 10 |+----------------------------+----------+
q：wait_timeout 和 interacitve_timeout 如何相互作用。
a：只有session.wait_timeout 会起效
mysql> set session interactive_timeout=10;query ok, 0 rows affected (0.00 sec)mysql> set session wait_timeout=20;query ok, 0 rows affected (0.00 sec)---------------------another connection-------------------------mysql> show full processlist;+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| id | user | host | db | command | time | state | info | rows_sent | rows_examined | rows_read |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| 1 | system user | | null | connect | 103749 | slave has read all relay log; waiting for the slave i/o thread to update it | null | 0 | 0 | 1 || 2 | system user | | null | connect | 103750 | connecting to master | null | 0 | 0 | 1 || 3 | root | localhost | null | query | 0 | null | show full processlist | 0 | 0 | 11 || 10 | root | localhost:58946 | null | sleep | 20 | | null | 0 | 0 | 11 |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+4 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| id | user | host | db | command | time | state | info | rows_sent | rows_examined | rows_read |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| 1 | system user | | null | connect | 103749 | slave has read all relay log; waiting for the slave i/o thread to update it | null | 0 | 0 | 1 || 2 | system user | | null | connect | 103750 | connecting to master | null | 0 | 0 | 1 || 3 | root | localhost | null | query | 0 | null | show full processlist | 0 | 0 | 11 |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+3 rows in set (0.00 sec)
q：global timeout和session timeout哪个起作用。
a：只有session timeout 会起作用。
测试1：
mysql> set session interactive_timeout = 10;
query ok, 0 rows affected (0.00 sec)mysql> set session wait_timeout = 10;query ok, 0 rows affected (0.00 sec)mysql> show session variables like '%timeout%';+----------------------------+----------+| variable_name | value |+----------------------------+----------+| interactive_timeout | 10 || wait_timeout | 10 |+----------------------------+----------+10 rows in set (0.00 sec)mysql> show global variables like '%timeout%';+----------------------------+----------+| variable_name | value |+----------------------------+----------+| interactive_timeout | 20 || wait_timeout | 20 |+----------------------------+----------+10 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| id | user | host | db | command | time | state | info | rows_sent | rows_examined | rows_read |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| 3 | root | localhost | null | query | 0 | null | show full processlist | 0 | 0 | 11 || 17 | root | localhost:60585 | null | sleep | 10 | | null | 10 | 10 | 11 |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+2 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| id | user | host | db | command | time | state | info | rows_sent | rows_examined | rows_read |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| 3 | root | localhost | null | query | 0 | null | show full processlist | 0 | 0 | 11 |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+1 rows in set (0.00 sec)
测试2：
mysql> show session variables like '%timeout%';+----------------------------+----------+| variable_name | value |+----------------------------+----------+| interactive_timeout | 20 || wait_timeout | 20 |+----------------------------+----------+10 rows in set (0.00 sec)mysql> show global variables like '%timeout%';+----------------------------+----------+| variable_name | value |+----------------------------+----------+| interactive_timeout | 10 |
| wait_timeout | 10 |+----------------------------+----------+10 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| id | user | host | db | command | time | state | info | rows_sent | rows_examined | rows_read |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| 3 | root | localhost | null | query | 0 | null | show full processlist | 0 | 0 | 11 || 19 | root | localhost:50276 | null | sleep | 19 | | null | 10 | 10 | 11 |+----+-------------+-----------------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+2 rows in set (0.00 sec)mysql> show full processlist;+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| id | user | host | db | command | time | state | info | rows_sent | rows_examined | rows_read |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+| 3 | root | localhost | null | query | 0 | null | show full processlist | 0 | 0 | 11 |+----+-------------+-----------+------+---------+--------+-----------------------------------------------------------------------------+-----------------------+-----------+---------------+-----------+1 rows in set (0.00 sec)
第三部分 总结由以上的阶段测试可以获得以下结论。
1. 超时时间只对非活动状态的connection进行计算。
2. 超时时间指通过 session wait_timeout 起效。
3. 交互式连接的wait_timeout 继承于 global.interactive_timeout
    非交互式连接的wait_timeout 继承于 global.wait_timeout
4. 继承关系和超时对 tcp/ip 和 socket 连接均有效果
bitscn.com