From: Annamalai Gurusami Date: May 10 2012 6:11am Subject: bzr push into mysql-5.1 branch (annamalai.gurusami:3732 to 3733) Bug#14007649 List-Archive: http://lists.mysql.com/commits/144170 X-Bug: 14007649 Message-Id: <201205100611.q4A6BNQ8003424@acsmt356.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3733 Annamalai Gurusami 2012-05-10 Bug #14007649 65111: INNODB SOMETIMES FAILS TO UPDATE ROWS INSERTED BY A CONCURRENT TRANSACTIO The member function QUICK_RANGE_SELECT::init_ror_merged_scan() performs a table handler clone. Innodb does not provide a clone operation. The ha_innobase::clone() is not there. The handler::clone() does not take care of the ha_innobase->prebuilt->select_lock_type. Because of this what happens is that for one index we do a locking read, and for the other index we were doing a non-locking (consistent) read. The patch introduces ha_innobase::clone() member function. It is implemented similar to ha_myisam::clone(). It calls the base class handler::clone() and then does any additional operation required. I am setting the ha_innobase->prebuilt->select_lock_type correctly. rb://1060 approved by Marko added: mysql-test/suite/innodb/r/innodb_bug14007649.result mysql-test/suite/innodb/t/innodb_bug14007649.test mysql-test/suite/innodb_plugin/r/innodb_bug14007649.result mysql-test/suite/innodb_plugin/t/innodb_bug14007649.test modified: storage/innobase/btr/btr0cur.c storage/innobase/handler/ha_innodb.cc storage/innobase/handler/ha_innodb.h storage/innodb_plugin/btr/btr0cur.c storage/innodb_plugin/handler/ha_innodb.cc storage/innodb_plugin/handler/ha_innodb.h 3732 Sunanda Menon 2012-05-08 [merge] Merge from mysql-5.1.63-release added: mysql-test/suite/innodb/r/innodb_bug13635833.result mysql-test/suite/innodb/t/innodb_bug13635833.test mysql-test/suite/innodb_plugin/r/innodb_bug13635833.result mysql-test/suite/innodb_plugin/t/innodb_bug13635833.test modified: include/my_base.h include/violite.h mysql-test/r/cast.result mysql-test/r/errors.result mysql-test/r/gis.result mysql-test/r/subselect_innodb.result mysql-test/t/cast.test mysql-test/t/errors.test mysql-test/t/gis.test mysql-test/t/subselect_innodb.test mysys/my_handler_errors.h sql/handler.cc sql/item.cc sql/item_subselect.cc sql/mysqld.cc sql/password.c sql/spatial.cc sql/sql_select.cc storage/innobase/dict/dict0dict.c storage/innobase/handler/ha_innodb.cc storage/innobase/include/db0err.h storage/innobase/row/row0ins.c storage/innobase/row/row0mysql.c storage/innodb_plugin/dict/dict0dict.c storage/innodb_plugin/handler/ha_innodb.cc storage/innodb_plugin/include/db0err.h storage/innodb_plugin/row/row0ins.c storage/innodb_plugin/row/row0mysql.c vio/viosocket.c === added file 'mysql-test/suite/innodb/r/innodb_bug14007649.result' --- a/mysql-test/suite/innodb/r/innodb_bug14007649.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/innodb/r/innodb_bug14007649.result revid:annamalai.gurusami@stripped @@ -0,0 +1,56 @@ +create table t1 ( +rowid int, +f1 int, +f2 int, +key i1 (f1, f2), +key i2 (f2)) engine=innodb; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `rowid` int(11) DEFAULT NULL, + `f1` int(11) DEFAULT NULL, + `f2` int(11) DEFAULT NULL, + KEY `i1` (`f1`,`f2`), + KEY `i2` (`f2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +insert into `t1` (rowid, f1, f2) values (1, 1, 10), (2, 1, NULL); +start transaction with consistent snapshot; +start transaction; +update t1 set f2 = 4 where f1 = 1 and f2 is null; +(b) Number of rows updated: +select row_count(); +row_count() +1 +insert into t1 values (3, 1, null); +(b) After update and insert query. +select rowid, f1, f2 from t1; +rowid f1 f2 +1 1 10 +2 1 4 +3 1 NULL +commit; +(a) Before the update statement is executed. +select rowid, f1, f2 from t1; +rowid f1 f2 +1 1 10 +2 1 NULL +SET SESSION debug="+d,bug14007649"; +update t1 set f2 = 6 where f1 = 1 and f2 is null; +(a) Number of rows updated: +select row_count(); +row_count() +1 +(a) After the update statement is executed. +select rowid, f1, f2 from t1; +rowid f1 f2 +1 1 10 +2 1 NULL +3 1 6 +commit; +"The trx with consistent snapshot ended." +select rowid, f1, f2 from t1; +rowid f1 f2 +1 1 10 +2 1 4 +3 1 6 +drop table t1; === added file 'mysql-test/suite/innodb/t/innodb_bug14007649.test' --- a/mysql-test/suite/innodb/t/innodb_bug14007649.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/innodb/t/innodb_bug14007649.test revid:annamalai.gurusami@stripped @@ -0,0 +1,58 @@ +--source include/have_innodb.inc +--source include/have_debug.inc + +create table t1 ( + rowid int, + f1 int, + f2 int, + key i1 (f1, f2), + key i2 (f2)) engine=innodb; + +show create table t1; +insert into `t1` (rowid, f1, f2) values (1, 1, 10), (2, 1, NULL); + +connect (a,localhost,root,,); +connect (b,localhost,root,,); + +connection a; +start transaction with consistent snapshot; + +connection b; +start transaction; +update t1 set f2 = 4 where f1 = 1 and f2 is null; + +-- echo (b) Number of rows updated: +select row_count(); + +insert into t1 values (3, 1, null); + +-- echo (b) After update and insert query. +select rowid, f1, f2 from t1; + +commit; + +connection a; + +-- echo (a) Before the update statement is executed. +select rowid, f1, f2 from t1; + +SET SESSION debug="+d,bug14007649"; +update t1 set f2 = 6 where f1 = 1 and f2 is null; + +-- echo (a) Number of rows updated: +select row_count(); + +-- echo (a) After the update statement is executed. +select rowid, f1, f2 from t1; + +commit; + +--echo "The trx with consistent snapshot ended." + +select rowid, f1, f2 from t1; + +connection default; +disconnect a; +disconnect b; + +drop table t1; === added file 'mysql-test/suite/innodb_plugin/r/innodb_bug14007649.result' --- a/mysql-test/suite/innodb_plugin/r/innodb_bug14007649.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/innodb_plugin/r/innodb_bug14007649.result revid:annamalai.gurusami@stripped @@ -0,0 +1,56 @@ +create table t1 ( +rowid int, +f1 int, +f2 int, +key i1 (f1, f2), +key i2 (f2)) engine=innodb; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `rowid` int(11) DEFAULT NULL, + `f1` int(11) DEFAULT NULL, + `f2` int(11) DEFAULT NULL, + KEY `i1` (`f1`,`f2`), + KEY `i2` (`f2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +insert into `t1` (rowid, f1, f2) values (1, 1, 10), (2, 1, NULL); +start transaction with consistent snapshot; +start transaction; +update t1 set f2 = 4 where f1 = 1 and f2 is null; +(b) Number of rows updated: +select row_count(); +row_count() +1 +insert into t1 values (3, 1, null); +(b) After update and insert query. +select rowid, f1, f2 from t1; +rowid f1 f2 +1 1 10 +2 1 4 +3 1 NULL +commit; +(a) Before the update statement is executed. +select rowid, f1, f2 from t1; +rowid f1 f2 +1 1 10 +2 1 NULL +SET SESSION debug="+d,bug14007649"; +update t1 set f2 = 6 where f1 = 1 and f2 is null; +(a) Number of rows updated: +select row_count(); +row_count() +1 +(a) After the update statement is executed. +select rowid, f1, f2 from t1; +rowid f1 f2 +1 1 10 +2 1 NULL +3 1 6 +commit; +"The trx with consistent snapshot ended." +select rowid, f1, f2 from t1; +rowid f1 f2 +1 1 10 +2 1 4 +3 1 6 +drop table t1; === added file 'mysql-test/suite/innodb_plugin/t/innodb_bug14007649.test' --- a/mysql-test/suite/innodb_plugin/t/innodb_bug14007649.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug14007649.test revid:annamalai.gurusami@stripped @@ -0,0 +1,58 @@ +--source include/have_innodb_plugin.inc +--source include/have_debug.inc + +create table t1 ( + rowid int, + f1 int, + f2 int, + key i1 (f1, f2), + key i2 (f2)) engine=innodb; + +show create table t1; +insert into `t1` (rowid, f1, f2) values (1, 1, 10), (2, 1, NULL); + +connect (a,localhost,root,,); +connect (b,localhost,root,,); + +connection a; +start transaction with consistent snapshot; + +connection b; +start transaction; +update t1 set f2 = 4 where f1 = 1 and f2 is null; + +-- echo (b) Number of rows updated: +select row_count(); + +insert into t1 values (3, 1, null); + +-- echo (b) After update and insert query. +select rowid, f1, f2 from t1; + +commit; + +connection a; + +-- echo (a) Before the update statement is executed. +select rowid, f1, f2 from t1; + +SET SESSION debug="+d,bug14007649"; +update t1 set f2 = 6 where f1 = 1 and f2 is null; + +-- echo (a) Number of rows updated: +select row_count(); + +-- echo (a) After the update statement is executed. +select rowid, f1, f2 from t1; + +commit; + +--echo "The trx with consistent snapshot ended." + +select rowid, f1, f2 from t1; + +connection default; +disconnect a; +disconnect b; + +drop table t1; === modified file 'storage/innobase/btr/btr0cur.c' --- a/storage/innobase/btr/btr0cur.c revid:sunanda.menon@stripped +++ b/storage/innobase/btr/btr0cur.c revid:annamalai.gurusami@stripped @@ -2792,6 +2792,8 @@ btr_estimate_n_rows_in_range( n_rows = n_rows * 2; } + DBUG_EXECUTE_IF("bug14007649", return(n_rows);); + /* Do not estimate the number of rows in the range to over 1 / 2 of the estimated rows in the whole table */ === modified file 'storage/innobase/handler/ha_innodb.cc' --- a/storage/innobase/handler/ha_innodb.cc revid:sunanda.menon@stripped +++ b/storage/innobase/handler/ha_innodb.cc revid:annamalai.gurusami@stripped @@ -3180,6 +3180,30 @@ table_opened: DBUG_RETURN(0); } +handler* +ha_innobase::clone( +/*===============*/ + const char* name, /*!< in: table name */ + MEM_ROOT* mem_root) /*!< in: memory context */ +{ + ha_innobase* new_handler; + + DBUG_ENTER("ha_innobase::clone"); + + new_handler = static_cast(handler::clone(name, + mem_root)); + if (new_handler) { + DBUG_ASSERT(new_handler->prebuilt != NULL); + DBUG_ASSERT(new_handler->user_thd == user_thd); + DBUG_ASSERT(new_handler->prebuilt->trx == prebuilt->trx); + + new_handler->prebuilt->select_lock_type + = prebuilt->select_lock_type; + } + + DBUG_RETURN(new_handler); +} + uint ha_innobase::max_supported_key_part_length() const { === modified file 'storage/innobase/handler/ha_innodb.h' --- a/storage/innobase/handler/ha_innodb.h revid:sunanda.menon@stripped +++ b/storage/innobase/handler/ha_innodb.h revid:annamalai.gurusami@stripped @@ -117,6 +117,7 @@ class ha_innobase: public handler const key_map *keys_to_use_for_scanning() { return &key_map_full; } int open(const char *name, int mode, uint test_if_locked); + handler* clone(const char *name, MEM_ROOT *mem_root); int close(void); double scan_time(); double read_time(uint index, uint ranges, ha_rows rows); === modified file 'storage/innodb_plugin/btr/btr0cur.c' --- a/storage/innodb_plugin/btr/btr0cur.c revid:sunanda.menon@stripped +++ b/storage/innodb_plugin/btr/btr0cur.c revid:annamalai.gurusami@stripped @@ -3194,6 +3194,8 @@ btr_estimate_n_rows_in_range( n_rows = n_rows * 2; } + DBUG_EXECUTE_IF("bug14007649", return(n_rows);); + /* Do not estimate the number of rows in the range to over 1 / 2 of the estimated rows in the whole table */ === modified file 'storage/innodb_plugin/handler/ha_innodb.cc' --- a/storage/innodb_plugin/handler/ha_innodb.cc revid:sunanda.menon@stripped +++ b/storage/innodb_plugin/handler/ha_innodb.cc revid:annamalai.gurusami@stripped @@ -3890,6 +3890,31 @@ table_opened: } UNIV_INTERN +handler* +ha_innobase::clone( +/*===============*/ + const char* name, /*!< in: table name */ + MEM_ROOT* mem_root) /*!< in: memory context */ +{ + ha_innobase* new_handler; + + DBUG_ENTER("ha_innobase::clone"); + + new_handler = static_cast(handler::clone(name, + mem_root)); + if (new_handler) { + DBUG_ASSERT(new_handler->prebuilt != NULL); + DBUG_ASSERT(new_handler->user_thd == user_thd); + DBUG_ASSERT(new_handler->prebuilt->trx == prebuilt->trx); + + new_handler->prebuilt->select_lock_type + = prebuilt->select_lock_type; + } + + DBUG_RETURN(new_handler); +} + +UNIV_INTERN uint ha_innobase::max_supported_key_part_length() const { === modified file 'storage/innodb_plugin/handler/ha_innodb.h' --- a/storage/innodb_plugin/handler/ha_innodb.h revid:sunanda.menon@stripped +++ b/storage/innodb_plugin/handler/ha_innodb.h revid:annamalai.gurusami@stripped @@ -132,6 +132,7 @@ class ha_innobase: public handler const key_map* keys_to_use_for_scanning(); int open(const char *name, int mode, uint test_if_locked); + handler* clone(const char *name, MEM_ROOT *mem_root); int close(void); double scan_time(); double read_time(uint index, uint ranges, ha_rows rows); No bundle (reason: useless for push emails).