Upgrading Bamboo Data Center fails with exception "Specified key was too long; max key length is 3072 bytes"
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles for non-Data Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Summary
This issue was seen while upgrading Bamboo to any version >= 10.1.x and using MySQL database. The upgrade fails with the below exception while executing one of the new upgrade tasks.
2025-02-17 14:21:27,484 ERROR [main] [AbstractUpgradeManager] java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes
java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytesEnvironment
- Bamboo - Any version >= 10.1.x
- Database - MySQL
Diagnosis
Looking at <bamboo-home>atlassian-bamboo.log file we can see that the below error
1) Upgrade task 100103 is failing with MySQL DB exception > Specified key was too long; max key length is 3072 bytes
2025-02-17 14:40:50,186 INFO [main] [AbstractUpgradeManager] -----------------------------------------------------------------------------------
2025-02-17 14:40:50,186 INFO [main] [AbstractUpgradeManager] 100103 : Extend Notification - Recipient column size to 1000 characters (bootstrap)
2025-02-17 14:40:50,186 INFO [main] [AbstractUpgradeManager] -----------------------------------------------------------------------------------2025-02-17 14:40:50,198 ERROR [main] [AbstractUpgradeManager] java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes
java.sql.SQLSyntaxErrorException: Specified key was too long; max key length is 3072 bytes
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.26.jar:8.0.26]
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.26.jar:8.0.26]
at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764) ~[mysql-connector-java-8.0.26.jar:8.0.26]2) From Bamboo 10.1.x, this upgrade task 100103 will Alter the NOTIFICATION table and column RECIPIENT to size 1000 from the current 255. This change was done as part of
BAM-25835
-
Extend notification RECIPIENT column in NOTIFICATIONS table
Closed
Cause
The root cause of this issue is the presence of an extra RECIPIENT_TYPE INDEX linked to the NOTIFICATIONS table in the affected MySQL DB which is not part of the default Bamboo schema. That Index is causing the issue because it consumes more than the allowed 3072 bytes.
To confirm the above theory please run the the below query on the MySQL DB.
SHOW CREATE TABLE NOTIFICATIONS;The result would look like below
NOTIFICATIONS | CREATE TABLE NOTIFICATIONS (
NOTIFICATION_RULE_ID bigint NOT NULL,
RECIPIENT_TYPE varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
RECIPIENT varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
NOTIFICATION_SET bigint DEFAULT NULL,
CONDITION_KEY varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
CONDITION_DATA varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
PRIMARY KEY (NOTIFICATION_RULE_ID),
UNIQUE KEY RECIPIENT_TYPE (RECIPIENT_TYPE,RECIPIENT,NOTIFICATION_SET,CONDITION_KEY,CONDITION_DATA),
KEY FK_9yq63rpvaew90wyh5wim6664j (NOTIFICATION_SET),
CONSTRAINT FK_9yq63rpvaew90wyh5wim6664j FOREIGN KEY (NOTIFICATION_SET) REFERENCES NOTIFICATION_SETS (NOTIFICATION_SET_ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_binIf you compare this with a fresh Installation of Bamboo, the result would look like below.
CREATE TABLE `NOTIFICATIONS` (
`NOTIFICATION_RULE_ID` bigint NOT NULL,
`RECIPIENT_TYPE` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`RECIPIENT` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
`NOTIFICATION_SET` bigint DEFAULT NULL,
`CONDITION_KEY` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
`CONDITION_DATA` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`NOTIFICATION_RULE_ID`),
KEY `FK_9yq63rpvaew90wyh5wim6664j` (`NOTIFICATION_SET`),
CONSTRAINT `FK_9yq63rpvaew90wyh5wim6664j` FOREIGN KEY (`NOTIFICATION_SET`) REFERENCES `NOTIFICATION_SETS` (`NOTIFICATION_SET_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_binPlease make a note of the extra unique key present in the result which is not available in default Bamboo Instance. We are unsure on how this was added to the DB Instance, our suspect is probably a third party plugin could cause this but we are unsure on this yet.
UNIQUE KEY RECIPIENT_TYPE (RECIPIENT_TYPE,RECIPIENT,NOTIFICATION_SET,CONDITION_KEY,CONDITION_DATA)Note : When you define a unique key on a column, MySQL automatically creates an index on that column to facilitate faster data retrieval and this Index is creating a problem here.
Solution
There are two solution to this issue
Solution 1
- You can let Bamboo ignore this upgrade task as it is just changing the character length of RECIPIENT column of from 255 characters to 1000.
- If you want to do that go to <bamboo-install>atlassian-bamboo>WEB-INF>classes and open upgrades.xml file
- Please comment out the below line from this file and perform the upgrade again.
<!-- upgrade build="100103" class="com.atlassian.bamboo.upgrade.tasks.v10_1.UpgradeTask100103ExtendNotificationRecipientColumnSize"/-->Please note this is just a short term fix and if any plugin requires RECIPIENT column to be of size greater than 255 characters you'll have to follow the workaround defined at
BAM-25835
-
Extend notification RECIPIENT column in NOTIFICATIONS table
Closed
Solution 2
Please make sure to take a backup of NOTIFICATIONS table before processing ahead with solution 2
- Run the below command and identify if there are any extra Indexes present, in the cause section above I've provided the schema details of NOTIFICATIONS table from a fresh Bamboo Installation. you can compare the results with that.
SHOW CREATE TABLE NOTIFICATIONS;- Run the below 2 command and check if there are no errors. Here I am assuming the extra Index name is RECIPIENT_TYPE, please replace the INDEX name if it is different in your Instance.
ALTER TABLE `NOTIFICATIONS` DROP INDEX `RECIPIENT_TYPE`;
ALTER TABLE `NOTIFICATIONS` MODIFY COLUMN `RECIPIENT` VARCHAR(1000);- If there are no error post the above step, please proceed ahead with the upgrade.