Search Index Issue

Francis Carroll asked on June 8, 2022 16:03

I am running IIs 10, Kentico 13 on asp .net core.

I hotfixed my project from version 13.0.70 to 13.0.72 and i am now getting issues with rebuilding my index.

Event ID:41850
Event type:Error
Event time:6/8/2022 2:49:52 PM
Source:Media files usage
Event code:PROCESS_SEARCH_TASK
IP address:
Description:Message:
[DataConnection.HandleError]:

Query:

WITH AllData AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY [ItemID]) AS [CMS_RN]
FROM
)
SELECT *
FROM AllData
WHERE CMS_RN BETWEEN 1 AND 1000
ORDER BY CMS_RN


Caused exception:
Incorrect syntax near ')'.


Exception type: System.Exception
Stack trace:
at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex)
at CMS.DataEngine.AbstractDataConnection.ExecuteReader(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, CommandBehavior commandBehavior)
at CMS.DataEngine.GeneralConnection.ExecuteReaderCore(QueryParameters query, CommandBehavior commandBehavior)
at CMS.DataEngine.SqlExecutionHelper.ExecuteWithRetryCore[TResult](Func`1 executionFunc, Int32 retryCount, Func`2 exceptionRetryPredicate)
at CMS.DataEngine.SqlExecutionHelper.ExecuteWithDeadlockRetryInternal[TResult](Func`1 executionFunc, Int32 retryCount)
at CMS.DataEngine.DataQueryBase`1.GetReaderFromDBInternal(CommandBehavior commandBehavior, Boolean newConnection)
at CMS.DataEngine.DataQueryBase`1.GetReader(CommandBehavior commandBehavior, Boolean newConnection)
at CMS.DataEngine.DataQueryBase`1.GetReader(CommandBehavior commandBehavior, Boolean newConnection)
at CMS.DataEngine.DataQueryBase`1.GetReader(CommandBehavior commandBehavior, Boolean newConnection)
at CMS.DataEngine.DataQueryBase`1.GetEnumerableResult(CommandBehavior commandBehavior, Boolean newConnection)+MoveNext()
at CMS.DataEngine.DataExtensions.As[InfoType](IEnumerable`1 dataRecords, Func`2 createObjectFunc)
at CMS.DataEngine.ObjectQueryBase`2.GetEnumerator()
at CMS.MediaLibrary.CustomTableItemSearchDocumentManager.GetSearchDocuments(SearchIndexInfo indexInfo)+MoveNext()
at System.Linq.Enumerable.ConcatIterator`1.MoveNext()
at CMS.Search.SearchHelper.RebuildIndex(SearchIndexInfo searchIndex, Func`2 getSearchDocuments)
at CMS.Search.SearchHelper.RebuildIndex(SearchIndexInfo searchIndex, Action`1 rebuildAction)
at CMS.MediaLibrary.MediaFilesUsageSearchIndexer.ExecuteRebuildTask(SearchTaskInfo taskInfo)
at CMS.MediaLibrary.MediaFilesUsageSearchIndexer.ExecuteTask(SearchTaskInfo searchTask)

Message: Incorrect syntax near ')'.

Exception type: System.Data.SqlClient.SqlException
Stack trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at CMS.DataEngine.AbstractDataConnection.ExecuteReader(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, CommandBehavior commandBehavior)
Machine name:
Event URL:
URL referrer:
User agent:

Any help would be appriciated.

iis 10 kentico 13 asp.net core

Recent Answers

Juraj Ondrus answered on June 9, 2022 09:04

Are there any other errors like this? There is the new media files usage smart search index. Isn't it possible that there is some custom, abandoned page type which has no fields?

0 votesVote for this answer Mark as a Correct answer

Francis Carroll answered on June 9, 2022 09:09 (last edited on June 9, 2022 09:42)

Yeah im getting the same issue on the search index on the project that was working before the hotfix. This is also stopping me from rebuilding the index.

Event ID:41983
Event type:Warning
Event time:6/9/2022 8:06:21 AM
Source:Smart search
Event code:INDEXERROR
IP address:
Description:Rebuild of search index causes exception and search index status is set to ERROR.
Index name: MediaFilesUsage

Exception:
[DataConnection.HandleError]:

Query:

WITH AllData AS
(
SELECT *, ROW_NUMBER() OVER (ORDER BY [ItemID]) AS [CMS_RN]
FROM
)
SELECT *
FROM AllData
WHERE CMS_RN BETWEEN 1 AND 1000
ORDER BY CMS_RN


Caused exception:
Incorrect syntax near ')'.


Stack trace: at CMS.DataEngine.AbstractDataConnection.HandleError(String queryText, Exception ex)
at CMS.DataEngine.AbstractDataConnection.ExecuteReader(String queryText, QueryDataParameters queryParams, QueryTypeEnum queryType, CommandBehavior commandBehavior)
at CMS.DataEngine.GeneralConnection.ExecuteReaderCore(QueryParameters query, CommandBehavior commandBehavior)
at CMS.DataEngine.SqlExecutionHelper.ExecuteWithRetryCore[TResult](Func`1 executionFunc, Int32 retryCount, Func`2 exceptionRetryPredicate)
at CMS.DataEngine.SqlExecutionHelper.ExecuteWithDeadlockRetryInternal[TResult](Func`1 executionFunc, Int32 retryCount)
at CMS.DataEngine.DataQueryBase`1.GetReaderFromDBInternal(CommandBehavior commandBehavior, Boolean newConnection)
at CMS.DataEngine.DataQueryBase`1.GetReader(CommandBehavior commandBehavior, Boolean newConnection)
at CMS.DataEngine.DataQueryBase`1.GetReader(CommandBehavior commandBehavior, Boolean newConnection)
at CMS.DataEngine.DataQueryBase`1.GetReader(CommandBehavior commandBehavior, Boolean newConnection)
at CMS.DataEngine.DataQueryBase`1.GetEnumerableResult(CommandBehavior commandBehavior, Boolean newConnection)+MoveNext()
at CMS.DataEngine.DataExtensions.As[InfoType](IEnumerable`1 dataRecords, Func`2 createObjectFunc)
at CMS.DataEngine.ObjectQueryBase`2.GetEnumerator()
at CMS.MediaLibrary.CustomTableItemSearchDocumentManager.GetSearchDocuments(SearchIndexInfo indexInfo)+MoveNext()
at System.Linq.Enumerable.ConcatIterator`1.MoveNext()
at CMS.Search.SearchHelper.RebuildIndex(SearchIndexInfo searchIndex, Func`2 getSearchDocuments)
Machine name:
Event URL:
URL referrer:
User agent:

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on June 9, 2022 11:57

What were the hotfix steps? Were also the NuGet packages updated? What about the page types - are there any types e.g. without any fields?

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on June 9, 2022 12:00

Have you tried re-signing macros?

0 votesVote for this answer Mark as a Correct answer

Francis Carroll answered on June 13, 2022 12:24

Hi,

I followed the steps outlined here https://docs.xperience.io/installation/hotfix-instructions-xperience-13

I updated all the relevant NuGet packages.

All the page types have at least a name field.

I'm unsure how to re-sign macros to see if this fixes it, i havent added any macros to the project myself.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on June 13, 2022 12:48

It could be also some macro used by default, which has invalid signature, which means does not resolve into appropriate value. See the documentation.

0 votesVote for this answer Mark as a Correct answer

Francis Carroll answered on June 13, 2022 14:22

Okay i re-signed the macros and this resolved on my local dev enviroment but this hasnt worked on the UAT enviroment.

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on June 13, 2022 14:56

Does the UAT has the same hash salt string set? I mean for both admin as well as front end app?

0 votesVote for this answer Mark as a Correct answer

Francis Carroll answered on June 13, 2022 14:59

Im unsure how do i check this

0 votesVote for this answer Mark as a Correct answer

Juraj Ondrus answered on June 14, 2022 10:52

Check how the hash salt string is being set in the admin and front end app - so, you need to check it in those locations.

0 votesVote for this answer Mark as a Correct answer