Mysterious Experience Profile Error

I saw a lot of blogs and bumped in to online links about an issue below, but, turns out every case is unique based on setup. The issue I was facing, I could not find a solution out there, but, yeah I cracked it. What does that mean? Yeah, you guessed it right, blog it, so, some one else can save their half day and enjoy a nature walk or something. 😉

So, I was trying to hook up Sitecore and Salesforce together, I will get another blog rolling soon on my learning, frustrations, happy moments noting that experience. But, for this one here, just important to know one of the prerequisites for shipping contacts from Sitecore to Salesforce, first I need to ensure I can see the contacts on Experience profile. All was well on my local Sitecore installation which for reference is version 9.1.1

But…. Experience profile was broken to say the least on upper environments. On Experience Profile UI, you will see something like below. Booooo, not helpful at all!!!!!! Sitecore really need to display some meaningful errors here especially if it is non production license.

Once I got in to servers and starting checking logs, I could see below errors

URL https://authoring.mysite.com/sitecore/api/ao/v1/contacts/search?&pageSize=20&pageNumber=1&sort=visitCount desc&Match=*&FromDate=null&ToDate=null

Exception System.NullReferenceException: Object reference not set to an instance of an object.
at Sitecore.Cintel.Endpoint.Plumbing.NegotiateLanguageFilter.OnActionExecuted(HttpActionExecutedContext actionExecutedContext)
at System.Web.Http.Filters.ActionFilterAttribute.OnActionExecutedAsync(HttpActionExecutedContext actionExecutedContext, CancellationToken cancellationToken)
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__6.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__5.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__5.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Filters.AuthorizationFilterAttribute.<ExecuteAuthorizationFilterAsyncCore>d__3.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__15.MoveNext()

Now, the above error had so many interpretations on web, but, led me no where , I then looked at XConnect logs to see if I can find deeper issue if any and I find the below error on XConnect logs

Sitecore.XConnect.Operations.FacetOperationException: Operation #1, ReferenceNotFound, Contact, Classification
2020-01-29 13:51:40.269 -05:00 [Error] Sitecore.XConnect.Operations.XdbSearchOperation`1[Sitecore.XConnect.Contact]: Sitecore.Xdb.Collection.Search.Solr.Failures.SolrResponseException: {
“responseHeader”:{
“status”:400,
“QTime”:12,
“params”:{
“fl”:”id”,
“cursorMark”:”*”,
“json”:”{\”query\”:\”(x_type_s:(\\\”ContactDataRecord\\\”) AND *:*)\”,\”sort\”:\”facets.engagementmeasures.mostrecentinteractionstartdatetime_dt desc,id asc\”}”,
“rows”:”20″,
“wt”:”json”},
“error”:{
“metadata”:[
“error-class”,”org.apache.solr.common.SolrException”,
“root-error-class”,”org.apache.solr.common.SolrException”],
“msg”:”sort param field can’t be found: id”,
“code”:400}

at Sitecore.Xdb.Collection.Search.Solr.SolrClient.EnsureSolrSuccessStatusCode(HttpResponseMessage response)
at Sitecore.Xdb.Collection.Search.Solr.SolrReader.<ExecuteQuery>d__14`1.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Xdb.Collection.Search.Solr.SolrReader.<GetSearchResults>d__13`2.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Xdb.Collection.Search.Solr.SolrReader.<SearchContacts>d__10.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Xdb.Collection.Repository.<SearchContacts>d__11.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.Xdb.Collection.RepositoryCountersDecorator.<SearchContacts>d__8.MoveNext()
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Sitecore.XConnect.Service.RepositorySearchInvoker.<Execute>d__7.MoveNext()
2020-01-29 13:51:40.269 -05:00 [Error] [“XdbContextLoggingPlugin”] XdbContext Batch Execution Exception
Sitecore.Xdb.Collection.Search.Solr.Failures.SolrResponseException: {
“responseHeader”:{
“status”:400,
“QTime”:12,
“params”:{
“fl”:”id”,
“cursorMark”:”*”,
“json”:”{\”query\”:\”(x_type_s:(\\\”ContactDataRecord\\\”) AND *:*)\”,\”sort\”:\”facets.engagementmeasures.mostrecentinteractionstartdatetime_dt desc,id asc\”}”,
“rows”:”20″,
“wt”:”json”}},
“error”:{
“metadata”:[
“error-class”,”org.apache.solr.common.SolrException”,
“root-error-class”,”org.apache.solr.common.SolrException”],
“msg”:”sort param field can’t be found: id”,
“code”:400}}

Below is the order of things I did to see if that will fix my issue, obviously no luck!!!

Ensured XConnect url is working properly that was provided on Connection strings
Rebuilt all indexes from Control Panel , ran successfully
Ensured SOLR url given on connection strings config works
Ensured NewtonSoft.Json.dll has correct version per Sitecore expectation
Restarted all Sitecore Windows Services + App pool recycle on CM and XConnect site
Also tried deploying all my marketing panel stuff – why not right? lol

Almost gave up, that is when I usually create a support ticket and wait for couple days before re-working on the issue. I did do that, but, in this case, I was having some more energy left, so, I kept debugging and kept digging- Paid real close attention to solr error above, I saw that the json was:
“json”:”{\”query\”:\”(x_type_s:(\\\”ContactDataRecord\\\”) AND *:*)\”,\”sort\”:\”facets.engagementmeasures.mostrecentinteractionstartdatetime_dt desc,id asc\”}”,
I fired up my solr console and entered this breakdown on query window and yep I could replicate the error. Now, I changed the sort field which was the one Solr did not like to _uniqueid and it worked. Why would it work with _uniqueid and not with id? Ringing bells? It did for me and for refresh of memory, I pulled open Sitecore documentation for my version on Solr setup instructions here
It says below – “repeat on all indexes”

But, XConnect code seems to fire Solr query with sort param as id, so, is xonnect UI incorrect? I was not sure and needed to confirm if Sitecore does not want us to do step #3 noted above on XDB indexes. So, I ran populate managed schema on my local and guess what all indexes have unique id modified to _uniqueid except for xdb related indexes. I mean this was still theory, I needed confirmation from Sitecore and my colleague bumped in to another set of documentation where there was NOTE, I could not believe my eyes when I saw that. So, yeah, my theory was correct and I had to login in to my not so favorite SSH Linux Solr server and hand edit the managed-schema file under both xdb and xdb_rebuild conf locations to have unique key as id and also replaced the settings line on schema to have id instead of _uniqueid to ensure all is swapped back. The NOTE that blew my mind and also comforted that I am on right path, screenshot below. May be Sitecore has to update documentation on the other spot to add this *****Important**** note to ensure folks do not change this on all indexes.

Now, once I did that and restarted Solr, Experience Profile is happy, there are no records just yet, but, no error either. yayyyy!!! On to my next challenge now. 🙂