A new Connections customer got in touch with a raft of problems after an upgrade to Connections 6. One of them was a problem with Touchpoint which stopped them from completing the on boarding process which caused them to repeatedly be directed to Touchpoint. What was happening was that they were able to get two or three screens in to “Add your interests” and then they couldn’t go further and had to use “finish later” or they were faced with “Error during prefetching for step profileTags.”
A quick Google of “profileTags” turned up references to search within Connections. I checked the index (which I hadn’t got around to doing just yet) and I didn’t find INDEX.READY. The search index had not been created due to LTPAToken exceptions which needed the scheduled tasks to be cleared and all clearScheduler.sql scripts run. Once the search index was created Touchpoint worked.
I was checking things after migrating IBM Connections from version 4.0 to 5.5 and found the following error in the application server hosting Search. It didn’t stop the search index and returning results.
[11/18/16 18:46:00:604 GMT] 000001ba XmlBeanDefini I org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
[11/18/16 18:46:00:627 GMT] 000001ba SQLErrorCodes I org.springframework.jdbc.support.SQLErrorCodesFactory <init> SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
[11/18/16 18:46:00:645 GMT] 000001ba IndexingTaskB W com.ibm.connections.search.ejbs.indexing.IndexingTaskBean processTask CLFRW0395E: An error occurred while running the scheduled indexing task named 15min-search-indexing-task.
com.ibm.connections.search.admin.index.exception.IndexingTaskException: org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL ; SQL state [null]; error code ; Error: executeQueryForObject returned too many results.; nested exception is java.sql.SQLException: Error: executeQueryForObject returned too many results.
I Googled “returned too many results” and it hinted at duplicate data in databases for different IBM products. Hmmm.
I enabled the following trace and ran a one of indexing task, SearchService.indexNow(“all_configured”)
com.ibm.connections.search.index.indexing.*=all: com.ibm.connections.search.seedlist.*=all: com.ibm.connections.httpClient.*=all
In trace.log I saw more information and just prior to the database exception I saw resume token messages
[11/18/16 18:46:00:580 GMT] 000001ba ResumeTokenIn > com.ibm.connections.search.seedlist.crawler.util.ResumeTokenInterpreter getInitialResumeToken ENTRY wikis
[11/18/16 18:46:00:580 GMT] 000001ba ResumeTokenIn > com.ibm.connections.search.seedlist.crawler.util.ResumeTokenInterpreter resumeTokenFromDate ENTRY Thu Jan 01 01:00:00 GMT 1970 wikis
[11/18/16 18:46:00:580 GMT] 000001ba ResumeTokenIn < com.ibm.connections.search.seedlist.crawler.util.ResumeTokenInterpreter resumeTokenFromDate RETURN AAAAAAAAAAA=
[11/18/16 18:46:00:580 GMT] 000001ba ResumeTokenIn < com.ibm.connections.search.seedlist.crawler.util.ResumeTokenInterpreter getInitialResumeToken RETURN AAAAAAAAAAA=
Resume tokens and references to duplicate data in the database, hmmm. Well HOMEPAGE has the SR_RESUME_TOKENS table. I opened it in dbVisualizer and saw this.
It didn’t look right and compared it with other deployments and found that others only have the one row per application. The knowledge center details how to manipulate them but not clear them.
I shut down all application servers and backed up HOMEPAGE database. I then cleared the table
# su – db2inst1
$ cd /opt2/db2backups/55_homepage_resumetokens/homepage/
$ db2 backup db homepage to ‘/opt2/db2backups/55_homepage_resumetokens/homepage/’
$ db2 connect to homepage
$ db2 “DELETE FROM HOMEPAGE.SR_RESUME_TOKENS WHERE NODE_ID = ‘*****Node01:InfraCluster_server1′”
$ db2 connect reset
On startup the errors have gone and there is only one row per application.