Unsuccessful: create index Error in Logs Using MySQL
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
Symptoms
The following appears in the atlassian-bamboo.log:
1
2
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 lengthDiagnosis
Check your <bamboo-home>/bamboo.cfg.xml for the following line:
1
<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.
Solution
Resolution
Shutdown Bamboo.
Edit your
<bamboo-home>/bamboo.cfg.xmland change thehibernate.dialectto match this line:1<property name="hibernate.dialect">com.atlassian.hibernate.dialect.MySQL5Dialect</property>Change columns to
varchar:
Create a database dump without data:
1mysqldump --no-data -u <username> -p <password> bamboodb > bamboodb.sqlSearch for columns using
textin their CREATE TABLE statements. As of Bamboo 5.0, the only column that should be usingtextis thePUBLIC_KEY_CLOBcolumn in theTRUSTED_APPStable.ℹ️ For Unix users, you can grep the information into an outfile in your working directory using:
1grep -B10 " text" /path/to/bamboo.sql | grep "CREATE TABLE\| text" |tee ./outfileModify all of your tables using this query format:
1ALTER table DEPLOYMENT_VERSION_COMMIT MODIFY column COMMIT_REVISION varchar(4000) DEFAULT NULL;
Sample script for Bamboo 5.0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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;Was this helpful?