Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5744

OQGRAPH backing table changes not reflected in OQGRAPH table

Details

    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

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-6783 OQGRAPH - allow dependant table with query cache

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            View 10 older comments
            rspadim roberto spadim added a comment -

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

            rspadim roberto spadim added a comment - that's right i was searching rev 4012 at .cc not .h
            rspadim roberto spadim added a comment -

            i will change documentation

            rspadim roberto spadim added a comment - i will change documentation
            rspadim roberto spadim added a comment -

            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

            rspadim roberto spadim added a comment - 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
            Lennard Dietz Lennard Dietz added a comment -

            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

            Lennard Dietz Lennard Dietz added a comment - 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
            serg Sergei Golubchik added a comment -

            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.

            serg Sergei Golubchik added a comment - 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.

            People

              elenst Elena Stepanova
              greenman Ian Gilfillan
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.