-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Low
-
None
-
Affects Version/s: 6.0.1, 6.6.3
-
Component/s: Capabilities
-
None
-
Severity 2 - Major
Summary
When we try to delete remote agent from the Bamboo UI, we get 500 error and upon restart, the remote agent will reappear.
Steps to Reproduce
There is NO possibility to create a duplicate capability
- as the UI doesn't allow us to add duplicate capability.
- as there is no API for adding a capability to define a duplicate.
- as we cannot add via bamboo-capabilities.properties. If duplicates are found, they will be replaced with new key values.
However, if a duplicate entry is found, the respective remote agent cannot be deleted.
Expected Results
The remote agent is deleted successfully.
Actual Results
The below exception is thrown in the log file:
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
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)
Notes
It's throwing this error because we cannot delete data from parent table CAPABILITY_SET leaving the data behind in the child table {{CAPABILITY }}which will result in orphan records.
And when Bamboo tries to delete data from CAPABILITY_SET, it leaves the data orphan in the table CAPABILITY.
Workaround
We can find the duplicates with the help of below queries and then delete them manually:
select KEY_IDENTIFIER ,value, count(*) from capability where capability_set in ( select capability_set_id from capability_set where capability_set_id=<ID>) group by KEY_IDENTIFIER,value having count(*) >1; select * from queue where agent_type='REMOTE' and capability_set in (select capability_set from capability);
Suggested Solution:
As we have validation in UI and even at the time of deletion of agent, we should have an unique constraint in the DB also to avoid duplicate entries.
- is duplicated by
-
-
- Closed
-