-- source include/have_query_cache.inc -- source include/have_ndb.inc --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; # Turn on thread that will fetch commit count for open tables set GLOBAL ndb_cache_check_time=5; reset query cache; flush status; # Wait for thread to wake up and start "working" sleep 20; # 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; insert into t1 value (1, 2, 3, 'First row'); # Perform one query which should be inerted in query cache select * from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; # Perform the same query and make sure the query cache is hit select * from t1; show status like "Qcache_hits"; # Update the table and make sure the correct data is returned update t1 set a=3 where pk=1; select * from t1; show status like "Qcache_inserts"; show status like "Qcache_hits"; # 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'); select * from t1; show status like "Qcache_inserts"; show status like "Qcache_hits"; select * from t1; show status like "Qcache_hits"; # Perform a "new" query and make sure the query cache is not hit select * from t1 where b=3; show status like "Qcache_queries_in_cache"; show status like "Qcache_hits"; # Same query again... select * from t1 where b=3; show status like "Qcache_hits"; # Delete from the table delete from t1 where c='Fourth row'; show status like "Qcache_queries_in_cache"; select * from t1 where b=3; show status like "Qcache_hits"; # Start another connection and check that the query cache is hit connect (con1,localhost,root,,); connection con1; use test; select * from t1; select * from t1 where b=3; show status like "Qcache_hits"; # Update the table and switch to other connection update t1 set a=4 where b=3; connect (con2,localhost,root,,); connection con2; use test; show status like "Qcache_queries_in_cache"; select * from t1; select * from t1; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con1; select * from t1; select * from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; # Use transactions and make sure the query cache is not updated until # transaction is commited begin; update t1 set a=5 where pk=1; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con2; select * from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con1; commit; # Sleep to let the query cache thread update commit count sleep 10; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con2; select * from t1; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con1; select * from t1; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; drop table t1; show status like "Qcache_queries_in_cache"; SET GLOBAL query_cache_size=0; SET GLOBAL ndb_cache_check_time=0;