Issue Summary

This issue can be reproduced on Data Center:

When there are duplicate Agent names referred to as 'title' in the Bamboo database queue table, Bamboo generates a "Registration attempt rejected with a message: query did not return a unique result: N" message. This is accompanied by a stack trace that doesn't provide any meaningful information to identify the problematic Agent(s).

Steps to Reproduce

This issue is an edge case and can occur when Bamboo or the Database crashes during an Agent registration, resulting in unvalidated data in the Queue table in the database.

1. Select an Agent in the DB queue table
2. Create a new row with the same contents as that Agent, except for its queue_id, which should be unique
3. Modify the uuid column in the DB in the cloned Agent row (add a random one)
4. Copy the <bamboo-agent-home> of the original Agent to a different location on the same server
5. Modify the cloned Agent's home folder and adjust the paths in wrapper.conf to point to the new location
6. On the cloned Agent, bamboo.cfg.xml – This is to simulate an unsuccessful registration of an Agent in the past that left records in the Database
   • Ensure that the agentUuid and name are the same on both Agents
   • Adjust any PATH in buildWorkingDirectory
   • Generate a random <id> that will not match queue_id of the cloned one.
7. Start the cloned Agent
8. Observe the Agent and Server logs.

Expected Results

1. The Bamboo Server should provide more detail in the logs about what's happening. Bamboo should log all duplicate IDs so the Bamboo Administrator can take action to fix the cloned Agent(s). A message such as the following could be useful:

   [RemoteAgentManagerImpl] Registration attempt rejected with a message: query did not return a unique result: 2; nested exception is org.hibernate.NonUniqueResultException: query did not return a unique result: 2.
   
   [RemoteAgentManagerImpl] Duplicate Agent IDs: 101777410, 105250820. Please check <Bamboo_URL>/admin/agent/viewAgent.action?agentId=AgentID and take action 

2. The stack trace that follows the event in the logs should only be reported when the log level is set to DEBUG as the message is quite noisy and disrupts regular troubleshooting of logs.

Actual Results

1. Bamboo only reports a large stack trace without meaningful information, making it difficult to troubleshoot and identify the root cause. In large instances, it's challenging to match the Agent causing such an event based solely on the logs.

   2023-10-14 22:17:41,569 INFO [ActiveMQ Session Task-8] [RemoteAgentManagerImpl] Registration attempt rejected with a message: query did not return a unique result: 2; nested exception is org.hibernate.NonUniqueResultException: query did not return a unique result: 2
   
   2023-10-14 22:17:41,972 WARN [ActiveMQ Session Task-8] [RemoteInvocationTraceInterceptor] Processing of BambooJmsInvokerServiceExporter remote call resulted in fatal exception: com.atlassian.bamboo.buildqueue.manager.RemotedRemoteAgentManager.registerAgent
   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:227) ~[spring-orm-5.3.28.jar:5.3.28]
   	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:370) ~[spring-orm-5.3.28.jar:5.3.28]
   	at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:320) ~[spring-orm-5.3.28.jar:5.3.28]
   	at com.atlassian.bamboo.buildqueue.dao.AgentHibernateDao.findAgentByName(AgentHibernateDao.java:211) ~[atlassian-bamboo-persistence-9.4.0-m198.jar:?]
   	at jdk.internal.reflect.GeneratedMethodAccessor1872.invoke(Unknown Source) ~[?:?]
   	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
   	at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
   	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.3.28.jar:5.3.28]
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198) ~[spring-aop-5.3.28.jar:5.3.28]
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.3.28.jar:5.3.28]
   	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) ~[spring-tx-5.3.28.jar:5.3.28]
   	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) ~[spring-tx-5.3.28.jar:5.3.28]
   	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) ~[spring-tx-5.3.28.jar:5.3.28]
   	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.28.jar:5.3.28]
   	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241) ~[spring-aop-5.3.28.jar:5.3.28]
   	at com.sun.proxy.$Proxy167.findAgentByName(Unknown Source) ~[?:?]
   	at com.atlassian.bamboo.buildqueue.manager.AgentManagerImpl.getAgentDefinitionByName(AgentManagerImpl.java:771) ~[atlassian-bamboo-agent-local-9.4.0-m198.jar:?]
   	at jdk.internal.reflect.GeneratedMethodAccessor1871.invoke(Unknown Source) ~[?:?]
   	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
   	at java.lang.reflect.Method.invoke(Method.java:566) ~[?:?]
   ...
   

2. The Bamboo Agent enters a bootstrap loop and reports the following errors:

   ...
   INFO   | jvm 1    | 2023/10/14 22:08:57 | Exiting due to fatal exception.
   INFO   | jvm 1    | 2023/10/14 22:08:57 | java.lang.reflect.InvocationTargetException
   INFO   | jvm 1    | 2023/10/14 22:08:57 | 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   INFO   | jvm 1    | 2023/10/14 22:08:57 | 	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   INFO   | jvm 1    | 2023/10/14 22:08:57 | 	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   INFO   | jvm 1    | 2023/10/14 22:08:57 | 	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
   INFO   | jvm 1    | 2023/10/14 22:08:57 | 	at com.atlassian.bamboo.agent.bootstrap.AgentRunner.run(AgentRunner.java:27)
   INFO   | jvm 1    | 2023/10/14 22:08:57 | 	at java.base/java.lang.Thread.run(Thread.java:829)
   INFO   | jvm 1    | 2023/10/14 22:08:57 | Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'agentRegistrationBean': Invocation of init method failed; nested exception is 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
   ...
   

Workaround

1. To find the duplicate Agents, run the following SQL:

   SELECT 
     QUEUE_ID as "agent_id", 
     AGENT_TYPE, 
     TITLE, 
     AGENT_DESCRIPTION, 
     ENABLED, 
     UUID 
   FROM 
     QUEUE 
   WHERE 
     TITLE IN ( SELECT TITLE 
                FROM QUEUE 
                GROUP BY TITLE 
                HAVING COUNT(*) > 1 )
   ORDER BY TITLE ASC;
   

2. In the SQL output, you should see something similar to this. For example:

   agent_id	agent_type	title	agent_description	enabled	uuid
   105250822	REMOTE	agent	Remote agent on host agent9	true	c1be9d3d-0b20-4bb0-b08e-307bd5b20908
   101777410	REMOTE	agent	Remote agent on host agent9	false	bf8fa73a-6a8d-11ee-8c99-0242ac120002

3. Search for the listed Agents in Bamboo, remove them and ask your users to recreate them. For each Agent, please take notes of any dedication or special capabilities those Agents have so you can recreate them later.
4. As Bamboo will only report one Agent at a time in the UI, you need to force the URL to match the Agent_ID. For each Agent access the URL below and take action to delete/keep them. Broken agents will report themselves as Offline.
   • <Bamboo_URL>/admin/agent/viewAgent.action?agentId=<Agent_ID>
5. As the DB entries are duplicates, Bamboo may refuse to delete an Agent due to DB constraints related to capabilities and throw an error. If that happens, delete all capabilities from that duplicate Agent before attempting do delete it.