Merge lp:~longbow/percona-server/ps55_kill_idle_trx into lp:percona-server/5.5

[Valentine Gostev]

Tests for innodb_kill_idle_transaction.patch

http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/93/

PS sporadic fails on centos5 and debian6 discovered.

[Oleg Tsarev (tsarev)]

Looks fine for me, except sleep(3) - but in current situation (timeout) this is reasonable.

review: Approve

[Stewart Smith (stewart)]

So, to me it looks like the test failures show an actual bug that should be fixed before we enable this. So, I'm marking as resubmit, for when we have this bug fixed.

i.e. please file a bug and point Yasufumi at it.

review: Needs Resubmitting

[Stewart Smith (stewart)]

merged in other merge reqs

Unmerged revisions

153. By longbow <longbow@mil-nb> on 2011-08-20

Added tests for innodb_kill_idle_transaction.patch and added patch to series

152. By Stewart Smith on 2011-08-16

merge 5.5.15 rebase

151. By Stewart Smith on 2011-08-13

use S3 download site

150. By Stewart Smith on 2011-08-13

merge a few quilt related fixes to misc scripts we use

149. By Stewart Smith on 2011-08-13

merge atomic_cas_64 fix

148. By Stewart Smith on 2011-08-06

merge compiler warning fix for innodb comparison of unsigned expression always true

147. By Stewart Smith on 2011-08-05

merge fix of innodb_bug60049 test failure. The master thread should purge if purge_thread seems already exited in shutdown state.

146. By Stewart Smith on 2011-08-04

update percona_server_variables_debug.result for disabled INNODB_FAKE_CHANGES and KILL_IDLE_TRANSACTION

145. By Stewart Smith on 2011-08-04

fix all_vars test for removal of INNODB FAKE_CHANGES and KILL_IDLE_TRANSACTION

144. By Stewart Smith on 2011-08-04

move valgrind_zlib_suppression patch into patches/

=== modified file 'mysql-test/percona_server_variables_debug.result'
--- mysql-test/percona_server_variables_debug.result	2011-08-04 23:23:45 +0000
+++ mysql-test/percona_server_variables_debug.result	2011-08-21 21:29:24 +0000
@@ -115,6 +115,7 @@
 INNODB_IBUF_MAX_SIZE
 INNODB_IMPORT_TABLE_FROM_XTRABACKUP
 INNODB_IO_CAPACITY
+INNODB_KILL_IDLE_TRANSACTION
 INNODB_LARGE_PREFIX
 INNODB_LAZY_DROP_TABLE
 INNODB_LOCKS_UNSAFE_FOR_BINLOG

=== modified file 'mysql-test/percona_server_variables_release.result'
--- mysql-test/percona_server_variables_release.result	2011-08-04 19:16:18 +0000
+++ mysql-test/percona_server_variables_release.result	2011-08-21 21:29:24 +0000
@@ -112,6 +112,7 @@
 INNODB_IBUF_MAX_SIZE
 INNODB_IMPORT_TABLE_FROM_XTRABACKUP
 INNODB_IO_CAPACITY
+INNODB_KILL_IDLE_TRANSACTION
 INNODB_LARGE_PREFIX
 INNODB_LAZY_DROP_TABLE
 INNODB_LOCKS_UNSAFE_FOR_BINLOG

=== renamed file 'innodb_kill_idle_transaction.patch' => 'patches/innodb_kill_idle_transaction.patch'
--- innodb_kill_idle_transaction.patch	2011-07-12 15:59:46 +0000
+++ patches/innodb_kill_idle_transaction.patch	2011-08-21 21:29:24 +0000
@@ -5,10 +5,9 @@
 #!!! 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-06-22 01:42:39.000000000 +0900
-+++ b/include/mysql/plugin.h	2011-07-12 22:19:58.000000000 +0900
-@@ -625,6 +625,12 @@
+--- a/include/mysql/plugin.h
++++ b/include/mysql/plugin.h
+@@ -639,6 +639,12 @@
  */
  void thd_set_ha_data(MYSQL_THD thd, const struct handlerton *hton,
                       const void *ha_data);
@@ -21,10 +20,9 @@
  #ifdef __cplusplus
  }
  #endif
-diff -ruN a/include/mysql/plugin_audit.h.pp b/include/mysql/plugin_audit.h.pp
---- a/include/mysql/plugin_audit.h.pp	2011-06-22 01:42:39.000000000 +0900
-+++ b/include/mysql/plugin_audit.h.pp	2011-07-13 00:53:06.000000000 +0900
-@@ -195,6 +195,9 @@
+--- a/include/mysql/plugin_audit.h.pp
++++ b/include/mysql/plugin_audit.h.pp
+@@ -205,6 +205,9 @@
  void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
  void thd_set_ha_data(void* thd, const struct handlerton *hton,
                       const void *ha_data);
@@ -34,10 +32,9 @@
  struct mysql_event_general
  {
    unsigned int event_subclass;
-diff -ruN a/include/mysql/plugin_auth.h.pp b/include/mysql/plugin_auth.h.pp
---- a/include/mysql/plugin_auth.h.pp	2011-06-22 01:42:39.000000000 +0900
-+++ b/include/mysql/plugin_auth.h.pp	2011-07-13 00:53:06.000000000 +0900
-@@ -195,6 +195,9 @@
+--- a/include/mysql/plugin_auth.h.pp
++++ b/include/mysql/plugin_auth.h.pp
+@@ -205,6 +205,9 @@
  void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
  void thd_set_ha_data(void* thd, const struct handlerton *hton,
                       const void *ha_data);
@@ -47,10 +44,9 @@
  #include <mysql/plugin_auth_common.h>
  typedef struct st_plugin_vio_info
  {
-diff -ruN a/include/mysql/plugin_ftparser.h.pp b/include/mysql/plugin_ftparser.h.pp
---- a/include/mysql/plugin_ftparser.h.pp	2011-06-22 01:42:39.000000000 +0900
-+++ b/include/mysql/plugin_ftparser.h.pp	2011-07-13 00:53:06.000000000 +0900
-@@ -148,6 +148,9 @@
+--- a/include/mysql/plugin_ftparser.h.pp
++++ b/include/mysql/plugin_ftparser.h.pp
+@@ -158,6 +158,9 @@
  void *thd_get_ha_data(const void* thd, const struct handlerton *hton);
  void thd_set_ha_data(void* thd, const struct handlerton *hton,
                       const void *ha_data);
@@ -60,10 +56,9 @@
  enum enum_ftparser_mode
  {
    MYSQL_FTPARSER_SIMPLE_MODE= 0,
-diff -ruN a/sql/sql_class.cc b/sql/sql_class.cc
---- a/sql/sql_class.cc	2011-06-22 01:42:40.000000000 +0900
-+++ b/sql/sql_class.cc	2011-07-12 22:23:35.000000000 +0900
-@@ -711,6 +711,26 @@
+--- a/sql/sql_class.cc
++++ b/sql/sql_class.cc
+@@ -744,6 +744,26 @@
    return buffer;
  }
  
@@ -90,10 +85,9 @@
  
  /**
    Implementation of Drop_table_error_handler::handle_condition().
-diff -ruN a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
---- a/storage/innobase/handler/ha_innodb.cc	2011-07-12 17:10:37.000000000 +0900
-+++ b/storage/innobase/handler/ha_innodb.cc	2011-07-12 22:33:06.000000000 +0900
-@@ -2828,6 +2828,10 @@
+--- a/storage/innobase/handler/ha_innodb.cc
++++ b/storage/innobase/handler/ha_innodb.cc
+@@ -2827,6 +2827,10 @@
  
  	innobase_commit_concurrency_init_default();
  
@@ -104,7 +98,7 @@
  #ifdef HAVE_PSI_INTERFACE
  	/* Register keys with MySQL performance schema */
  	if (PSI_server) {
-@@ -11586,6 +11590,48 @@
+@@ -11585,6 +11589,48 @@
  	return(false);
  }
  
@@ -153,7 +147,7 @@
  static SHOW_VAR innodb_status_variables_export[]= {
    {"Innodb",                   (char*) &show_innodb_vars, SHOW_FUNC},
    {NullS, NullS, SHOW_LONG}
-@@ -11872,6 +11918,15 @@
+@@ -11871,6 +11917,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);
  
@@ -169,7 +163,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.",
-@@ -12162,6 +12217,7 @@
+@@ -12161,6 +12216,7 @@
    MYSQL_SYSVAR(fast_checksum),
    MYSQL_SYSVAR(commit_concurrency),
    MYSQL_SYSVAR(concurrency_tickets),
@@ -177,9 +171,8 @@
    MYSQL_SYSVAR(data_file_path),
    MYSQL_SYSVAR(doublewrite_file),
    MYSQL_SYSVAR(data_home_dir),
-diff -ruN a/storage/innobase/include/srv0srv.h b/storage/innobase/include/srv0srv.h
---- a/storage/innobase/include/srv0srv.h	2011-07-12 17:10:37.000000000 +0900
-+++ b/storage/innobase/include/srv0srv.h	2011-07-12 22:33:57.000000000 +0900
+--- a/storage/innobase/include/srv0srv.h
++++ b/storage/innobase/include/srv0srv.h
 @@ -291,6 +291,7 @@
  extern ulint	srv_activity_count;
  extern ulint	srv_fatal_semaphore_wait_threshold;
@@ -188,9 +181,8 @@
  
  extern mutex_t*	kernel_mutex_temp;/* mutex protecting the server, trx structs,
  				query threads, and lock table: we allocate
-diff -ruN a/storage/innobase/include/trx0trx.h b/storage/innobase/include/trx0trx.h
---- a/storage/innobase/include/trx0trx.h	2011-07-12 16:47:24.000000000 +0900
-+++ b/storage/innobase/include/trx0trx.h	2011-07-12 22:35:39.000000000 +0900
+--- a/storage/innobase/include/trx0trx.h
++++ b/storage/innobase/include/trx0trx.h
 @@ -594,6 +594,8 @@
  					replication has processed */
  	const char*	mysql_relay_log_file_name;
@@ -200,9 +192,8 @@
  	/*------------------------------*/
  	ulint		n_mysql_tables_in_use; /* number of Innobase tables
  					used in the processing of the current
-diff -ruN a/storage/innobase/srv/srv0srv.c b/storage/innobase/srv/srv0srv.c
---- a/storage/innobase/srv/srv0srv.c	2011-07-12 17:10:37.000000000 +0900
-+++ b/storage/innobase/srv/srv0srv.c	2011-07-12 22:43:22.000000000 +0900
+--- a/storage/innobase/srv/srv0srv.c
++++ b/storage/innobase/srv/srv0srv.c
 @@ -87,6 +87,11 @@
  #include "mysql/plugin.h"
  #include "mysql/service_thd_wait.h"
@@ -262,9 +253,8 @@
  	/* Flush stderr so that a database user gets the output
  	to possible MySQL error file */
  
-diff -ruN a/storage/innobase/trx/trx0trx.c b/storage/innobase/trx/trx0trx.c
---- a/storage/innobase/trx/trx0trx.c	2011-07-12 17:05:25.000000000 +0900
-+++ b/storage/innobase/trx/trx0trx.c	2011-07-12 22:44:08.000000000 +0900
+--- a/storage/innobase/trx/trx0trx.c
++++ b/storage/innobase/trx/trx0trx.c
 @@ -143,6 +143,9 @@
  	trx->mysql_relay_log_file_name = "";
  	trx->mysql_relay_log_pos = 0;
@@ -275,3 +265,165 @@
  	mutex_create(trx_undo_mutex_key, &trx->undo_mutex, SYNC_TRX_UNDO);
  
  	trx->rseg = NULL;
+--- /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';
+--- /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/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/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/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

=== modified file 'patches/mysql-test.diff'
--- patches/mysql-test.diff	2011-08-04 21:14:39 +0000
+++ patches/mysql-test.diff	2011-08-21 21:29:24 +0000
@@ -1260,17 +1260,19 @@
  delete from t2 where variable_name='innodb_change_buffering_debug';
  update t2 set variable_name= replace(variable_name, "PERFORMANCE_SCHEMA_", "PFS_");
  select variable_name as `There should be *no* long test name listed below:` from t2
-@@ -11,13 +11,13 @@
+@@ -11,13 +11,15 @@
  select variable_name as `There should be *no* variables listed below:` from t2
  left join t1 on variable_name=test_name where test_name is null;
  There should be *no* variables listed below:
 -INNODB_ROLLBACK_SEGMENTS
 -INNODB_STATS_METHOD
  INNODB_FILE_FORMAT_MAX
++INNODB_KILL_IDLE_TRANSACTION
  INNODB_LARGE_PREFIX
  INNODB_ROLLBACK_SEGMENTS
  INNODB_STATS_METHOD
  INNODB_FILE_FORMAT_MAX
++INNODB_KILL_IDLE_TRANSACTION
  INNODB_LARGE_PREFIX
 +INNODB_ROLLBACK_SEGMENTS
 +INNODB_STATS_METHOD

=== modified file 'patches/series'
--- patches/series	2011-08-04 17:53:32 +0000
+++ patches/series	2011-08-21 21:29:24 +0000
@@ -53,3 +53,4 @@
 utf8_general50_ci.patch
 bug813587.patch
 valgrind_zlib_suppression.patch
+innodb_kill_idle_transaction.patch