Sonar's "result returns more than one elements"
We have been hitting the dreaded Sonar error lately, resulting in constantly broken Hudson builds:
Caused by: javax.persistence.NonUniqueResultException: result returns more than one elements
The only workaround I could find was Pti’s database clean up. Doing this manually was a pain, especially after coming back from holidays, when a couple of weeks of records had been created.
So I have come up with a quick JRuby script to semi-automate this. And here is how to use it:
- Save this script in
clean_sonar.rb, - Update the Sonar database details with the correct values,
- Download mysql connector jar, and save it in the same folder as your ruby script,
- Backup your database,
- Make sure your database backup is ok,
- Check again (I guess you’re now warned),
- Run the script as follows:
$ jruby clean_sonar.rb
And here is the (quick’n‘dirty) script.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| require 'java' | |
| # Update with the name of jar you have downloaded | |
| require 'mysql-connector-java-5.0.8-bin.jar' | |
| Java::com.mysql.jdbc.Driver | |
| # Update the JDBC details as needed | |
| jdbc_url = 'jdbc:mysql://localhost:3306/sonar?useUnicode=true&characterEncoding=utf8' | |
| login = 'sonar' | |
| password = 'sonar' | |
| connection = java.sql.DriverManager.get_connection(jdbc_url, login, password) | |
| query =<<-EOS | |
| 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; | |
| EOS | |
| statement = connection.create_statement | |
| result_set = statement.execute_query(query) | |
| while result_set.next() | |
| project_id = result_set.getInt(1) | |
| another_statement = connection.prepare_statement("select * from snapshots where project_id = ? and islast = 1 order by id") | |
| another_statement.setInt(1, project_id) | |
| example_rset = another_statement.execute_query | |
| # Only delete the first record | |
| if example_rset.next | |
| example_to_delete = example_rset.getInt("id") | |
| delete_statement = connection.prepare_statement("delete from snapshots where id = ?") | |
| delete_statement.setInt(1, example_to_delete) | |
| delete_statement.execute_update | |
| puts "Deleted record #{example_to_delete}" | |
| delete_statement.close() | |
| end | |
| example_rset.close() | |
| another_statement.close() | |
| end | |
| result_set.close() | |
| statement.close() |
