Spotfire Statistics Services fails to function or fails to boot up, with an error related to failure in updating the HSQL database.

Spotfire Statistics Services fails to function or fails to boot up, with an error related to failure in updating the HSQL database.

TIBCO Spotfire Statistics Services (TSSS) fails with the error shown below, and is unable to execute new jobs.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.insightful.splusserver.SplusStateInitializer#0' defined in class path resource [applicationContext-job-execution.xml]: Invocation of init method failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [update SPLUS_JOBS set STATUS = -1 where STATUS = 1 and SERVER_INSTANCE = ?]; SQL state [HY000]; error code [50000]; General error: "java.lang.NullPointerException";

Resolution

Spotfire Statistics Services uses the HSQL database, which has a 2GB size limit in the server configuration. It fails when this limit is reached and the server is unable to update the job status in the database.

Quick Recovery:

An easy test, and a way get the server back up and running quickly, is to move the SPSERVER_HOME/db/db.data file to a different location on the local machine, then start the Statistics Services service again. With the db.data file missing in SPSERVER_HOME/db, the server should automatically bootstrap a new empty db.data file on startup.

Long Term Fix:

To prevent this issue from recurring, there are two properties that can be set in the SPSERVER_HOME/conf/spserver.properties file which will enable database cleanup of old jobs in the queue.

1) db.cleanup.run.interval.minutes

2) db.cleanup.time.to.live.days

These properties can be configured so that the database cleanup occurs at a specified interval to clean up the jobs which are over a certain age in the job queue database. By default, "db.cleanup.run.interval.minutes" is set to 0, which means that the Statistics Services job queue database will grow indefinitely. More information on these properties follows.

1) db.cleanup.run.interval.minutes

Default Value: 0

The interval used for monitoring the length of the Spotfire Statistics Services job queue. This property works in combination with "db.cleanup.time.to.live.days". The default value of 0 specifies that the Spotfire Statistics Services job queue database should grow indefinitely. Note that the job queue can also be managed manually, using the delete function included with the URL API. (See "Help" for the URL API, available from the server landing page.)

This property may be necessary to automate the process. For example, this property might be useful in a Spotfire Statistics Services cluster, or if your group generates thousands of requests. The optimal value usually depends on the rate of incoming requests and the amount of time the server has been active. This setting works with the db.cleanup.time.to.live.days setting which is set to 7 days by default.

2) db.cleanup.time.to.live.days

Default Value: 7

An indicator of whether an entry in the job queue is old enough for deletion when the cleanup runs. The optimal value usually depends on the rate of incoming requests and the amount of time the server has been active. Once these settings have been set to work for your rate of incoming requests and the amount of time the server is active, the Statistics Services service needs to be restarted (as a Windows or Linux service) to make the changes take effect.

Reference

https://docs.tibco.com/pub/sf_statsvcs/7.0.0/doc/pdf/TIB_sf_statsvcs_7.0.0_installation.pdf