Confluence flooded with errors that the table denormalised_state does not exist after upgrade
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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:
1
2
3
4
5
6
7
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 existEnvironment
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.
1
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:
1
2
3
4
5
6
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:
1
2
3
4
5
6
7
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);Was this helpful?