Summary

In Confluence 8.3 or newer, a space can't be accessed or searched by any user, including the space owner. This can also happen in macros and apps that list space directories.

The logs show this error: NonUniqueResultException: query did not return a unique result.

Inaccessible space resolution

Due to a manual SQL UPDATE, INSERT, or other activity performed on the database server, the database has multiple rows with the same LOWERSPACEKEY value. There could also be a mismatch between SPACEKEY and LOWERSPACEKEY. As a result, Confluence can't find a unique row for the space. The solution is to check for duplicate or mismatched space entries, flush the caches in case the node has cached invalid space data, and then update the incorrect SPACES row in the database if necessary.

Check for duplicate LOWERSPACEKEY values

First, check for inconsistencies by running these queries. 

• Check for incorrect LOWERSPACEKEY values:  

  • PostgreSQL
  • MS SQL

  SELECT * FROM SPACES WHERE lowerspacekey <> LOWER(spacekey)

  SELECT * FROM dbo.SPACES WHERE lowerspacekey <> LOWER(spacekey);

• Check for duplicate LOWERSPACEKEY values: 

  • PostgreSQL

  SELECT lowerspacekey, COUNT(*) FROM SPACES
  GROUP BY lowerspacekey HAVING COUNT(*) >= 2

Flush the cache and repair the spaces table

If either of these queries return rows, follow these steps.

1. Flush all of the Confluence caches.
   1. Navigate to General Configuration > Cache Management.
   2. Click the Flush All button.
2. After flushing the cache, try to access the space.
3. If the issue persists, do these steps during your next maintenance window.

4. 1. Shutdown Confluence.

   2. Backup your database.

   3. Run this SQL statement:

      UPDATE spaces SET LOWERSPACEKEY = LOWER(spacekey) where LOWERSPACEKEY='<LOWERSPACEKEY>' ;

      Replace <LOWERSPACEKEY> with the Confluence space key that has the mismatch.

   4. Run the diagnostic queries again. If the problem persists, you might need to rename or delete one of the duplicate space key rows.
   5. Start Confluence.

Here is more detail on the error in the atlassian-confluence.log file.

2023-08-18 19:14:37,752 ERROR [http-nio-8080-exec-144 url: /display/SPACE_NAME, /spaces/viewspace.action; user: admin] [apache.struts2.dispatcher.DefaultDispatcherErrorHandler] sendErrorResponse Exception occurred during processing request: query did not return a unique result: 5
 -- url: /display/SPACE_NAME | userName: npa| traceId: 7bb5bc0d626fab89
org.hibernate.NonUniqueResultException: query did not return a unique result: 5
	at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.uniqueResult(HibernateObjectDao.java:587)
...
...

The unique result count can be other numbers.

2024-10-03 22:59:43,349 ERROR [http-nio-8090-exec-280] [rest.api.model.ExceptionConverter] convertServiceException No status code found for exception, converting to internal server error : 
 -- url: /rest/refinedtheme/latest/category/eu/ | userName: user123 | referer: https://xxxx.xxxx.xxx/ | traceId: 05c16dc2089fab56
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
    at com.atlassian.confluence.core.persistence.hibernate.HibernateObjectDao.uniqueResult(HibernateObjectDao.java:587)