Symptom

An in-place upgrade of a Bamboo Server with MySQL database fails with errors like the following in the logs:

INFO | jvm 1 | 2010/07/08 10:02:47 | 2010-07-08 10:02:47,025 ERROR [WrapperSimpleAppMain] [SchemaUpdate] Unsuccessful: create index label_name_namespace_index on LABEL (NAME, NAMESPACE)
INFO | jvm 1 | 2010/07/08 10:02:47 | 2010-07-08 10:02:47,025 ERROR [WrapperSimpleAppMain] [SchemaUpdate] Specified key was too long; max key length is 1000 bytes

This error only occurs when the MyISAM engine is used for the table and the character set is set to UTF8. You can determine which engine and character set your table uses with the following statement:

show create table BUILD;

The engine and character set will be specified near the end of the output, as in:

CREATE TABLE `BUILD` (
  `BUILD_ID` bigint(20) NOT NULL,
  `CREATED_DATE` datetime DEFAULT NULL,
  `UPDATED_DATE` datetime DEFAULT NULL,
  `FULL_KEY` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `BUILDKEY` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `TITLE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `REVISION_KEY` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `FIRST_BUILD_NUMBER` int(11) DEFAULT NULL,
  `LATEST_BUILD_NUMBER` int(11) DEFAULT NULL,
  `NEXT_BUILD_NUMBER` int(11) DEFAULT NULL,
  `SUSPENDED_FROM_BUILDING` bit(1) DEFAULT NULL,
  `MARKED_FOR_DELETION` bit(1) DEFAULT NULL,
  `PROJECT_ID` bigint(20) NOT NULL,
  `NOTIFICATION_SET` bigint(20) DEFAULT NULL,
  `REQUIREMENT_SET` bigint(20) DEFAULT NULL,
  `BUILD_TYPE` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `DESCRIPTION` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`BUILD_ID`),
  KEY `build_key_idx` (`FULL_KEY`),
  KEY `FK3C9CE4E707D72EE` (`NOTIFICATION_SET`),
  KEY `FK3C9CE4EA77F0821` (`PROJECT_ID`),
  KEY `FK3C9CE4E645E1626` (`REQUIREMENT_SET`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

To list all tables' status to see which engine is used, use the following:

show table status;

Cause

There is a known bug with MySQL related to MyISAM, the UTF8 character set and indexes:

http://bugs.mysql.com/bug.php?id=4541

Resolution

Configure all tables to use the InnoDB engine.

1. If you have already attempted to upgrade Bamboo and have run into this problem, restore your database from backup to the state before the upgrade attempt

2. For each table you'll need to set the correct DB engine with a query such as ALTER TABLE <table_name> ENGINE=InnoDB;
   You can generate a list of ALTER TABLE statements for all tables in your database with the following SQL query:

   SET @DATABASE_NAME = 'name_of_your_db';
   
   SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
   FROM    information_schema.tables AS tb
   WHERE   table_schema = @DATABASE_NAME
   AND     `ENGINE` = 'MyISAM'
   AND     `TABLE_TYPE` = 'BASE TABLE'
   ORDER BY table_name DESC;

3. Change the default storage engine for your database so new tables are created appropriately:

   SET GLOBAL storage_engine='InnoDb';

4. Attempt the upgrade of Bamboo again