Broken Build Processor Inserts fail on SQL Server when no IDENTITY column defined
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
When the Broken Build Processor is inserting responsible user information into the database, the inserts can fail because auto-increment ("IDENTITY") is not defined on certain columns in the related tables:
- AO_7A45FB_AOTRACKING_ENTRY
- AO_7A45FB_AOTRACKING_RESULT
- AO_7A45FB_AOTRACKING_USER
The following appears in the atlassian-bamboo.log
2017-03-16 14:23:45,742 ERROR [AtlassianEvent::0-BAM::EVENTS:pool-1-thread-16] [ChainPluginSupportImpl] file:<bamboo-install>/atlassian-bamboo/WEB-INF/atlassian-bundled-plugins/atlassian-bamboo-plugin-brokenbuildtracker-<version>.jar, BrokenBuildPostProcessor: com.atlassian.bamboo.brokenbuildtracker.BrokenBuildPostProcessor: There was a SQL exception thrown by the Active Objects library:
Database:
- name:Microsoft SQL Server
- version:12.00.4100
- minor version:0
- major version:12
Driver:
- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
- version:1.3.1
java.sql.SQLException: Cannot insert the value NULL into column 'TRACKING_ID', table '<bamboo-db>.dbo.AO_7A45FB_AOTRACKING_ENTRY'; column does not allow nulls. INSERT fails.
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:Microsoft SQL Server
- version:12.00.4100
- minor version:0
- major version:12
Driver:
- name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase
- version:1.3.1
java.sql.SQLException: Cannot insert the value NULL into column 'TRACKING_ID', table '<bamboo-db>.dbo.AO_7A45FB_AOTRACKING_ENTRY'; column does not allow nulls. INSERT fails.
...
Caused by: java.sql.SQLException: Cannot insert the value NULL into column 'TRACKING_ID', table '<bamboo-db>.dbo.AO_7A45FB_AOTRACKING_ENTRY'; column does not allow nulls. INSERT fails.
...Diagnosis
Environment
- MS SQL Server
Diagnostic Steps
Please run the following commands on your database:
sp_help '[dbo].[AO_7A45FB_AOTRACKING_RESULT]'
sp_help '[dbo].[AO_7A45FB_AOTRACKING_USER]'
sp_help '[dbo].[AO_7A45FB_AOTRACKING_ENTRY]'
Please check the output for the phrase No identity column defined. If that phrase exists, please continue reading for cause and resolution.
Cause
Root cause is currently undefined, but the tables were not created with IDENTITY columns. Bamboo relies on these columns when inserting, for the auto-increment functionality.
Resolution
Since these tables are created on Bamboo startup, please:
- Stop Bamboo
- Drop the following tables:
AO_7A45FB_AOTRACKING_ENTRYAO_7A45FB_AOTRACKING_RESULTAO_7A45FB_AOTRACKING_USER
- Start Bamboo