Specified key was too long errors in the logs even when Bamboo database is correctly set to InnoDB

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

Following errors show up in the log files:

2014-05-14 14:16:58,936 ERROR [localhost-test] [SchemaUpdate] Unsuccessful: create index dep_ver_commit_rev_idx on DEPLOYMENT_VERSION_COMMIT (COMMIT_REVISION)
2014-05-14 14:16:58,936 ERROR [localhost-test] [SchemaUpdate] Specified key was too long; max key length is 767 bytes
2014-05-14 14:16:58,952 ERROR [localhost-test] [SchemaUpdate] Unsuccessful: create index commit_rev_idx on USER_COMMIT (COMMIT_REVISION)
2014-05-14 14:16:58,952 ERROR [localhost-test] [SchemaUpdate] Specified key was too long; max key length is 767 bytes

Although this looks like the same problem as detailed in the knowledge base article: Upgrading Bamboo with MySQL database fails with messages like "specified key was too long",  the solution does not apply since all tables are already set to InnoDB.

Cause

The character length value set for the 'COMMIT_REVISION' column in 'DEPLOYMENT_VERSION_COMMIT' and 'USER_COMMIT' tables is too big. These errors are harmless and the worst they can do is affecting performance. Also, they only show up if the database in use is MySQL.  

Resolution

Create a back up of your database, a copy of <bamboo-home> directory, then proceed with the following:

  1. Double check to make sure all tables are using the InnoDB engine, if not follow the instructions in this KB to change the storage engine.
  2. Refer to the related section in this document: innodb_large_prefix and enable "innodb_large_prefix" option to allow index key prefixes longer than 767 bytes (up to 3072 bytes).
  3. Run the following two queries against your Bamboo database:

    SELECT Max(CHAR_LENGTH('COMMIT_REVISION')) AS Max FROM DEPLOYMENT_VERSION_COMMIT;
    SELECT Max(CHAR_LENGTH('COMMIT_REVISION')) AS Max FROM USER_COMMIT;
  4. If the result of both is smaller than 255 then run the following two:

    ALTER TABLE DEPLOYMENT_VERSION_COMMIT MODIFY COMMIT_REVISION VARCHAR(255);
    ALTER TABLE USER_COMMIT MODIFY COMMIT_REVISION VARCHAR(255);
Last modified on May 30, 2014

Was this helpful?

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