Upgrade fails from 3.2 to 3.5.13 due to: failed during the SCHEMA_UPGRADE phase due to: StatementCallback
Symptoms
The upgrade fails to complete due to failed alter table commands. The following appears in the atlassian-confluence.log:
2012-03-12 11:48:32,599 WARN [main] [confluence.upgrade.upgradetask.AbstractConstraintCreationUpgradeTask] doUpgrade Could not create unique constraint:
alter table cwd_membership add constraint cwd_unique_user_membership unique (parent_id, child_user_id), StatementCallback; bad SQL grammar
[alter table cwd_membership add constraint cwd_unique_user_membership unique (parent_id, child_user_id)]; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'cwd_unique_user_membership'
2012-03-12 11:48:32,601 WARN [main] [confluence.upgrade.upgradetask.AbstractConstraintCreationUpgradeTask] doUpgrade Could not create unique constraint:
alter table cwd_user add constraint cwd_user_name_dir_id unique (lower_user_name, directory_id), StatementCallback; bad SQL grammar
[alter table cwd_user add constraint cwd_user_name_dir_id unique (lower_user_name, directory_id)]; nested exception is
comm.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'cwd_user_name_dir_id'
2012-03-12 11:48:32,602 WARN [main] confluence.upgrade.upgradetask.AbstractConstraintCreationUpgradeTask] doUpgrade Could not create unique constraint:
alter table cwd_group add constraint cwd_group_name_dir_id unique (lower_group_name, directory_id), StatementCallback; bad SQL grammar
[alter table cwd_group add constraint cwd_group_name_dir_id unique (lower_group_name, directory_id)]; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'cwd_group_name_dir_id'
2012-03-12 11:48:32,604 WARN [main] confluence.upgrade.upgradetask.AbstractConstraintCreationUpgradeTask] doUpgrade Could not create unique constraint:
alter table cwd_user_attribute add constraint cwd_unique_usr_attr unique (directory_id, user_id, attribute_name, attribute_lower_value), StatementCallback;
bad SQL grammar [alter table cwd_user_attribute add constraint cwd_unique_usr_attr unique (directory_id, user_id, attribute_name, attribute_lower_value)];
nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'cwd_unique_usr_attr'
2012-03-12 11:48:32,606 WARN [main] confluence.upgrade.upgradetask.AbstractConstraintCreationUpgradeTask] doUpgrade Could not create unique constraint:
alter table cwd_group_attribute add constraint cwd_unique_grp_attr unique (directory_id, group_id, attribute_name, attribute_lower_value), StatementCallback;
bad SQL grammar [alter table cwd_group_attribute add constraint cwd_unique_grp_attr unique (directory_id, group_id, attribute_name, attribute_lower_value)];
nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'cwd_unique_grp_attr'
2012-03-12 11:48:32,607 WARN [main] [confluence.upgrade.upgradetask.AbstractConstraintCreationUpgradeTask] doUpgrade Could not create unique constraint:
alter table cwd_membership add constraint cwd_unique_group_membership unique (parent_id, child_group_id), StatementCallback; bad SQL grammar
[alter table cwd_membership add constraint cwd_unique_group_membership unique (parent_id, child_group_id)]; nested exception is
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Duplicate key name 'cwd_unique_group_membership'
2012-03-12 11:48:32,621 INFO [main] confluence.upgrade.upgradetask.BandanaKeyUniqueConstraintCleaner] cleanUp Checking for invalid entries in the Bandana table
2012-03-12 11:48:32,631 INFO [main] confluence.upgrade.upgradetask.AddBandanaConstraintsUpgradeTask] doUpgrade Adding not null and unique constraints on
Bandana context and key columns
2012-03-12 11:48:32,636 INFO [main] confluence.upgrade.upgradetask.AddBandanaConstraintsUpgradeTask] doUpgrade Ignoring non-existence of band_key_idx constraint
2012-03-12 11:48:32,690 INFO [main] [confluence.upgrade.ddl.AlterTableExecutor] alterTableUngrouped Database is reporting that the column already has the
property that we want. SQL: alter table BANDANA add constraint bandana_unique_key unique (BANDANACONTEXT, BANDANAKEY)
2012-03-12 11:48:32,716 INFO [main] [confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask] doUpgrade Beginning task to add not null and
unique constraints on Content Permissions and Content Permission Set columns
2012-03-12 11:48:32,754 ERROR [main] [confluence.upgrade.ddl.AlterTableExecutor] alterTable Failed to run alter table commands
2012-03-12 11:48:32,779 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized Upgrade failed, application will not start:
Upgrade task com.atlassian.confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask@520ebd99 failed during the SCHEMA_UPGRADE phase due to:
StatementCallback; uncategorized SQLException for SQL [alter table CONTENT_PERM modify CPS_ID bigint not null, add constraint cp_unique_user unique
(CPS_ID, CP_TYPE, USERNAME), add constraint cp_unique_group unique (CPS_ID, CP_TYPE, GROUPNAME)]; SQL state [HY000]; error code [1025];
Error on rename of './confluence_dbo/#sql-12e1_43c' to './confluence_dbo/content_perm' (errno: 150); nested exception is java.sql.SQLException:
Error on rename of './confluence_dbo/#sql-12e1_43c' to './confluence_dbo/content_perm' (errno: 150) com.atlassian.confluence.upgrade.UpgradeException:
Upgrade task com.atlassian.confluence.upgrade.upgradetask.ContentPermissionConstraintsUpgradeTask@520ebd99 failed during the SCHEMA_UPGRADE phase due to:
StatementCallback; uncategorized SQLException for SQL [alter table CONTENT_PERM modify CPS_ID bigint not null, add constraint cp_unique_user unique
(CPS_ID, CP_TYPE, USERNAME), add constraint cp_unique_group unique (CPS_ID, CP_TYPE, GROUPNAME)]; SQL state [HY000]; error code [1025];
Error on rename of './confluence_dbo/#sql-12e1_43c' to './confluence_dbo/content_perm' (errno: 150); nested exception is java.sql.SQLException:
Error on rename of './confluence_dbo/#sql-12e1_43c' to './confluence_dbo/content_perm' (errno: 150)Cause
Upgrade fails on running statements like this:
alter table CONTENT_PERM modify CPS_ID bigint not null, add constraint cp_unique_user unique (CPS_ID, CP_TYPE, USERNAME), add constraint cp_unique_group unique (CPS_ID, CP_TYPE, GROUPNAME)The cause for the failure relates to the datatype of the field in the database. In this example, the CPS_ID was defined as a decimal (possibly because of the MySQL importer tool that was used to import the DB) and due to a foreign key constraint on the column, the alter table statement fails.
Workaround
Possible that a reimport of the database using tool other than MySQL tool kit, which may have converted some of the column data types when importing to decimal(19,0) . Ensure that there are no columns with type decimal(19,0) that are not intended to have that.
Resolution
- In order for the upgrade tasks to run successfully, you will need to modify the database by running the statements from the error messages in the log
You may also need to drop constraints on the table in order to modify the table, as well as any foreign key constraints. You can find these by examining the table metadata.
alter table CONTENT_PERM drop foreign key `FKBD74B31676E33274`; alter table CONTENT_PERM_SET modify ID bigint not null; alter table CONTENT_PERM modify CPS_ID bigint not null, add constraint cp_unique_user unique (CPS_ID, CP_TYPE, USERNAME), add constraint cp_unique_group unique (CPS_ID, CP_TYPE, GROUPNAME); alter table content_perm add constraint FKBD74B31676E33274 foreign key (CPS_ID) references test.CONTENT_PERM_SET (ID);These commands are for MySQL database. You may need to modify the syntax for different database types.
Last modified on Mar 30, 2016
Powered by Confluence and Scroll Viewport.