Bamboo Database Error - MySQL (errno: 150)
Symptoms
Bamboo starts normally, but it shows database related foreign key errors like the ones below:
...
2012-07-26 16:01:28,703 ERROR [main] [SchemaUpdate] Unsuccessful: alter table USER_COMMENT add constraint FK19DA09CBA958B29F foreign key (BUILDRESULTSUMMARY_ID) references BUILDRESULTSUMMARY (BUILDRESULTSUMMARY_ID)
2012-07-26 16:01:28,703 ERROR [main] [SchemaUpdate] Can't create table 'bamboodb.#sql-f8f_139' (errno: 150)
alter table TEST_CLASS_RESULT add constraint FK3521FF71A958B29F foreign key (BUILDRESULTSUMMARY_ID) references BUILDRESULTSUMMARY (BUILDRESULTSUMMARY_ID)
2012-07-26 16:01:29,107 ERROR [main] [SchemaUpdate] Unsuccessful: alter table TEST_CLASS_RESULT add constraint FK3521FF71A958B29F foreign key (BUILDRESULTSUMMARY_ID) references BUILDRESULTSUMMARY (BUILDRESULTSUMMARY_ID)
2012-07-26 16:01:29,107 ERROR [main] [SchemaUpdate] Can't create table 'bamboodb.#sql-f8f_139' (errno: 150)
alter table ACL_ENTRY add constraint FK2FB5F83D988CEFE9 foreign key (ACL_OBJECT_IDENTITY) references ACL_OBJECT_IDENTITY (ID)
alter table CAPABILITY add constraint FKEE341118A542349B foreign key (CAPABILITY_SET) references CAPABILITY_SET (CAPABILITY_SET_ID)
alter table BUILDRESULTSUMMARY_CUSTOMDATA add constraint FK30932C1FA958B29F foreign key (BUILDRESULTSUMMARY_ID) references BUILDRESULTSUMMARY (BUILDRESULTSUMMARY_ID)
alter table CHAIN_STAGE add constraint FKB613CFC0D96054AC foreign key (BUILD_ID) references BUILD (BUILD_ID)
2012-07-26 16:02:05,173 ERROR [main] [SchemaUpdate] Unsuccessful: alter table CHAIN_STAGE add constraint FKB613CFC0D96054AC foreign key (BUILD_ID) references BUILD (BUILD_ID)
2012-07-26 16:02:05,174 ERROR [main] [SchemaUpdate] Can't create table 'bamboodb.#sql-f8f_139' (errno: 150)
... Cause
There can be many reasons for getting MySQL 'errno 150' error as per this page. However, one of the main reasons is using MyISAM DB engine or MyISAM engine for some of the tables (for which Bamboo fails to set foreign keys).
Resolution
1. One of the ways is to get a DB dump of Bamboo's MySQL DB with '--no-data' option (which returns only the definitions, and no data).
mysqldump --no-data -u username bamboodb > dump_no_data.sqlAfter, go over the DB dump and find the tables that use MyISAM engine.
DROP TABLE IF EXISTS `BUILD`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `BUILD` (
`BUILD_ID` bigint(20) NOT NULL,
`CREATED_DATE` timestamp NULL DEFAULT NULL,
...
KEY `FK3C9CE4E7C814E1C` (`STAGE_ID`),
KEY `plan_deletion_idx` (`MARKED_FOR_DELETION`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;Next, change them to use InnoDB. For example, changing the engine for CAPABILITY table will look like this:
ALTER TABLE CAPABILITY ENGINE=InnoDB;
2. Another approach would be using a tool like the DBVisualizer to find and change the engine to InnoDB for all the tables that have MyISAM engine.