Issue Summary

Confluence allows users to access the view mode of a shared draft as a regular page.
As shared drafts should be accessible by the user only when editing a page, exposing it from the view mode may create inconsistencies in the database, such as when a user clicks on the like button when viewing a draft.
When such inconsistence is created, it can then lead to other problems, such as preventing Collaborative Editing from being enabled.

Steps to Reproduce

1. Have a fresh installation of Confluence.
2. Create a new page and publish it.
3. Edit this page and save a draft without publishing it.
   • At this point you a have a published version + a draft with unpublished changes.
     
     Content Table

     65545;25;"PAGE";"A quick look at the editor (step 2 of 9)";"a quick look at the editor (step 2 of 9)";1;"";"2017-05-10 14:51:51.826";"";"2018-12-21 11:38:11.465";"''";;"current";;98305;1;65547;"";"";"";;"";"";"";;;""
     65593;2;"PAGE";"A quick look at the editor (step 2 of 9)";"a quick look at the editor (step 2 of 9)";1;"402806b267d0fabd0167d0fdf69c0000";"2018-12-21 11:42:43.205";"402806b267d0fabd0167d0fdf69c0000";"2018-12-21 11:42:43.205";"''";65545;"draft";;98305;;;"";"";"";;"";"";"";;;""
     ;;"";"";"";;"";"";"";"";"";;"";;;;;"";"";"";;"";"";"";;;""
     

4. Click on the Edit button of the page and collect the value of the draftId from the browser URL bar.
   
5. Publish the draft and at this point you two versions of the page + a stale draft in the database.
   Content Table

   65545;27;"PAGE";"A quick look at the editor (step 2 of 9)";"a quick look at the editor (step 2 of 9)";2;"";"2017-05-10 14:51:51.826";"402806b267d0fabd0167d0fdf69c0000";"2018-12-21 11:51:59.986";"''";;"current";;98305;1;65547;"";"";"";;"";"";"";;;""
   65593;2;"PAGE";"A quick look at the editor (step 2 of 9)";"a quick look at the editor (step 2 of 9)";1;"402806b267d0fabd0167d0fdf69c0000";"2018-12-21 11:42:43.205";"402806b267d0fabd0167d0fdf69c0000";"2018-12-21 11:42:43.205";"''";65545;"draft";;98305;;;"";"";"";;"";"";"";;;""
   65594;25;"PAGE";"A quick look at the editor (step 2 of 9)";"a quick look at the editor (step 2 of 9)";1;"";"2017-05-10 14:51:51.826";"";"2018-12-21 11:38:11.465";"''";65545;"current";;;1;;"";"";"";;"";"";"";;;""
   ;;"";"";"";;"";"";"";"";"";;"";;;;;"";"";"";;"";"";"";;;""
   

6. Access the <Confluence Base URL>/pages/viewpage.action?pageId=<draftId> URL.
   • Confluence will load the draft as a regular page, with a message You are viewing an old version of this page.
     
7. Click on the Like button associated with the draft and a record in the likes table is created.
8. Go to Cog Icon > General Configuration > Collaborative Editing.
9. In the Collaborative editing (CE) administration page, turn off CE.
10. Turn on CE and monitor atlassian-confluence.log log file for the entry below.

    2018-12-21 15:29:34,552 ERROR [Long running task: EnableTask] [engine.jdbc.spi.SqlExceptionHelper] logExceptions ERROR: update or delete on table "content" violates foreign key constraint "fkbdoiwi70i7o3tc7hpbu4vnlmy" on table "likes"
      Detail: Key (contentid)=(65595) is still referenced from table "likes".
     -- url: /c6611/rest/synchrony-interop/enable | referer: http://localhost:26611/c6611/admin/confluence-collaborative-editor-plugin/configure.action | traceId: 40d0f7294185c642 | userName: admin
    
    (...)
    
    2018-12-21 15:29:34,555 WARN [Long running task: EnableTask] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doCommit Commit failed. Rolling back. Error: Hibernate operation: could not execute statement; sql=n/a; SQL []; ERROR: update or delete on table "content" violates foreign key constraint "fkbdoiwi70i7o3tc7hpbu4vnlmy" on table "likes"
      Detail: Key (contentid)=(65595) is still referenced from table "likes".; nested exception is org.postgresql.util.PSQLException: ERROR: update or delete on table "content" violates foreign key constraint "fkbdoiwi70i7o3tc7hpbu4vnlmy" on table "likes"
      Detail: Key (contentid)=(65595) is still referenced from table "likes".
     -- url: /c6611/rest/synchrony-interop/enable | referer: http://localhost:26611/c6611/admin/confluence-collaborative-editor-plugin/configure.action | traceId: 40d0f7294185c642 | userName: admin
    2018-12-21 15:29:34,555 WARN [Long running task: EnableTask] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
        [com.atlassian.confluence.pages.DefaultPageManager.removeStaleSharedDrafts]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #1675969270)
          ->[null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #1926773504)
     -- url: /c6611/rest/synchrony-interop/enable | referer: http://localhost:26611/c6611/admin/confluence-collaborative-editor-plugin/configure.action | traceId: 40d0f7294185c642 | userName: admin
    2018-12-21 15:29:34,556 WARN [Long running task: EnableTask] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
      ->[com.atlassian.confluence.pages.DefaultPageManager.removeStaleSharedDrafts]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #1675969270)
            [null]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #1926773504)
     -- url: /c6611/rest/synchrony-interop/enable | referer: http://localhost:26611/c6611/admin/confluence-collaborative-editor-plugin/configure.action | traceId: 40d0f7294185c642 | userName: admin
    2018-12-21 15:29:34,556 ERROR [Long running task: EnableTask] [plugins.synchrony.tasks.AbstractConfigLongRunningTask] runInternal An error occurred when running a Synchrony ConfigLongRunningTask
     -- url: /c6611/rest/synchrony-interop/enable | referer: http://localhost:26611/c6611/admin/confluence-collaborative-editor-plugin/configure.action | traceId: 40d0f7294185c642 | userName: admin
    org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute statement; sql=n/a; SQL []; ERROR: update or delete on table "content" violates foreign key constraint "fkbdoiwi70i7o3tc7hpbu4vnlmy" on table "likes"
      Detail: Key (contentid)=(65595) is still referenced from table "likes".; nested exception is org.postgresql.util.PSQLException: ERROR: update or delete on table "content" violates foreign key constraint "fkbdoiwi70i7o3tc7hpbu4vnlmy" on table "likes"
      Detail: Key (contentid)=(65595) is still referenced from table "likes".
    	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:102)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
    	at org.springframework.orm.hibernate.HibernateTransactionManager.convertJdbcAccessException(HibernateTransactionManager.java:619)
    	at org.springframework.orm.hibernate.HibernateTransactionManager.convertHibernateAccessException(HibernateTransactionManager.java:605)
    	at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:518)
    	at com.atlassian.confluence.impl.hibernate.ConfluenceHibernateTransactionManager.doCommit(ConfluenceHibernateTransactionManager.java:69)
    	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761)
    	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730)
    (...)
    	at com.sun.proxy.$Proxy2546.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)
    (...)
    

Expected Results

A shared draft should not be exposed to the user on the same way as a regular page.
When <Confluence Base URL>/pages/viewpage.action?pageId=<draftId> URL is accessed, it should retrieve a 404 response page, even when the draft exists in the database.
Confluence REST API protects the user from this bug, providing a 404 message when trying to access the draft using the /rest/api/content method.

When Collaborative Editing is enabled and the removeStaleSharedDrafts method is executed, it should consider the likes table – and any other with a constraint related to the content table.

Actual Results

A shared draft can be viewed through <Confluence Base URL>/pages/viewpage.action?pageId=<draftId> URL.
Then a user can like the draft, what may create a problem if CE is turned off and on.

Workaround

When enabling Collaborative Editing after a long time with it disabled, consider manually removing stale drafts.