Cannot drop index because one or more underlying files are not available

[marco-brandizi]

ArcadeDB Version:

23.7.1

OS and JDK Version:

Linux 4.18.0-425.19.2.el8_7.x86_64 - OpenJDK 64-Bit Server VM 17.0.8 (Temurin-17.0.8+7)

Expected behavior

I'm trying to run this SQL script from the web interface (the Studio), to create a number of indexes on an existing database. The script is generated my a Python program of mine, which uses a list of types and fields, but that's not very relevant here.

In 23.4.1, macOS, Java 17, the indexes were created without problems. Here, I was hoping for the same.

Actual behavior

The script fails with the error: Cannot drop index 'CelComp:Concept:Resource[identifier]' because one or more underlying files are not available

Steps to reproduce

1. Set up Arcade with this DB dump
2. Start the the server
3. Open the Studio and the database above
4. Use the SQL Script option to run the attached .sql
5. It gives the error above to me.

[marco-brandizi]

Maybe I've got something more on this:

Caused by: com.arcadedb.exception.SchemaException: Index with name 'Accession%25253AResource_0_8835859003821190' was not found
        at com.arcadedb.schema.EmbeddedSchema.getIndexByName(EmbeddedSchema.java:474)
        at com.arcadedb.database.TransactionIndexContext.addFilesToLock(TransactionIndexContext.java:216)
        at com.arcadedb.database.TransactionContext.lockFilesInOrder(TransactionContext.java:733)
        at com.arcadedb.database.TransactionContext.commit1stPhase(TransactionContext.java:540)
        at com.arcadedb.database.TransactionContext.commit(TransactionContext.java:121)
        at com.arcadedb.database.EmbeddedDatabase.lambda$commit$2(EmbeddedDatabase.java:363)
        at com.arcadedb.database.EmbeddedDatabase.executeInReadLock(EmbeddedDatabase.java:1361)
        at com.arcadedb.database.EmbeddedDatabase.commit(EmbeddedDatabase.java:358)
        at com.arcadedb.index.lsm.LSMTreeIndex.lambda$build$12(LSMTreeIndex.java:602)
        at com.arcadedb.database.EmbeddedDatabase.lambda$scanBucket$9(EmbeddedDatabase.java:496)
        at com.arcadedb.engine.Bucket.scan(Bucket.java:198)
        at com.arcadedb.database.EmbeddedDatabase.lambda$scanBucket$10(EmbeddedDatabase.java:494)
        at com.arcadedb.database.EmbeddedDatabase.executeInReadLock(EmbeddedDatabase.java:1361)
        at com.arcadedb.database.EmbeddedDatabase.scanBucket(EmbeddedDatabase.java:489)
        at com.arcadedb.database.EmbeddedDatabase.scanBucket(EmbeddedDatabase.java:482)
        at com.arcadedb.index.lsm.LSMTreeIndex.build(LSMTreeIndex.java:596)
        at com.arcadedb.schema.EmbeddedSchema.createBucketIndex(EmbeddedSchema.java:1299)
        ... 31 more

When using the console, I get this message for a few types/fields only. The one above is for:

CREATE INDEX ON `Accession:Resource` ( identifier ) NOTUNIQUE

So, maybe it doesn't like the type name, but I can't understand why.

[lvca]

Are you building it inside a transaction?

[marco-brandizi]

Are you building it inside a transaction?

I've tried both BEGIN/COMMIT and without transaction demarcation (assuming the Studio or the console auto-commit).

[lvca]

I was able to reproduce the issue. Checking on it.

[lvca]

It looks like the : character is not ArcadeDB schema friendly.

[lvca]

Ok, the issue is definitely with the encoding of the name. It's called multiple times and if the name contains special characters, then the result of encoding is different (encoding an encoded char).

[marco-brandizi]

Thank you. I guessed the problem might be in the building the index name.

For the moment, I'm using the older 23.4.1, I can possibly switch to other type names in future (at the moment, I need the X:Y syntax to overcome the Gremlin limit of not accepting multiple labels for a node).

While this can be fixed with escaping, in my opinion, it would be good if I could set an explicit index name for automatic indexes too.

[lvca]

I was able to reproduce it with this simple test case:

@Test
  public void testIndexNameSpecialCharacters() {
    VertexType type = database.getSchema().createVertexType("This.is:special");
    type.createProperty("other.special:property", Type.STRING);
    final TypeIndex idx = type.createTypeIndex(Schema.INDEX_TYPE.LSM_TREE, true, "other.special:property");

    database.command("sql", "rebuild index " + idx.getName());
  }

Working to a fix.

By the way: you could use inheritance with ArcadeDB, so you could keep name simpler.

In your use case can you please provide an example of vertex that needs multiple labels?

[lvca]

After some tests, actually it works:

  @Test
  public void testIndexNameSpecialCharactersUsingSQL() {
    database.command("sql", "create vertex type `This.is:special`");
    database.command("sql", "create property `This.is:special`.`other.special:property` string");

    database.async().waitCompletion();

    database.command("sql", "create index on `This.is:special`(`other.special:property`) unique");
    database.command("sql", "rebuild index `This.is:special[other.special:property]`");
  }

So there must be something in your schema that encoded twice the index names. Trying to figure it out.

As a quick workaround, can you use _ as a separator?

[lvca]

Ok, finally able to isolate it with a test case:

  @Test
  public void testIndexNameSpecialCharactersUsingSQL() {
    database.command("sql", "create vertex type `This.is:special`");
    database.command("sql", "create property `This.is:special`.`other.special:property` string");
    database.transaction(() -> {
      database.newVertex("This.is:special").set("other.special:property", "testEncoding").save();
    });

    database.async().waitCompletion();

    database.command("sql", "create index on `This.is:special`(`other.special:property`) unique");
    database.command("sql", "rebuild index `This.is:special[other.special:property]`");

    database.close();

    database = factory.exists() ? factory.open() : factory.create();
    database.command("sql", "rebuild index `This.is:special[other.special:property]`");

    Assertions.assertEquals("testEncoding",
        database.query("sql", "select from `This.is:special` where `other.special:property` = 'testEncoding'").nextIfAvailable()
            .getProperty("other.special:property"));
  }

[marco-brandizi]

Thanks again. If _ works, I can switch to that.
I can't use Arcade-specific features like inheritance, cause I'm trying to test Gremlin with data that come from graphml and where all the nodes have at least two labels attached. It seems the only way to emulate that in Gremlin is via tricks like this.

[lvca]

Fixed in the latest snapshot. Now the encoding is processed correctly, no need to change to _. ;-)