From 391ea219c21a66e24ea9985ca2581f75bbd7c8f5 Mon Sep 17 00:00:00 2001 From: Annamalai Gurusami Date: Thu, 10 May 2012 10:18:31 +0530 Subject: 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 --- .../suite/innodb/r/innodb_bug14007649.result | 56 +++++++++++++++++++++ mysql-test/suite/innodb/t/innodb_bug14007649.test | 58 ++++++++++++++++++++++ .../innodb_plugin/r/innodb_bug14007649.result | 56 +++++++++++++++++++++ .../suite/innodb_plugin/t/innodb_bug14007649.test | 58 ++++++++++++++++++++++ 4 files changed, 228 insertions(+) create mode 100644 mysql-test/suite/innodb/r/innodb_bug14007649.result create mode 100644 mysql-test/suite/innodb/t/innodb_bug14007649.test create mode 100644 mysql-test/suite/innodb_plugin/r/innodb_bug14007649.result create mode 100644 mysql-test/suite/innodb_plugin/t/innodb_bug14007649.test (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb_bug14007649.result b/mysql-test/suite/innodb/r/innodb_bug14007649.result new file mode 100644 index 00000000000..1f802dcd146 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb_bug14007649.result @@ -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; diff --git a/mysql-test/suite/innodb/t/innodb_bug14007649.test b/mysql-test/suite/innodb/t/innodb_bug14007649.test new file mode 100644 index 00000000000..2832bd41f3c --- /dev/null +++ b/mysql-test/suite/innodb/t/innodb_bug14007649.test @@ -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; diff --git a/mysql-test/suite/innodb_plugin/r/innodb_bug14007649.result b/mysql-test/suite/innodb_plugin/r/innodb_bug14007649.result new file mode 100644 index 00000000000..1f802dcd146 --- /dev/null +++ b/mysql-test/suite/innodb_plugin/r/innodb_bug14007649.result @@ -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; diff --git a/mysql-test/suite/innodb_plugin/t/innodb_bug14007649.test b/mysql-test/suite/innodb_plugin/t/innodb_bug14007649.test new file mode 100644 index 00000000000..e1d27ecd742 --- /dev/null +++ b/mysql-test/suite/innodb_plugin/t/innodb_bug14007649.test @@ -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; -- cgit v1.2.1