-
Type:
Bug
-
Resolution: Fixed
-
Priority:
Medium
-
Affects Version/s: 6.0.1, 6.6.3
-
Component/s: Agents, Capabilities
-
None
-
1
-
Severity 3 - Minor
Problem Definition
Issue while deleting the Remote Agent.
When you try to delete remote agent from the Bamboo UI you will be greeted with 500 error and upon restart the remote agent will reappear.
Database Used: MySQL
The following error can be found from the logs:
2017-09-26 21:19:38,973 INFO [http-nio-8085-exec-17] [AgentManagerImpl] Attempting to remove build agent... 2017-09-26 21:19:39,022 INFO [http-nio-8085-exec-17] [AbstractBatchImpl] HHH000010: On release of batch it still contained JDBC statements 2017-09-26 21:19:39,023 WARN [http-nio-8085-exec-17] [SqlExceptionHelper] SQL Error: 1451, SQLState: 23000 2017-09-26 21:19:39,023 ERROR [http-nio-8085-exec-17] [SqlExceptionHelper] Cannot delete or update a parent row: a foreign key constraint fails (`new`.`capability`, CONSTRAINT `FK_sm8xum6eu61qtx5twb34enyf9` FOREIGN KEY (`CAPABILITY_SET`) REFERENCES `capability_set` (`CAPABILITY_SET_ID`)) 2017-09-26 21:19:39,023 ERROR [http-nio-8085-exec-17] [BatchingBatch] HHH000315: Exception executing batch [could not execute batch] 2017-09-26 21:19:39,023 ERROR [http-nio-8085-exec-17] [SessionImpl] HHH000346: Error during managed flush [could not execute batch] 2017-09-26 21:19:39,028 ERROR [http-nio-8085-exec-17] [ExceptionMappingInterceptor] could not execute batch; SQL [delete from CAPABILITY_SET where CAPABILITY_SET_ID=?]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [delete from CAPABILITY_SET where CAPABILITY_SET_ID=?]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:164)
Caused by: java.sql.BatchUpdateException: Cannot delete or update a parent row: a foreign key constraint fails (`new`.`capability`, CONSTRAINT `FK_sm8xum6eu61qtx5twb34enyf9` FOREIGN KEY (`CAPABILITY_SET`) REFERENCES `capability_set` (`CAPABILITY_SET_ID`))
2017-09-26 21:19:39,041 ERROR [http-nio-8085-exec-17] [FiveOhOh] 500 Exception was thrown.
org.springframework.dao.DataIntegrityViolationException: could not execute batch; SQL [delete from CAPABILITY_SET where CAPABILITY_SET_ID=?]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: could not execute batch
It's throwing this error because you cannot delete from parent table leaving the data behind in the child table which will result into orphan records.
Here the parent table is CAPABILITY_SET and the child table is CAPABILITY.
And Bamboo is trying to delete data from CAPABILITY_SET leaving the data orphan in the table CAPABILITY.
Steps to Reproduce:
- There are no steps to reproduce as from Bamboo UI it is not possible to add the duplicate records in the CAPABILITY table for the column KEY_IDENTIFIER.
Workaround:
- Backup your current Bamboo database state.
- Use the following SQL query to find the duplicates and manually delete the duplicates from the table CAPABILITY and then delete the agents from the Bamboo UI:
SELECT * FROM queue WHERE capability_set IN (SELECT DISTINCT t.CAPABILITY_SET FROM (SELECT c.CAPABILITY_SET, c.KEY_IDENTIFIER, c.VALUE, count(*) FROM capability c GROUP BY CAPABILITY_SET, KEY_IDENTIFIER, c.VALUE HAVING count(*) > 1) AS t);
