Problem

The following appears in the atlassian-bitbucket.log when upgrading to from a Bitbucket Server version < 4.3.x to Bitbucket Server 4.4.x or later:

2012-10-10 12:42:18,286 ERROR [main]  liquibase Change Set liquibase/r1_3/m01.xml::BSERVDEV-1651-6::bturner failed.  Error: Error executing SQL ALTER TABLE `bitbucket`.`sta_comment` ADD CONSTRAINT `fk_sta_comment_author` FOREIGN KEY (`author_id`) REFERENCES `bitbucket`.`bitbucket_user` (`id`): Can't create table '<BITBUCKET_DB>.#sql-XXX_XX' (errno: 150)
liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE `bitbucket`.`sta_comment` ADD CONSTRAINT `fk_sta_comment_author` FOREIGN KEY (`author_id`) REFERENCES `bitbucket`.`bitbucket_user` (`id`): Can't create table '<BITBUCKET_DB>.#sql-XXX_XX' (errno: 150)
...
Caused by: java.sql.SQLException: Can't create table '<BITBUCKET_DB>.#sql-XXX_XX' (errno: 150)
...

Diagnosis

Environment

• MySQL as Database

Diagnostic Steps

Run the following query to check if the Table is set as MyISAM:

SELECT ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<BITBUCKET_DB>';

Cause

One or more of your tables are a MyISAM. In order to perform the migration correctly, the tables must be InnoDB.

Resolution

Create a dump of the old database, create a new database (according to our guide for MySQL), restore the dump of the old database into the new database and try to upgrade Bitbucket Server again:

 Before doing the steps below, remember to stop your Bitbucket Server instance.

1. Create a Dump file of the old Database

   mysqldump -u root -p[root_password] [database_name] > /PATH/TO/dumpfilename.sql

2. The dump contains the storage engine. The following command replaces the MyISAM with the innodb one

   sed -i.bak 's#MyISAM#InnoDB#g' /PATH/TO/dumpfilename.sql

3. Create a new database

   mysql> SET GLOBAL storage_engine = 'InnoDB';
   mysql> CREATE DATABASE bitbucket CHARACTER SET utf8 COLLATE utf8_bin;
   mysql> GRANT ALL PRIVILEGES ON bitbucket.* TO 'bitbucketuser'@'localhost' IDENTIFIED BY 'password';
   mysql> FLUSH PRIVILEGES;
   mysql> QUIT

4. Restore the old Database into the new Database 

   mysql -u root -p[root_password] [database_name] < /PATH/TO/dumpfilename.sql

5. Change the <BITBUCKET_HOME>/bitbucket.properties to use the new Database.
6. Now just try to upgrade the instance again and the procedure should go without any problems.