版权声明：声明：本文档可以转载，须署名原作者。 作者：无为 qq:490073687 周祥兴 zhou.xiangxing210@163.com
# Connection timeout variables
#interactive_timeout:
#参数含义：服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使用CLIENT_INTERACTIVE选项的客户端。
#参数默认值：28800秒（8小时）
#wait_timeout:
#参数含义：服务器关闭非交互连接之前等待活动的秒数。
#在线程启动时，根据全局wait_timeout值或全局interactive_timeout值初始化会话wait_timeout值，
#取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义)。
#参数默认值：28800秒（8小时）
继续深入这两个变量wait_timeout的取值范围是1-2147483(Windows)，1-31536000(linux)。
验证问题如下：
1.修改my.cnf文件，分别设置wait_timeout和interactive_timeout不同的值，最终看谁覆盖谁？
2.interactive_timeout对wait_timeout的影响。
2.1 session.interactive_timeout<cnf.wait_timeout时，session.interactive_timeout是否会覆盖wait_timeout？
2.2 session.interactive_timeout>cnf.wait_timeout时，session.interactive_timeout是否会覆盖wait_timeout？
2.3 global.interactive_timeout<cnf.wait_timeout时，global.interactive_timeout是否会覆盖wait_timeout？
2.4 global.interactive_timeout>cnf.wait_timeout时，global.interactive_timeout是否会覆盖wait_timeout？
3.wait_timeout对interactive_timeout的影响。
3.1 session.wait_timeout<cnf.interactive_timeout时，session.wait_timeout是否会覆盖interactive_timeout？
3.2 session.wait_timeout>cnf.interactive_timeout时，session.wait_timeout是否会覆盖interactive_timeout？
3.3 global.wait_timeout<cnf.interactive_timeout时，global.wait_timeout是否会覆盖interactive_timeout？
3.4 global.wait_timeout>cnf.interactive_timeout时，global.wait_timeout是否会覆盖interactive_timeout？
4.什么要同时设置wait_timeout和interactive_timeout？
5.set global wait_timeout=13;是否会影响到已经创建的会话？ 不会
先贴一下小结，后面实验有点长。
4.什么要同时设置wait_timeout和interactive_timeout？
对于应用来说，
如果用的是连接池，并且没有F5（F5可能会设置一个超时，把连接时间过长的session kill掉），那么两个timeout时间尽量设置长一些，避免应用连接池不断的重新连接数据库。
如果你是短连接，那么就需要把两个timeout时间设置得短一些，否则数据库的最大连接数是有限的，很多连接一直不释放掉的话，浪费数据库资源，同时可能会有”MySQL: ERROR 1040: Too many connections”错误。
小结：
a.在2.1和2.2两个试验中，修改会话级的session.interactive_timeout，对当前会话的超时时间wait_timeout没有影响。超时时间还是global.wait_timeout=cnf.interactive_timeout。
b.在2.3和2.4两个实验中，修改全局级的global.interactive_timeout，对当前会话的超时时间wait_timeout不会马上有影响。
当当前会话超时之后，才会生效（相当于新会话了）。对于新创建的会议由影响，而且全局级的interactive_timeout修改会覆盖新会话的wait_timeout。
c.在3.1和3.2两个实验中，修改session.wait_timeout，
如果session.wait_timeout<global.interactive_timeout时，起作用的超时时间是global.interactive_timeout。
如果session.wait_timeout>global.interactive_timeout时，起作用的超时时间是session.wait_timeout。
d.在3.3和3.4两个实验中，修改global.wait_timeout，不管值如何修改，起作用的超时时间是global.interactive_timeout。
e. 2.3和2.4 ， 3.3和3.4 这4个实验也间接验证实验1中的情况，cnf.interactive_timeout覆盖wait_timeout，起作用的是cnf.interactive_timeout和global.interactive_timeout。
f.从这8个实验可以看出：对于修改wait_timeout和interactive_timeout(包括session和global)，只会修改show [global] variables like '%time%';中的参数值，
但是具体超时时间，并不以这两个值为准。---有点绕，需要理解。
g. set global wait_timeout和interactive_timeout，修改了当前会话全局参数值，并不修改当前会话级别的值。
只会对新建会话生效（set global wait_timeout和interactive_timeout之后创建的会话），
对于已经创建的会话不生效（set global wait_timeout和interactive_timeout之前创建的会话）。1.修改my.cnf文件，分别设置wait_timeout和interactive_timeout不同的值，最终看谁覆盖谁？
答：interactive_timeout会覆盖wait_timeout。
修改参数文件，重启mysql
# Connection timeout variables
wait_timeout=15
interactive_timeout=10
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442817811.601474 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
查看会话超时时间 --- 会话超时时间是10s
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 3 | ca | localhost | NULL | Sleep | 10 | | NULL |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
修改参数文件，重启mysql
# Connection timeout variables
wait_timeout=10
interactive_timeout=15
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 15 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 10 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 15 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442817650.922342 |
| wait_timeout | 15 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
查看会话超时时间 --会话超时时间是15秒
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
| 4 | ca | localhost | NULL | Sleep | 15 | | NULL |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 2 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)2.interactive_timeout对wait_timeout的影响。--下面测试的参数文件是
# Connection timeout variables
wait_timeout=15
interactive_timeout=10
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442818199.946527 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
2.1 session.interactive_timeout<cnf.interactive_timeout 时，session.interactive_timeout是否会覆盖wait_timeout？
set interactive_timeout=8; （8<10）
> set interactive_timeout=8;
Query OK, 0 rows affected (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442818460.657640 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
当前会话超时时间----------- 超时时间是10s（不是8s），修改会话级的interactive_timeout没有覆盖wait_timeout。
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 5 | ca | localhost | NULL | Sleep | 10 | | NULL |
| 6 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 6 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
2.2 session.interactive_timeout>cnf.interactive_timeout 时，session.interactive_timeout是否会覆盖wait_timeout？
> set interactive_timeout=12;
Query OK, 0 rows affected (0.00 sec)
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442818758.020616 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
当前会话超时时间----------- 超时时间是10s（不是12s），修改会话级的interactive_timeout没有覆盖wait_timeout。
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 10 | ca | localhost | NULL | Sleep | 10 | | NULL |
| 11 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 11 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.01 sec)
2.3 global.interactive_timeout<cnf.interactive_timeout 时，global.interactive_timeout是否会覆盖wait_timeout？
set global interactive_timeout=8;
当前会话 --- 当前会话的超时时间还是10秒，并未马上修改成8秒。等当前会话超时之后，才重新修改成了8秒。
> set global interactive_timeout=8;
Query OK, 0 rows affected (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819026.134650 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
> show variables like '%time%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 22
Current database: *** NONE ***
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819970.559386 |
| wait_timeout | 8 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
新建的会话---新的会话修改成了8秒，而且新会话的interactive_timeout和wait_timeout都是8秒，全局的interactive_timeout=8秒，全局的wait_timeout=15保持不变。
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 8 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819080.061477 |
| wait_timeout | 8 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
2.4 global.interactive_timeout>cnf.interactive_timeout 时，global.interactive_timeout是否会覆盖wait_timeout？
当前会话 --- 当前会话的超时时间并未马上修改成12秒。等当前会话超时之后，才重新修改成了12秒。
> set global interactive_timeout=12;
Query OK, 0 rows affected (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
> show variables like '%time%'; --当前会话没有立马生效，等了“一会儿”
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 17
Current database: *** NONE ***
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819701.923548 |
| wait_timeout | 12 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.01 sec)
当前会话的超时时间
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 18 | ca | localhost | NULL | Sleep | 12 | | NULL |
| 19 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 19 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
新会话-------
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442819629.180842 |
| wait_timeout | 12 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 12 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
新会话的超时时间，不测了。3.wait_timeout对interactive_timeout的影响。--下面测试的参数文件是
# Connection timeout variables
wait_timeout=15
interactive_timeout=10
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442818199.946527 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.01 sec)
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
3.1 session.wait_timeout<cnf.interactive_timeout时，session.wait_timeout是否会覆盖interactive_timeout？
set wait_timeout=8;
> set wait_timeout=8;
Query OK, 0 rows affected (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442820543.624578 |
| wait_timeout | 8 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
当前会话超时时间 -- 超时时间是interactive_timeout=10秒，不是8秒，修改当前会话的wait_timeout，没有覆盖interactive_timeout，起作用的是interactive_timeout
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 6 | ca | localhost | NULL | Sleep | 10 | | NULL |
| 7 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 7 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.01 sec)
3.2 session.wait_timeout>cnf.interactive_timeout时，session.wait_timeout是否会覆盖interactive_timeout？
set wait_timeout=12;
> set wait_timeout=12;
Query OK, 0 rows affected (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442820744.921596 |
| wait_timeout | 12 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 15 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
当前会话超时时间---超时时间是wait_timeout=12秒，不是10秒，修改当前会话的wait_timeout，没有覆盖interactive_timeout，但是起作用的是wait_timeout
> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 8 | ca | localhost | NULL | Sleep | 12 | | NULL |
| 9 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
(casystem)root@localhost [(none)]> show full processlist;
+----+------+-----------+------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+-----------------------+
| 9 | root | localhost | NULL | Query | 0 | init | show full processlist |
+----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)3.3 global.wait_timeout<cnf.interactive_timeout时，global.wait_timeout是否会覆盖interactive_timeout？
set global wait_timeout=8;
当前会话---当前会话的超时时间是10，并未修改成8，当前会话超时之后，也没有修改成8.（原因，实验1，以interactive_timeout为准）
> set global wait_timeout=8;
Query OK, 0 rows affected (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821194.483634 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 8 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 12
Current database: *** NONE ***
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821244.086780 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
新会话--新会话的超时时间，未修改8秒。 （原因，实验1，以interactive_timeout为准）
> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821214.497556 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 8 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)3.4 global.wait_timeout>cnf.interactive_timeout时，global.wait_timeout是否会覆盖interactive_timeout？
set global wait_timeout=12;
当前会话---当前会话超时时间并未修改成12秒，而且超时之后也没有修改成12秒 （原因，实验1，以interactive_timeout为准）
> set global wait_timeout=12;
Query OK, 0 rows affected (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821797.229564 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 12 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 19
Current database: *** NONE ***
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821862.560630 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
新会话 -- 新会话的超时时间，未修改12秒。 （原因，实验1，以interactive_timeout为准）
> show global variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| wait_timeout | 12 |
+---------------------------------+-------------------+
24 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]> show variables like '%time%';
+---------------------------------+-------------------+
| Variable_name | Value |
+---------------------------------+-------------------+
| binlog_max_flush_queue_time | 0 |
| connect_timeout | 10 |
| datetime_format | %Y-%m-%d %H:%i:%s |
| delayed_insert_timeout | 300 |
| explicit_defaults_for_timestamp | OFF |
| flush_time | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_time | 1000 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 10 |
| lc_time_names | en_US |
| lock_wait_timeout | 31536000 |
| long_query_time | 0.020000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| slow_launch_time | 2 |
| system_time_zone | CST |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1442821846.568606 |
| wait_timeout | 10 |
+---------------------------------+-------------------+
25 rows in set (0.00 sec)
(casystem)ca@localhost [(none)]>
1434
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
