How to solve "Missing constraint for table" error

Products	Versions
Spotfire Server	7.5 and higher

Description

After upgrading Spotfire Server from version 7.0 to 7.5+, you may see an issue where the Spotfire Server does not start up with following error message in the logs:

ERROR 2017-10-19T08:11:08,656+0300 [*Initialization*] web.context.ContextLoader: Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'lifecycleManager' defined in ServletContext resource [/WEB-INF/applicationContext.xml]: Bean instantiation via constructor failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.spotfire.server.LifecycleManager]: Constructor threw exception; nested exception is com.spotfire.server.ServerInitializationException: TIBCO Spotfire Server will not be started. Database consistency verification failed. Errors found:
Unexpected constraint for table ROUTING_RULES: OracleDatabaseConstraint[
  name: ROUTING_RULES_STATUS
  type: C
  searchCondition: (STATUS = 0) or (STATUS = 1) or (STATUS = 2)
]
Missing constraint for table ROUTING_RULES: OracleDatabaseConstraint[
  name: ROUTING_RULES_STATUS
  type: C
  searchCondition: (STATUS = 0) or (STATUS = 1) or (STATUS = 2) or (STATUS = 3)
]

    at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:279) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1193) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1095) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:513) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:483) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:306) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:230) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:302) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:197) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:761) ~[spring-beans.jar:4.3.7.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:866) ~[spring-context.jar:4.3.7.RELEASE]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:542) ~[spring-context.jar:4.3.7.RELEASE]
    at org.springframework.web.context.ContextLoader.configureAndRefreshWebApplicationContext(ContextLoader.java:443) ~[spring-web.jar:4.3.7.RELEASE]
    at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:325) ~[spring-web.jar:4.3.7.RELEASE]
    at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:107) ~[spring-web.jar:4.3.7.RELEASE]
    at com.spotfire.server.lifecycle.SpotfireServerInitializer.contextInitialized(SpotfireServerInitializer.java:56) ~[server.jar:?]
    at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4743) ~[catalina.jar:8.5.16]
    at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5207) ~[catalina.jar:8.5.16]
    at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150) ~[catalina.jar:8.5.16]
    at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:752) ~[catalina.jar:8.5.16]
    at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:728) ~[catalina.jar:8.5.16]
    at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:734) ~[catalina.jar:8.5.16]
    at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1144) ~[catalina.jar:8.5.16]
    at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1878) ~[catalina.jar:8.5.16]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_131]
    at java.util.concurrent.FutureTask.run(FutureTask.java:266) ~[?:1.8.0_131]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_131]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_131]
    at java.lang.Thread.run(Thread.java:748) [?:1.8.0_131]
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [com.spotfire.server.LifecycleManager]: Constructor threw exception; nested exception is com.spotfire.server.ServerInitializationException: TIBCO Spotfire Server will not be started. Database consistency verification failed. Errors found:
Unexpected constraint for table ROUTING_RULES: OracleDatabaseConstraint[
  name: ROUTING_RULES_STATUS
  type: C
  searchCondition: (STATUS = 0) or (STATUS = 1) or (STATUS = 2)
]
Missing constraint for table ROUTING_RULES: OracleDatabaseConstraint[
  name: ROUTING_RULES_STATUS
  type: C
  searchCondition: (STATUS = 0) or (STATUS = 1) or (STATUS = 2) or (STATUS = 3)

This means that Spotfire Database consistency verification expects to find four constrains on ROUTING_RULES table (or any other table in the similar context),

(STATUS = 0) or (STATUS = 1) or (STATUS = 2) or (STATUS = 3)

but only finds three.

(STATUS = 0) or (STATUS = 1) or (STATUS = 2)

Issue/Introduction

After upgrading Spotfire Server from version 7.0 to 7.5+, you may see an issue where the Spotfire Server does not start up

Environment

Windows

Resolution

To resolve this issue, you can manually alter database entry for relevant table and add statuses by executing following commands:

ALTER TABLE ROUTING_RULES DROP CONSTRAINT ROUTING_RULES_STATUS
/

to delete constrains from ROUTING_RULES_STATUS

ALTER TABLE ROUTING_RULES ADD CONSTRAINT ROUTING_RULES_STATUS CHECK ((STATUS = 0) or (STATUS = 1) or (STATUS = 2) or (STATUS = 3))
/

to add new constrains to ROUTING_RULES_STATUSES

Welcome to "KB Articles"