Enabling a custom Artifact handler on a Plan breaks the XML on XML_DEFINITION_DATA

Issue Summary

This is reproducible on Data Center:

When a Bamboo Plan has a defined custom artifact handler, the XML format on the DB gets broken and prevents any utilities that rely on the correct XML formatting from working.

Steps to Reproduce

1. Create a Plan
2. Enable the a custom Artifact Handler – we'll use S3 here
3. Configure the Plan and Enable/Disable a custom artifact handler
4. Try to extract data from the DB using XPATH:

   SELECT  B.FULL_KEY,
           B.TITLE,
           CAST((XPATH('//branches/branchRemovalCleanUpEnabled/text()', CAST(BD.XML_DEFINITION_DATA AS XML)))[1] AS TEXT)::BOOLEAN  AS "Branch_Removal_Cleanup"
    FROM   BUILD B
    JOIN   BUILD_DEFINITION BD
      ON   BD.BUILD_ID = B.BUILD_ID
   WHERE   B.BUILD_TYPE = 'CHAIN'
   

Expected Results

The SQL should work normally

Actual Results

Logs of XML syntax errors related to comAtlassianBambooPluginArtifactHandlerRemote:S3ArtifactHandler:enabledForShared and :enabledForNonShared are reported:

ERROR:  could not parse XML document
DETAIL:  line 2: Failed to parse QName 'comAtlassianBambooPluginArtifactHandlerRemote:S3ArtifactHandler:'
rtifactHandlers><comAtlassianBambooPluginArtifactHandlerRemote:S3ArtifactHandler
                                                                               ^
line 2: Namespace prefix comAtlassianBambooPluginArtifactHandlerRemote on S3ArtifactHandler:enabledForShared is not defined
comAtlassianBambooPluginArtifactHandlerRemote:S3ArtifactHandler:enabledForShared
                                                                               ^
line 2: Failed to parse QName 'comAtlassianBambooPluginArtifactHandlerRemote:S3ArtifactHandler:'
rtifactHandlers><comAtlassianBambooPluginArtifactHandlerRemote:S3ArtifactHandler
                                                                               ^
line 2: Namespace prefix comAtlassianBambooPluginArtifactHandlerRemote on S3ArtifactHandler:enabledForNonShared is not defined
AtlassianBambooPluginArtifactHandlerRemote:S3ArtifactHandler:enabledForNonShared
                                                                               ^
SQL state: 2200M 

NOTE: Removing or disabling the custom artifact handler leaves the broken entries on the DB and changes it to disabled, but the issue persists.

Workaround

As a temporary fix, you will have to create a temporary table and replace the broken tags in it, then run the query using the TMP_BUILD_DEFINITION table instead. E.g. on PostgreSQL:

-- Create a temporary table for BUILD_DEFINITION to fix broken XML on XML_DEFINITION_DATA
DROP TABLE IF EXISTS TMP_BUILD_DEFINITION;
CREATE TEMPORARY TABLE TMP_BUILD_DEFINITION ON COMMIT DROP AS SELECT * FROM BUILD_DEFINITION;
UPDATE TMP_BUILD_DEFINITION SET XML_DEFINITION_DATA = REGEXP_REPLACE(XML_DEFINITION_DATA,
  ':(enabledForShared|enabledForNonShared|S3ArtifactHandler|ServerLocalArtifactHandler|BambooRemoteArtifactHandler|AgentLocalArtifactHandler|SftpArtifactHandler)', 
    '', 'g');

NOTE 1: MySQL 5 has no REGEXP_REPLACE function (MySQL 8 does). You will have to run multiple nested REPLACE(REPLACE(REPLACE(REPLACE for every element you want to replace with an empty string. Replacing the tags should work with MySQL, Oracle and MSSQL normally if you follow the equivalent replacement functions. You can also create a function that will work pretty much the same way as PostgreSQL's REGEX_REPLACE then call a produce to create the temporary table.

DELIMITER $$
CREATE FUNCTION CUSTOM_REGEXP_REPLACE(original TEXT, pattern TEXT, replacement TEXT)
RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE temp TEXT;
    DECLARE ch TEXT;
    DECLARE i INT;

    SET temp = '';
    SET i = 1;

    IF original REGEXP pattern THEN
        loop_label: LOOP
            IF i>CHAR_LENGTH(original) THEN
                LEAVE loop_label;
            END IF;
            SET ch = SUBSTRING(original, i, 1);
            IF ch REGEXP pattern THEN
                SET temp = CONCAT(temp, replacement);
            ELSE
                SET temp = CONCAT(temp, ch);
            END IF;
            SET i = i + 1;
        END LOOP;
    ELSE
        SET temp = original;
    END IF;

    RETURN temp;
END$$
DELIMITER ;

SET SESSION SQL_SAFE_UPDATES = 0;

DELIMITER $$
CREATE PROCEDURE TMP_BUILD_DEFINITION()
    DETERMINISTIC
BEGIN
    DROP TABLE IF EXISTS TMP_BUILD_DEFINITION;

    -- Create a new table with the same structure as BUILD_DEFINITION
    CREATE TABLE TMP_BUILD_DEFINITION SELECT * FROM BUILD_DEFINITION;

    -- Update XML_DEFINITION_DATA column
    UPDATE TMP_BUILD_DEFINITION SET XML_DEFINITION_DATA = CUSTOM_REGEXP_REPLACE(XML_DEFINITION_DATA,
      ':(enabledForShared|enabledForNonShared|S3ArtifactHandler|ServerLocalArtifactHandler|BambooRemoteArtifactHandler|AgentLocalArtifactHandler|SftpArtifactHandler)', 
        '');
END$$
DELIMITER ;
CALL TMP_BUILD_DEFINITION;

NOTE 2: If your query needs specific information related to the Artifact Handler replaced tags, you may decide to replace it with something else or just strip the extra “:” that is breaking the syntax.

NOTE 3: Depending on your AUTOCOMMIT settings(To check on PSQL run: \echo :AUTOCOMMIT) you may need to change the temporary table creation to not remove on commit and manually clean up the temporary table after you're done querying the build definition information.

CREATE TEMPORARY TABLE TMP_BUILD_DEFINITION AS SELECT * FROM BUILD_DEFINITION;