Database Migration to MySQL Fails with 'com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException'
Symptoms
Database migration from HSQLDB to MySQL fails with the following exception reported in the fisheye-debug-<date>.log:
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:
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.
Last modified on Jul 31, 2018
Powered by Confluence and Scroll Viewport.