From: Date: May 28 2008 5:11pm Subject: commit into mysql-5.1 branch (msvensson:2654) Bug#36692 List-Archive: http://lists.mysql.com/commits/47150 X-Bug: 36692 Message-Id: <20080528151122.9633D40C246@shellback.mysql.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit #At file:///home/msvensson/mysql/my51-bug36692/ 2654 Magnus Svensson 2008-05-28 Bug#36692 Query cache only works with cluster if AUTOCOMMIT is active - Allow results to be taken out of the query cache as long as current transaction hasn't changed the table. added: mysql-test/include/show_qc_status.inc mysql-test/suite/ndb/r/ndb_cache_trans.result mysql-test/suite/ndb/t/ndb_cache_trans.test modified: sql/ha_ndbcluster.cc per-file comments: mysql-test/include/show_qc_status.inc New utility include file for displaying the status of query cache mysql-test/suite/ndb/r/ndb_cache_trans.result Results mysql-test/suite/ndb/t/ndb_cache_trans.test Tests sql/ha_ndbcluster.cc Add all changed tables to thd_ndb's changed_table list, then use that list to check if a result is allowed to be taken out of the cache while a transaction is ongoing === added file 'mysql-test/include/show_qc_status.inc' --- a/mysql-test/include/show_qc_status.inc 1970-01-01 00:00:00 +0000 +++ b/mysql-test/include/show_qc_status.inc 2008-05-28 15:11:14 +0000 @@ -0,0 +1,10 @@ +# +# Retrieve the value of 'show status like "Qcache_[inserts, hits, queries]"' +# and display it in a minimal fashion on one line +# +let $_qcache_inserts= query_get_value(SHOW STATUS LIKE "Qcache_inserts", Value, 1); +let $_qcache_hits= query_get_value(SHOW STATUS LIKE "Qcache_hits", Value, 1); +let $_qcache_queries= query_get_value(SHOW STATUS LIKE "Qcache_queries_in_cache", Value, 1); +echo Qcache_queries $_qcache_queries; +echo Qcache_inserts $_qcache_inserts; +echo Qcache_hits $_qcache_hits; === added file 'mysql-test/suite/ndb/r/ndb_cache_trans.result' --- a/mysql-test/suite/ndb/r/ndb_cache_trans.result 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/r/ndb_cache_trans.result 2008-05-28 15:11:14 +0000 @@ -0,0 +1,317 @@ +result_format: 2 +drop table if exists t1; +set GLOBAL query_cache_type=on; +set GLOBAL query_cache_size=1355776; +reset query cache; +flush status; + +## Turn off autocommit, instead use COMMIT after each statement +set AUTOCOMMIT=off; + +## Create test table in NDB +CREATE TABLE t1 ( + pk int not null primary key, + a int, + b int not null, + c varchar(20) +) ENGINE=ndbcluster; + +## Add first row +insert into t1 value (1, 2, 3, 'First row'); +COMMIT; + +## Query should be inserted in qcache +Qcache_queries 0 +Qcache_inserts 0 +Qcache_hits 0 +select * from t1; +pk a b c +1 2 3 First row +Qcache_queries 1 +Qcache_inserts 1 +Qcache_hits 0 +COMMIT; +Qcache_queries 1 +Qcache_inserts 1 +Qcache_hits 0 + +## Perform the same query and make sure the query cache is hit +Qcache_queries 1 +Qcache_inserts 1 +Qcache_hits 0 +select * from t1; +pk a b c +1 2 3 First row +COMMIT; +Qcache_queries 1 +Qcache_inserts 1 +Qcache_hits 1 + +## Update the table, should be no queries in cache afterwards +update t1 set a=3 where pk=1; +COMMIT; +Qcache_queries 0 +Qcache_inserts 1 +Qcache_hits 1 + +## Read row after update, should not hit the cache, but get inserted +select * from t1; +pk a b c +1 3 3 First row +COMMIT; +Qcache_queries 1 +Qcache_inserts 2 +Qcache_hits 1 + +## Read row from cache +select * from t1; +pk a b c +1 3 3 First row +COMMIT; +Qcache_queries 1 +Qcache_inserts 2 +Qcache_hits 2 + +## Insert two new rows, queries in cache should be zero +insert into t1 value (2, 7, 8, 'Second row'); +insert into t1 value (4, 5, 6, 'Fourth row'); +COMMIT; +Qcache_queries 0 +Qcache_inserts 2 +Qcache_hits 2 + +## Read the three rows, should not hit the cache +select * from t1 order by pk; +pk a b c +1 3 3 First row +2 7 8 Second row +4 5 6 Fourth row +COMMIT; +Qcache_queries 1 +Qcache_inserts 3 +Qcache_hits 2 + +## Read the three rows, should now hit the cache! +select * from t1 order by pk; +pk a b c +1 3 3 First row +2 7 8 Second row +4 5 6 Fourth row +COMMIT; +Qcache_queries 1 +Qcache_inserts 3 +Qcache_hits 3 + +## Two selects in the same transaction should hit cache +select * from t1 order by pk; +pk a b c +1 3 3 First row +2 7 8 Second row +4 5 6 Fourth row +select * from t1 order by pk; +pk a b c +1 3 3 First row +2 7 8 Second row +4 5 6 Fourth row +COMMIT; +Qcache_queries 1 +Qcache_inserts 3 +Qcache_hits 5 + +## Perform a "new" query and make sure the query cache is not hit +select * from t1 where b=3; +pk a b c +1 3 3 First row +COMMIT; +Qcache_queries 2 +Qcache_inserts 4 +Qcache_hits 5 + +## Same query again... +select * from t1 where b=3; +pk a b c +1 3 3 First row +COMMIT; +Qcache_queries 2 +Qcache_inserts 4 +Qcache_hits 6 + +## Delete from the table, should clear the cache +delete from t1 where c='Fourth row'; +COMMIT; +Qcache_queries 0 +Qcache_inserts 4 +Qcache_hits 6 +select * from t1 where b=3; +pk a b c +1 3 3 First row +COMMIT; +Qcache_queries 1 +Qcache_inserts 5 +Qcache_hits 6 + +## Start another connection and check that the query cache is hit +set AUTOCOMMIT=off; +use test; +select * from t1 order by pk; +pk a b c +1 3 3 First row +2 7 8 Second row +select * from t1 where b=3; +pk a b c +1 3 3 First row +Qcache_queries 2 +Qcache_inserts 6 +Qcache_hits 7 + +## Update the table and switch to other connection +update t1 set a=4 where b=3; +COMMIT; + +## Connection 2 +set AUTOCOMMIT=off; +use test; + +## Should not hit cache, table updated +Qcache_queries 0 +Qcache_inserts 6 +Qcache_hits 7 +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 4 3 First row +Qcache_queries 1 +Qcache_inserts 7 +Qcache_hits 7 +## Should hit cache +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 4 3 First row +Qcache_queries 1 +Qcache_inserts 7 +Qcache_hits 8 + +## Connection 1, should hit the cache +Qcache_queries 1 +Qcache_inserts 7 +Qcache_hits 8 +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 4 3 First row +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 4 3 First row +Qcache_queries 1 +Qcache_inserts 7 +Qcache_hits 10 + +## Starting transaction and update t1 +begin; +update t1 set a=5 where pk=1; +Qcache_queries 0 +Qcache_inserts 7 +Qcache_hits 10 + +## Connection 2 +## Update has flushed the qc for t1, should not hit qc +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 4 3 First row +Qcache_queries 1 +Qcache_inserts 8 +Qcache_hits 10 + +## Connection 1 +commit; +Qcache_queries 1 +Qcache_inserts 8 +Qcache_hits 10 + +## Connection 2 +## Update is now committed, should not hit the cache +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 5 3 First row +Qcache_queries 1 +Qcache_inserts 9 +Qcache_hits 10 +COMMIT; +Qcache_queries 1 +Qcache_inserts 9 +Qcache_hits 10 + +## Connection 1 +## Should hit the cache +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 5 3 First row +Qcache_queries 1 +Qcache_inserts 9 +Qcache_hits 11 + +update t1 set a=6 where pk=1; + +## Following query should not be taken from cache, trans is ongoing +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 6 3 First row +Qcache_queries 0 +Qcache_inserts 9 +Qcache_hits 11 + +## Connection 2 should still see old data and not hit cache +Qcache_queries 0 +Qcache_inserts 9 +Qcache_hits 11 +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 5 3 First row +Qcache_queries 1 +Qcache_inserts 10 +Qcache_hits 11 + +## Connection 1 +COMMIT; + +## Update has just been committed, should not hit cache +Qcache_queries 1 +Qcache_inserts 10 +Qcache_hits 11 +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 6 3 First row +Qcache_queries 1 +Qcache_inserts 11 +Qcache_hits 11 + +## Connection 2 + +## Should hit cache +Qcache_queries 1 +Qcache_inserts 11 +Qcache_hits 11 +select * from t1 order by pk desc; +pk a b c +2 7 8 Second row +1 6 3 First row +Qcache_queries 1 +Qcache_inserts 11 +Qcache_hits 12 + +drop table t1; + +## Finally, there should be no queries in cache +Qcache_queries 0 +Qcache_inserts 11 +Qcache_hits 12 + +SET GLOBAL query_cache_size=0; === added file 'mysql-test/suite/ndb/t/ndb_cache_trans.test' --- a/mysql-test/suite/ndb/t/ndb_cache_trans.test 1970-01-01 00:00:00 +0000 +++ b/mysql-test/suite/ndb/t/ndb_cache_trans.test 2008-05-28 15:11:14 +0000 @@ -0,0 +1,200 @@ +-- source include/have_query_cache.inc +-- source include/have_ndb.inc +-- source include/not_embedded.inc + +--result_format 2 + +--disable_warnings +drop table if exists t1; +--enable_warnings + +# Turn on and reset query cache +set GLOBAL query_cache_type=on; +set GLOBAL query_cache_size=1355776; +reset query cache; +flush status; + +## Turn off autocommit, instead use COMMIT after each statement +set AUTOCOMMIT=off; + +## Create test table in NDB +CREATE TABLE t1 ( + pk int not null primary key, + a int, + b int not null, + c varchar(20) +) ENGINE=ndbcluster; + +## Add first row +insert into t1 value (1, 2, 3, 'First row'); +COMMIT; + +## Query should be inserted in qcache +--source include/show_qc_status.inc +select * from t1; +--source include/show_qc_status.inc +COMMIT; +--source include/show_qc_status.inc + +## Perform the same query and make sure the query cache is hit +--source include/show_qc_status.inc +select * from t1; +COMMIT; +--source include/show_qc_status.inc + +## Update the table, should be no queries in cache afterwards +update t1 set a=3 where pk=1; +COMMIT; +--source include/show_qc_status.inc + +## Read row after update, should not hit the cache, but get inserted +select * from t1; +COMMIT; +--source include/show_qc_status.inc + +## Read row from cache +select * from t1; +COMMIT; +--source include/show_qc_status.inc + +## Insert two new rows, queries in cache should be zero +insert into t1 value (2, 7, 8, 'Second row'); +insert into t1 value (4, 5, 6, 'Fourth row'); +COMMIT; +--source include/show_qc_status.inc + +## Read the three rows, should not hit the cache +select * from t1 order by pk; +COMMIT; +--source include/show_qc_status.inc + +## Read the three rows, should now hit the cache! +select * from t1 order by pk; +COMMIT; +--source include/show_qc_status.inc + +## Two selects in the same transaction should hit cache +select * from t1 order by pk; +select * from t1 order by pk; +COMMIT; +--source include/show_qc_status.inc + +## Perform a "new" query and make sure the query cache is not hit +select * from t1 where b=3; +COMMIT; +--source include/show_qc_status.inc + +## Same query again... +select * from t1 where b=3; +COMMIT; +--source include/show_qc_status.inc + +## Delete from the table, should clear the cache +delete from t1 where c='Fourth row'; +COMMIT; +--source include/show_qc_status.inc +select * from t1 where b=3; +COMMIT; +--source include/show_qc_status.inc + +## Start another connection and check that the query cache is hit +connect (con1,localhost,root,,); +connection con1; +set AUTOCOMMIT=off; +use test; +select * from t1 order by pk; +select * from t1 where b=3; +--source include/show_qc_status.inc + +## Update the table and switch to other connection +update t1 set a=4 where b=3; +COMMIT; + +## Connection 2 +connect (con2,localhost,root,,); +connection con2; +set AUTOCOMMIT=off; +use test; + +## Should not hit cache, table updated +--source include/show_qc_status.inc +select * from t1 order by pk desc; +--source include/show_qc_status.inc +## Should hit cache +select * from t1 order by pk desc; +--source include/show_qc_status.inc + +## Connection 1, should hit the cache +connection con1; +--source include/show_qc_status.inc +select * from t1 order by pk desc; +select * from t1 order by pk desc; +--source include/show_qc_status.inc + + +## Starting transaction and update t1 +begin; +update t1 set a=5 where pk=1; +--source include/show_qc_status.inc + +## Connection 2 +connection con2; +## Update has flushed the qc for t1, should not hit qc +select * from t1 order by pk desc; +--source include/show_qc_status.inc + +## Connection 1 +connection con1; +commit; +--source include/show_qc_status.inc + +## Connection 2 +connection con2; +## Update is now committed, should not hit the cache +select * from t1 order by pk desc; +--source include/show_qc_status.inc +COMMIT; +--source include/show_qc_status.inc + +## Connection 1 +connection con1; +## Should hit the cache +select * from t1 order by pk desc; +--source include/show_qc_status.inc + +update t1 set a=6 where pk=1; + +## Following query should not be taken from cache, trans is ongoing +select * from t1 order by pk desc; +--source include/show_qc_status.inc + + +## Connection 2 should still see old data and not hit cache +connection con2; +--source include/show_qc_status.inc +select * from t1 order by pk desc; +--source include/show_qc_status.inc + +## Connection 1 +connection con1; +COMMIT; + +## Update has just been committed, should not hit cache +--source include/show_qc_status.inc +select * from t1 order by pk desc; +--source include/show_qc_status.inc + +## Connection 2 +connection con2; + +## Should hit cache +--source include/show_qc_status.inc +select * from t1 order by pk desc; +--source include/show_qc_status.inc + +drop table t1; + +## Finally, there should be no queries in cache +--source include/show_qc_status.inc + +SET GLOBAL query_cache_size=0; === modified file 'sql/ha_ndbcluster.cc' --- a/sql/ha_ndbcluster.cc 2008-03-29 08:02:54 +0000 +++ b/sql/ha_ndbcluster.cc 2008-05-28 15:11:14 +0000 @@ -4608,21 +4608,24 @@ { DBUG_PRINT("info", ("lock_type == F_UNLCK")); - if (ndb_cache_check_time && m_rows_changed) + if (m_rows_changed) { - DBUG_PRINT("info", ("Rows has changed and util thread is running")); + DBUG_PRINT("info", ("Rows has changed")); if (thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) { - DBUG_PRINT("info", ("Add share to list of tables to be invalidated")); + DBUG_PRINT("info", ("Add share to list of changed tables")); /* NOTE push_back allocates memory using transactions mem_root! */ thd_ndb->changed_tables.push_back(m_share, &thd->transaction.mem_root); } - pthread_mutex_lock(&m_share->mutex); - DBUG_PRINT("info", ("Invalidating commit_count")); - m_share->commit_count= 0; - m_share->commit_count_lock++; - pthread_mutex_unlock(&m_share->mutex); + if (ndb_cache_check_time) + { + pthread_mutex_lock(&m_share->mutex); + DBUG_PRINT("info", ("Invalidating commit_count")); + m_share->commit_count= 0; + m_share->commit_count_lock++; + pthread_mutex_unlock(&m_share->mutex); + } } if (!--thd_ndb->lock_count) @@ -7956,20 +7959,34 @@ ulonglong *engine_data) { Uint64 commit_count; - bool is_autocommit= !(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)); char *dbname= full_name; char *tabname= dbname+strlen(dbname)+1; #ifndef DBUG_OFF char buff[22], buff2[22]; #endif DBUG_ENTER("ndbcluster_cache_retrieval_allowed"); - DBUG_PRINT("enter", ("dbname: %s, tabname: %s, is_autocommit: %d", - dbname, tabname, is_autocommit)); + DBUG_PRINT("enter", ("dbname: %s, tabname: %s", + dbname, tabname)); - if (!is_autocommit) + if (thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) { - DBUG_PRINT("exit", ("No, don't use cache in transaction")); - DBUG_RETURN(FALSE); + /* Don't allow qc to be used if table has been previously + modified in transaction */ + Thd_ndb *thd_ndb= get_thd_ndb(thd); + if (!thd_ndb->changed_tables.is_empty()) + { + NDB_SHARE* share; + List_iterator_fast it(thd_ndb->changed_tables); + while ((share= it++)) + { + if (strcmp(share->table_name, tabname) == 0 && + strcmp(share->db, dbname) == 0) + { + DBUG_PRINT("exit", ("No, transaction has changed table")); + DBUG_RETURN(FALSE); + } + } + } } if (ndb_get_commitcount(thd, dbname, tabname, &commit_count)) @@ -8031,15 +8048,30 @@ #ifndef DBUG_OFF char buff[22]; #endif - bool is_autocommit= !(thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)); DBUG_ENTER("ha_ndbcluster::register_query_cache_table"); - DBUG_PRINT("enter",("dbname: %s, tabname: %s, is_autocommit: %d", - m_dbname, m_tabname, is_autocommit)); - - if (!is_autocommit) + DBUG_PRINT("enter",("dbname: %s, tabname: %s", + m_dbname, m_tabname)); + + + if (thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)) { - DBUG_PRINT("exit", ("Can't register table during transaction")); - DBUG_RETURN(FALSE); + /* Don't allow qc to be used if table has been previously + modified in transaction */ + Thd_ndb *thd_ndb= get_thd_ndb(thd); + if (!thd_ndb->changed_tables.is_empty()) + { + DBUG_ASSERT(m_share); + NDB_SHARE* share; + List_iterator_fast it(thd_ndb->changed_tables); + while ((share= it++)) + { + if (m_share == share) + { + DBUG_PRINT("exit", ("No, transaction has changed table")); + DBUG_RETURN(FALSE); + } + } + } } if (ndb_get_commitcount(thd, m_dbname, m_tabname, &commit_count))