commit into mysql-5.1 branch (msvensson:2654) Bug#36692

[Magnus Svensson]

#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<NDB_SHARE> 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<NDB_SHARE> 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))