Reputation: 147
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.
Upvotes: 4
Views: 4619
Reputation: 647
there might be empty value in your pk column, delete in azure sql, delete table_name where pk = ''
Upvotes: -1
Reputation: 4671
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.
Upvotes: 3