DVCS Repository Sync Fails after the Primary Key on the AO_E8B6CC_MESSAGE_TAG table reaches its Maximum Value
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs 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
When trying to sync your DVCS repositories from Settings > Applications > DVCS Accounts, the synchronization fails due to a SQLIntegrityConstraintViolationException. Although this appears as a duplicate value found in the AO_E8B6CC_MESSAGE_TAG table, if the value is 2,147,483,647 then the table has reached it's maximum capacity.
Environment
Jira 8.22.6 through 10.1.x
Diagnosis
In the atlassian-jira.log file, you should expect to see error message like this. The most important entry is: Duplicate entry '2147483647' for key 'AO_E8B6CC_MESSAGE_TAG.PRIMARY'
1
2
3
4
2024-04-29 14:59:58,294-0400 http-nio-8080-exec-30 ERROR /rest/bitbucket/1.0/repository/[id]/softsync
[c.a.j.p.d.sync.impl.DefaultSynchronizer] There was a SQL exception thrown by the Active Objects library:
...
java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '2147483647' for key 'AO_E8B6CC_MESSAGE_TAG.PRIMARY' Cause
The Primary Key on the AO_E8B6CC_MESSAGE_TAG is a type: INT and therefore the maximum value it can reach is: 2,147,483,647 . When the table hits this value, no new entries can be added then processed by Jira which results in the linked repositories becoming stale in Jira, and will always fail to sync.
Solution
ℹ️ Upgrade to Jira 10.2.0 in order to Resolve this issue. If that is not possible, then please refer to the work arounds below:
On databases which use sequences, such as PostgreSQL, you are able to:
Stop Jira
Backup your Database
Reset the Sequence
Start Jira
Postgres
1
2
3
4
5
6
7
8
9
BEGIN;
LOCK TABLE "AO_E8B6CC_MESSAGE" IN ROW EXCLUSIVE MODE ;
LOCK TABLE "AO_E8B6CC_MESSAGE_QUEUE_ITEM" in ROW EXCLUSIVE MODE;
LOCK TABLE "AO_E8B6CC_MESSAGE_TAG" in ROW EXCLUSIVE MODE ;
ALTER SEQUENCE "AO_E8B6CC_MESSAGE_TAG_ID_seq" RESTART;
UPDATE "AO_E8B6CC_MESSAGE_TAG" SET "ID" = DEFAULT;
COMMIT; As for MySQL, you can use this to provide the same effect
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE refreshMTags()
BEGIN
DROP TABLE IF EXISTS jira.temp_tag;
CREATE TABLE temp_tag LIKE jira.AO_E8B6CC_MESSAGE_TAG;
INSERT INTO temp_tag(MESSAGE_ID, TAG)
SELECT MESSAGE_ID , TAG
FROM AO_E8B6CC_MESSAGE_TAG;
truncate TABLE AO_E8B6CC_MESSAGE_TAG;
ALTER TABLE AO_E8B6CC_MESSAGE_TAG AUTO_INCREMENT = 1;
INSERT INTO AO_E8B6CC_MESSAGE_TAG SELECT * FROM temp_tag;
DROP TABLE temp_tag;
END; Bug:
This bug is being tracked at: JSWSERVER-25929 - Better scaling for "AO_E8B6CC_MESSAGE_TAG" table
Please Upgrade to Jira 10.2.0 in order to Resolve this issue
Was this helpful?