ORA-00904 during upgrade from Spotfire server 7.7 to 11.4

[Frank Schmitt]

When trying to upgrade an existing Spotfire server from 7.7 to 11.4, I get an ORA-00904 PROPERTIES_JSON: invalid identifier:

Internal error during database connection.

com.spotfire.server.tools.upgrade.UpgradeException: Unable to determine the site that the server belongs to: Error while looking up the site for the node

at com.spotfire.server.tools.upgrade.UpgradeManager.preProcess(UpgradeManager.java:217) ~[:]

at com.spotfire.server.tools.upgrade.steps.DatabaseConnectionStep.prepareUpdate(DatabaseConnectionStep.java:69) ~[:]

...

Caused by: java.sql.SQLSyntaxErrorException: [tibcosoftwareinc][Oracle JDBC Driver][Oracle]ORA-00904: "PROPERTIES_JSON": invalid identifier

 

We're using Oracle as the backend. An ORA-00904 usually indicates that the referenced column doesn't exist.

And lo and behold, the setup-Script in the 11.4 installation directory (TIB_sfire_server_11.4.0_winscriptsoracle_installcreate_server_db.sql) contains a CREATE TABLE statement with the offending column:

create table SITES (

SITE_ID char(36) not null,

NAME nvarchar2(200) not null,

PROPERTIES_JSON blob null,

constraint PK_SITES primary key (SITE_ID)

)

 

whereas our current table from the 7.7 installation looks like this:

CREATE TABLE SITES"(

SITE_ID CHAR(36) NOT NULL ENABLE,

NAME NVARCHAR2(200) NOT NULL ENABLE,

CONSTRAINT "PK_SITES" PRIMARY KEY (SITE_ID)

)

 

Q:Can I simply add the missing column PROPERTIES_JSON and re-run the upgrade Or will this destroy my existing installation

[Jens Borgland]

This sounds like a product defect. Please report it to TIBCO Support (with a complete set of upgrade tool logs).