NonUniqueResultException occurs when analyzing both a maven module and its parent

How can I remove the project? It seems some of the projects don't have project roles assigned to them which means that admin account does not see the "delete" button on the settings page or the "project roles" link in the menu etc.

Hi Joachim, you can also directly update the 'islast' column to '0' on involved snapshots. That will fix the issue.

While waiting for Sonar 2.8, here is the SQL request to execute in order to remove those "duplications":

*MySQL

update snapshots old_snap, snapshots new_snap set old_snap.islast=0 where old_snap.islast=1 and new_snap.created_at > old_snap.created_at and new_snap.project_id = old_snap.project_id and new_snap.islast=1

*Oracle

update sonar.snapshots old_snap set old_snap.islast=0 where islast=1 and exists(select * from sonar.snapshots new_snap where new_snap.created_at > old_snap.created_at and new_snap.project_id = old_snap.project_id and new_snap.islast=1)

Thank you for the workaround, it works fine.

Here it is for PostgreSQL:

update snapshots old_snap set islast=false where islast=true and exists(select * from snapshots new_snap where new_snap.created_at > old_snap.created_at and new_snap.project_id = old_snap.project_id and new_snap.islast=true)

Just upgrading to 2.8 didn't resolve this issue. I had to run the above update query to get things working. Before running the query I got the following exception:

Caused by: javax.persistence.NonUniqueResultException: Expected single result, but got : [Snapshot[resourceId=1148,createdAt=2011-04-29 23:08:23.0,version=01.01.01.1054-SNAPSHOT,last=true,status=P,scope=PRJ,path=,depth=0,qualifier=TRK,rootId=<null>,parentId=<null>,rootProjectId=1148,period1Mode=previous_analysis,period2Mode=days,period3Mode=days,period4Mode=<null>,period5Mode=<null>,period1Param=2011-04-19,period2Param=5,period3Param=30,period4Param=<null>,period5Param=<null>,period1Date=2011-04-19 05:06:16.0,period2Date=2011-04-24 23:08:23.0,period3Date=2011-03-30 23:08:23.0,period4Date=<null>,period5Date=<null>,id=486870], Snapshot[resourceId=1148,createdAt=2011-05-04 09:52:04.0,version=01.01.01.1054-SNAPSHOT,last=true,status=P,scope=PRJ,path=495746.,depth=1,qualifier=BRC,rootId=495746,parentId=495746,rootProjectId=1149,period1Mode=previous_analysis,period2Mode=days,period3Mode=days,period4Mode=<null>,period5Mode=<null>,period1Param=2011-04-29,period2Param=5,period3Param=30,period4Param=<null>,period5Param=<null>,period1Date=2011-04-29 23:08:23.0,period2Date=2011-04-29 09:52:04.0,period3Date=2011-04-04 09:52:04.0,period4Date=<null>,period5Date=<null>,id=495748]]
at org.sonar.jpa.session.JpaDatabaseSession.getSingleResult(JpaDatabaseSession.java:194)
at org.sonar.jpa.session.JpaDatabaseSession.getSingleResult(JpaDatabaseSession.java:212)
at org.sonar.batch.ProjectBuilder.isLatestAnalysis(ProjectBuilder.java:114)
at org.sonar.batch.ProjectBuilder.configure(ProjectBuilder.java:91)
at org.sonar.batch.ProjectBuilder.configure(ProjectBuilder.java:86)
at org.sonar.batch.ProjectTree.start(ProjectTree.java:97)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.picocontainer.lifecycle.ReflectionLifecycleStrategy.invokeMethod(ReflectionLifecycleStrategy.java:110)
... 35 more

Hi Marcel, if a duplication already exists in the Sonar DB, Sonar 2.8 doesn't automatically fix the issue and the above SQL request needs to be manually executed. But Sonar 2.8 prevents such duplications to appear.

and here is the sql for MS SQL Server

update snapshots 
set snapshots.islast = 0  
where snapshots.islast = 1 
and exists(select * from snapshots new_snap 
            where new_snap.created_at > snapshots.created_at 
            and new_snap.project_id = snapshots.project_id 
            and new_snap.islast = 1)

It seems that since Sonar 2.9, the bug reappeared...

Hi Laurent, please describe your use case ? If your DB was already corrupted before the migration you first need to execute a SQL request.
Thanks
Freddy

Hi Freddy,

I have made a migration from Sonar 2.8 to Sonar 2.9, and the exception appears after this migration.
I executed the workaround and it works for me.

But I was forced to execute it 4 or 5 times before the DB was fully functionnal.

Hi Laurent, in fact only one other user has reported this issue on Sonar 2.9 and this was finally due to Sonar 2.8. So I'm not opening another JIRA ticket. Have you faced this issue another time after your first fix ?
Thanks

Hi Freddy,

I've faced this issue 2 or 3 times after my first fix, but now, it seems to work again with no exception...
I'll inform you if this error appears again.

I just upgraded from sonar 2.6 to 2.9 and I'm facing the issue as well.

I'm going to try out the sql statements mentioned above now.

I upgraded from sonar 2.5 to sonar 2.9 and have repeatedly fixed the issue using the sql statements above, only to have it reappear. We have dozens of projects, and it does not happen with every build, but happens sporadically across most of our projects, roughly once or twice a day.

Hi Bill, in that case, please start a new thread of discussion in the Sonar user mailing. Indeed, the root cause can't be the same and we need to figure out what happens. Thanks

Hi there, I am having the same issue with 2.9. We have run the above script multiple times and it seems to work every time, but the exception randomly comes back. I tried searching for a new ticket and looked in the mailing list for any discussion, but I haven't found anything.

So is there a new bug for 2.9 or somewhere else I should be looking for updates to this issue?

I'm using Derby inbuilt DB coming along with sonar stand-alone version), I'm wondering, can any one suggest me the fix/work-around?

Hi John, the only known open bug/limitation which can lead to such duplication is SONAR-2761.

Raghunathan,

Probably too late but the query for Derby is:

update sonar.snapshots old_snap set islast=false where islast=true and exists(select * from sonar.snapshots new_snap where new_snap.created_at > old_snap.created_at and new_snap.project_id = old_snap.project_id and new_snap.islast=true)

Hi,

I'm hitting this issue in Sonar 3.1.1. Like the description I'm running sonar on a multi-module Maven project.
I run sonar on a top level pom and also on a child pom.

Please send an email to the Sonar User mailing list @DK with the Sonar analysis log file. Indeed I'm pretty sure that you're not facing exactly the same issue.

We are hitting this again with Sonar v.3.2. Will open an extra ticket cause @Freddy suggest its "pretty sure.. not exactly the same issue in 3.1"

Hi @Toni, just send an email to the Sonar mailing list and I'll take care to try understanding what happens. When I say "pretty sure.. not exactly the same issue .." I'm not saying that the Sonar user is lying or that the exception can't appear anymore, I'm just saying that the root cause of the exception is not what we fixed with this JIRA ticket and so we must first fully undertand what other root cause can lead to get the same exception.
Thanks

This is still happening with Sonar 3.4.1 and two projects that, while sharing the groupId, differ on artifactIds

Sample:

Project1: org.sample:module1:1.0.1-SNAPSHOT
Project2: org.sample.module12:1.0.4-SNAPSHOT

If both projects are located at different maven projects and compiled/analysed at the same time (eg: nightly builds) the issue appears.

Obviously the workaround works... until the next periodical analysis is triggered.

@David, please open a dedicated thread on the Sonar User mailing list because I'm pretty sure that's your issue doesn't relate to this ticket. Thanks