版权声明:声明:本文档可以转载,须署名原作者。 作者:无为 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)]>