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;