Summary

Bamboo gives an error message every time a user tries to mark plans as favorite: query did not return a unique result: 2

Environment

All supported versions of Bamboo.

Diagnosis

The following error can be seen in the logs:

2012-08-03 10:49:59,915 ERROR [730902023@qtp-2005327631-18171] [FiveOhOh] 500 Exception was thrown.
org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2
	at org.springframework.orm.hibernate.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:590)
	at org.springframework.orm.hibernate.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:353)
	at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:375)
	at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:337)
	at com.atlassian.bamboo.labels.LabelHibernateDao.findLabelByNameAndNamespace(LabelHibernateDao.java:35)
	at sun.reflect.GeneratedMethodAccessor471.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

Cause

Somehow there were 2 rows added to BUILDRESULTSUMMARY_LABEL and LABEL tables.

Solution

1. In order to find out what table is involved, enable detailed SQL logging and reproduce the problem. After, immediately shut down your Bamboo server and check the last logs. You will see the involved table(s).

2. In this specific case let's say we saw that querying LABEL table failed. Running this SQL query we confirm that there are 2 records somehow inserted into LABEL table that have the same value in the 'NAME' field:

   select * from label where namespace='problematic_user_name';

3. Next, note that LABEL_ID is used only in BUILDRESULTSUMMARY_LABEL table.
   

4. In order to find out which row should be deleted (the one that isn't linked from BUILDRESULTSUMMARY_LABEL), run this SQL query (change (id1,id2) with a list of the label IDs received from the previous query):

   select * from BUILDRESULTSUMMARY_LABEL where LABEL_ID in (id1,id2);

5. After getting the results of the previous query, delete the duplicate rows and leave only one by following the next steps.
6. Stop your Bamboo server, and make sure that you have backed up your Bamboo database before making any changes to the DB.

7. Make sure that you have a backup of your Bamboo DB. Next, run this SQL query to delete the row that isn't linked from BUILDRESULTSUMMARY_LABEL and LABEL tables (there might be duplicates in both tables):

   delete FROM BUILDRESULTSUMMARY_LABEL where LABEL_ID=<id2>;
       
   delete from label where LABEL_ID=<id2>;

8. Start your Bamboo server.