Phoenix global secondary index stuck in FAILED state

Answer first

A global secondary index that ends in FAILED in Apache Phoenix 4.x/5.x will not auto-retry. Once SYSTEM.CATALOG shows INDEX_STATE = FAILED for the index, the entry stays FAILED until the index is manually dropped and recreated after the underlying region problem is resolved.

Confirmed behavior: Phoenix does not have an automated retry for a failed global secondary index build. Manual DROP INDEX then CREATE INDEX is required.

Likely explanation vs confirmed facts

Confirmed: A failed build is recorded in SYSTEM.CATALOG and blocks subsequent DDL on the parent table. Recovery requires dropping the failed index and recreating it.

Likely: The abort is typically caused by a region server becoming unavailable during the background build, e.g., RegionServerNotServingRegionException / NotServingRegionException / IOException for the index partition.

Log patterns to identify an abort

Phoenix query server log, e.g., phoenix-query-server.log:

• WARN/ERROR containing org.apache.phoenix.index.GlobalSecondaryIndexBuilder$IndexBuildTask
• Message pattern: Index build failed for index <indexName> on table <tableName>: <Exception>
• Common exceptions seen with region issues: RegionServerNotServingRegionException, NotServingRegionException, IOException

Client side:

• SQLState 53000 SERVER_ERROR with message Index build failed for index <indexName> on table <tableName>

HBase region server logs for the region hosting the index partition:

• Failed to open region
• RegionMovedException
• Correlation with the index build timestamp

Scoped verification and recovery steps

1. Confirm state:

   SELECT INDEX_NAME, INDEX_STATE FROM SYSTEM.CATALOG WHERE TABLE_NAME='MY_TABLE' AND INDEX_NAME='MY_IDX';

2. Check Phoenix query server log around creation time for GlobalSecondaryIndexBuilder ERROR/WARN and capture the exception stack.
3. Check HBase region server logs for the region that stores the index data for RegionServerNotServingRegionException / Failed to open region errors at the same timestamp.
4. Fix the underlying cluster issue: region server up, disk space, HBase stability.
5. Drop the failed index:

   DROP INDEX MY_IDX ON MY_TABLE;

6. Recreate the index after health is confirmed:

   CREATE INDEX MY_IDX ON MY_TABLE (col1, col2) ...;

Assumption: Phoenix 4.x and 5.x share the same GlobalSecondaryIndexBuilder task and SYSTEM.CATALOG INDEX_STATE model. Log wording can vary slightly by patch version.

One missing detail that changes recommendation: current health of the region server that hosts the index partition at the time of retry. If the node is still down or the region is still moving, recreating will fail again.