MySQL SQL script error - drop foreign key

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptom 1

Restoring a backup into an existing database with the force option fails with the following error:

java.io.IOException: Error talking to database: Problem running drop script (you may have to manually drop the DB) /data1/attlasian/fecru-2.9.1/sql/MYSQL/schema/drop_67.sql
at com.atlassian.crucible.migration.item.SQLBackup.restore(SQLBackup.java:201)
at com.atlassian.crucible.migration.item.SQLBackup$1.restore(SQLBackup.java:169)
at com.cenqua.fisheye.ctl.Restore.run(Restore.java:191)
at com.cenqua.fisheye.ctl.Restore.main(Restore.java:267)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

...
Caused by: com.cenqua.crucible.hibernate.CruDBException: Problem running drop script (you may have to manually drop the DB) /data1/attlasian/fecru-2.9.1/sql/MYSQL/schema/drop_67.sql
...
Caused by: com.cenqua.crucible.hibernate.CruDBException: SQL script error on line 1: "alter table cru_changeset_comment drop foreign key FK1C588BB9C50DAE44;" 
(Error on rename of './fisheyedb/cru_changeset_comment' to './fisheyedb/#sql2-3eed-26559' (errno: 152)), please contact http://www.atlassian.com/support/
...
Caused by: java.sql.SQLException: Error on rename of './fisheyedb/cru_changeset_comment' to './fisheyedb/#sql2-3eed-26559' (errno: 152)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)

Symptom 2

Upgrading Fisheye fails with the following error in atlassian-fisheye-<date>.log

2012-12-12 17:40:15,898 INFO  [main ] fisheye DefaultDBControl-upgrade - Upgrading DB from version 73 using /Users/foong/Desktop/CustomWare/fisheye/fecru-2.9.1/sql/MYSQL/upgrade/upgrade_73.sql
2012-12-12 17:40:16,567 ERROR [main ] org.springframework.web.context.ContextLoader ContextLoader-initWebApplicationContext - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dbControlFactory' defined in ServletContext resource [/WEB-INF/applicationContext.xml]: Instantiation of bean failed; nested exception is org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [com.cenqua.crucible.hibernate.DBControlFactoryImpl]: Constructor threw exception; nested exception is com.cenqua.crucible.hibernate.CruDBException: Problem upgrading with script /Users/foong/Desktop/CustomWare/fisheye/fecru-2.9.1/sql/MYSQL/upgrade/upgrade_73.sql: SQL script error on line 3: "alter table cru_comment drop foreign key FKE5A1D106C50DAE44;" 
(Error on rename of './fecru236/cru_comment' to './fecru236/#sql2-103-82' (errno: 152)), please contact http://www.atlassian.com/support/
	at org.springframework.beans.factory.support.ConstructorResolver.autowireConstructor(ConstructorResolver.java:288)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.autowireConstructor(AbstractAutowireCapableBeanFactory.java:1003)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:907)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:485)
...
Caused by: org.springframework.beans.BeanInstantiationException: Could not instantiate bean class [com.cenqua.crucible.hibernate.DBControlFactoryImpl]: Constructor threw exception; nested exception is com.cenqua.crucible.hibernate.CruDBException: Problem upgrading with script /Users/foong/Desktop/CustomWare/fisheye/fecru-2.9.1/sql/MYSQL/upgrade/upgrade_73.sql: SQL script error on line 3: "alter table cru_comment drop foreign key FKE5A1D106C50DAE44;" 
(Error on rename of './fecru236/cru_comment' to './fecru236/#sql2-103-82' (errno: 152)), please contact http://www.atlassian.com/support/
...
Caused by: com.cenqua.crucible.hibernate.CruDBException: Problem upgrading with script /Users/foong/Desktop/CustomWare/fisheye/fecru-2.9.1/sql/MYSQL/upgrade/upgrade_73.sql: SQL script error on line 3: "alter table cru_comment drop foreign key FKE5A1D106C50DAE44;" 
(Error on rename of './fecru236/cru_comment' to './fecru236/#sql2-103-82' (errno: 152)), please contact http://www.atlassian.com/support/
...
Caused by: com.cenqua.crucible.hibernate.CruDBException: SQL script error on line 3: "alter table cru_comment drop foreign key FKE5A1D106C50DAE44;" 
(Error on rename of './fecru236/cru_comment' to './fecru236/#sql2-103-82' (errno: 152)), please contact http://www.atlassian.com/support/
...
Caused by: java.sql.SQLException: Error on rename of './fecru236/cru_comment' to './fecru236/#sql2-103-82' (errno: 152)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)

Diagnosis

Check if the foreign key exist in the MySQL database:

USE INFORMATION_SCHEMA;
SELECT * FROM KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME='FK1C588BB9C50DAE44';

The constraint might be different depending on the version. Check the error for the constraint name.

Cause

Restoring/upgrading Fisheye for a multiple time in the same database might have deleted the foreign key previously.

Workaround

  1. Restore the database to the state before the restore/upgrade.
  2. Check if the foreign key exist in the database according to the Diagnosis section.
  3. Check the database schema of the Fisheye/Crucible before restore/upgrade at FISHEYE_HOME/sql/MYSQL/schema/constraints_*.sql.

    Refer to the largest number of the file.

  4. Add back the specific constraint to the database according to the file.
  5. Restore/upgrade Fisheye/Crucible again

Resolution

  1. Restore the database to the state before the restore/upgrade.
  2. Create a backup of the database
  3. Create a new database
  4. Restore the backup into the new database

If the problem persist, please contact Atlassian Support at https://support.atlassian.com/browse/CRC

Last modified on Jul 31, 2018

Was this helpful?

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