How to solve "Missing constraint for table" error

How to solve "Missing constraint for table" error

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)

  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