Build fail with duplicate key value error for AO tables
Problem
Build fail with the following appears in the atlassian-bamboo.log
2018-06-04 06:22:03,752 ERROR [AtlassianEvent::0-BAM::EVENTS:pool-1-thread-2] [BambooPluginUtils] Error while executing com.atlassian.bamboo.brokenbuildtracker.BrokenBuildPostProcessor
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.1.18
- minor version:1
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1209
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_7A45FB_AOTRACKING_RESULT_pkey"
Detail: Key ("RESULT_ID")=(49324) already exists.
...
2018-06-04 06:07:00,815 ERROR [AtlassianEvent::0-BAM::EVENTS:pool-1-thread-7] [BambooPluginUtils] Error while executing com.atlassian.bamboo.brokenbuildtracker.BrokenBuildPostProcessor
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.1.18
- minor version:1
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1209
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_7A45FB_AOTRACKING_ENTRY_pkey"
Detail: Key ("TRACKING_ID")=(12522) already exists.Cause
PostgreSQL database server might have restarted when Bamboo is still running causing data inconsistency.
Resolution
- Shutdown Bamboo
- Backup database for rollback purposes
Run the following SQL queries:
SELECT MAX("TRACKING_ID") AS "lastTrackingID" FROM "AO_7A45FB_AOTRACKING_ENTRY"; SELECT MAX("RESULT_ID") AS "lastResultID" FROM "AO_7A45FB_AOTRACKING_RESULT";Run the following SQL queries depending on the above results:
SELECT SETVAL('"AO_7A45FB_AOTRACKING_ENTRY_TRACKING_ID_seq"',lastTrackingID); SELECT SETVAL('"AO_7A45FB_AOTRACKING_RESULT_RESULT_ID_seq"',lastResultID);Replace
lastTrackingIDandlastResultIDwith the results in Step 3- Restart Bamboo
Last modified on Jul 10, 2018
Powered by Confluence and Scroll Viewport.