-- source include/have_query_cache.inc -- source include/have_binlog_format_row.inc -- source include/have_ndb.inc -- source include/not_embedded.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; reset query cache; flush status; # 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 order by pk; show status like "Qcache_inserts"; show status like "Qcache_hits"; select * from t1 order by pk; 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 order by pk; 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 order by pk desc; select * from t1 order by pk desc; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con1; select * from t1 order by pk desc; select * from t1 order by pk desc; 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; # Note!! the below test shows that table is invalidated # before transaction is committed # TODO Fix so that cache is not invalidated HERE! show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con2; select * from t1 order by pk desc; show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con1; commit; # TODO Here query is invalidated once again, commit count in NDB has changed show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con2; select * from t1 order by pk desc; show status like "Qcache_inserts"; show status like "Qcache_hits"; connection con1; select * from t1 order by pk desc; 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; # End of 4.1 tests