MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:msvensson Date:May 28 2008 2:26pm
Subject:bk commit into 5.1 tree (msvensson:1.2597) BUG#36692
View as plain text  
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-28 16:26:26+02:00, msvensson@shellback.(none) +4 -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 the table.

  mysql-test/include/show_qc_status.inc@stripped, 2008-05-28 16:26:21+02:00, msvensson@shellback.(none) +10 -0
    New BitKeeper file ``mysql-test/include/show_qc_status.inc''

  mysql-test/include/show_qc_status.inc@stripped, 2008-05-28 16:26:21+02:00, msvensson@shellback.(none) +0 -0

  mysql-test/suite/ndb/r/ndb_cache_trans.result@stripped, 2008-05-28 16:26:22+02:00, msvensson@shellback.(none) +317 -0
    Results

  mysql-test/suite/ndb/r/ndb_cache_trans.result@stripped, 2008-05-28 16:26:22+02:00, msvensson@shellback.(none) +0 -0

  mysql-test/suite/ndb/t/ndb_cache_trans.test@stripped, 2008-05-28 16:26:22+02:00, msvensson@shellback.(none) +200 -0
    Tests

  mysql-test/suite/ndb/t/ndb_cache_trans.test@stripped, 2008-05-28 16:26:22+02:00, msvensson@shellback.(none) +0 -0

  sql/ha_ndbcluster.cc@stripped, 2008-05-28 16:26:21+02:00, msvensson@shellback.(none) +54 -21
    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/include/show_qc_status.inc b/mysql-test/include/show_qc_status.inc
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/include/show_qc_status.inc	2008-05-28 16:26:21 +02:00
@@ -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;
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-28 16:26:22 +02:00
@@ -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;
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-28 16:26:22 +02:00
@@ -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;
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-28 16:26:21 +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)
@@ -8060,20 +8065,34 @@ ndbcluster_cache_retrieval_allowed(THD *
                                    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))
@@ -8135,15 +8154,29 @@ ha_ndbcluster::register_query_cache_tabl
 #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));
+  DBUG_PRINT("enter",("dbname: %s, tabname: %s",
+		      m_dbname, m_tabname));
 
-  if (!is_autocommit)
+  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))
Thread
bk commit into 5.1 tree (msvensson:1.2597) BUG#36692msvensson28 May