FTSIncrementalIndexer agent is not working
FTSIncrementalIndexer agent is not able to process the requests in the queue especially when there are some work items getting created in bulk or updates to existing work item during the process.
com.pega.pegarules.pub.database.DatabaseException: code: 16943 SQLState: S0004 Message: Could not complete cursor operation because the table schema changed after the cursor was declared.
Steps to Reproduce
Proper indexes are not picking at runtime.
Perform the following local-change steps:
- Ensure that the FTSIncrementalIndexer run on all nodes. This takes care of keeping Rules, Data and Work indexes in synchronization.
One can turn off SystemWorkIndexer and SystemIndexer agents on all nodes.
- Take backup of pr_sys_queue_ftsindexer table.
- Truncate pr_sys_queue_ftsindexer table.
- Create an index on pr_sys_queue_ftsindexer table as follows:
CREATE INDEX "PEGADATA"."PR_SYS_QUEUE_FTSINDEXER_INDEX3" ON "PEGADATA"."PR_SYS_QUEUE_FTSINDEXER" ("PYITEMSTATUS", PXOBJCLASS, "PYMINIMUMDATETIMEFORPROCESSING","PZINSKEY");
- Stop the JVM.
- Remove all the contents in the search index directory.
- Start the JVM.
- Go to search landing page and manually begin re-indexing WORK-,RULE-,DATA-.
- Go to SMA and restart FTSIncremental Agent.
- Make sure FTSIncremental Agent is running every 60 seconds.
- Create and search newly created work objects.