oqgraphv3. Read only but no updates from basetable

Bug #796647 reported by Erkan Yanar on 2011-06-13
16
This bug affects 2 people
Affects Status Importance Assigned to Milestone
OQGRAPH
High
Antony T Curtis

Bug Description

Moin,
MariaDB [test]> 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: 50300.0
        PLUGIN_LIBRARY: ha_oqgraph.so
PLUGIN_LIBRARY_VERSION: 0.0
         PLUGIN_AUTHOR: Arjen Lentz & Antony T Curtis, Open Query
    PLUGIN_DESCRIPTION: Open Query Graph Computation Engine (http://openquery.com/graph)
        PLUGIN_LICENSE: GPL
       PLUGIN_MATURITY: Unknown
   PLUGIN_AUTH_VERSION: Unknown
MariaDB [test]> select version();
+---------------------+
| version() |
+---------------------+
| 5.3.0-MariaDB-alpha |
+---------------------+

Its great you can use persistent tables to store the graph. A drawback with version v3 is if you chenge the base/persistent table it is ignored by the memory-table. Dropping/creating the memory-table is a workaround.
MariaDB [test]> desc base;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| von | int(11) | NO | PRI | NULL | |
| nach | int(11) | NO | PRI | NULL | |
| weight | double | NO | | NULL | |
+--------+---------+------+-----+---------+-------+
MariaDB [test]> desc graph;
+--------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+----------------------+------+-----+---------+-------+
| latch | smallint(5) unsigned | YES | MUL | NULL | |
| origid | bigint(20) unsigned | YES | | NULL | |
| destid | bigint(20) unsigned | YES | | NULL | |
| weight | double | YES | | NULL | |
| seq | bigint(20) unsigned | YES | | NULL | |
| linkid | bigint(20) unsigned | YES | | NULL | |
+--------+----------------------+------+-----+---------+-------+

3 rows in the basetable:
MariaDB [test]> SELECT * FROM base;
+-----+------+--------+
| von | nach | weight |
+-----+------+--------+
| 3 | 5 | 2 |
| 5 | 4 | 1 |
| 5 | 6 | 1 |
+-----+------+--------+

and in the graph-table
MariaDB [test]> SELECT * from graph;
+-------+--------+--------+--------+------+--------+
| latch | origid | destid | weight | seq | linkid |
+-------+--------+--------+--------+------+--------+
| NULL | 3 | 5 | 2 | NULL | NULL |
| NULL | 5 | 4 | 1 | NULL | NULL |
| NULL | 5 | 6 | 1 | NULL | NULL |
+-------+--------+--------+--------+------+--------+

but:
MariaDB [test]> insert into base values(6,3,1);
MariaDB [test]> SELECT * FROM base;
+-----+------+--------+
| von | nach | weight |
+-----+------+--------+
| 3 | 5 | 2 |
| 5 | 4 | 1 |
| 5 | 6 | 1 |
| 6 | 3 | 1 |
+-----+------+--------+

MariaDB [test]> SELECT * from graph;
+-------+--------+--------+--------+------+--------+
| latch | origid | destid | weight | seq | linkid |
+-------+--------+--------+--------+------+--------+
| NULL | 3 | 5 | 2 | NULL | NULL |
| NULL | 5 | 4 | 1 | NULL | NULL |
| NULL | 5 | 6 | 1 | NULL | NULL |
+-------+--------+--------+--------+------+--------+

Is this going to be fixed?
Or a design-issue?

Regards
Erkan
(btw: why do I need keys on the base-table?)

Arjen Lentz (arjen-lentz) wrote :

Curious. thanks for your report!
I think Antony put in a bit of caching to speed up operations, and obviously those need to be invalidated when the underlying table changes. I've assigned this issue to Antony to take a look and see what might be going on.

Changed in oqgraph:
assignee: nobody → Antony T Curtis (atcurtis)
Arjen Lentz (arjen-lentz) wrote :

As we're looking at v3, this bug probably needs attending to.

Changed in oqgraph:
importance: Undecided → High
status: New → Confirmed
Antony T Curtis (atcurtis) wrote :

I haven't tested this yet but ... was the query cache enabled?

There is no caching between statements, OQGraph does not know when the underlying table was changed and so is unable to notify mysql to invalidate its cached data.

Arjen Lentz (arjen-lentz) wrote :

Antony - how does the server handle views and virtual columns, in terms of the query cache?
Is a storage engine able to tell the server that it shouldn't cache its tables?

How does the query cache know that an underlying table has been modified - is that done by the server somewhere in the execution of insert/update/delete and other relevant statements, or is there a notification mechanism up from engines that we could perhaps also use?

Andrew McDonnell (andymc73) wrote :

Had a look at this one. I think it is still manifesting somehow.

I cant repeat it exactly as shown here; in basic.test I add and delete rows at various points so just now added some extra selects at various points to check the added data is as expected.
One condition that must hold is select count(*) from graph_base == select count(*) from graph.

But then I had an odd result in a final select * from graph after deleting some rows - from a 23 row graph and having deleted two rows then running a dijkstras query then doing a select *, the data returned from graph only returned the first 6 rows of what should have been 21... so something causes the return from `select * from graph` to not show everything after some combinations of modifications to the underlying graph.

I'll try and isolate a specific test separately from basic.test for regression testing

Andrew McDonnell (andymc73) wrote :

However using the exact sequence above I get the correct result.

regression_796647.test (the sequence above) works for me
regression_796647b.test loses rows

Andrew McDonnell (andymc73) wrote :

The test (b) attached here reports 6 when the result should be 21

Andrew McDonnell (andymc73) wrote :

The test (c) reports 9 rows when there should be 7 i.e. the deletes are not reflected

Andrew McDonnell (andymc73) wrote :

Suspect needs same fix as bug 1195735 but in a different place - i.e. adjust count to catch mods to base table

Andrew McDonnell (andymc73) wrote :

bug 1195735 only handles TRUNCATE TABLE case - because when the count is updated to zero this is picked up.
But otherwise, this goes into an infinite loop now instead of returning the wrong count...

So as guessed, changes to underlying data not getting reflected.
Maybe we need to re-open the table when a new random scan is started...

Andrew McDonnell (andymc73) wrote :

What we probably need is the equivalent of a TRIGGER that sets a flag telling us to reopen the table on the next random scan...

Andrew McDonnell (andymc73) wrote :

But why is it things work OK when records added to the underlying table, but not when deleted?

Andrew McDonnell (andymc73) wrote :

OK finally, looks like ha_rnd_next() can return HA_RECORD_DELETED and we need to handle that inside thunk seek() !

Andrew McDonnell (andymc73) wrote :

Fixes case 796647c. Bt 796647b still returning incomplete result set

Andrew McDonnell (andymc73) wrote :

Looking at output, result truncated place where first delete would have been...

Andrew McDonnell (andymc73) wrote :

ok, finally. fixed commit

Changed in oqgraph:
status: Confirmed → Fix Committed
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers