Summary

After upgrading to a 7.11+ version errors indicating that the DENORMALISED_STATE table does not exist flood the logs. This has been observed on an Oracle database, so the error code was ORA-00942.

The following will appear in the stack trace:

Caused by: Error : 942, Position : 134, Sql = select denormalis0_.SERVICE_TYPE as col_0_0_, denormalis0_.STATE as col_1_0_, denormalis0_.LAST_UP_TO_DATE_TIMESTAMP as
col_2_0_ from DENORMALISED_STATE denormalis0_, OriginalSql = select denormalis0_.SERVICE_TYPE as col_0_0_, denormalis0_.STATE as col_1_0_, denormalis0_.LAST_UP_TO_DATE_TIMESTAMP as
col_2_0_ from DENORMALISED_STATE denormalis0_, Error Msg = ORA-00942: table or view does not exist

...

Caused by: java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

Environment

• Confluence 7.11+
• Instance upgraded from versions less than 7.11
• Using Oracle

Diagnosis

This issue can be easily diagnosed by confirming that the table does not exist. A select query for that table or a listing of all tables in the Database can be used for this confirmation.

SELECT * FROM DENORMALISED_STATE;

Please be mindful of column capitalisation.

Cause

This table is meant to be created the first time that Confluence starts after an upgrade to a 7.11+ version. It is not clear why that may not occur.

Solution

The solution would be to create that table. For Oracle this can be done the following way:

CREATE TABLE "DENORMALISED_STATE" 
   (	"SERVICE_TYPE" NVARCHAR2(255) NOT NULL ENABLE, 
	"STATE" NVARCHAR2(255), 
	"LAST_UP_TO_DATE_TIMESTAMP" NUMBER(19,0), 
	 PRIMARY KEY ("SERVICE_TYPE")
	 );

Though this has only been encountered with an Oracle database, the same can be done for other databases, by altering the creation statements to be according to their specifications. I will note the Postgres creation statement below as an example:

CREATE TABLE DENORMALISED_STATE (
    service_type character varying(255) NOT NULL,
    state character varying(255),
    last_up_to_date_timestamp bigint
);
ALTER TABLE ONLY DENORMALISED_STATE       
    ADD CONSTRAINT denormalised_state_pkey PRIMARY KEY (service_type);