Summary

The upgrade process fails while trying to upgrade bamboo to version 8.2.3, below SQL_exception is seen in the atlassian-bamboo.log file

Task for build 80202 failed with exception: could not execute batch; SQL [insert into BRANCH_METADATA (BRANCH_DISPLAY_NAME, BRANCH_NAME, MARKED_INVALID, VCS_REPOSITORY_ID, PLAN_ID) values (?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.DataException: could not execute batch

Environment

Upgrading Bamboo to 8.2.3

Diagnosis

• The issue is related to upgrade task: 80202 which was introduced in Bamboo 8.2 release. 

  Task for build 80202 failed with exception: could not execute batch; SQL [insert into BRANCH_METADATA (BRANCH_DISPLAY_NAME, BRANCH_NAME, MARKED_INVALID, VCS_REPOSITORY_ID, PLAN_ID) values (?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.DataException: could not execute batch

• In the below example we can see that value of branch_name and branch_display name is greater than the allowed character limit of 255.

  Caused by: java.sql.BatchUpdateException: 80 (BRANCH_DISPLAY_NAME, BRANCH_NAME, MARKED_INVALID, VCS_REPOSITORY_ID, PLAN_ID) values  was aborted: ERROR: value too long for type character varying(255)

Cause

• The metadata of the table branch_metadata specifies column length of name and display_name columns to be of 255 characters length, if the character length of plan_branch name or display_name is greater than 255 character, it will throw SQL exception highlighted above. 

• This name is taken from xml_definition_data column of vcs_location table, as this a xml type column there is no error when this is stored in Bamboo.  

Solution

There are 2 ways in which we can approach the solution, I'll mention both of the steps below

Solution 1

• The first approach is to change the plan_branch name in the source repository and resave so that in Bamboo the length is reduced to < 255 characters and then try to upgrade with the new data. This can always be validated in the DB by running the below query to check if there are any branches which has long name/display_name.

select unnest(xpath('//vcsBranch[@class="com.atlassian.bamboo.plan.branch.VcsBranchImpl"]/name/text()',CAST(VL.XML_DEFINITION_DATA as XML)))::text AS XML, VL.NAME 
from vcs_location vl 

SET QUOTED_IDENTIFIER ON;

SELECT VL.NAME AS RepoName, XC.value('(./text())[1]','VARCHAR(MAX)') AS BranchName
FROM VCS_LOCATION VL 
CROSS APPLY 
    (SELECT CAST(XML_DEFINITION_DATA AS XML) AS ParsedXML) AS Parsed 
CROSS APPLY 
    Parsed.ParsedXML.nodes('//vcsBranch[@class="com.atlassian.bamboo.plan.branch.VcsBranchImpl"]/name') AS XT(XC);

Solution 2

The second approach is to identify the plan_branch in Bamboo which is causing this issue and if the repository update is not possible as per above, delete the plan_branch in Bamboo and it can be recreated post upgrade. 

The details of the plan_branch can be found by running the below query

select * from build where build_id=67975909

build_id can be found from the plan_id column which is mentioned in the error description under the diagnosis section above.