diff -ur mysql-5.1.34.orig/mysql-test/r/query_cache.result mysql-5.1.34/mysql-test/r/query_cache.result --- mysql-5.1.34.orig/mysql-test/r/query_cache.result 2009-03-31 16:27:19.000000000 +0100 +++ mysql-5.1.34/mysql-test/r/query_cache.result 2009-05-28 19:18:17.000000000 +0100 @@ -38,10 +38,16 @@ show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 0 drop table t1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 1 create table t1 (a int not null); insert into t1 values (1),(2),(3); create table t2 (a int not null); @@ -69,10 +75,16 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 1 insert into t2 values (7); show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 2 select * from t1; a 1 @@ -89,10 +101,16 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 2 insert into t3 values (8); show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 3 select * from t3; a 1 @@ -106,10 +124,16 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 3 update t2 set a=9 where a=7; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 4 select * from t1; a 1 @@ -119,10 +143,16 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 4 update t3 set a=10 where a=1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 5 select * from t3; a 10 @@ -136,10 +166,16 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 5 delete from t2 where a=9; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 6 select * from t1; a 10 @@ -149,10 +185,16 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 6 delete from t3 where a=10; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 7 drop table t1, t2, t3; set query_cache_type=demand; create table t1 (a int not null); @@ -197,11 +239,17 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 7 set query_cache_type=on; reset query cache; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 7 select sql_no_cache * from t1; a 1 @@ -475,6 +523,9 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 15 insert delayed into t1 values (4); select a from t1; a @@ -485,6 +536,9 @@ show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 +show status like "Qcache_queries_invalidated"; +Variable_name Value +Qcache_queries_invalidated 16 drop table t1; show status like "Qcache_queries_in_cache"; Variable_name Value diff -ur mysql-5.1.34.orig/mysql-test/t/query_cache.test mysql-5.1.34/mysql-test/t/query_cache.test --- mysql-5.1.34.orig/mysql-test/t/query_cache.test 2009-03-31 16:26:32.000000000 +0100 +++ mysql-5.1.34/mysql-test/t/query_cache.test 2009-05-28 19:16:45.000000000 +0100 @@ -37,10 +37,12 @@ show status like "Qcache_queries_in_cache"; show status like "Qcache_inserts"; show status like "Qcache_hits"; +show status like "Qcache_queries_invalidated"; drop table t1; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; # # MERGE TABLES with INSERT/UPDATE and DELETE @@ -55,32 +57,44 @@ select * from t3; show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; insert into t2 values (7); show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; select * from t1; select * from t1; show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; insert into t3 values (8); show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; # update select * from t3; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; update t2 set a=9 where a=7; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; select * from t1; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; update t3 set a=10 where a=1; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; #delete select * from t3; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; delete from t2 where a=9; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; select * from t1; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; delete from t3 where a=10; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; drop table t1, t2, t3; # # SELECT SQL_CACHE ... @@ -100,12 +114,14 @@ select * from t1 where a IN (select a from t1 union select sql_cache a from t1); show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; set query_cache_type=on; # # RESET QUERY CACHE # reset query cache; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; # # SELECT SQL_NO_CACHE # @@ -299,10 +315,12 @@ select * from t1; select * from t1; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; insert delayed into t1 values (4); --sleep 5 # Wait for insert delayed to be executed. select a from t1; show status like "Qcache_queries_in_cache"; +show status like "Qcache_queries_invalidated"; drop table t1; show status like "Qcache_queries_in_cache"; @@ -1302,7 +1320,7 @@ CREATE TABLE t1 (a LONGBLOB); INSERT INTO t1 SET a = 'aaaa'; INSERT INTO t1 SET a = 'aaaa'; -SELECT 1 FROM t1 GROUP BY + SELECT 1 FROM t1 GROUP BY (SELECT LAST_INSERT_ID() FROM t1 ORDER BY MIN(a) ASC LIMIT 1); DROP TABLE t1; diff -ur mysql-5.1.34.orig/sql/mysqld.cc mysql-5.1.34/sql/mysqld.cc --- mysql-5.1.34.orig/sql/mysqld.cc 2009-03-31 15:39:47.000000000 +0100 +++ mysql-5.1.34/sql/mysqld.cc 2009-05-28 13:48:55.000000000 +0100 @@ -7382,6 +7382,7 @@ {"Qcache_inserts", (char*) &query_cache.inserts, SHOW_LONG}, {"Qcache_lowmem_prunes", (char*) &query_cache.lowmem_prunes, SHOW_LONG}, {"Qcache_not_cached", (char*) &query_cache.refused, SHOW_LONG}, + {"Qcache_queries_invalidated",(char*) &query_cache.invalidated, SHOW_LONG}, {"Qcache_queries_in_cache", (char*) &query_cache.queries_in_cache, SHOW_LONG_NOFLUSH}, {"Qcache_total_blocks", (char*) &query_cache.total_blocks, SHOW_LONG_NOFLUSH}, #endif /*HAVE_QUERY_CACHE*/ diff -ur mysql-5.1.34.orig/sql/sql_cache.cc mysql-5.1.34/sql/sql_cache.cc --- mysql-5.1.34.orig/sql/sql_cache.cc 2009-03-31 15:39:51.000000000 +0100 +++ mysql-5.1.34/sql/sql_cache.cc 2009-05-28 13:48:26.000000000 +0100 @@ -2664,6 +2664,7 @@ Query_cache_block *query_block= list_root->next->block(); BLOCK_LOCK_WR(query_block); free_query(query_block); + query_cache.invalidated++; DBUG_EXECUTE_IF("debug_cache_locks", sleep(10);); } } diff -ur mysql-5.1.34.orig/sql/sql_cache.h mysql-5.1.34/sql/sql_cache.h --- mysql-5.1.34.orig/sql/sql_cache.h 2009-03-31 15:39:51.000000000 +0100 +++ mysql-5.1.34/sql/sql_cache.h 2009-05-28 13:23:13.000000000 +0100 @@ -268,7 +268,7 @@ ulong query_cache_size, query_cache_limit; /* statistics */ ulong free_memory, queries_in_cache, hits, inserts, refused, - free_memory_blocks, total_blocks, lowmem_prunes; + free_memory_blocks, total_blocks, lowmem_prunes, invalidated; private: