Index: mysql-test/include/server_kill_idle_trx_show.inc =================================================================== --- mysql-test/include/server_kill_idle_trx_show.inc (.../trunk/mysql-5.1.61) (revision 0) +++ mysql-test/include/server_kill_idle_trx_show.inc (.../branches/my5161-r1088-kill-idle-trx) (revision 1665) @@ -0,0 +1,2 @@ +SHOW VARIABLES LIKE 'trx%idle_timeout'; +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; Index: mysql-test/r/server_kill_idle_trx.result =================================================================== --- mysql-test/r/server_kill_idle_trx.result (.../trunk/mysql-5.1.61) (revision 0) +++ mysql-test/r/server_kill_idle_trx.result (.../branches/my5161-r1088-kill-idle-trx) (revision 1665) @@ -0,0 +1,166 @@ +DROP TABLE IF EXISTS t1; +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 0 +trx_idle_timeout 0 +trx_readonly_idle_timeout 0 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 0 +TRX_READONLY_IDLE_TIMEOUT 0 +TRX_IDLE_TIMEOUT 0 +SET trx_idle_timeout=1; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 0 +trx_idle_timeout 1 +trx_readonly_idle_timeout 0 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 0 +TRX_READONLY_IDLE_TIMEOUT 0 +TRX_IDLE_TIMEOUT 1 +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +COMMIT; +SELECT * FROM t1; +a +1 +2 +3 +BEGIN; +INSERT INTO t1 VALUES (4),(5),(6); +SELECT * FROM t1; +ERROR HY000: MySQL server has gone away +SELECT * FROM t1; +a +1 +2 +3 +DROP TABLE t1; +SET trx_idle_timeout=0; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 0 +trx_idle_timeout 0 +trx_readonly_idle_timeout 0 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 0 +TRX_READONLY_IDLE_TIMEOUT 0 +TRX_IDLE_TIMEOUT 0 +DROP TABLE IF EXISTS t1; +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 0 +trx_idle_timeout 0 +trx_readonly_idle_timeout 0 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 0 +TRX_READONLY_IDLE_TIMEOUT 0 +TRX_IDLE_TIMEOUT 0 +SET trx_readonly_idle_timeout=1; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 0 +trx_idle_timeout 0 +trx_readonly_idle_timeout 1 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 0 +TRX_READONLY_IDLE_TIMEOUT 1 +TRX_IDLE_TIMEOUT 0 +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1; +a +1 +2 +3 +COMMIT; +SELECT * FROM t1; +a +1 +2 +3 +SELECT * FROM t1; +ERROR HY000: MySQL server has gone away +SELECT * FROM t1; +a +1 +2 +3 +DROP TABLE t1; +SET trx_readonly_idle_timeout=0; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 0 +trx_idle_timeout 0 +trx_readonly_idle_timeout 0 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 0 +TRX_READONLY_IDLE_TIMEOUT 0 +TRX_IDLE_TIMEOUT 0 +DROP TABLE IF EXISTS t1; +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 0 +trx_idle_timeout 0 +trx_readonly_idle_timeout 0 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 0 +TRX_READONLY_IDLE_TIMEOUT 0 +TRX_IDLE_TIMEOUT 0 +SET trx_changes_idle_timeout=1; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 1 +trx_idle_timeout 0 +trx_readonly_idle_timeout 0 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 1 +TRX_READONLY_IDLE_TIMEOUT 0 +TRX_IDLE_TIMEOUT 0 +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1; +a +1 +2 +3 +COMMIT; +SELECT * FROM t1; +a +1 +2 +3 +SELECT * FROM t1; +a +1 +2 +3 +INSERT INTO t1 VALUES (4),(5),(6); +SELECT * FROM t1; +ERROR HY000: MySQL server has gone away +DROP TABLE t1; +SET trx_changes_idle_timeout=0; +SHOW VARIABLES LIKE 'trx%idle_timeout'; +Variable_name Value +trx_changes_idle_timeout 0 +trx_idle_timeout 0 +trx_readonly_idle_timeout 0 +SELECT * FROM INFORMATION_SCHEMA.SESSION_VARIABLES WHERE VARIABLE_NAME LIKE 'trx%idle_timeout'; +VARIABLE_NAME VARIABLE_VALUE +TRX_CHANGES_IDLE_TIMEOUT 0 +TRX_READONLY_IDLE_TIMEOUT 0 +TRX_IDLE_TIMEOUT 0 Index: mysql-test/t/server_kill_idle_trx.test =================================================================== --- mysql-test/t/server_kill_idle_trx.test (.../trunk/mysql-5.1.61) (revision 0) +++ mysql-test/t/server_kill_idle_trx.test (.../branches/my5161-r1088-kill-idle-trx) (revision 1665) @@ -0,0 +1,89 @@ +# Test Case For Kill All Transactions +--source include/have_innodb.inc +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; + +--source include/server_kill_idle_trx_show.inc +SET trx_idle_timeout=1; +--source include/server_kill_idle_trx_show.inc + +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +COMMIT; +SELECT * FROM t1; + +BEGIN; +INSERT INTO t1 VALUES (4),(5),(6); +sleep 3; + +--enable_reconnect +--error 2006 --error CR_SERVER_GONE_ERROR +SELECT * FROM t1; +SELECT * FROM t1; +DROP TABLE t1; +SET trx_idle_timeout=0; +--source include/server_kill_idle_trx_show.inc + +# Test Case For Kill Read-Only Transaction +--source include/have_innodb.inc +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; + +--source include/server_kill_idle_trx_show.inc +SET trx_readonly_idle_timeout=1; +--source include/server_kill_idle_trx_show.inc + +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1; +COMMIT; +SELECT * FROM t1; + +sleep 3; + +--enable_reconnect +--error 2006 --error CR_SERVER_GONE_ERROR +SELECT * FROM t1; +SELECT * FROM t1; +DROP TABLE t1; +SET trx_readonly_idle_timeout=0; +--source include/server_kill_idle_trx_show.inc + +# Test Case For Kill Changes Transaction +--source include/have_innodb.inc +--disable_warnings +DROP TABLE IF EXISTS t1; +--enable_warnings + +SET autocommit=0; +CREATE TABLE t1 (a INT) ENGINE=InnoDB; + +--source include/server_kill_idle_trx_show.inc +SET trx_changes_idle_timeout=1; +--source include/server_kill_idle_trx_show.inc + +BEGIN; +INSERT INTO t1 VALUES (1),(2),(3); +SELECT * FROM t1; +COMMIT; +SELECT * FROM t1; + +sleep 3; + +SELECT * FROM t1; +INSERT INTO t1 VALUES (4),(5),(6); +sleep 3; +--enable_reconnect +--error 2006 --error CR_SERVER_GONE_ERROR +SELECT * FROM t1; +DROP TABLE t1; +SET trx_changes_idle_timeout=0; +--source include/server_kill_idle_trx_show.inc Index: sql/sql_class.h =================================================================== --- sql/sql_class.h (.../trunk/mysql-5.1.61) (revision 1088) +++ sql/sql_class.h (.../branches/my5161-r1088-kill-idle-trx) (revision 1665) @@ -323,6 +323,11 @@ ulong net_read_timeout; ulong net_retry_count; ulong net_wait_timeout; + /* Add For Kill Idle Transaction By P.Linux */ + ulong trx_idle_timeout; + ulong trx_readonly_idle_timeout; + ulong trx_changes_idle_timeout; + /* End */ ulong net_write_timeout; ulong optimizer_prune_level; ulong optimizer_search_depth; Index: sql/handler.h =================================================================== --- sql/handler.h (.../trunk/mysql-5.1.61) (revision 1088) +++ sql/handler.h (.../branches/my5161-r1088-kill-idle-trx) (revision 1665) @@ -2042,6 +2042,17 @@ int ha_prepare(THD *thd); int ha_recover(HASH *commit_list); +/* Add For Kill Idle Transaction By P.Linux */ +inline int ha_check_trx_read_only(Ha_trx_info *ha_list) +{ + Ha_trx_info *ha_info; + for (ha_info= ha_list; ha_info; ha_info= ha_info->next()) + if (ha_info->is_trx_read_write()) + return FALSE; + return TRUE; +} +/* End */ + /* transactions: these functions never call handlerton functions directly */ int ha_commit_trans(THD *thd, bool all); int ha_autocommit_or_rollback(THD *thd, int error); Index: sql/mysqld.cc =================================================================== --- sql/mysqld.cc (.../trunk/mysql-5.1.61) (revision 1088) +++ sql/mysqld.cc (.../branches/my5161-r1088-kill-idle-trx) (revision 1665) @@ -5616,6 +5616,11 @@ OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE, OPT_TMP_TABLE_SIZE, OPT_THREAD_STACK, OPT_WAIT_TIMEOUT, + /* Add For Kill Idle Transaction By P.Linux */ + OPT_TRX_IDLE_TIMEOUT, + OPT_TRX_READONLY_IDLE_TIMEOUT, + OPT_TRX_CHANGES_IDLE_TIMEOUT, + /* End */ OPT_ERROR_LOG_FILE, OPT_DEFAULT_WEEK_FORMAT, OPT_RANGE_ALLOC_BLOCK_SIZE, OPT_ALLOW_SUSPICIOUS_UDFS, @@ -7141,6 +7146,26 @@ &max_system_variables.net_wait_timeout, 0, GET_ULONG, REQUIRED_ARG, NET_WAIT_TIMEOUT, 1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT), 0, 1, 0}, + /* Add For Kill Idle Transaction By P.Linux */ + {"trx_idle_timeout", OPT_TRX_IDLE_TIMEOUT, + "The number of seconds the server waits for next statement on a transaction before closing it.", + &global_system_variables.trx_idle_timeout, + &max_system_variables.trx_idle_timeout, + 0, GET_ULONG, REQUIRED_ARG, 0, 0, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT), + 0, 1, 0}, + {"trx_readonly_idle_timeout", OPT_TRX_READONLY_IDLE_TIMEOUT, + "The number of seconds the server waits for next statement on a transaction-readonly before closing it.", + &global_system_variables.trx_readonly_idle_timeout, + &max_system_variables.trx_readonly_idle_timeout, + 0, GET_ULONG, REQUIRED_ARG, 0, 0, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT), + 0, 1, 0}, + {"trx_changes_idle_timeout", OPT_TRX_CHANGES_IDLE_TIMEOUT, + "The number of seconds the server waits for next statement on a transaction-not-readonly before closing it.", + &global_system_variables.trx_changes_idle_timeout, + &max_system_variables.trx_changes_idle_timeout, + 0, GET_ULONG, REQUIRED_ARG, 0, 0, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT), + 0, 1, 0}, + /* End */ {"binlog-direct-non-transactional-updates", OPT_BINLOG_DIRECT_NON_TRANS_UPDATE, "Causes updates to non-transactional engines using statement format to be " "written directly to binary log. Before using this option, make sure that " Index: sql/sql_parse.cc =================================================================== --- sql/sql_parse.cc (.../trunk/mysql-5.1.61) (revision 1088) +++ sql/sql_parse.cc (.../branches/my5161-r1088-kill-idle-trx) (revision 1665) @@ -821,8 +821,28 @@ the client, the connection is closed or "net_wait_timeout" number of seconds has passed */ - my_net_set_read_timeout(net, thd->variables.net_wait_timeout); - + /* Add For Kill Idle Transaction By P.Linux */ + THD_TRANS *trans= &thd->transaction.all; + Ha_trx_info *ha_info= trans->ha_list; + bool is_trx_read_only= ha_check_trx_read_only(ha_info); + if (thd->active_transaction()) + { + if (thd->variables.trx_idle_timeout > 0) + { + my_net_set_read_timeout(net, thd->variables.trx_idle_timeout); + } else if ((thd->variables.trx_readonly_idle_timeout > 0) && is_trx_read_only) + { + my_net_set_read_timeout(net, thd->variables.trx_readonly_idle_timeout); + } else if ((thd->variables.trx_changes_idle_timeout > 0) && !is_trx_read_only) + { + my_net_set_read_timeout(net, thd->variables.trx_changes_idle_timeout); + } else { + my_net_set_read_timeout(net, thd->variables.net_wait_timeout); + } + } else { + my_net_set_read_timeout(net, thd->variables.net_wait_timeout); + } + /* End */ /* XXX: this code is here only to clear possible errors of init_connect. Consider moving to init_connect() instead. Index: sql/set_var.cc =================================================================== --- sql/set_var.cc (.../trunk/mysql-5.1.61) (revision 1088) +++ sql/set_var.cc (.../branches/my5161-r1088-kill-idle-trx) (revision 1665) @@ -677,6 +677,15 @@ static sys_var_thd_ulong sys_net_wait_timeout(&vars, "wait_timeout", &SV::net_wait_timeout); +/* Add For Kill Idle Transaction By P.Linux*/ +static sys_var_thd_ulong sys_trx_idle_timeout(&vars, "trx_idle_timeout", + &SV::trx_idle_timeout); +static sys_var_thd_ulong sys_trx_readonly_idle_timeout(&vars, "trx_readonly_idle_timeout", + &SV::trx_readonly_idle_timeout); +static sys_var_thd_ulong sys_trx_changes_idle_timeout(&vars, "trx_changes_idle_timeout", + &SV::trx_changes_idle_timeout); +/* End */ + /* Condition pushdown to storage engine */ static sys_var_thd_bool sys_engine_condition_pushdown(&vars, "engine_condition_pushdown",