"Spanner operation failed" creating NULL_FILTERED indexes

Question

I am unable to create indexes on Cloud Spanner tables, receiving the error, "Spanner operation failed".

Even after increasing the cluster size to from 6 to 16 nodes, I am unable to create two indexes on a ~12 million row table.

What I did:

• Created objects table in Spanner on 3-node cluster
• Table has 10-12 columns consisting of STRING, INT64, and one ARRAY<STRING>
• Primary key is two columns; a shard value (hash of object_id) and object_id
• Loaded ~12 million rows
• Table had no indexes (other than the primary key) when loaded
• Loading pegged 3-node; upgraded to 6 nodes

What I tried:

• Tried to build three indexes (via DDL in console) -- received "Spanner operation failed"
• Increased Spanner node count from 6 --> 12,
• Was able to build 1 of 3 indexes (UNIQUE on single STRING column)
• Tried to build other two indexes (UNIQUE NULL_FILTERED on single STRING columns) -- received "Spanner operation failed"
• Increased Spanner nodes from 12 --> 16 (account max)
• Tried to build other two indexes (UNIQUE NULL_FILTERED on single STRING columns) -- received "Spanner operation failed"

What else I tried (updated):

• Removed NULL_FILTERED clauses and tried to build other two indexes. Did not resolve, still unable to build.

Answer 1

Response from GCS:

Our product team was able to find the reason the indexes are not able to be created. As it appears, within the data, you have two entries that are not unique and thus creates a uniqueness violation[1] and prevent the index from being created. This error is thrown prior to the index attempt and that is why the index creation fails before it is even attempted.

You can use a query to find the duplicate entries:

SELECT column, count(column) 
FROM table
GROUP BY column
HAVING COUNT(column) > 1

You can modify this query to search all keys at the same time or modify it after each search. Once the duplicates have been taken care of, you should be able to manage these entries and run the index creation again.

---

I hope the Spanner team can fix this bug and return the correct error in a future release.