MySQL SQL script error - drop foreign key
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
Symptom 1
Restoring a backup into an existing database with the force option fails with the following error:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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:
1
2
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.
Solution
Workaround
Restore the database to the state before the restore/upgrade.
Check if the foreign key exist in the database according to the Diagnosis section.
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.
Add back the specific constraint to the database according to the file.
Restore/upgrade Fisheye/Crucible again
Resolution
Restore the database to the state before the restore/upgrade.
Create a backup of the database
Create a new database
Restore the backup into the new database
If the problem persist, please contact Atlassian Support at https://support.atlassian.com/browse/CRC
Was this helpful?