Automated dead edge removal

[jimmarino]

Sometimes we wind up with a dead in-vertex on an edge. This can happen due to an application error (e.g. a vertex is deleted that is an in-vertex of a uni-directional edge and the app does not also remove the edge) or in other situations. This results in a RecordNotFoundException when traversing a dead edge.

It would be helpful to have a command (or the ability) to have the database remove dead edges either by edge type or out-vertex type/edge type combination.

[lvca]

This is a good idea for those who are using not bidirectional edges. In fact, in case you have an edge that connects vertices A to B, if you delete vertex B, ArcadeDB doesn't know it's pointing back to A through that edge because it was one way only from A to B.

I suggest ALWAYS using bidirectional edges to keep the graph consistent unless you're looking for an optimization where you never cross the graph back from B to A and you take care of cascade deletion at the application level.

So this command still makes sense for those cases and also in the cases where bugs would keep ghost edges for an unknown reason.

I was thinking of a generic REPAIR DATABASE SQL command that:

1. browses all the vertices and load all the incoming and outgoing edges. Once loaded, check the out/in vertex. If not exist, delete the edge.
2. while browsing on step (1) check also the out/in vertices in the edge list correspond to the out/in found in the edge
3. keep the good vertices' rids in a HashSet (or a bloom filter in case the available RAM is not enough or the database is too big) while browsing on (1).
4. browse all the edges by scanning the buckets and check in the HashSet or the bloom filter created in (3) if they exist. If they don't, delete the edge

Starting from the vertices instead of the edges allows checking both standard and lightweight edges.

Using a bloom filter allows running this with a constant, pre-defined amount of RAM, no matter the database size. The cons are that there could be false positives and the edge not deleted on step (4). This is a minor drawback because it means edges without any vertices pointing to them would still be in the database causing no harm unless you query from the edges instead from vertices that is the most common use case.

[jimmarino]

That sounds good. I think it would be helpful if the user can define the scope of the repair, e.g. limit it to a vertex or edge type, particularly for very large dbs.

[lvca]

Ok, the 1st version of this command is done, pushed in master, and it will be released with 21.12.1. I suggest running CHECK DATABASE without the FIX option first to have a report about any issues found.

We need more tests by simulating more broken databases (not that easy).

---

Executes an integrity check and in case of a repair of the database. This command analyzes the following things:

• buckets: all the pages and records are scanned and checked if can be loaded (no physical corruption)
• vertices: all the vertices are loaded and all the connected edges are checked. In case some edges point to records that have been
  deleted they can be fixed automatically if the FIX option is enabled.
• edges: scan all the edges and check the incoming and outgoing links are consistent in the relative vertices. If not, the edges can
  be automatically removed if the FIX option is enabled.

Syntax

CHECK DATABASE [ TYPE <type-name>[,]* ] [ BUCKET <bucket-name>[,]* ] [ FIX ]

• <type-name> Optional, if specified limit the check (and the fix) only to the specific types
• <bucket-name> Optional, if specified limit the check (and the fix) only to the specific buckets
• FIX Optional, if defined auto fix the issue found with the check

The command returns the integrity check report in one record.

Examples

• Execute the integrity check of the entire database without fixing any issue found.

ArcadeDB> CHECK DATABASE

• Execute the integrity check of the types 'Account' and 'Bill' without fixing any issue found.

ArcadeDB> CHECK DATABASE BUCKET "Account", "Bill"

• Execute the integrity check of the entire database and auto-fix any issues found.

ArcadeDB> CHECK DATABASE FIX

---

Official docs are updated with this new command: https://docs.arcadedb.com/#SQL-Check-Database.

[lvca]

Implemented further tests to break the database. it's all good, closing the issue

#225