如何从Confluence数据库手动删除陈旧草稿

如果您的Confluence数据库存储了大量过时草稿，则协作编辑可能需要很长时间甚至无法启用。查看下面的错误了解更多详情：CONFSERVER-55745-如果数据库关闭时存在大量陈旧的草稿，则协作编辑需要花费不可行的时间诊断诊断1要在数据库中标识此状态下的草稿数，请运行以下...

chilin6834

718人浏览 · 2019-02-02 10:33:00

chilin6834 · 2019-02-02 10:33:00 发布

如果您的Confluence数据库存储了大量过时草稿，则协作编辑可能需要很长时间甚至无法启用。查看下面的错误了解更多详情：

CONFSERVER-55745 - 如果数据库关闭时存在大量陈旧的草稿，则协作编辑需要花费不可行的时间

诊断

诊断1

要在数据库中标识此状态下的草稿数，请运行以下查询：

SELECT d.contentid, d.title, d.prevver, d.lastmoddate, c.lastmoddate
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate;

如果它导致成千上万的草稿，请继续下面的解决方案。

诊断2

在 atlassian-confluence.log尝试启用协同编辑的时候抛出这个堆栈跟踪：

2018-07-05 22:26:37,795 ERROR [Long running task: EnableTask] [plugins.synchrony.tasks.AbstractConfigLongRunningTask] runInternal An error occurred when running a Synchrony ConfigLongRunningTask
 -- url: /rest/synchrony-interop/enable | referer: http://localhost:8090/admin/confluence-collaborative-editor-plugin/configure.action | traceId: d0e2ba5342874779 | userName: admin
org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:241)
    at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:366)
    at org.springframework.orm.hibernate5.HibernateTemplate.executeWithNativeSession(HibernateTemplate.java:326)
    at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.findNamedQueryStringParams(HibernateObjectDao.java:444)
    at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.findNamedQueryStringParam(HibernateObjectDao.java:320)
    at com.atlassian.confluence.pages.persistence.dao.hibernate.AbstractHibernateAttachmentDao.getLatestVersionsOfAttachmentsWithAnyStatus(AbstractHibernateAttachmentDao.java:134)
    at com.atlassian.confluence.pages.attachments.CachingAttachmentDao.getLatestVersionsOfAttachmentsWithAnyStatus(CachingAttachmentDao.java:141)
    at com.atlassian.confluence.pages.DefaultAttachmentManager.getLatestVersionsOfAttachmentsWithAnyStatus(DefaultAttachmentManager.java:288)
    at com.atlassian.confluence.pages.DelegatorAttachmentManager.getLatestVersionsOfAttachmentsWithAnyStatus(DelegatorAttachmentManager.java:75)
    at com.atlassian.confluence.pages.CachingAttachmentManager.getLatestVersionsOfAttachmentsWithAnyStatus(CachingAttachmentManager.java:67)
    at sun.reflect.GeneratedMethodAccessor2351.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
    at com.sun.proxy.$Proxy58.getLatestVersionsOfAttachmentsWithAnyStatus(Unknown Source)
    at com.atlassian.confluence.core.DefaultContentEntityManager.removeContentEntity(DefaultContentEntityManager.java:213)
    at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1374)
    at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:580)
    at com.atlassian.confluence.pages.DefaultPageManager.lambda$removeStaleSharedDrafts$10(DefaultPageManager.java:766)
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
    at com.atlassian.confluence.pages.DefaultPageManager.removeStaleSharedDrafts(DefaultPageManager.java:764)
    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:497)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at com.atlassian.spring.interceptors.SpringProfilingInterceptor.invoke(SpringProfilingInterceptor.java:16)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at com.atlassian.confluence.util.profiling.ConfluenceMonitoringMethodInterceptor.invoke(ConfluenceMonitoringMethodInterceptor.java:34)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
    at com.sun.proxy.$Proxy107.removeStaleSharedDrafts(Unknown Source)
    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:497)
    at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
    at com.sun.proxy.$Proxy258.removeStaleSharedDrafts(Unknown Source)
    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:497)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
    at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invokeUnprivileged(ServiceTCCLInterceptor.java:70)
    at org.eclipse.gemini.blueprint.service.util.internal.aop.ServiceTCCLInterceptor.invoke(ServiceTCCLInterceptor.java:53)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.eclipse.gemini.blueprint.service.importer.support.LocalBundleContextAdvice.invoke(LocalBundleContextAdvice.java:57)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133)
    at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
    at com.sun.proxy.$Proxy2280.removeStaleSharedDrafts(Unknown Source)
    at com.atlassian.confluence.plugins.synchrony.tasks.EnableTask.execute(EnableTask.java:53)
    at com.atlassian.confluence.plugins.synchrony.tasks.AbstractConfigLongRunningTask.runInternal(AbstractConfigLongRunningTask.java:65)
    at com.atlassian.confluence.util.longrunning.ConfluenceAbstractLongRunningTask.run(ConfluenceAbstractLongRunningTask.java:26)
    at com.atlassian.confluence.util.longrunning.ManagedTask.run(ManagedTask.java:52)
    at com.atlassian.confluence.impl.util.concurrent.ConfluenceExecutors$ThreadLocalContextTaskWrapper.lambda$wrap$1(ConfluenceExecutors.java:90)
    at com.atlassian.confluence.vcache.VCacheRequestContextOperations.lambda$doInRequestContext$0(VCacheRequestContextOperations.java:50)
    at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContextInternal(VCacheRequestContextManager.java:87)
    at com.atlassian.confluence.impl.vcache.VCacheRequestContextManager.doInRequestContext(VCacheRequestContextManager.java:71)
    at com.atlassian.confluence.vcache.VCacheRequestContextOperations.doInRequestContext(VCacheRequestContextOperations.java:49)
    at com.atlassian.confluence.vcache.VCacheRequestContextOperations.lambda$withRequestContext$2(VCacheRequestContextOperations.java:66)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:112)
    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.executeUpdate(ResultSetReturnImpl.java:208)
    at org.hibernate.engine.jdbc.batch.internal.NonBatchingBatch.addToBatch(NonBatchingBatch.java:45)
    at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3315)
    at org.hibernate.persister.entity.AbstractEntityPersister.delete(AbstractEntityPersister.java:3552)
    at org.hibernate.action.internal.EntityDeleteAction.execute(EntityDeleteAction.java:99)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:586)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:460)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:337)
    at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:50)
    at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1389)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1474)
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1441)
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1410)
    at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.lambda$findNamedQueryStringParams$5(HibernateObjectDao.java:468)
    at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:359)
    ... 85 more
Caused by: java.sql.SQLException: The DELETE statement conflicted with the REFERENCE constraint "FK_NOTIFICATIONS_CONTENT". The conflict occurred in database "CONFLUENCE", table "dbo.NOTIFICATIONS", column 'CONTENTID'.
    at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
    at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:727)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)
    at org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:97)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:205)
    ... 99 more

这似乎是由Hibernate启动自动刷新时表的顺序引起的。继续执行下面的解决方案，手动清除数据库中的陈旧草稿。

解决方案

停止Confluence（如果运行DC，则为Synchrony）
备份数据库
运行以下查询以删除Stale Draft条目：

DELETE FROM NOTIFICATIONS WHERE CONTENTID IN (SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate);
 
DELETE FROM CONFANCESTORS WHERE DESCENDENTID IN (SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate);
 
DELETE FROM BODYCONTENT WHERE CONTENTID IN (SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate);
 
DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN (SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate);
 
DELETE FROM LINKS WHERE CONTENTID IN (SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate);
 
DELETE FROM usercontent_relation WHERE targetcontentid IN (SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate);
 
DELETE FROM extrnlnks WHERE CONTENTID IN (SELECT d.contentid
FROM CONTENT d JOIN CONTENT c
 ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
 AND d.prevver IS NOT NULL
 AND c.lastmoddate > d.lastmoddate);
 
DELETE FROM likes WHERE CONTENTID IN (SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate);
 
DELETE FROM CONTENT WHERE CONTENTID IN (SELECT d.contentid
FROM CONTENT d
JOIN CONTENT c
  ON d.prevver = c.contentid
WHERE d.content_status = 'draft'
  AND d.prevver IS NOT NULL
  AND c.lastmoddate > d.lastmoddate);

delete from bandana where bandanakey = 'synchrony_collaborative_editor_app_registered'
         or bandanakey = 'synchrony_collaborative_editor_app_secret'
         or bandanakey = 'synchrony_collaborative_editor_app_id';

启动Confluence
转到Collaborative Editing管理页面
尝试将模式更改为ON

转载于:https://my.oschina.net/dushougudu/blog/3008238

腾讯云开发者社区

腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。

更多推荐

Js分割字符串(单个分割符、多个分割符、正则)

1.单个分割符1.1定义和用法split() 方法用于把一个字符串分割成字符串数组。1.2语法stringObject.split(separator,howmany)参数描述separator必需。字符串或正则表达式，从该参数指定的地方分割 stringObject。howmany可选。该参数可指定返回的数组的最大长度。如果设置了该参数，返回的子串不会多于这个参数指定的数组。如果没有设置该参数，

腾讯云开发者社区

curl

什么是curl命令？curl是利用URL语法在命令行方式下工作的开源文件传输工具。它被广泛应用在Unix、多种Linux发行版中，并且有DOS和Win32、Win64下的移植版本。如何在windows下使用curl命令？第一步：进入curl下载官网，下载合适的版本，我这里下载的是windows 64位的curl。其中我下载的zip文件。另外CAB文件也是压缩文件，这...

腾讯云开发者社区

Zynq7000 USB2.0协议解析及USB控制器详解

USB 2.0规范及控制器文章目录USB 2.0规范及控制器USB2.0Univerasl Serial BusUSBHostUSB总线接口标准总线信号USB拓扑结构数据流模型数据编解码和位填充USB逻辑部件USB时间基准USB 描述符USB设备USB设备供电方式USB设备分层USB设备插入检测机制USB设备状态USB总线枚举USB传输传输类型包(Packet)事务(transaction)传输(