arcadedb icon indicating copy to clipboard operation
arcadedb copied to clipboard
verified publisher icon ArcadeData

[SQL] `REBUILD INDEX` deletes indexes when used via async HTTP-API

Open gramian opened this issue 8 months ago • 3 comments

ArcadeDB Server v25.3.2 (build 5995e2d561169414958d65552371bf5ec149ebcd/1742848840057/main)

Running on Linux 6.2.0-26-generic - OpenJDK 64-Bit Server VM 21.0.6

Running REBUILD INDEX * via a sqlscript using awaitResponse=false causes indexes to be deleted, ie:

2025-03-27 12:05:39.357 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_3_9923666682517'. The index will be removed
2025-03-27 12:05:39.760 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_6_9923733586282'. The index will be removed
2025-03-27 12:05:40.161 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_5_9923706338499'. The index will be removed
2025-03-27 12:05:40.562 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_7_9923625797715'. The index will be removed
2025-03-27 12:05:40.971 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_6_9923689850485'. The index will be removed
2025-03-27 12:05:41.372 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_5_9923730843811'. The index will be removed
2025-03-27 12:05:41.773 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_1_9923784495009'. The index will be removed
2025-03-27 12:05:42.174 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_6_9923815682048'. The index will be removed
2025-03-27 12:05:42.576 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_6_9923830752750'. The index will be removed
2025-03-27 12:05:42.977 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_4_9923669864477'. The index will be removed
2025-03-27 12:05:43.378 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_3_9923702441298'. The index will be removed
2025-03-27 12:05:43.779 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_0_9923714552799'. The index will be removed
2025-03-27 12:05:44.180 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_0_9923739504812'. The index will be removed
2025-03-27 12:05:44.581 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_4_9923750626814'. The index will be removed
2025-03-27 12:05:44.982 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_3_9923590601627'. The index will be removed
2025-03-27 12:05:45.383 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_4_9923704305467'. The index will be removed
2025-03-27 12:05:45.784 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_2_9923587607968'. The index will be removed
2025-03-27 12:05:46.185 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_2_9923661557291'. The index will be removed
2025-03-27 12:05:46.586 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_4_9923770876476'. The index will be removed
2025-03-27 12:05:46.987 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_7_9923711770954'. The index will be removed
2025-03-27 12:05:47.388 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_0_9923759915984'. The index will be removed
2025-03-27 12:05:47.789 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_7_9923817376543'. The index will be removed
2025-03-27 12:05:48.190 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_0_9923819912492'. The index will be removed
2025-03-27 12:05:48.591 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_4_9923826260415'. The index will be removed
2025-03-27 12:05:48.992 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_1_9923584543291'. The index will be removed
2025-03-27 12:05:49.394 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_3_9923768390062'. The index will be removed
2025-03-27 12:05:49.795 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_1_9923837591602'. The index will be removed
2025-03-27 12:05:50.196 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_0_9923678555747'. The index will be removed
2025-03-27 12:05:50.597 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_6_9923846705987'. The index will be removed
2025-03-27 12:05:50.998 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_3_9923723427057'. The index will be removed
2025-03-27 12:05:51.399 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_4_9923643412347'. The index will be removed
2025-03-27 12:05:51.800 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_2_9923809117110'. The index will be removed
2025-03-27 12:05:52.201 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_5_9923688174244'. The index will be removed
2025-03-27 12:05:52.604 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_0_9923781498294'. The index will be removed
2025-03-27 12:05:53.008 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_7_9923601969519'. The index will be removed
2025-03-27 12:05:53.411 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_7_9923832522494'. The index will be removed
2025-03-27 12:05:53.814 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_7_9923848104351'. The index will be removed
2025-03-27 12:05:54.216 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_6_9923754983793'. The index will be removed
2025-03-27 12:05:54.619 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_3_9923840803053'. The index will be removed
2025-03-27 12:05:55.021 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_3_9923748249654'. The index will be removed
2025-03-27 12:05:55.424 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_3_9923684866889'. The index will be removed
2025-03-27 12:05:55.827 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_6_9923776062002'. The index will be removed
2025-03-27 12:05:56.230 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_7_9923778532618'. The index will be removed
2025-03-27 12:05:56.633 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_1_9923821964540'. The index will be removed
2025-03-27 12:05:57.034 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_0_9923605398290'. The index will be removed
2025-03-27 12:05:57.436 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_4_9923686546913'. The index will be removed
2025-03-27 12:05:57.837 SEVER [RebuildIndexStatement] Error on rebuild invalid index 'metadata_1_9923743391247'. The index will be removed

Using the same script in sync mode (without awaitResponse) works fine. I will follow up here as soon as I have a minimum example.

gramian avatar Mar 27 '25 11:03 gramian

Here is a minimal example:

CREATE DOCUMENT TYPE doc;
CREATE PROPERTY doc.num LONG;
CREATE INDEX ON doc (num) UNIQUE;

INSERT INTO doc SET num = 0;
INSERT INTO doc SET num = 1;

wget -qO- http://localhost:2480/api/v1/command/test --post-data='{"language":"sqlscript","awaitResponse":false,"command":"REBUILD INDEX *;"}' --user=root --password=password

gramian avatar Mar 27 '25 11:03 gramian

Works for me. Awesome! Thank you very much.

gramian avatar Apr 02 '25 06:04 gramian

Unfortunately, a REBUILD INDEX * in an async HTTP command can still can cause an error:

Error on rebuilding index 'metadata_2_4579875538774': Asynchronous queue 0 is stalled. This could happen when an asynchronous task schedules more asynchronous tasks
com.arcadedb.exception.DatabaseOperationException: Asynchronous queue 0 is stalled. This could happen when an asynchronous task schedules more asynchronous tasks
	at com.arcadedb.database.async.DatabaseAsyncExecutorImpl.scheduleTask(DatabaseAsyncExecutorImpl.java:770)
	at com.arcadedb.schema.BucketIndexBuilder.create(BucketIndexBuilder.java:65)
	at com.arcadedb.query.sql.parser.RebuildIndexStatement.lambda$buildIndex$1(RebuildIndexStatement.java:167)
	at com.arcadedb.database.LocalDatabase.executeLockingFiles(LocalDatabase.java:1476)
	at com.arcadedb.query.sql.parser.RebuildIndexStatement.buildIndex(RebuildIndexStatement.java:152)
	at com.arcadedb.query.sql.parser.RebuildIndexStatement.executeDDL(RebuildIndexStatement.java:96)
	at com.arcadedb.query.sql.executor.DDLExecutionPlan.executeInternal(DDLExecutionPlan.java:60)
	at com.arcadedb.query.sql.executor.ScriptLineStep.syncPull(ScriptLineStep.java:54)
	at com.arcadedb.query.sql.executor.ScriptExecutionPlan.executeFull(ScriptExecutionPlan.java:232)
	at com.arcadedb.query.sql.parser.WhileStep.syncPull(WhileStep.java:55)
	at com.arcadedb.query.sql.executor.SelectExecutionPlan.fetchNext(SelectExecutionPlan.java:44)
	at com.arcadedb.query.sql.executor.UpdateExecutionPlan.executeInternal(UpdateExecutionPlan.java:64)
	at com.arcadedb.query.sql.executor.ScriptLineStep.syncPull(ScriptLineStep.java:52)
	at com.arcadedb.query.sql.executor.ScriptExecutionPlan.executeUntilReturn(ScriptExecutionPlan.java:199)
	at com.arcadedb.query.sql.executor.ScriptExecutionPlan.doExecute(ScriptExecutionPlan.java:97)
	at com.arcadedb.query.sql.executor.ScriptExecutionPlan.fetchNext(ScriptExecutionPlan.java:62)
	at com.arcadedb.query.sql.parser.LocalResultSet.fetchNext(LocalResultSet.java:44)
	at com.arcadedb.query.sql.parser.LocalResultSet.<init>(LocalResultSet.java:40)
	at com.arcadedb.query.sql.SQLScriptQueryEngine.executeInternal(SQLScriptQueryEngine.java:220)
	at com.arcadedb.query.sql.SQLScriptQueryEngine.command(SQLScriptQueryEngine.java:121)
	at com.arcadedb.database.LocalDatabase.command(LocalDatabase.java:1342)
	at com.arcadedb.database.async.DatabaseAsyncCommand.execute(DatabaseAsyncCommand.java:69)
	at com.arcadedb.database.async.DatabaseAsyncExecutorImpl$AsyncThread.run(DatabaseAsyncExecutorImpl.java:143)

2025-04-23 14:12:49.992 SEVER [PostCommandHandler$1] Async command in database "metadatalake" failed.
com.arcadedb.index.IndexException: Error on rebuilding index 'metadata_2_4579875538774' (error=Asynchronous queue 0 is stalled. This could happen when an asynchronous task schedules more asynchronous tasks)
	at com.arcadedb.query.sql.parser.RebuildIndexStatement.executeDDL(RebuildIndexStatement.java:114)
	at com.arcadedb.query.sql.executor.DDLExecutionPlan.executeInternal(DDLExecutionPlan.java:60)
	at com.arcadedb.query.sql.executor.ScriptLineStep.syncPull(ScriptLineStep.java:54)
	at com.arcadedb.query.sql.executor.ScriptExecutionPlan.executeFull(ScriptExecutionPlan.java:232)
	at com.arcadedb.query.sql.parser.WhileStep.syncPull(WhileStep.java:55)
	at com.arcadedb.query.sql.executor.SelectExecutionPlan.fetchNext(SelectExecutionPlan.java:44)
	at com.arcadedb.query.sql.executor.UpdateExecutionPlan.executeInternal(UpdateExecutionPlan.java:64)
	at com.arcadedb.query.sql.executor.ScriptLineStep.syncPull(ScriptLineStep.java:52)
	at com.arcadedb.query.sql.executor.ScriptExecutionPlan.executeUntilReturn(ScriptExecutionPlan.java:199)
	at com.arcadedb.query.sql.executor.ScriptExecutionPlan.doExecute(ScriptExecutionPlan.java:97)
	at com.arcadedb.query.sql.executor.ScriptExecutionPlan.fetchNext(ScriptExecutionPlan.java:62)
	at com.arcadedb.query.sql.parser.LocalResultSet.fetchNext(LocalResultSet.java:44)
	at com.arcadedb.query.sql.parser.LocalResultSet.<init>(LocalResultSet.java:40)
	at com.arcadedb.query.sql.SQLScriptQueryEngine.executeInternal(SQLScriptQueryEngine.java:220)
	at com.arcadedb.query.sql.SQLScriptQueryEngine.command(SQLScriptQueryEngine.java:121)
	at com.arcadedb.database.LocalDatabase.command(LocalDatabase.java:1342)
	at com.arcadedb.database.async.DatabaseAsyncCommand.execute(DatabaseAsyncCommand.java:69)
	at com.arcadedb.database.async.DatabaseAsyncExecutorImpl$AsyncThread.run(DatabaseAsyncExecutorImpl.java:143)
Caused by: com.arcadedb.exception.DatabaseOperationException: Asynchronous queue 0 is stalled. This could happen when an asynchronous task schedules more asynchronous tasks
	at com.arcadedb.database.async.DatabaseAsyncExecutorImpl.scheduleTask(DatabaseAsyncExecutorImpl.java:770)
	at com.arcadedb.schema.BucketIndexBuilder.create(BucketIndexBuilder.java:65)
	at com.arcadedb.query.sql.parser.RebuildIndexStatement.lambda$buildIndex$1(RebuildIndexStatement.java:167)
	at com.arcadedb.database.LocalDatabase.executeLockingFiles(LocalDatabase.java:1476)
	at com.arcadedb.query.sql.parser.RebuildIndexStatement.buildIndex(RebuildIndexStatement.java:152)
	at com.arcadedb.query.sql.parser.RebuildIndexStatement.executeDDL(RebuildIndexStatement.java:96)
	... 17 more

I am unable to provide a minimal example to reproduce ATM.

gramian avatar Apr 23 '25 13:04 gramian