Strange error on index creation

SQL++
1

I have a cluster with two nodes. I want to create an index on one of the nodes using:

CREATE INDEX `geo_nearest_location` ON `data`((ALL (ARRAY [(`p`.`lat`), (`p`.`lon`)] FOR `p` IN `points` END)),`type`,`name`,`key`)
WHERE (`type` IN ['Lake', 'Stream', 'PutTakeLake', 'CoastLocalArea', 'SeaLocalArea']) WITH {'defer_build':TRUE,'nodes':['db1.aqua.dtu.dk:8091']}

and it returns:

[
  {
    "code": 5000,
    "msg": "GSI CreateIndex() - cause: Encountered transient error.  Index creation will be retried in background.  Error: Indexer Cannot Process Create Index - Rebalance In Progress\n",
    "query": "CREATE INDEX `geo_nearest_location` ON `data`((ALL (ARRAY [(`p`.`lat`), (`p`.`lon`)] FOR `p` IN `points` END)),`type`,`name`,`key`)\nWHERE (`type` IN ['Lake', 'Stream', 'PutTakeLake', 'CoastLocalArea', 'SeaLocalArea']) WITH {'defer_build':TRUE,'nodes':['db1.aqua.dtu.dk:8091']}"
  }
]

However, as far as I can see, no rebalance is in progress…???

image
image1513×567 90.3 KB

If I try the index creation once again I get:

[
  {
    "code": 5000,
    "msg": "GSI CreateIndex() - cause: Index already exist.  Fail to create geo_nearest_location_1 in bucket data",
    "query": "CREATE INDEX `geo_nearest_location_1` ON `data`((ALL (ARRAY [(`p`.`lat`), (`p`.`lon`)] FOR `p` IN `points` END)),`type`,`name`,`key`)\nWHERE (`type` IN ['Lake', 'Stream', 'PutTakeLake', 'CoastLocalArea', 'SeaLocalArea']) WITH {'nodes':['db1.aqua.dtu.dk:8091']}"
  }
]

But if I try to query the index:

SELECT * FROM system:indexes WHERE name='geo_nearest_location'

it just returns

{
  "results": []
}

The index works fine in our test environment (same version servers and similar cluster setup).

Edit:
Forgot to mention that I have tried to manually build the index:

BUILD INDEX ON `data` (`geo_nearest_location`)

and it just returns:

[
  {
    "code": 5000,
    "msg": "GSI index geo_nearest_location not found.",
    "query": "BUILD INDEX ON `data` (`geo_nearest_location`)"
  }
]

What could be wrong here? How do I get the index created?

Thanks!
/John

I’m on Community Edition 6.6.0 build 7909 on CentOS 7.9

2

Hi @jda ,

I am not sure why this can happen, but it looks like the query service is unaware of the index - but index service seems to believe that index is present.

Can you please check the indexes tab on Web UI and check if you can see the index? And if you can see the index, what is its state ?

Thanks.

3

Yep, forgot to write at first. The index is not visible in the webUI. I guess it may by using the system:indexes as well

4

Also didn’t write that I even tried to reboot both of the database servers and try the operation again - without success

5

We just had a power failure at the hosting centre :frowning: - so I had the chance to do a rebalance once both servers had come up and were all “green” - but it still fails and the index does not show from the list… So seems like a kind of a “deadlock” situation…

6

Hang on…!

Though it failed with the same error it appears that the index has now been created - and is starting to build…

I’ve got no idea why this happened now. But everything seems to be Ok no :ok_hand::+1:

7

Hi @jda ,

I am glad that the problem got resolved.

I would like to help you with the root cause of problem, but I will need the logs for that. If you can share the logs, you can use cbcollect to collect and share.

Thanks.

8

I have run the cbcollect on both servers and uploaded the files via WeTransfer (available for 7 days):

/John

9

@jda In one of your examples (retry) you had the index name as geo_nearest_location_1 but in the rest it was named geo_nearest_location. Did geo_nearest_location_1 get created but geo_nearest_location not get created?

10

That was just to try and re-create the issue…

It behaved the same way