Confluence flooded with errors that the table denormalised_state does not exist after upgrade
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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);