bk commit into 5.1 tree (msvensson:1.2597) BUG#36692

[msvensson]

Below is the list of changes that have just been committed into a local
5.1 repository of msvensson.  When msvensson does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-05-20 17:14:10+02:00, msvensson@shellback.(none) +3 -0
  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 any table.
   - It's potentially possible to loop through the "changed_table" list
  and compare which tables has been changed, should figure out if that is
  safe. But for now, only allow the query to be taken from cache
  if NO table has been changed.

  mysql-test/suite/ndb/r/ndb_cache_trans.result@stripped, 2008-05-20 17:14:09+02:00,
msvensson@shellback.(none) +264 -0
    Results

  mysql-test/suite/ndb/r/ndb_cache_trans.result@stripped, 2008-05-20 17:14:09+02:00,
msvensson@shellback.(none) +0 -0

  mysql-test/suite/ndb/t/ndb_cache_trans.test@stripped, 2008-05-20 17:14:09+02:00,
msvensson@shellback.(none) +168 -0
    Tests

  mysql-test/suite/ndb/t/ndb_cache_trans.test@stripped, 2008-05-20 17:14:09+02:00,
msvensson@shellback.(none) +0 -0

  sql/ha_ndbcluster.cc@stripped, 2008-05-20 17:14:09+02:00, msvensson@shellback.(none) +26
-13
    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

diff -Nrup a/mysql-test/suite/ndb/r/ndb_cache_trans.result
b/mysql-test/suite/ndb/r/ndb_cache_trans.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/suite/ndb/r/ndb_cache_trans.result	2008-05-20 17:14:09 +02:00
@@ -0,0 +1,264 @@
+drop table if exists t1;
+set GLOBAL query_cache_type=on;
+set GLOBAL query_cache_size=1355776;
+reset query cache;
+flush status;
+set AUTOCOMMIT=off;
+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');
+COMMIT;
+select * from t1;
+pk	a	b	c
+1	2	3	First row
+COMMIT;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	1
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	0
+select * from t1;
+pk	a	b	c
+1	2	3	First row
+COMMIT;
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+update t1 set a=3 where pk=1;
+COMMIT;
+select * from t1;
+pk	a	b	c
+1	3	3	First row
+COMMIT;
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+insert into t1 value (2, 7, 8, 'Second row');
+COMMIT;
+insert into t1 value (4, 5, 6, 'Fourth row');
+COMMIT;
+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;
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	3
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	1
+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;
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+select * from t1 where b=3;
+pk	a	b	c
+1	3	3	First row
+COMMIT;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	2
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	2
+select * from t1 where b=3;
+pk	a	b	c
+1	3	3	First row
+COMMIT;
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	3
+delete from t1 where c='Fourth row';
+COMMIT;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+select * from t1 where b=3;
+pk	a	b	c
+1	3	3	First row
+COMMIT;
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	3
+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
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	4
+update t1 set a=4 where b=3;
+COMMIT;
+set AUTOCOMMIT=off;
+use test;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+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
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	7
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	5
+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
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	7
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	7
+begin;
+update t1 set a=5 where pk=1;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	7
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	7
+select * from t1 order by pk desc;
+pk	a	b	c
+2	7	8	Second row
+1	4	3	First row
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	8
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	7
+commit;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	8
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	7
+select * from t1 order by pk desc;
+pk	a	b	c
+2	7	8	Second row
+1	5	3	First row
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	9
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	7
+COMMIT;
+select * from t1 order by pk desc;
+pk	a	b	c
+2	7	8	Second row
+1	5	3	First row
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	1
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	9
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	8
+update t1 set a=6 where pk=1;
+select * from t1 order by pk desc;
+pk	a	b	c
+2	7	8	Second row
+1	6	3	First row
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	9
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	8
+select * from t1 order by pk desc;
+pk	a	b	c
+2	7	8	Second row
+1	5	3	First row
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	10
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	8
+select * from t1 order by pk desc;
+pk	a	b	c
+2	7	8	Second row
+1	5	3	First row
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	10
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	9
+COMMIT;
+select * from t1 order by pk desc;
+pk	a	b	c
+2	7	8	Second row
+1	6	3	First row
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	11
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	9
+select * from t1 order by pk desc;
+pk	a	b	c
+2	7	8	Second row
+1	6	3	First row
+show status like "Qcache_inserts";
+Variable_name	Value
+Qcache_inserts	11
+show status like "Qcache_hits";
+Variable_name	Value
+Qcache_hits	10
+drop table t1;
+show status like "Qcache_queries_in_cache";
+Variable_name	Value
+Qcache_queries_in_cache	0
+SET GLOBAL query_cache_size=0;
diff -Nrup a/mysql-test/suite/ndb/t/ndb_cache_trans.test
b/mysql-test/suite/ndb/t/ndb_cache_trans.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/suite/ndb/t/ndb_cache_trans.test	2008-05-20 17:14:09 +02:00
@@ -0,0 +1,168 @@
+-- source include/have_query_cache.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;
+
+# 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;
+insert into t1 value (1, 2, 3, 'First row');
+COMMIT;
+
+# Perform one query which should be inserted in query cache
+select * from t1;
+COMMIT;
+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;
+COMMIT;
+show status like "Qcache_hits";
+
+# Update the table and make sure the correct data is returned
+update t1 set a=3 where pk=1;
+COMMIT;
+select * from t1;
+COMMIT;
+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');
+COMMIT;
+insert into t1 value (4, 5, 6, 'Fourth row');
+COMMIT;
+select * from t1 order by pk;
+COMMIT;
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from t1 order by pk;
+COMMIT;
+show status like "Qcache_hits";
+
+# Perform a "new" query and make sure the query cache is not hit
+select * from t1 where b=3;
+COMMIT;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_hits";
+
+# Same query again...
+select * from t1 where b=3;
+COMMIT;
+show status like "Qcache_hits";
+
+# Delete from the table
+delete from t1 where c='Fourth row';
+COMMIT;
+show status like "Qcache_queries_in_cache";
+select * from t1 where b=3;
+COMMIT;
+show status like "Qcache_hits";
+
+# 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;
+show status like "Qcache_hits";
+
+# Update the table and switch to other connection 
+update t1 set a=4 where b=3;
+COMMIT;
+connect (con2,localhost,root,,);
+connection con2;
+set AUTOCOMMIT=off;
+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";
+
+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 order by pk desc;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+
+connection con1;
+commit;
+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";
+COMMIT;
+
+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";
+
+update t1 set a=6 where pk=1;
+
+# Following query should not be taken from cachec
+select * from t1 order by pk desc;
+show status like "Qcache_queries_in_cache";
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+
+# Other connection should still see old data
+connection con2;
+select * from t1 order by pk desc;
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+select * from t1 order by pk desc;
+show status like "Qcache_inserts";
+show status like "Qcache_hits";
+
+connection con1;
+COMMIT;
+
+select * from t1 order by pk desc;
+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";
+
+drop table t1;
+
+show status like "Qcache_queries_in_cache";
+SET GLOBAL query_cache_size=0;
+# End of 4.1 tests
diff -Nrup a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc
--- a/sql/ha_ndbcluster.cc	2008-04-09 17:29:39 +02:00
+++ b/sql/ha_ndbcluster.cc	2008-05-20 17:14:09 +02:00
@@ -4614,21 +4614,26 @@ int ha_ndbcluster::external_lock(THD *th
   {
     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);
+        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)
@@ -8072,8 +8077,12 @@ ndbcluster_cache_retrieval_allowed(THD *
 
   if (!is_autocommit)
   {
-    DBUG_PRINT("exit", ("No, don't use cache in transaction"));
-    DBUG_RETURN(FALSE);
+    Thd_ndb *thd_ndb= get_thd_ndb(thd);
+    if (!thd_ndb->changed_tables.is_empty())
+    {
+      DBUG_PRINT("exit", ("No, transaction has changed table(s)"));
+      DBUG_RETURN(FALSE);
+    }
   }
 
   if (ndb_get_commitcount(thd, dbname, tabname, &commit_count))
@@ -8142,8 +8151,12 @@ ha_ndbcluster::register_query_cache_tabl
 
   if (!is_autocommit)
   {
-    DBUG_PRINT("exit", ("Can't register table during transaction"));
-    DBUG_RETURN(FALSE);
+    Thd_ndb *thd_ndb= get_thd_ndb(thd);
+    if (!thd_ndb->changed_tables.is_empty())
+    {
+      DBUG_PRINT("exit", ("No, transaction has changed table(s)"));
+      DBUG_RETURN(FALSE);
+    }
   }
 
   if (ndb_get_commitcount(thd, m_dbname, m_tabname, &commit_count))