Usage of Gliffy plugin version 8.2.0 causes the creation of multiple page drafts

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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

Problem

Versions 8.2.0 of the Gliffy plugin has been known to create multiple page drafts.  This, in turn, has resulted in a few known problems within Confluence.

Space Deletion

First,  if deleting a space in which this version of the plugin was used, then users may run into the following error which prevents the deletion from occurring:

2019-04-25 05:56:07,150 ERROR [Long running task: Space removal long running task] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ERROR: update or delete on table "content" violates foreign key constraint "fkk6kbb7suqeloj82nx7xdcd803" on table "content"
  Detail: Key (contentid)=(84848484) is still referenced from table "content".
 -- url: /conf/rest/api/latest/space/KEY | traceId: 26386459d9ac9452 | userName: admin
2019-04-25 05:56:07,170 ERROR [Long running task: Space removal long running task] [atlassian.confluence.event.ConfluenceEventDispatcher] run There was an exception thrown trying to dispatch event [com.atlassian.confluence.event.events.space.SpaceWillRemoveEvent[source=com.atlassian.confluence.spaces.DefaultSpaceManager@1b7afa5]] from the invoker [com.atlassian.confluence.event.ConfluenceListenerHandlersConfiguration$TimingListenerHandler$1$1@510d7048]
 -- url: /conf/rest/api/latest/space/KEY | traceId: 26386459d9ac9452 | userName: admin
java.lang.RuntimeException: could not execute statement; SQL [n/a]; constraint [fkk6kbb7suqeloj82nx7xdcd803]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement. Listener: com.atlassian.confluence.core.listeners.BundledContentSpaceRemovalListener event: com.atlassian.confluence.event.events.space.SpaceWillRemoveEvent
    at com.atlassian.event.internal.SingleParameterMethodListenerInvoker.invoke(SingleParameterMethodListenerInvoker.java:55)
    at com.atlassian.confluence.event.ConfluenceListenerHandlersConfiguration$TimingListenerHandler$1$1.invoke(ConfluenceListenerHandlersConfiguration.java:69)
    at com.atlassian.confluence.event.ConfluenceEventDispatcher$VCacheRequestContextRunnableFactory$1.lambda$run$0(ConfluenceEventDispatcher.java:93)
    ...
Caused by: org.postgresql.util.PSQLException: ERROR: update or delete on table "content" violates foreign key constraint "fkk6kbb7suqeloj82nx7xdcd803" on table "content"
  Detail: Key (contentid)=(84848484) is still referenced from table "content".
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2427)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2169)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:132)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:205)
    ... 91 more

Note the occurrence of the message  Unable to delete space due to foreign key violation - fkk6kbb7suqeloj82nx7xdcd803. 

Page Deletion

We can expect similar error as above to appear while deletion of a page too.

2020-09-02 16:58:03,375 ERROR [http-nio-8080-exec-11] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ERROR: update or delete on table "content" violates foreign key constraint "fkk6kbb7suqeloj82nx7xdcd803" on table "content"
  Detail: Key (contentid)=(239081412) is still referenced from table "content".
 -- referer: https://confluence/pages/viewpreviousversions.action?pageId=239081410 | url: /pages/removehistoricalversion.action | traceId: 42b38bf299b133c2 | userName: admin | action: removehistoricalversion


Unable to Save a Page Edit 

If Collaborative Editing is enabled and multiple drafts exist, users may encounter the following message while trying to save a page: "Cant Sync with Server"

Diagnosis

Environment

  • Gliffy plugin installed on the instance
  • Version 8.2.0 of this plugin is in use or has been used in the past

Diagnostic Steps - Space Deletion

If we inspect the FK fkk6kbb7suqeloj82nx7xdcd803, here is its definition:

TABLE "content" CONSTRAINT "fkk6kbb7suqeloj82nx7xdcd803" FOREIGN KEY (prevver) REFERENCES content(contentid)

To understand what is causing its violation, use the contentid mentioned in the error message and search for where it is mentioned in the prevver column:

select contentid, contenttype, title, version, prevver from content where prevver = 84848484;

(warning) Replace the id with the one that can be seen in the logs.

If the query above returns entries with contenttype = draft and you see the version column with values bigger than 1, proceed with the Resolution - Space Deletion.

Diagnostic Steps - Page Deletion

A SQL similar to the above section will be required. With the log snippet in hand (from the section of Page Deletion) where content id 239081412 is being deleted and it runs into issues due to reference from another row, we can use the following SQL.

SELECT * FROM content where contentid = 239081412 or prevver = 239081412;

Normally, we will get an output like follows, where we will see another content id (239081417 in this example) referencing it.

contentidcontenttypeprevvercontent_status
239081417PAGE239081412draft

This page with content id 239081417 is actually a draft version and referencing page 239081412 as the previous version of the page. This shows an inconsistency on the table of content, as this draft should reference the current/most recent version of the page which is 239081410 (notice the log snippet viewpreviousversions.action?pageId=239081410)


Diagnostic Steps - Unable to Save a Page Edit 

In this instance, if a HAR file is captured while trying to save the page, the following request will result in a HTTP 409 return code:

https://abc.com:8443/rest/api/content/70721436?status=draft

Additionally, the response to this call will include a message like the following:

"Draft versioning is not supported. Current version is : 3"


Cause

These problems are caused by the fact that Confluence does not support versioned drafts in its database. 

This situation is caused by a bug that exists in Gliffy plugin version 8.2.0. It is mentioned in the release notes of version 8.2.1 as we can see below:

 Gliffy is creating new versions of unpublished drafts. A bug in the update to save an existing diagram was causing multiple copies of a page draft to be created, which could cause a page to not save when collaborative editing is turned. The issue is resolved in 8.2.1.

Resolutions

Resolution - Space Deletion

To resolve this problem and successfully delete the space, we need to remove the versioned drafts first. To achieve that, run the following set of queries:

(warning) Make sure to stop Confluence and take a database backup before running the queries below.

(warning) The queries below were tested in Postgres. If you are using a different database, you may need to adjust these first.

DELETE FROM NOTIFICATIONS WHERE CONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM CONFANCESTORS WHERE DESCENDENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM BODYCONTENT WHERE CONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM CONTENTPROPERTIES WHERE CONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM LINKS WHERE CONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM CONTENT_LABEL WHERE CONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM USERCONTENT_RELATION WHERE TARGETCONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM EXTRNLNKS WHERE CONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM LIKES WHERE CONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM OS_PROPERTYENTRY WHERE ENTITY_ID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM TRACKBACKLINKS WHERE CONTENTID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM CONTENT_PERM WHERE CPS_ID IN (
SELECT ID FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft')));

DELETE FROM CONTENT_PERM_SET WHERE CONTENT_ID IN (
SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft'));

DELETE FROM CONTENT WHERE PREVVER IN (
SELECT CONTENTID FROM CONTENT
WHERE VERSION > 1
AND CONTENT_STATUS='draft');

UPDATE CONTENT SET VERSION = 1
WHERE VERSION > 1
AND CONTENT_STATUS='draft';

Those queries will:

  1. Remove the references on other tables for the draft versions below the latest one
  2. Delete those older versions from the Content table itself
  3. Lastly, set the latest version back to 1, which is the expected value

To confirm that we no longer have versioned drafts, run the query below and confirm that zero results are returned:

select contentid from content
where version > 1
and content_status='draft';

Once the queries are executed, restart Confluence and attempt to delete the space again.

Then, to prevent this from occurring again, upgrade the plugin to at least version 8.2.1.

Resolution - Page Deletion

(warning) Backup the Confluence database and run the SQL Query below.

update content set prevver = 239081410 where contentid = 239081417;

The values in here are for the sample log snippet from the section of Page Deletion. Adjust it accordingly for your diagnosis. 

Resolution - Unable to Save a Page Edit 

Copy the content of the original problematic draft to a new draft and then save.

Then, to prevent this from occurring again, upgrade the plugin to at least version 8.2.1.


DescriptionUsage of Gliffy plugin version 8.2.0 causes the creation of multiple page drafts 
ProductConfluence

Last modified on Mar 5, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.