Fix for Sonar choking on 'result returns more than one elements'

Recently our sonar installation on our Hudson CI choked again, this time with an error I have not seen before. It was just before the release of an important milestone for the team, so not being unable to publish new version on the test server could not come on a worse time.

In the Console Log of a failing built we found the message.

\[INFO\] ------------------------------------------------------------------------
\[INFO\] BUILD FAILURE
\[INFO\] ------------------------------------------------------------------------
\[INFO\] Total time: 5.791s
\[INFO\] Finished at: Thu Jun 23 09:20:05 CEST 2011
\[INFO\] Final Memory: 20M/429M
\[INFO\] ------------------------------------------------------------------------
\[ERROR\] Failed to execute goal org.codehaus.mojo:sonar-maven-plugin:2.0-beta-2:sonar (default-cli) on project pfus: Can not execute Sonar: PicoLifecycleException: method 'public void org.sonar.batch.ProjectTree.start() throws java.io.IOException', instance 'org.sonar.batch.ProjectTree@50a6023a, java.lang.RuntimeException: wrapper: result returns more than one elements -> \[Help 1\]

Ok, apparently something returns 2 results which should only have one result and it stands to reason that it is coming from the database. Pity there is no indication on which table would be affected.

Some googling retrieved this post which points to the snapshots table and more specifically to records with the field islast set to true.

A quick check in .../conf/sonar.properties revealed the database connection parameters.

Ok, this is a good time to check you have an up-to-date backup of your database. (Or make one if you're not sure)

Using your SQL query tool of choice (I used the built in Data Sources tool in IntelliJ) connect to the database.

The snapshots table contains a project_id which confusingly does not really contains projects, but actually all kinds of assets, artefacts, files, however you ike to call it. Unfortunately there are many thousands. Even if I limited to only the ones with islast=1 there were close to 1000 records.

Narrowing further down with :

**select project\_id, cnt
  from (select project\_id,
          count(\*) as cnt
        from snapshots
        where islast=1
        group by project\_id) as cntsnap
  where cnt > 1**

Gave me the project_id I've been looking for, in my case 2089 Now a quick

select * from snapshots where project_id=2089

Gave the 2 offending rows. A quick glance showed that one of them was very suspicious : the parent_id was the same as his own project_id and there were lots of null columns. I deleted the row based on the id. Retriggered hudson to rebuild the project, and the build succeeded and sonar seems to be happy again. I hope there will be no other repercussions. For our purposes the sonar data is not really critical and we could also restart without any impact. If your sonar data is more critical a better choice would be to restore from backup before the build started failing.