Upgrade to Bitbucket Server 4.4+: Can't create table '<bitbucket_db>.#sql-XXX_XX' (errno: 150)

Still need help?

The Atlassian Community is here for you.

Ask the community

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:

(info) 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.

Last modified on Dec 15, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.