From: Date: October 22 2008 1:04am Subject: bzr commit into mysql-5.1 tree (frazer:2720) Bug#39295 List-Archive: http://lists.mysql.com/commits/56765 X-Bug: 39295 Message-Id: <200810212304.m9LN43a3019322@forth.ndb.mysql.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="===============0693428617==" --===============0693428617== MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline #At file:///home/frazer/bzr/mysql-5.1-telco-6.3/ ------------------------------------------------------------ revno: 2720 revision-id: frazer@stripped parent: jonas@stripped parent: frazer@stripped committer: Frazer Clement branch nick: mysql-5.1-telco-6.3 timestamp: Wed 2008-10-22 00:03:34 +0100 message: Merge 6.2 -> 6.3 modified: mysql-test/suite/ndb/r/ndb_cache.result sp1f-ndb_cache.result-20040505082127-7f3odnak2v4dmp7dmov5mmrupaikeyo6 mysql-test/suite/ndb/t/ndb_cache.test sp1f-ndb_cache.test-20040505082127-skjoypw7pwzs55cz5haz4svzk3dfvtcp mysql-test/suite/ndb/t/ndb_cache2.test sp1f-ndb_cache2.test-20050201144302-xdkoatxps766l7aczrr2mullhyb346sz storage/ndb/src/kernel/blocks/dbacc/DbaccMain.cpp sp1f-dbaccmain.cpp-20040414082418-khzyzke4vwea2aiengvt2htq7ubfqssz ------------------------------------------------------------ revno: 2572.34.10 revision-id: frazer@stripped parent: jonas@stripped committer: Frazer Clement branch nick: mysql-5.1-telco-6.2 timestamp: Tue 2008-10-21 23:53:32 +0100 message: Bug#39295 cluster table with TEXT column cannot be cached in query cache Read of Text column resulted in lock upgrade to read/shared. Since rows are disk based, TUP-order scan is chosen TUP and TUX ordered scans with locks have unwanted side-effect of incrementing fragment commit_count when locks are freed. This results in query cache invalidation. Fix is to remove unwanted fragment commit_count increment for TUP + TUX ordered scans. modified: mysql-test/suite/ndb/r/ndb_cache.result sp1f-ndb_cache.result-20040505082127-7f3odnak2v4dmp7dmov5mmrupaikeyo6 mysql-test/suite/ndb/t/ndb_cache.test sp1f-ndb_cache.test-20040505082127-skjoypw7pwzs55cz5haz4svzk3dfvtcp mysql-test/suite/ndb/t/ndb_cache2.test sp1f-ndb_cache2.test-20050201144302-xdkoatxps766l7aczrr2mullhyb346sz storage/ndb/src/kernel/blocks/dbacc/DbaccMain.cpp sp1f-dbaccmain.cpp-20040414082418-khzyzke4vwea2aiengvt2htq7ubfqssz --===============0693428617== MIME-Version: 1.0 Content-Type: text/text/x-diff; charset="us-ascii"; name="patch-2720.diff" Content-Transfer-Encoding: 7bit Content-Disposition: inline === modified file 'mysql-test/suite/ndb/r/ndb_cache.result' --- a/mysql-test/suite/ndb/r/ndb_cache.result 2007-06-27 12:28:02 +0000 +++ b/mysql-test/suite/ndb/r/ndb_cache.result 2008-10-21 22:53:32 +0000 @@ -188,4 +188,183 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +reset query cache; +flush status; +create table t1 (a int primary key, +b text, +c int, +key(c)) +engine=ndb; +insert into t1 values (1,'Alexandra', 1), (2,'Palace', 2); +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select * from t1 order by a; +a b c +1 Alexandra 1 +2 Palace 2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select * from t1 order by a; +a b c +1 Alexandra 1 +2 Palace 2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select * from t1 force index(c) where c < 2 order by c; +a b c +1 Alexandra 1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select * from t1 force index(c) where c < 2 order by c; +a b c +1 Alexandra 1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +drop table t1; +CREATE LOGFILE GROUP lfg +ADD UNDOFILE 'myundo.log' +INITIAL_SIZE 16M +UNDO_BUFFER_SIZE = 1M +ENGINE=NDB; +CREATE TABLESPACE tbsp +ADD DATAFILE 'mydatafile.fil' +USE LOGFILE GROUP lfg +INITIAL_SIZE 12M +ENGINE=NDB; +create table t1 (a int primary key, +b text, +c int, +key(c)) +storage disk tablespace tbsp engine ndb; +insert into t1 values (1,'Alexandra', 1), (2,'Palace', 2); +reset query cache; +flush status; +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 0 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 0 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select * from t1 order by a; +a b c +1 Alexandra 1 +2 Palace 2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 0 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select * from t1 order by a; +a b c +1 Alexandra 1 +2 Palace 2 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 1 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 1 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select * from t1 force index (c) where c < 2 order by c; +a b c +1 Alexandra 1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 1 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +select * from t1 force index (c) where c < 2 order by c; +a b c +1 Alexandra 1 +show status like "Qcache_queries_in_cache"; +Variable_name Value +Qcache_queries_in_cache 2 +show status like "Qcache_inserts"; +Variable_name Value +Qcache_inserts 2 +show status like "Qcache_hits"; +Variable_name Value +Qcache_hits 2 +show status like "Qcache_not_cached"; +Variable_name Value +Qcache_not_cached 0 +drop table t1; +alter tablespace tbsp drop datafile 'mydatafile.fil' engine ndb; +drop tablespace tbsp engine ndb; +drop logfile group lfg engine ndb; SET GLOBAL query_cache_size=0; === modified file 'mysql-test/suite/ndb/t/ndb_cache.test' --- a/mysql-test/suite/ndb/t/ndb_cache.test 2007-11-29 10:29:35 +0000 +++ b/mysql-test/suite/ndb/t/ndb_cache.test 2008-10-21 23:03:34 +0000 @@ -116,6 +116,132 @@ show status like "Qcache_queries_in_cache"; +# End of 4.1 tests + +# Bug#39295 cluster table with TEXT column cannot be cached in query cache +# Disk table scans and Ordered index scans with locks were resulting +# in the table's commit_count being incremented, making the query cache +# ineffective + +reset query cache; +flush status; + +create table t1 (a int primary key, + b text, + c int, + key(c)) + engine=ndb; + +insert into t1 values (1,'Alexandra', 1), (2,'Palace', 2); + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# Normal, ACC sourced scan #1, should insert in cache +select * from t1 order by a; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# Normal, ACC sourced scan #2, should hit in cache +select * from t1 order by a; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# TUX sourced scan #1, should insert in cache +select * from t1 force index(c) where c < 2 order by c; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# TUX sourced scan #2, should hit in cache +select * from t1 force index(c) where c < 2 order by c; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +drop table t1; + + +# Now test with TUP-sourced scan with DD +CREATE LOGFILE GROUP lfg +ADD UNDOFILE 'myundo.log' +INITIAL_SIZE 16M +UNDO_BUFFER_SIZE = 1M +ENGINE=NDB; + +CREATE TABLESPACE tbsp +ADD DATAFILE 'mydatafile.fil' +USE LOGFILE GROUP lfg +INITIAL_SIZE 12M +ENGINE=NDB; + +create table t1 (a int primary key, + b text, + c int, + key(c)) + storage disk tablespace tbsp engine ndb; + +insert into t1 values (1,'Alexandra', 1), (2,'Palace', 2); + +reset query cache; +flush status; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# Normal, TUP sourced scan #1, should insert in cache +select * from t1 order by a; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# Normal, TUP sourced scan #2, should hit in cache +select * from t1 order by a; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# TUX sourced scan #1 should insert in cache +select * from t1 force index (c) where c < 2 order by c; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +# TUX sourced scan #2 should hit in cache +select * from t1 force index (c) where c < 2 order by c; + +show status like "Qcache_queries_in_cache"; +show status like "Qcache_inserts"; +show status like "Qcache_hits"; +show status like "Qcache_not_cached"; + +drop table t1; +alter tablespace tbsp drop datafile 'mydatafile.fil' engine ndb; +drop tablespace tbsp engine ndb; +drop logfile group lfg engine ndb; + +# end of tests for bug#39395 + SET GLOBAL query_cache_size=0; -# End of 4.1 tests + === modified file 'mysql-test/suite/ndb/t/ndb_cache2.test' --- a/mysql-test/suite/ndb/t/ndb_cache2.test 2007-11-29 10:29:35 +0000 +++ b/mysql-test/suite/ndb/t/ndb_cache2.test 2008-10-21 23:03:34 +0000 @@ -62,6 +62,9 @@ # Update the table and make sure the correct data is returned update t1 set a1=3 where pk=1; +# Sleep to give Qcache thread time to detect change +--real_sleep 1 + select * from t1; show status like "Qcache_inserts"; show status like "Qcache_hits"; @@ -69,6 +72,9 @@ # Insert a new record and make sure the correct data is returned insert into t1 value (2, 7, 8, 'Second row'); insert into t1 value (4, 5, 6, 'Fourth row'); +# Sleep to give Qcache thread time to detect change +--real_sleep 1 + select * from t1 order by pk desc; show status like "Qcache_inserts"; show status like "Qcache_hits"; @@ -86,6 +92,8 @@ # Delete from the table delete from t1 where c1='Fourth row'; +# Sleep to give Qcache thread time to detect change +--real_sleep 1 show status like "Qcache_queries_in_cache"; select * from t1 where b1=3; show status like "Qcache_hits"; @@ -100,6 +108,9 @@ # Update the table and switch to other connection update t1 set a1=4 where b1=3; +# Sleep to give Qcache thread time to detect change +--real_sleep 1 + connect (con2,localhost,root,,); connection con2; use test; @@ -132,6 +143,8 @@ flush status; begin; update t1 set a1=5 where pk=1; +# Sleep to give Qcache thread time to detect change +--real_sleep 1 show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; @@ -165,6 +178,8 @@ flush status; begin; update t1 set a1=6 where pk=1; +# Sleep to give Qcache thread time to detect change +--real_sleep 1 show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; @@ -206,6 +221,8 @@ flush status; begin; insert into t1 set pk=5, a1=6, b1=3, c1="New row"; +# Sleep to give Qcache thread time to detect change +--real_sleep 1 show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; @@ -244,6 +261,8 @@ flush status; begin; delete from t1 where pk=2; +# Sleep to give Qcache thread time to detect change +--real_sleep 1 show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; @@ -286,6 +305,8 @@ update t3 set a3=9 where pk=1; update t4 set a4=9 where a4=2; update t5 set a5=9 where pk=1; +# Sleep to give Qcache thread time to detect change +--real_sleep 1 show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; === modified file 'storage/ndb/src/kernel/blocks/dbacc/DbaccMain.cpp' --- a/storage/ndb/src/kernel/blocks/dbacc/DbaccMain.cpp 2008-05-29 15:58:58 +0000 +++ b/storage/ndb/src/kernel/blocks/dbacc/DbaccMain.cpp 2008-10-21 22:53:32 +0000 @@ -2329,7 +2329,9 @@ ndbassert(operationRecPtr.i == tmp); ndbassert(operationRecPtr.p == ptr); operationRecPtr.p->m_op_bits = Operationrec::OP_INITIAL; - if(Toperation != ZREAD){ + if((Toperation != ZREAD) && + (Toperation != ZSCAN_OP)) + { fragrecptr.p->m_commit_count++; if (Toperation != ZINSERT) { if (Toperation != ZDELETE) { --===============0693428617==--