Database connections locked up due to foreign key constraint on table 'cru_frx'

Still need help?

The Atlassian Community is here for you.

Ask the community

Symptoms

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

2013-01-14 10:44:10,592 WARN  [btpool0-209 ] org.hibernate.util.JDBCExceptionReporter JDBCExceptionReporter-logExceptions - SQL Error: 0, SQLState: 23503
2013-01-14 10:44:10,592 ERROR [btpool0-209 ] org.hibernate.util.JDBCExceptionReporter JDBCExceptionReporter-logExceptions - Batch entry 0 delete from cru_frx where cru_frx_id='244397' was aborted.  Call getNextException to see the cause.
2013-01-14 10:44:10,593 WARN  [btpool0-209 ] org.hibernate.util.JDBCExceptionReporter JDBCExceptionReporter-logExceptions - SQL Error: 0, SQLState: 23503
2013-01-14 10:44:10,593 ERROR [btpool0-209 ] org.hibernate.util.JDBCExceptionReporter JDBCExceptionReporter-logExceptions - ERROR: update or delete on table "cru_frx" violates foreign key constraint "fkd3011b4773591d1a" on table "cru_frx_revision"
  Detail: Key (cru_frx_id)=(244397) is still referenced from table "cru_frx_revision".
2013-01-14 10:44:10,594 ERROR [btpool0-209 ] org.hibernate.event.def.AbstractFlushingEventListener AbstractFlushingEventListener-performExecutions - Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:96)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:262)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:183)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
...
Caused by: java.sql.BatchUpdateException: Batch entry 0 delete from cru_frx where cru_frx_id='244397' was aborted.  Call getNextException to see the cause.

Diagnosis

From the above error, run the following SQL commands to search for the data:

SELECT * FROM cru_frx_revision WHERE cru_frx_id=244397;
SELECT * FROM cru_frx WHERE cru_frx_id=244397;

If there is any data found in the table cru_frx_revision, then Fisheye/Crucible fail to clear the constraint.

Cause

Fisheye/Crucible fail to clear the foreign key constraint in table cru_frx_revision before deleting the data in cru_frx.

Resolution

Backup Fisheye/Crucible for rollback purposes

  1. Stop Fisheye/Crucible
  2. Run the following SQL commands based on the error found:

    DELETE FROM cru_frx_revision WHERE cru_frx_id=244397;
    DELETE FROM cru_frx WHERE cru_frx_id=244397; 
  3. Start Fisheye/Crucible
Last modified on Nov 2, 2018

Was this helpful?

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