Unsuccessful: create index Error in Logs Using MySQL

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

The following appears in the atlassian-bamboo.log:

2013-08-13 22:58:19,471 ERROR [main] [SchemaUpdate] Unsuccessful: create index dep_ver_commit_rev_idx on DEPLOYMENT_VERSION_COMMIT (COMMIT_REVISION)
2013-08-13 22:58:19,471 ERROR [main] [SchemaUpdate] BLOB/TEXT column 'COMMIT_REVISION' used in key specification without a key length

Diagnosis

Check your <bamboo-home>/bamboo.cfg.xml for the following line:

<property name="hibernate.dialect">com.atlassian.hibernate.dialect.MySQLDialect</property>

If it is present, Hibernate is using an outdated dialect for processing SQL statements.

Cause

A table in your Bamboo database is using text for the column type, but the query to add the index is expecting varchar.  As of MySQL 5.0.3, larger varchars can be specified, so Bamboo no longer creates many of these tables as text (see this bug report for additional details).  Per the diagnosis section, the dialect is causing Hibernate to create queries compatible with versions of MySQL prior to 5.0.3.

Resolution

  • Shutdown Bamboo.

  • Edit your <bamboo-home>/bamboo.cfg.xml and change the hibernate.dialect to match this line:

    <property name="hibernate.dialect">com.atlassian.hibernate.dialect.MySQL5Dialect</property>

     

  • Change columns to varchar:

    1. Create a database dump without data:

      mysqldump --no-data -u <username> -p <password> bamboodb > bamboodb.sql

    2. Search for columns using text in their CREATE TABLE statements. As of Bamboo 5.0, the only column that should be using text is the PUBLIC_KEY_CLOB column in the TRUSTED_APPS table.
      (info) For Unix users, you can grep the information into an outfile in your working directory using:

      grep -B10 " text" /path/to/bamboo.sql | grep "CREATE TABLE\| text" |tee ./outfile

    3. Modify all of your tables using this query format:

      ALTER table DEPLOYMENT_VERSION_COMMIT MODIFY column COMMIT_REVISION varchar(4000) DEFAULT NULL;
Sample script for Bamboo 5.0 
ALTER TABLE `AUDIT_LOG` MODIFY column `MSG` varchar(4000);
ALTER TABLE `BRANCH_COMMIT_INFO` MODIFY column `CREATING_CHANGE_SET_ID` varchar(4000), MODIFY column `LATEST_COMMIT_CHANGE_SET_ID` varchar(4000);
ALTER TABLE `CAPABILITY` MODIFY column `VALUE` varchar(4000);
ALTER TABLE `COMMIT_FILES` MODIFY column `COMMIT_FILE_NAME` varchar(1000), MODIFY column `COMMIT_FILE_REIVISION` varchar(4000);
ALTER TABLE `DEPLOYMENT_VARIABLE_SUBS` MODIFY column `VARIABLE_VALUE` varchar(4000);
ALTER TABLE `DEPLOYMENT_VERSION_CHANGESET` MODIFY column `CHANGESET_ID` varchar(4000);
ALTER TABLE `DEPLOYMENT_VERSION_COMMIT` MODIFY column `COMMIT_REVISION` varchar(4000);
ALTER TABLE `MERGE_RESULT` MODIFY column `FAILURE_REASON` varchar(4000);
ALTER TABLE `NOTIFICATIONS` MODIFY column `CONDITION_DATA` varchar(4000);
ALTER TABLE `REPOSITORY_CHANGESET` MODIFY column `CHANGESET_ID` varchar(4000);
ALTER TABLE `REQUIREMENT` MODIFY column `MATCH_VALUE` varchar(4000), MODIFY column `PLUGIN_MODULE_KEY` varchar(4000);
ALTER TABLE `TEST_CASE` MODIFY column `TEST_CASE_NAME` varchar(4000), MODIFY column `QUARANTINING_USERNAME` varchar(1000), MODIFY column `LINKED_JIRA_ISSUE` varchar(256);
ALTER TABLE `TEST_CLASS` MODIFY column `TEST_CLASS_NAME` varchar(4000);
ALTER TABLE `VARIABLE_BASELINE_ITEM` MODIFY column `VARIABLE_VALUE` varchar(4000);
ALTER TABLE `VCS_LOCATION` MODIFY column `PLUGIN_KEY` varchar(4000) NOT NULL;
Last modified on Mar 30, 2016

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.