[MDEV-5744] OQGRAPH backing table changes not reflected in OQGRAPH table Created: 2014-02-26  Updated: 2021-06-05  Resolved: 2014-02-27

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - OQGRAPH
Affects Version/s: 10.0.8
Fix Version/s: 10.0.9

Type: Bug Priority: Major
Reporter: Ian Gilfillan Assignee: Elena Stepanova
Resolution: Fixed Votes: 0
Labels: oqgraph

Issue Links:
Relates
relates to MDEV-6783 OQGRAPH - allow dependant table with ... Open
Sub-Tasks:
Key
 Summary
 Type
 Status
 Assignee
MDEV-6772 still a problem at 10.0 and 10.1 (git... Technical task Closed  

 Description   

An update to the backing OQGRAPH table does not reflect when querying the OQGRAPH table. The change reflects after a server restart.

Running Ubuntu 12.04 32-bit. MariaDB 10.0.7 installed from standard package, 3.2.0-58-gerneric-pae kernel, dual CPU

How to repeat:

CREATE TABLE oq2_backing (
 
  origid INT UNSIGNED NOT NULL,
 
  destid INT UNSIGNED NOT NULL,
 
  weight DOUBLE NOT NULL,
 
  PRIMARY KEY (origid, destid),
 
  KEY (destid)
 
);
 
 
 
INSERT INTO oq2_backing(origid, destid, weight)
 
 VALUES (1,2,1), (2,3,1), (3,4,3), (4,5,1), (2,6,10), (5,6,2);
 

CREATE TABLE oq2_graph (
 
  latch VARCHAR(32) NULL,
 
  origid BIGINT UNSIGNED NULL,
 
  destid BIGINT UNSIGNED NULL,
 
  weight DOUBLE NULL,
 
  seq BIGINT UNSIGNED NULL,
 
  linkid BIGINT UNSIGNED NULL,
 
  KEY (latch, origid, destid) USING HASH,
 
  KEY (latch, destid, origid) USING HASH
 
)
 
ENGINE=OQGRAPH
 
data_table='oq2_backing' origid='origid' destid='destid' weight='weight';
 
 
 
UPDATE oq2_backing SET weight=4 WHERE origid=2 AND destid=6;
 

SELECT * FROM oq2_backing;
 
+--------+--------+--------+
 
| origid | destid | weight |
 
+--------+--------+--------+
 
| 1 | 2 | 1 |
 
| 2 | 3 | 1 |
 
| 2 | 6 | 4 |
 
| 3 | 4 | 3 |
 
| 4 | 5 | 1 |
 
| 5 | 6 | 2 |
 
+--------+--------+--------+
 

SELECT * FROM oq2_graph;
 
+-------+--------+--------+--------+------+--------+
 
| latch | origid | destid | weight | seq | linkid |
 
+-------+--------+--------+--------+------+--------+
 
| NULL | 1 | 2 | 1 | NULL | NULL |
 
| NULL | 2 | 3 | 1 | NULL | NULL |
 
| NULL | 2 | 6 | 10 | NULL | NULL |
 
| NULL | 3 | 4 | 3 | NULL | NULL |
 
| NULL | 4 | 5 | 1 | NULL | NULL |
 
| NULL | 5 | 6 | 2 | NULL | NULL |
 
+-------+--------+--------+--------+------+--------+

I have repeated the bug on another machine, Ubuntu 12.04 64-bit, both fairly standard my.cnf settings. The problem persists on 10.0.8.

I have reported this bug upstream at https://bugs.launchpad.net/oqgraph/+bug/1279246 but there has been no activity.

The problem also appears to affect others - see https://mariadb.com/kb/en/how-do-i-keep-the-graph-table-and-data-table-in-sync/#comment_1055

 Comments   
Comment by Elena Stepanova [ 2014-02-26 ]

Do you have query_cache enabled?
I can reproduce it, but only with query cache (which is still a bug – the engine should declare that it's not query-cache-capable, I remember it being done for other engines).

Comment by Elena Stepanova [ 2014-02-26 ]

Here is how it was done in Cassandra (MDEV-3997):

       ------------------------------------------------------------
 
        revno: 3492.2.2
 
        revision-id: psergey@askmonty.org-20130204061420-xq39drhjw0hrj6i2
 
        parent: psergey@askmonty.org-20130204052229-txtd59or730oeq36
 
        committer: Sergey Petrunya <psergey@askmonty.org>
 
        branch nick: 10.0
 
        timestamp: Mon 2013-02-04 10:14:20 +0400
 
        message:
 
          MDEV-3997: Querying a Cassandra table on a server with query cache enabled is likely to cause problems
 
          - Disable query cache for Cassandra tables.
 
        ------------------------------------------------------------
 

=== modified file 'storage/cassandra/ha_cassandra.h'
 
--- storage/cassandra/ha_cassandra.h    2013-01-31 08:48:19 +0000
 
+++ storage/cassandra/ha_cassandra.h    2013-02-04 06:14:20 +0000
 
@@ -272,4 +272,18 @@
 
 
   THR_LOCK_DATA **store_lock(THD *thd, THR_LOCK_DATA **to,
 
                              enum thr_lock_type lock_type);     ///< required
 
+
 
+  my_bool register_query_cache_table(THD *thd, char *table_key,
 
+                                     uint key_length,
 
+                                     qc_engine_callback
 
+                                     *engine_callback,
 
+                                     ulonglong *engine_data)
 
+  {
 
+    /* 
+      Do not put data from Cassandra tables into query cache (because there 
+      is no way to tell whether the data in cassandra cluster has changed or 
+      not)
 
+    */
 
+    return FALSE;
 
+  }
 
 };

Comment by Elena Stepanova [ 2014-02-26 ]

I applied the same solution to OQGRAPH, seems to do the trick:

=== modified file 'storage/oqgraph/ha_oqgraph.h'
 
--- storage/oqgraph/ha_oqgraph.h	2013-09-28 13:03:29 +0000
 
+++ storage/oqgraph/ha_oqgraph.h	2014-02-26 14:47:32 +0000
 
@@ -115,6 +115,20 @@
 
   virtual const char *table_type() const { return hton_name(ht)->str; }
 
 #endif
 
 
+  my_bool register_query_cache_table(THD *thd, char *table_key,
 
+                                     uint key_length,
 
+                                     qc_engine_callback
 
+                                     *engine_callback,
 
+                                     ulonglong *engine_data)
 
+  {
 
+    /* 
+      Do not put data from OQGRAPH tables into query cache (because there 
+      is no way to tell whether the data in the backing table has changed or 
+      not)
 
+    */
 
+    return FALSE;
 
+  }
 
+
 
 private:
 
   int oqgraph_check_table_structure (TABLE *table_arg);
 

MariaDB [test]> SELECT * FROM oq2_graph;
 
+-------+--------+--------+--------+------+--------+
 
| latch | origid | destid | weight | seq  | linkid |
 
+-------+--------+--------+--------+------+--------+
 
| NULL  |      1 |      2 |      1 | NULL |   NULL |
 
| NULL  |      2 |      3 |      1 | NULL |   NULL |
 
| NULL  |      2 |      6 |     34 | NULL |   NULL |
 
| NULL  |      3 |      4 |      3 | NULL |   NULL |
 
| NULL  |      4 |      5 |      1 | NULL |   NULL |
 
| NULL  |      5 |      6 |      2 | NULL |   NULL |
 
+-------+--------+--------+--------+------+--------+
 
 
 
MariaDB [test]> UPDATE oq2_backing SET weight=44 WHERE origid=2 AND destid=6;
 
Query OK, 1 row affected (0.08 sec)
 
 
 
MariaDB [test]> SELECT * FROM oq2_graph;
 
+-------+--------+--------+--------+------+--------+
 
| latch | origid | destid | weight | seq  | linkid |
 
+-------+--------+--------+--------+------+--------+
 
| NULL  |      1 |      2 |      1 | NULL |   NULL |
 
| NULL  |      2 |      3 |      1 | NULL |   NULL |
 
| NULL  |      2 |      6 |     44 | NULL |   NULL |
 
| NULL  |      3 |      4 |      3 | NULL |   NULL |
 
| NULL  |      4 |      5 |      1 | NULL |   NULL |
 
| NULL  |      5 |      6 |      2 | NULL |   NULL |
 
+-------+--------+--------+--------+------+--------+
 

MariaDB [test]> show variables like '%query_cache%';
 
+------------------------------+---------+
 
| Variable_name                | Value   |
 
+------------------------------+---------+
 
| have_query_cache             | YES     |
 
| query_cache_limit            | 1048576 |
 
| query_cache_min_res_unit     | 4096    |
 
| query_cache_size             | 1048576 |
 
| query_cache_strip_comments   | OFF     |
 
| query_cache_type             | ON      |
 
| query_cache_wlock_invalidate | OFF     |
 
+------------------------------+---------+

Comment by Ian Gilfillan [ 2014-02-26 ]

Yes, the query_cache is enabled by default, so this was active in both cases. I wasn't aware it needed to be disabled - I will document this as well.

Comment by Elena Stepanova [ 2014-02-26 ]

Actually, query_cache is disabled by default – it is ON, but with 0 size, which means no cache, but can be changed at runtime.
So, you need to check whether your query_cache_size > 0, and if so, it must be hiding somewhere in the config.

Comment by Arjen Lentz [ 2014-02-27 ]

Thanks for the bugreport Ian!

Hi Elena - thanks for tracking that.
I think your patch, adding the register_query_cache_table() function, returning FALSE, is correct - for the current implementation of the query cache.
Can you apply this in the active MariaDB tree so it'll be in the next build?
Thanks

Comment by Elena Stepanova [ 2014-02-27 ]

Pushed to 10.0 tree as http://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/4012

Comment by roberto spadim [ 2014-09-24 ]

please remove INSERT DELAYED too, since aria and maybe innodb don't support it

Comment by roberto spadim [ 2014-09-24 ]

i think this problem isn't solved, at least to 10.1 and launchpad 10.0
http://bazaar.launchpad.net/~maria-captains/maria/10.0/changes?filter_file_id=ha_oqgraph.cc-20091109115125-39rbuqeoqlrz4bux-11

Comment by Elena Stepanova [ 2014-09-24 ]

The change was in ha_oqgraph.h, while you are looking at ha_oqgraph.cc.
If you tthink it doesn't work, please provide a test case.

Comment by roberto spadim [ 2014-09-24 ]

that's right i was searching rev 4012 at .cc not .h

Comment by roberto spadim [ 2014-09-24 ]

i will change documentation

Comment by roberto spadim [ 2014-09-24 ]

docs changed...
but i'm with another idea....
internally at query cache we have tables used in a query, right?
instead of only report to query cache as can't cache queries, could we include the data table to query cache? when the data table invalidate query cache it will check that the oqgraph queries are using the data table and will invalidate it too, for example:

select * from oq_graph
this will include "select * from oq_graph" at query cache using table 'oq_graph'
but instead of only using 'oq_graph' we include 'oq_graph' AND 'data_table' even when we 'only' used oq_graph at FROM part
this is more interesting than turning query cache off to oq_graph

Comment by Lennard Dietz [ 2021-05-31 ]

Hello guys,
even though this problem is pretty old and closed, we have stumbled upon it recently. Currently our only solution is to either restart our MariaDB Server or recreate the graph table.
As this issue was already solved long ago, we suspect that somehow our installed OQGRAPH Plugin is a very old version.
It would be very nice if you could help us.

We are using MariaDB Version 10.5.8 and the OQGRAPH Plugin is installed with the following information in our DB:

SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME="OQGRAPH" \G;
 
*************************** 1. row ***************************
 
        PLUGIN_NAME: OQGRAPH
 
        PLUGIN_VERSION: 3.0
 
        PLUGIN_STATUS: ACTIVE
 
        PLUGIN_TYPE: STORAGE ENGINE
 
        PLUGIN_TYPE_VERSION: 100508.0
 
        PLUGIN_LIBRARY: ha_oqgraph.so
 
        PLUGIN_LIBRARY_VERSION: 1.14
 
        PLUGIN_AUTHOR: Arjen Lentz & Antony T Curtis, Open Query, and Andrew McDonnell
 
        PLUGIN_DESCRIPTION: Open Query Graph Computation Engine (http://openquery.com/graph)
 
        PLUGIN_LICENSE: GPL
 
        LOAD_OPTION: ON
 
        PLUGIN_MATURITY: Gamma
 
        PLUGIN_AUTH_VERSION: 3.0

Comment by Sergei Golubchik [ 2021-06-05 ]

I don't think it's possible that you've got an old OQGRAPH version. This one was definitely compiled for 10.5.8 (PLUGIN_TYPE_VERSION) and the old OQGRAPH wouldn't even compile for 10.5.8.

Could be a different bug.

Generated at Thu Feb 08 07:06:44 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.