Azure Search Indexer error: "Document key cannot be missing or empty."

Question

I have an index in azure search which is synced to an SQL server table through Change Tracking.

I randomly start getting this error after I make some changes to the table (but not always, and I can't seem to replicate it consistently unfortunately):

[
  {
    "key": null,
    "errorMessage": "Document key cannot be missing or empty."
  }
]

I have checked my table and there are no null values in the column that Azure Search uses for the key (enforced by an sql not null constraint). There is no other solution than deleting the index and recreating it from scratch. Not even deleting all the the documents and running the indexer again gets rid of the error.

[UPDATE - Solved]

As Eugene's answer highlighted, the problem was that the sql table tracked by Azure Search had a primary key that wasn't mapped to the Azure Search key (we were using another unique column as the azure key instead). This cannot happen when using the "SQL Integrated Change Tracking Policy" mode, as the indexer cannot reference deleted rows (the indexer will fail if you reissue an index operation after deleting some rows in the sql tracked table).

After setting the primary key of the sql table to be the same as the indexed azure key, everything seems to run smoothly, even on deletes.

Group

Are there any empty string ("") values in that column by any chance? – Essence

No, and even then, I would expect that reindexing an empty table would solve the problem, but the error stays the same. – Group

You mentioned that you made changes to the table. Have you changed the primary key column by any chance? – Essence

Sorry, I was unclear. No changes to the schema, just normal updates, deletes and inserts. The primary key is a simple int IDENTITY. – Group

Please send me your service and indexer details to eugenesh at the usual Microsoft domain and we'll take a look. Thanks – Essence

Answer 1

In this case, search index's key field is not the same as the primary key column in the table. In such situation, deletion tracking using SQL integrated change tracking policy is not supported because changes table doesn't contain values for the column that maps to index key field. Inserts and updates will work correctly, though.

If possible, consider making table and index keys the same.

Essence

Thank you for this answer Eugene. We had this same issue, due to the fact we had composite PK on the table and had to introduce a new column, just for the purpose of acting like the Azure search index' key, which we see it won't work, because of this issue that Francesco reported. Do you have any suggestions what to do when composite PKs are present on the table and the integrated SQL change tracking is needed? – Meadows

Answer 2

there might be empty value in your pk column, delete in azure sql, delete table_name where pk = ''

Lauryn

This is not possible. Take a look at this SO question for more info: dba.stackexchange.com/questions/115431/… – Meadows