Duplicate key in table, or Table not found errors in Schema Update section of Startup process
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
Problem
When upgrading Bamboo, you see INFO and ERROR lines such as the following in the atlassian-bamboo.log
1
2
3
4
2017-03-28 09:48:22,618 INFO [localhost-startStop-1] [DatabaseMetaData] HHH000262: Table not found: QUICK_FILTERS
...
2017-03-28 09:48:25,175 ERROR [localhost-startStop-1] [SchemaUpdate] HHH000388: Unsuccessful: alter table BRS_CONSUMED_SUBSCRIPTION add constraint FK_l4tpi519m59qhdvh4qx2cjp2n foreign key (CONSUMER_RESULTSUMMARY_ID) references BUILDRESULTSUMMARY (BUILDRESULTSUMMARY_ID)
2017-03-28 09:48:25,175 ERROR [localhost-startStop-1] [SchemaUpdate] Can't write; duplicate key in table '#sql-1620_1fb7d'Cause
Bamboo does a schema check (and auto correct) on every startup to ensure its integrity. A fixed set of commands are run and afterwards, if the database is configured correctly, the errors may still appear.
Validation
You can validate that the foreign keys are already in the tables to ensure everything is correct as per the error above. And then, you can safely ignore these errors.
Locate an unsuccessful alter command in the logs:
1alter table BRS_CONSUMED_SUBSCRIPTION add constraint FK_l4tpi519m59qhdvh4qx2cjp2n foreign key (CONSUMER_RESULTSUMMARY_ID) references BUILDRESULTSUMMARY (BUILDRESULTSUMMARY_ID)Run the following command in the Bamboo database:
1show create table BRS_CONSUMED_SUBSCRIPTION;Look for a line in the output referring to the constraint being added:
1 2 3... CONSTRAINT `FK_l4tpi519m59qhdvh4qx2cjp2n` FOREIGN KEY (`CONSUMER_RESULTSUMMARY_ID`) REFERENCES `buildresultsummary` (`BUILDRESULTSUMMARY_ID`) ...
To check the remaining Table not found errors, you can run queries using the tables listed in the errors. Below are some examples based on database type:
MySQL
1
show tables like 'QUICK_FILTERS';PostgreSQL
1
\dt 'QUICK_FILTERS'MS SQL
1
2
3
4
5
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'QUICK_FILTERS')
BEGIN
PRINT 'Table Exists'
END
Oracle
1
SHOW TABLES LIKE 'QUICK_FILTERS';Was this helpful?