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

如果您的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启动自动刷新时表的顺序引起的。继续执行下面的解决方案,手动清除数据库中的陈旧草稿。

解决方案

  1. 停止Confluence(如果运行DC,则为Synchrony)
  2. 备份数据库
  3. 运行以下查询以删除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';
  1. 启动Confluence
  2. 转到Collaborative Editing管理页面
  3. 尝试将模式更改为ON

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值