Upgrade to Stash 1.3+: Can't create table '<stash_db>.#sql-XXX_XX' (errno: 150)
Problem
The following appears in the atlassian-stash.log when upgrading from Stash 1.2.X to Stash 1.3.X:
2012-10-10 12:42:18,286 ERROR [main] liquibase Change Set liquibase/r1_3/m01.xml::STASHDEV-1651-6::bturner failed. Error: Error executing SQL ALTER TABLE `stash`.`sta_comment` ADD CONSTRAINT `fk_sta_comment_author` FOREIGN KEY (`author_id`) REFERENCES `stash`.`stash_user` (`id`): Can't create table '<STASH_DB>.#sql-XXX_XX' (errno: 150)
liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE `stash`.`sta_comment` ADD CONSTRAINT `fk_sta_comment_author` FOREIGN KEY (`author_id`) REFERENCES `stash`.`stash_user` (`id`): Can't create table '<STASH_DB>.#sql-XXX_XX' (errno: 150)
...
Caused by: java.sql.SQLException: Can't create table '<STASH_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 = '<STASH_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.
Create a
Dump fileof the oldDatabasemysqldump -u root -p[root_password] [database_name] > /PATH/TO/dumpfilename.sqlThe 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.sqlCreate 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> QUITRestore the
old Databaseinto thenew Databasemysql -u root -p[root_password] [database_name] < /PATH/TO/dumpfilename.sql- Change the
<STASH_HOME>/stash-config.propertiesto use the newDatabase. - Now just try to
upgradethe instance again and the procedure should go without any problems.