Summary

The issue described involves a plugin for Confluence that appears correctly in the web interface with the correct license key upon installation. However, after a restart of Confluence, the plugin no longer appears in the web interface. This suggests a problem with the persistence of the plugin's installation status or a potential issue with the plugin loading properly after a restart.

Environment

Confluence Server and Data-Center.

Diagnosis

When searching the logs we can see the following error for the plugin installation.

2023-10-31 11:48:59,831 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.BandanaPluginStateStore] save Exception when saving plugins state to the database. Saving to in memory state.
org.springframework.orm.hibernate5.HibernateSystemException: Batch update returned unexpected row count from update [0]; actual row count: 2; expected: 1; nested exception is org.hibernate.jdbc.BatchedTooManyRowsAffectedException: Batch update returned unexpected row count from update [0]; actual row count: 2; expected: 1
    at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:267)
    at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:374)  

This error states that Confluence is trying to insert a value into its database but failing to do so because it violates a table constraint that enforces the values to be unique. Instead, it is saving the data in memory which will be deleted upon a restart.

Running a query in the BANDANA table to search for duplicated entries:

SELECT bandanaid, COUNT(bandanaid) from BANDANA GROUP BY bandanaid HAVING COUNT(bandanaid) > 1;

Many duplicates were found, for example:

In this instance, deleting both the original and duplicate entries would result in the loss of essential information from the BANDANA table. Therefore, we modified a Postgres query to remove only the duplicate entries by utilizing the CTID column.

• Postgres CTID is an internal table column identifying how Postgres stores table data physically on the disc.

Solution

1. Shutdown Confluence
2. To delete the duplicate entries in the BANDANA table for Postgres we can use the following query:

3. DELETE FROM BANDANA a
   WHERE ctid > (SELECT MIN(ctid) FROM BANDANA b
   WHERE b.bandanaid=a.bandanaid);

4. To confirm that the deletion worked please re-run the SELECT query:

5. SELECT bandanaid, COUNT(bandanaid) from BANDANA GROUP BY bandanaid HAVING COUNT(bandanaid) > 1;

6. Start Confluence
7. Run a Content Index