Database Migration to MySQL Fails with 'com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException'
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
Symptoms
Database migration from HSQLDB to MySQL fails with the following exception reported in the fisheye-debug-<date>.log:
1
2
3
4
5
6
7
8
2012-08-21 15:18:22,357 ERROR [ThreadPool1 ] fisheye.app DBEditHelper-doGet - Database migration failed: com.cenqua.crucible.hibernate.CruDBException: Problem with constraints script /Users/ganand/fecru_installs/fecru-2.8.0/sql/MYSQL/schema/constraints_78.sql
com.cenqua.crucible.hibernate.CruDBException: Problem with constraints script /Users/ganand/fecru_installs/fecru-2.8.0/sql/MYSQL/schema/constraints_78.sql
Caused by: com.cenqua.crucible.hibernate.CruDBException: SQL script error on line 129: "alter table cru_user_profile add index FK4498CEEEA697BC70 (cru_user_id), add constraint FK4498CEEEA697BC70 foreign key (cru_user_id) references cru_user (cru_user_id);"
(Cannot add or update a child row: a foreign key constraint fails (`fisheye`.`#sql-b2_b`, CONSTRAINT `FK4498CEEEA697BC70` FOREIGN KEY (`cru_user_id`) REFERENCES `cru_user` (`cru_user_id`))), please contact http://www.atlassian.com/support/
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`fisheye`.`#sql-b2_b`, CONSTRAINT `FK4498CEEEA697BC70` FOREIGN KEY (`cru_user_id`) REFERENCES `cru_user` (`cru_user_id`))
Cause
The Fisheye/Crucible database has orphaned records, which is preventing the respective tables to be updated during database migration. This is normally experienced when migrated from the embedded HSQLDB.
Resolution
Ensure a complete backup of Fisheye/Crucible database has been taken prior to proceeding further. This will help revert the changes if the following steps fail.
Shut down the application and run the following queries on Fisheye/Crucible database:
1
2
select * from cru_user_profile where CRU_user_id not in (select CRU_user_id from CRU_user);
delete from cru_user_profile where CRU_user_id not in (select CRU_user_id from CRU_user);
Then restart Fisheye and attempt the database migration again on a fresh MySQL database.
Was this helpful?