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

関連コンテンツ

  • 関連コンテンツがありません

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。

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.

*Fisheye および Crucible は除く

 

要約

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:

環境

Confluence 7.x 

診断

Confluence Logs

Confluence logs give the following warning message:

2020-05-16 00:15:30,000 WARN [http-nio-8090-exec-8] [plugins.synchrony.service.SynchronyContentService] runRecoveryInTransaction Could not reconcile content for restored. ID: 909090909 Caused by: com.atlassian.confluence.plugins.synchrony.model.SynchronyException: UNKNOWN_ERROR
-- space: 12345678 | url: /plugins/editor-loader/editor.action | page: 88888888 | traceId: 4d276b889a47b5e9 | userName: test | referer: https://confluence.com/pages/viewpage.action?pageId=88888888 | action: editor

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

2020-05-16 00:10:00,048 ERROR [Caesium-1-3] [impl.schedule.caesium.JobRunnerWrapper] runJob Scheduled job SynchronyEventsSoftRemovalScheduledJob#SynchronyEventsSoftRemovalScheduledJob failed to run
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:230)
at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:391)
at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:336)
at com.atlassian.confluence.pages.persistence.dao.DefaultSynchronyEvictionDao.findSafeContentWithHistoryOlderThan(DefaultSynchronyEvictionDao.java:35)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at com.atlassian.spring.interceptors.SpringProfilingInterceptor.invoke(SpringProfilingInterceptor.java:16)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:295)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy166.findSafeContentWithHistoryOlderThan(Unknown Source)
at com.atlassian.confluence.pages.collab.impl.DefaultSynchronyDataService.lambda$softRemoveHistoryOlderThan$0(DefaultSynchronyDataService.java:90)
at com.atlassian.confluence.pages.collab.impl.DefaultSynchronyDataService.removeContentHistoryInBatches(DefaultSynchronyDataService.java:151)
at com.atlassian.confluence.pages.collab.impl.DefaultSynchronyDataService.softRemoveHistoryOlderThan(DefaultSynchronyDataService.java:89)
at com.atlassian.confluence.impl.schedule.jobs.synchrony.eviction.SynchronyEventsSoftRemovalScheduledJob.runJob(SynchronyEventsSoftRemovalScheduledJob.java:39)
at com.atlassian.confluence.impl.schedule.caesium.JobRunnerWrapper.doRunJob(JobRunnerWrapper.java:117)
at com.atlassian.confluence.impl.schedule.caesium.JobRunnerWrapper.lambda$runJob$0(JobRunnerWrapper.java:87)
at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContextInternal(VCacheRequestContextManager.java:84)
at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:68)
at com.atlassian.confluence.impl.schedule.caesium.JobRunnerWrapper.runJob(JobRunnerWrapper.java:87)
at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:134)
at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:106)
at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:90)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:435)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeLocalJob(CaesiumSchedulerService.java:402)
at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:380)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:66)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:60)
at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:35)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:80)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2123)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1911)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1887)
at org.hibernate.loader.Loader.doQuery(Loader.java:932)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
at org.hibernate.loader.Loader.doList(Loader.java:2615)
at org.hibernate.loader.Loader.doList(Loader.java:2598)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2430)
at org.hibernate.loader.Loader.list(Loader.java:2425)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:370)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1481)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1441)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1410)
at com.atlassian.confluence.pages.persistence.dao.DefaultSynchronyEvictionDao.lambda$findSafeContentWithHistoryOlderThan$0(DefaultSynchronyEvictionDao.java:41)
at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:384)
... 35 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'snapshots0_.contentid' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:431)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:71)
... 52 more


Synchrony Logs

Following entries found in the Synchrony logs:

2020-05-16 00:15:29,991 DEBUG [502:StdOutHandler [/opt/atlassian/confluence/jre/bin/java]] 2020-05-15 16:15:29,991 WARN [async-dispatch-5] [synchrony.sync.hubs] Caught BatchUpdateException for insert into `EVENTS` (`history`, `rev`, `partition`, `sequence`, `event`, `inserted`, `contentid`) values (?, ?, ?, ?, ?, ?, ?) {:entity "/Synchrony-b12345d1-1234-5678-9102-80000b33f55a/confluence-88888888", :throwable #error {
2020-05-16 00:15:29,991 DEBUG [502:StdOutHandler [/opt/atlassian/confluence/jre/bin/java]] :cause "Unknown column 'inserted' in 'field list'"
2020-05-16 00:15:29,991 DEBUG [502:StdOutHandler [/opt/atlassian/confluence/jre/bin/java]] :via
2020-05-16 00:15:29,991 DEBUG [502:StdOutHandler [/opt/atlassian/confluence/jre/bin/java]] [{:type com.mysema.query.QueryException


原因

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.

ソリューション

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

    MySQL のクエリ 
    SELECT @@character_set_database, @@collation_database;
    Postgres のクエリ 
    SELECT datname, 
       datcollate
FROM pg_database;

SHOW server_encoding;

    (lightbulb) Skip this step for Microsoft SQL Server

  2. Turn OFF the Collaborative Editing
  3. Confluence をシャットダウンします。
  4. (warning) Backup the Database

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

    MySQL, Postgres or Microsoft SQL Server Queries 
    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 のクエリ 
    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;
    CREATE TABLE `SECRETS` (
`key` varchar(255) COLLATE utf8_bin NOT NULL,
`value` text COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`key`)
) ENGINE=InnoDB;
    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 のクエリ 
    -- 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;
    -- 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>;
    -- 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 のクエリ 
    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 );
    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])
    );
    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 )


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

  7. Confluence を起動します。
  8.  Turn ON the Collaborative Editing
  9.  Verify if the issue still persists.


最終更新日: 2021 年 10 月 5 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する

関連コンテンツ

  • 関連コンテンツがありません
Powered by Confluence and Scroll Viewport.