Editing and creating pages fails on Confluence due to content reconciliation errors - Unknown column 'snapshots0_.contentid' in 'field list'

Summary

Collaborative Editing is not working upon upgrading to Confluence 7.x. Truncating the Synchrony tables does not resolve the issues as per the below article DOES NOT resolve the issue:

• Editing and creating pages fails on Confluence due to content reconciliation errors

Environment

Confluence 7.x

Diagnosis

Confluence Logs

Confluence logs give the following warning message:

The Soft Eviction job failed due to the column does not exist in the table:

Synchrony Logs

Following entries found in the Synchrony logs:

Cause

During the upgrade to Confluence 7.x from the earlier platform of Confluence versions (5.x or 6.x), the columns were not dropped and recreated correctly. The columns from the previous Confluence version did not have the exact same columns as Confluence 7.x for the synchrony tables which is causing the issue.

Solution

1. Get the Collation & Character_Set of the DB - this should be set to UTF-8 :

   MySQL Queries

   1 SELECT @@character_set_database, @@collation_database;

   Postgres Queries

   1 2 3 4 5 SELECT datname, datcollate FROM pg_database; SHOW server_encoding;

   💡Skip this step for Microsoft SQL Server

2. Turn OFF the Collaborative Editing

3. Shutdown Confluence

4. ⚠️ Backup the Database

5. Drop the Synchrony tables by running the following query in your database:

   MySQL, Postgres or Microsoft SQL Server Queries

   1 2 3 DROP TABLE SECRETS; DROP TABLE SNAPSHOTS; DROP TABLE EVENTS;

6. Run the following SQL queries to recreate the Synchrony tables (match/change the Collation & Character_Set accordingly as per the result in Step1)

   MySQL Queries

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE TABLE `EVENTS` ( `rev` varchar(255) COLLATE utf8_bin NOT NULL, `history` varchar(255) COLLATE utf8_bin NOT NULL, `partition` int(11) NOT NULL, `sequence` int(11) NOT NULL, `event` mediumblob, `contentid` bigint(20) NOT NULL, `inserted` datetime NOT NULL, PRIMARY KEY (`rev`,`history`), UNIQUE KEY `e_h_r_idx` (`history`,`rev`), UNIQUE KEY `e_h_p_s_idx` (`history`,`partition`,`sequence`), KEY `e_c_i_idx` (`contentid`,`inserted`), KEY `e_i_c_idx` (`inserted`,`contentid`) ) ENGINE=InnoDB;
   1 2 3 4 5 CREATE TABLE `SECRETS` ( `key` varchar(255) COLLATE utf8_bin NOT NULL, `value` text COLLATE utf8_bin NOT NULL, PRIMARY KEY (`key`) ) ENGINE=InnoDB;
   1 2 3 4 5 6 7 8 9 CREATE TABLE `SNAPSHOTS` ( `key` varchar(255) COLLATE utf8_bin NOT NULL, `value` mediumblob NOT NULL, `contentid` bigint(20) NOT NULL, `inserted` datetime NOT NULL, PRIMARY KEY (`key`), KEY `s_c_i_idx` (`contentid`,`inserted`), KEY `s_i_c_idx` (`inserted`,`contentid`) ) ENGINE=InnoDB;

   Postgres Queries

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 -- assumes schema is named public -- Table: public."EVENTS" -- DROP TABLE public."EVENTS"; CREATE TABLE public."EVENTS" ( rev character varying(255) COLLATE pg_catalog."default" NOT NULL, history character varying(255) COLLATE pg_catalog."default" NOT NULL, partition integer NOT NULL, sequence integer NOT NULL, event bytea, contentid bigint NOT NULL, inserted timestamp without time zone NOT NULL, CONSTRAINT "EVENTS_pkey" PRIMARY KEY (rev, history) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE public."EVENTS" OWNER to <enter-table-owner-id>; -- Index: e_c_i_idx -- DROP INDEX public.e_c_i_idx; CREATE INDEX e_c_i_idx ON public."EVENTS" USING btree (contentid, inserted) TABLESPACE pg_default; -- Index: e_h_p_s_idx -- DROP INDEX public.e_h_p_s_idx; CREATE UNIQUE INDEX e_h_p_s_idx ON public."EVENTS" USING btree (history COLLATE pg_catalog."default", partition, sequence) TABLESPACE pg_default; -- Index: e_h_r_idx -- DROP INDEX public.e_h_r_idx; CREATE UNIQUE INDEX e_h_r_idx ON public."EVENTS" USING btree (history COLLATE pg_catalog."default", rev COLLATE pg_catalog."default") TABLESPACE pg_default; -- Index: e_i_c_idx -- DROP INDEX public.e_i_c_idx; CREATE INDEX e_i_c_idx ON public."EVENTS" USING btree (inserted, contentid) TABLESPACE pg_default;
   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 -- assumes schema is named public -- Table: public."SECRETS" -- DROP TABLE public."SECRETS"; CREATE TABLE public."SECRETS" ( key character varying(255) COLLATE pg_catalog."default" NOT NULL, value character varying(256) COLLATE pg_catalog."default" NOT NULL, CONSTRAINT "SECRETS_pkey" PRIMARY KEY (key) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE public."SECRETS" OWNER to <enter-table-owner-id>;
   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 -- assumes schema is named public -- Table: public."SNAPSHOTS" -- DROP TABLE public."SNAPSHOTS"; CREATE TABLE public."SNAPSHOTS" ( key character varying(255) COLLATE pg_catalog."default" NOT NULL, value bytea NOT NULL, contentid bigint NOT NULL, inserted timestamp without time zone NOT NULL, CONSTRAINT "SNAPSHOTS_pkey" PRIMARY KEY (key) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE public."SNAPSHOTS" OWNER to <enter-table-owner-id>; -- Index: s_c_i_idx -- DROP INDEX public.s_c_i_idx; CREATE INDEX s_c_i_idx ON public."SNAPSHOTS" USING btree (contentid, inserted) TABLESPACE pg_default; -- Index: s_i_c_idx -- DROP INDEX public.s_i_c_idx; CREATE INDEX s_i_c_idx ON public."SNAPSHOTS" USING btree (inserted, contentid) TABLESPACE pg_default;

   Microsoft SQL Server Queries

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE EVENTS ( rev NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, history NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, partition INT NOT NULL, sequence INT NOT NULL, event VARBINARY(MAX), contentid NUMERIC(19,0) NOT NULL, inserted DATETIME NOT NULL, PRIMARY KEY (rev, history) ); CREATE INDEX e_c_i_idx ON EVENTS ( contentid, inserted ); CREATE UNIQUE INDEX e_h_p_s_idx ON EVENTS ( history, partition, sequence ); CREATE UNIQUE INDEX e_h_r_idx ON EVENTS ( history, rev ); CREATE INDEX e_i_c_idx ON EVENTS ( inserted, contentid );
   1 2 3 4 5 6 7 CREATE TABLE SECRETS ( [key] NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, value NVARCHAR(256) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, PRIMARY KEY ([key]) );
   1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE SNAPSHOTS ( [key] NVARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL, value VARBINARY(MAX) NOT NULL, contentid NUMERIC(19,0) NOT NULL, inserted DATETIME NOT NULL, PRIMARY KEY ([key]) ); CREATE INDEX s_i_c_idx ON SNAPSHOTS ( inserted, contentid ); CREATE INDEX s_c_i_idx ON SNAPSHOTS ( contentid, inserted )

   ℹ️ These SQL queries are for MySQL, Postgres and Microsoft SQL Server. Please make the changes accordingly for other types of databases.

7. Start Confluence

8. Turn ON the Collaborative Editing

9. Verify if the issue still persists.