I got this error: org.springframework.dao.IncorrectResultSizeDataAccessException

[Basem Abdulrahem]

After I disabled the Synchronization of users' directories to Jira and I am using only the internal directory..

When I tried to access the list of users: I got this Error,

Cause

org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2; nested exception is org.hibernate.NonUniqueResultException: query did not return a unique result: 2
    at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:256)

caused by: org.hibernate.NonUniqueResultException: query did not return a unique result: 2
    at org.hibernate.query.internal.AbstractProducedQuery.uniqueElement(AbstractProducedQuery.java:1483)

Stack Trace:[hide]

org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2; nested exception is org.hibernate.NonUniqueResultException: query did not return a unique result: 2
	at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:256)
	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:362)
	at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:313)
	at com.atlassian.confluence.user.persistence.dao.hibernate.HibernateConfluenceUserDao.findByUsername(HibernateConfluenceUserDao.java:71)
	at com.atlassian.confluence.impl.user.persistence.dao.CachingConfluenceUserDao.findByUsername(CachingConfluenceUserDao.java:82)
	at sun.reflect.GeneratedMethodAccessor611.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:497)

Here is the system logs: 

https://drive.google.com/file/d/15RYTJgTaQhyXs2arpVWVDDtFnNru8Epq/view?usp=sharing

Can you please advice?

Regards,

Basem

   

[AnnWorley · Accepted solution]

Hi Basem,

Thank you for the Tomcat log and screen shot.

The error seems to be appearing due to a duplicate record in the cwd_group table in the database. This is revealed by this entry from your stack trace: 

com.atlassian.confluence.user.persistence.dao.hibernate.HibernateConfluenceUserDao.findByUsername(HibernateConfluenceUserDao.java:71)

For more details: User related issues in Confluence with the error message 'query did not return a unique result'

For the resolution, please see this guide: Synchronization with external directory fails with error: query did not return unique result due to duplicate groups

I realize that you are seeing the stack trace when viewing the users rather than during synchronization but the root cause of duplicate groups seems to be the same.

For more logging related to Confluence, please see the <Confluence_Home_dir>/logs/atlassian-confluence.log. You may find more relevant information there.

Thanks,

Ann

[Basem Abdulrahem]

Hello Ann,

Thank you very much for your answer. 

But, I can't access the DB. When I connected to the Database "PostgreSQL", I didn't find the database of Confluense. So, How can I get the username and password that Confluence application uses it to access the Database?

Thanks,

Basem

[AnnWorley]

Hi Basem,

The username, password and database URL are usually defined in <confluence_home>/confluence.cfg.xml. Look for these lines:

 <property name="hibernate.connection.password">db_password</property>
 <property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/confluencedatabasename</property>
 <property name="hibernate.connection.username">yourDBuser</property>

Some Confluence instances define these parameters in the <confluence_install>/conf/server.xml file.

I look forward to your next update.

Thanks,

Ann

[Basem Abdulrahem]

Hi Ann, 

I got it, Thanks..

But, here we are... I didn't find users' table!! Did I search in the wrong place?

 

Many thanks,

Basem

[AnnWorley]

Hi Basem,

The only database I see under the PostgreSQL database connection in the screen shot is the default postgres database that is installed with PostgreSQL. I do not see another database dedicated to Confluence. Maybe the connection is to a different PostgreSQL instance?

Please examine your <confluence_home>/confluence.cfg.xml to double check the PostgreSQL server and database names, for example:

<property name="hibernate.connection.url">jdbc:postgresql://Servername:port/confluence_database_name</property>

Then use the username and password to connect in DB Visualizer:

 <property name="hibernate.connection.username">yourDBuser</property>

 <property name="hibernate.connection.password">db_password</property>

 The cwd_group table can be queried for duplicates once you connect to the database Confluence is using.

Please let me know how it goes.

Thanks,

Ann

[Basem Abdulrahem]

Hello Ann,

I already did. I logged in by the confluenceuser and the password which I found in the xml file. 

Thanks,

Basem

[AnnWorley]

Screen shots are a great help, thank you.

I think the database needs to be confluence instead of postgres:

[Basem Abdulrahem]

Many thanks Ann. I really appreciate your help. I managed to connect and edit the record which cause this conflict. 

Regards,

Basem

[AnnWorley]

Wow, that was fast! Can you list users now without getting an error?

[Basem Abdulrahem]

Yes, Just it took a little time to remove duplication in user_mapping table because of dependencies.. But Finally, It works and now I can list all users without any issues.

Thank you so much for your precious notes and your help for troubleshooting. 

Regards,

Basem