Migrating from HSQLDB to any database fails

Related content

  • No related content found

Still need help?

The Atlassian Community is here for you.

Ask the community

Problem

The following appears in the atlassian-fisheye-<date>.log for Oracle migration:

2012-11-13 15:01:32,595 WARN  - An error occurred from hibernate. Failing silently
org.hibernate.HibernateException: Hibernate has been shutdown
        at com.cenqua.crucible.hibernate.HibernateUtil.currentState(HibernateUtil.java:143)
        at com.cenqua.crucible.hibernate.HibernateUtil.currentSession(HibernateUtil.java:134)
        at com.atlassian.fecru.user.UserDAOImpl.session(UserDAOImpl.java:26)
...
        at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
        at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:410)
        at org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:451)
2012-11-13 15:12:38,740 ERROR - Database migration failed: com.cenqua.crucible.hibernate.CruDBException: Problem with constraints script /data/fisheye/sql/ORACLE/schema/constraints_79.sql
com.cenqua.crucible.hibernate.CruDBException: Problem with constraints script /data/fisheye/sql/ORACLE/schema/constraints_79.sql
        at com.cenqua.crucible.hibernate.DefaultDBControl.addConstraints(DefaultDBControl.java:317)
        at com.cenqua.crucible.hibernate.OracleDBControl.addConstraints(OracleDBControl.java:122)
        at com.atlassian.crucible.migration.item.DBImporter.importData(DBImporter.java:159)
...
Caused by: com.cenqua.crucible.hibernate.CruDBException: SQL script error on line 45: "alter table cru_fr_detail add constraint FK4F2200B487D2F44 foreign key (cru_revision_id) references cru_revision;" 
(ORA-02298: cannot validate (CRUCIBLE.FK4F2200B487D2F44) - parent keys not found
), please contact http://www.atlassian.com/support/
        at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:483)
        at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:414)
        at com.cenqua.crucible.hibernate.DefaultDBControl.addConstraints(DefaultDBControl.java:311)
        ... 11 more
Caused by: java.sql.SQLException: ORA-02298: cannot validate (CRUCIBLE.FK4F2200B487D2F44) - parent keys not found
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

MySQL:

2014-04-17 17:30:44,531 ERROR [ThreadPool3 ] fisheye DBEditHelper-doGet - Database migration failed: com.cenqua.crucible.hibernate.CruDBException: Problem with constraints script /opt/fecru-3.2.1/sql/MYSQL/schema/constraints_87.sql
com.cenqua.crucible.hibernate.CruDBException: Problem with constraints script /opt/fecru-3.2.1/sql/MYSQL/schema/constraints_87.sql
	at com.cenqua.crucible.hibernate.DefaultDBControl.addConstraints(DefaultDBControl.java:411)
	at com.atlassian.crucible.migration.item.DBImporter.importData(DBImporter.java:145)
	at com.atlassian.crucible.actions.admin.database.DBEditHelper$ImportRunner.call(DBEditHelper.java:90)
	at com.atlassian.crucible.actions.admin.database.DBEditHelper$ImportRunner.call(DBEditHelper.java:72)
	at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
	at java.util.concurrent.FutureTask.run(FutureTask.java:138)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:98)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:206)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
	at java.lang.Thread.run(Thread.java:695)
Caused by: com.cenqua.crucible.hibernate.CruDBException: SQL script error on line 49: "alter table cru_fr_detail add index FK4F2200B487D2F44 (cru_revision_id), add constraint FK4F2200B487D2F44 foreign key (cru_revision_id) references cru_revision (cru_revision_id);" 
(Cannot add or update a child row: a foreign key constraint fails (`fisheye`.<result 2 when explaining filename '#sql-1d80_4bc8'>, CONSTRAINT `FK4F2200B487D2F44` FOREIGN KEY (`cru_revision_id`) REFERENCES `cru_revision` (`cru_revision_id`))), please contact http://www.atlassian.com/support/
	at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:593)
	at com.cenqua.crucible.hibernate.DefaultDBControl.executeScript(DefaultDBControl.java:524)
	at com.cenqua.crucible.hibernate.DefaultDBControl.addConstraints(DefaultDBControl.java:405)
	... 10 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`fisheye`.<result 2 when explaining filename '#sql-1d80_4bc8'>, CONSTRAINT `FK4F2200B487D2F44` FOREIGN KEY (`cru_revision_id`) REFERENCES `cru_revision` (`cru_revision_id`))

Cause

There is a database integrity issue in your HSQLDB. 

Resolution

  1. Backup your Fisheye/Crucible for rollback purposes
  2. Stop Fisheye/Crucible

  3. Use createForeignKeyCleanupScript.pyto generate a script for your Fisheye/Crucible:

    python createForeignKeyCleanupScript.py VERSION FISHEYE_HOME/sql/HSQL/schema/ 

    This script is compatible with Python version 2.x only.

    That will output an sql file containing cleanup scripts for the version of fisheye in your checkout. The VERSION is the version number of the db of the customer, but its usually safe to just use the largest number in the files in FISHEYE_HOME/sql/HSQL/schema/, e.g. tables_67.sql.

  4. Run the following command:

    java -Xms512m -Xmx1024m -jar FISHEYE_HOME/lib/hsqldb-*.jar --inlineRC "URL=jdbc:hsqldb:file:FISHEYE_INST/var/data/crudb/crucible;shutdown=true,USER=sa,PASSWORD=" foreign_key_cleanup_VERSION.sql

    Replace FISHEYE_INST with the fully qualified locations (see Administration >> Systems Settings >> System Info, if you are not sure of the fully qualified paths). 

    The script will return a result such as:

    637 rows updated.
196 rows updated.

    If the script fails with an out of memory problem, retry again but raise the -Xmx value, e.g. from -Xmx1024m to -Xmx2048m.

  5. Start Fisheye/Crucible
  6. Perform the database migration again

 

If the script fails with the following message, this is because the CRU_FR_DETAIL contains a reference to the CRU_REVISION table:

Integrity constraint violation FK4F2200B487D2F44 table: CRU_FR_DETAIL
 

To resolve this, apply the following prior to run the script once again:

  • Run the query to identify the revisions that will be deleted:
	 SELECT * FROM cru_revision WHERE cru_path NOT IN (SELECT cru_path_id FROM cru_stored_path)
  • retrieve the ids from the query above and delete them with the following statement:
    DELETE FROM cru_fr_detail WHERE cru_revision_id IN (<comma separated list of ids>)
Last modified on Jul 31, 2018

Was this helpful?

Yes
No
Provide feedback about this article

Related content

  • No related content found
Powered by Confluence and Scroll Viewport.