Merge lp:~longbow/percona-server/ps51_kill_idle_trx into lp:percona-server/5.1

[Valentine Gostev]

Added innodb_kill_idle_transaction patch and test.

[Stewart Smith (stewart)]

(as mentioned on IRC)

1) decrease sleep
2) create a single patch including tests?
3) on reconnect, do the select again to enuser content of table is correct
4) another test that took row locks (SELECT FOR UPDATE) may be useful to doubly ensure they're released properly

review: Needs Fixing

[Oleg Tsarev (tsarev)]

1) Unnecessary error directive:

198 ++--error 2006 --error CR_SERVER_GONE_ERROR

Enough just a
198 ++--error CR_SERVER_GONE_ERROR

2) SHOW GLOBAL VARIABLES instead of SHOW VARIABLES
248 ++SHOW VARIABLES LIKE 'innodb_kill_idle_transaction';
249 ++SET GLOBAL innodb_kill_idle_transaction=5;

3) Use also SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES ORDER BY Variable_name (not only SHOW GLOBAL VARIABLES)

review: Needs Fixing

[Oleg Tsarev (tsarev)]

Please merge two commits to single

review: Needs Fixing

[Oleg Tsarev (tsarev)]

> Please merge two commits to single

Single commit more clear and simpler for search problems/analyse changes, than two.

Thank you.

[Oleg Tsarev (tsarev)]

Where is Jenkins's build URL?

review: Needs Information

[Oleg Tsarev (tsarev)]

LGTM

review: Approve

[Stewart Smith (stewart)]

https://code.launchpad.net/~longbow/percona-server/ps55_kill_idle_trx/+merge/72361

needs same fix as ^ (i.e. there are bugs)

review: Needs Resubmitting

[Stewart Smith (stewart)]

This patch has been merged in other commits.

Unmerged revisions

279. By longbow <longbow@mil-nb> on 2011-08-19

Added tests for innodb_kill_idle_transaction.patch and added patch to series

=== renamed file 'innodb_kill_idle_transaction.patch' => 'patches/innodb_kill_idle_transaction.patch'
--- innodb_kill_idle_transaction.patch	2011-07-07 13:02:19 +0000
+++ patches/innodb_kill_idle_transaction.patch	2011-08-19 16:57:59 +0000
@@ -5,9 +5,8 @@
 #!!! notice !!!
 # Any small change to this file in the main branch
 # should be done or reviewed by the maintainer!
-diff -ruN a/include/mysql/plugin.h b/include/mysql/plugin.h
---- a/include/mysql/plugin.h	2011-07-07 19:38:24.650374103 +0900
-+++ b/include/mysql/plugin.h	2011-07-07 19:39:44.611713196 +0900
+--- a/include/mysql/plugin.h
++++ b/include/mysql/plugin.h
 @@ -845,6 +845,12 @@
  */
  void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton,
@@ -21,9 +20,8 @@
  #ifdef __cplusplus
  }
  #endif
-diff -ruN a/include/mysql/plugin.h.pp b/include/mysql/plugin.h.pp
---- a/include/mysql/plugin.h.pp	2011-07-07 19:38:24.423414787 +0900
-+++ b/include/mysql/plugin.h.pp	2011-07-07 19:39:44.615372555 +0900
+--- a/include/mysql/plugin.h.pp
++++ b/include/mysql/plugin.h.pp
 @@ -150,3 +150,6 @@
  void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
  void thd_set_ha_data(void* thd, const struct handlerton *hton,
@@ -31,9 +29,8 @@
 +int thd_command(const void* thd);
 +long long thd_start_time(const void* thd);
 +void thd_kill(void* thd);
-diff -ruN a/sql/sql_class.cc b/sql/sql_class.cc
---- a/sql/sql_class.cc	2011-07-07 19:38:24.999372326 +0900
-+++ b/sql/sql_class.cc	2011-07-07 19:39:44.619370533 +0900
+--- a/sql/sql_class.cc
++++ b/sql/sql_class.cc
 @@ -468,6 +468,26 @@
    return buffer;
  }
@@ -61,10 +58,9 @@
  
  /**
    Implementation of Drop_table_error_handler::handle_error().
-diff -ruN a/storage/innodb_plugin/handler/ha_innodb.cc b/storage/innodb_plugin/handler/ha_innodb.cc
---- a/storage/innodb_plugin/handler/ha_innodb.cc	2011-07-07 19:38:26.162373407 +0900
-+++ b/storage/innodb_plugin/handler/ha_innodb.cc	2011-07-07 19:39:44.625370650 +0900
-@@ -2511,6 +2511,10 @@
+--- a/storage/innodb_plugin/handler/ha_innodb.cc
++++ b/storage/innodb_plugin/handler/ha_innodb.cc
+@@ -2521,6 +2521,10 @@
  
  	innobase_commit_concurrency_init_default();
  
@@ -75,7 +71,7 @@
  	/* Since we in this module access directly the fields of a trx
  	struct, and due to different headers and flags it might happen that
  	mutex_t has a different size in this module and in InnoDB
-@@ -11237,6 +11241,48 @@
+@@ -11247,6 +11251,48 @@
  	return(false);
  }
  
@@ -124,7 +120,7 @@
  static SHOW_VAR innodb_status_variables_export[]= {
    {"Innodb",                   (char*) &show_innodb_vars, SHOW_FUNC},
    {NullS, NullS, SHOW_LONG}
-@@ -11480,6 +11526,15 @@
+@@ -11500,6 +11546,15 @@
    "Number of times a thread is allowed to enter InnoDB within the same SQL query after it has once got the ticket",
    NULL, NULL, 500L, 1L, ~0L, 0);
  
@@ -140,7 +136,7 @@
  static MYSQL_SYSVAR_LONG(file_io_threads, innobase_file_io_threads,
    PLUGIN_VAR_RQCMDARG | PLUGIN_VAR_READONLY | PLUGIN_VAR_NOSYSVAR,
    "Number of file I/O threads in InnoDB.",
-@@ -11762,6 +11817,7 @@
+@@ -11784,6 +11839,7 @@
    MYSQL_SYSVAR(fast_checksum),
    MYSQL_SYSVAR(commit_concurrency),
    MYSQL_SYSVAR(concurrency_tickets),
@@ -148,10 +144,9 @@
    MYSQL_SYSVAR(data_file_path),
    MYSQL_SYSVAR(doublewrite_file),
    MYSQL_SYSVAR(data_home_dir),
-diff -ruN a/storage/innodb_plugin/include/srv0srv.h b/storage/innodb_plugin/include/srv0srv.h
---- a/storage/innodb_plugin/include/srv0srv.h	2011-07-07 19:38:26.128372970 +0900
-+++ b/storage/innodb_plugin/include/srv0srv.h	2011-07-07 19:39:44.646824971 +0900
-@@ -281,6 +281,7 @@
+--- a/storage/innodb_plugin/include/srv0srv.h
++++ b/storage/innodb_plugin/include/srv0srv.h
+@@ -284,6 +284,7 @@
  extern ulint	srv_activity_count;
  extern ulint	srv_fatal_semaphore_wait_threshold;
  extern ulint	srv_dml_needed_delay;
@@ -159,9 +154,8 @@
  
  extern mutex_t*	kernel_mutex_temp;/* mutex protecting the server, trx structs,
  				query threads, and lock table: we allocate
-diff -ruN a/storage/innodb_plugin/include/trx0trx.h b/storage/innodb_plugin/include/trx0trx.h
---- a/storage/innodb_plugin/include/trx0trx.h	2011-07-07 19:38:25.705377859 +0900
-+++ b/storage/innodb_plugin/include/trx0trx.h	2011-07-07 19:39:44.650304314 +0900
+--- a/storage/innodb_plugin/include/trx0trx.h
++++ b/storage/innodb_plugin/include/trx0trx.h
 @@ -600,6 +600,8 @@
  	ulint		mysql_process_no;/* since in Linux, 'top' reports
  					process id's and not thread id's, we
@@ -171,9 +165,8 @@
  	/*------------------------------*/
  	ulint		n_mysql_tables_in_use; /* number of Innobase tables
  					used in the processing of the current
-diff -ruN a/storage/innodb_plugin/srv/srv0srv.c b/storage/innodb_plugin/srv/srv0srv.c
---- a/storage/innodb_plugin/srv/srv0srv.c	2011-07-07 19:38:26.147372081 +0900
-+++ b/storage/innodb_plugin/srv/srv0srv.c	2011-07-07 19:39:44.658303810 +0900
+--- a/storage/innodb_plugin/srv/srv0srv.c
++++ b/storage/innodb_plugin/srv/srv0srv.c
 @@ -86,6 +86,11 @@
  #include "trx0i_s.h"
  #include "os0sync.h" /* for HAVE_ATOMIC_BUILTINS */
@@ -196,7 +189,7 @@
  /* How much data manipulation language (DML) statements need to be delayed,
  in microseconds, in order to reduce the lagging of the purge thread. */
  UNIV_INTERN ulint	srv_dml_needed_delay = 0;
-@@ -2549,6 +2557,36 @@
+@@ -2554,6 +2562,36 @@
  		old_sema = sema;
  	}
  
@@ -233,9 +226,8 @@
  	/* Flush stderr so that a database user gets the output
  	to possible MySQL error file */
  
-diff -ruN a/storage/innodb_plugin/trx/trx0trx.c b/storage/innodb_plugin/trx/trx0trx.c
---- a/storage/innodb_plugin/trx/trx0trx.c	2011-07-07 19:38:25.723372433 +0900
-+++ b/storage/innodb_plugin/trx/trx0trx.c	2011-07-07 19:39:44.665304098 +0900
+--- a/storage/innodb_plugin/trx/trx0trx.c
++++ b/storage/innodb_plugin/trx/trx0trx.c
 @@ -137,6 +137,9 @@
  	trx->mysql_relay_log_file_name = "";
  	trx->mysql_relay_log_pos = 0;
@@ -246,3 +238,165 @@
  	mutex_create(&trx->undo_mutex, SYNC_TRX_UNDO);
  
  	trx->rseg = NULL;
+--- /dev/null
++++ b/mysql-test/r/percona_innodb_kill_idle_trx.result
+@@ -0,0 +1,41 @@
++DROP TABLE IF EXISTS t1;
++SET autocommit=0;
++CREATE TABLE t1 (a INT) ENGINE=InnoDB;
++SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
++Variable_name   Value
++innodb_kill_idle_transaction    0
++SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
++VARIABLE_NAME   VARIABLE_VALUE
++INNODB_KILL_IDLE_TRANSACTION    0
++SET GLOBAL innodb_kill_idle_transaction=1;
++SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
++Variable_name   Value
++innodb_kill_idle_transaction    1
++SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
++VARIABLE_NAME   VARIABLE_VALUE
++INNODB_KILL_IDLE_TRANSACTION    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 GLOBAL innodb_kill_idle_transaction=0;
++SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
++Variable_name   Value
++innodb_kill_idle_transaction    0
++SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
++VARIABLE_NAME   VARIABLE_VALUE
++INNODB_KILL_IDLE_TRANSACTION    0
+--- /dev/null
++++ b/mysql-test/t/percona_innodb_kill_idle_trx.test
+@@ -0,0 +1,28 @@
++--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/percona_innodb_kill_idle_trx_show.inc
++SET GLOBAL innodb_kill_idle_transaction=1;
++--source include/percona_innodb_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 GLOBAL innodb_kill_idle_transaction=0;
++--source include/percona_innodb_kill_idle_trx_show.inc
+--- /dev/null
++++ b/mysql-test/r/percona_innodb_kill_idle_trx_locks.result
+@@ -0,0 +1,45 @@
++DROP TABLE IF EXISTS t1;
++SET autocommit=0;
++CREATE TABLE t1 (a INT) ENGINE=InnoDB;
++SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
++Variable_name   Value
++innodb_kill_idle_transaction    0
++SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
++VARIABLE_NAME   VARIABLE_VALUE
++INNODB_KILL_IDLE_TRANSACTION    0
++SET GLOBAL innodb_kill_idle_transaction=5;
++SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
++Variable_name   Value
++innodb_kill_idle_transaction    5
++SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
++VARIABLE_NAME   VARIABLE_VALUE
++INNODB_KILL_IDLE_TRANSACTION    5
++BEGIN;
++INSERT INTO t1 VALUES (1),(2),(3);
++COMMIT;
++SELECT * FROM t1;
++a
++1
++2
++3
++### Locking rows. Lock should be released when idle trx is killed.
++BEGIN;
++SELECT * FROM t1 FOR UPDATE;
++a
++1
++2
++3
++UPDATE t1 set a=4;
++SELECT * FROM t1;
++a
++4
++4
++4
++DROP TABLE t1;
++SET GLOBAL innodb_kill_idle_transaction=0;
++SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
++Variable_name   Value
++innodb_kill_idle_transaction    0
++SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';
++VARIABLE_NAME   VARIABLE_VALUE
++INNODB_KILL_IDLE_TRANSACTION    0
+--- /dev/null
++++ b/mysql-test/t/percona_innodb_kill_idle_trx_locks.test
+@@ -0,0 +1,31 @@
++--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/percona_innodb_kill_idle_trx_show.inc
++SET GLOBAL innodb_kill_idle_transaction=5;
++--source include/percona_innodb_kill_idle_trx_show.inc
++
++connect (conn1,localhost,root,,);
++connection conn1;
++
++BEGIN;
++INSERT INTO t1 VALUES (1),(2),(3);
++COMMIT;
++SELECT * FROM t1;
++
++--echo ### Locking rows. Lock should be released when idle trx is killed.
++BEGIN;
++SELECT * FROM t1 FOR UPDATE;
++
++connection default;
++UPDATE t1 set a=4;
++
++SELECT * FROM t1;
++DROP TABLE t1;
++SET GLOBAL innodb_kill_idle_transaction=0;
++--source include/percona_innodb_kill_idle_trx_show.inc
+--- /dev/null
++++ b/mysql-test/include/percona_innodb_kill_idle_trx_show.inc
+@@ -0,0 +1,2 @@
++SHOW GLOBAL VARIABLES LIKE 'innodb_kill_idle_transaction';
++SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE VARIABLE_NAME='innodb_kill_idle_transaction';

=== modified file 'patches/series'
--- patches/series	2011-08-01 21:45:17 +0000
+++ patches/series	2011-08-19 16:57:59 +0000
@@ -60,3 +60,4 @@
 bug813587.patch
 innodb_bug47167_test_fix.patch
 disable_query_cache_28249_test_sporadic_failure.patch
+innodb_kill_idle_transaction.patch