Database connections locked up due to foreign key constraint on table 'cru_frx'
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
- Stop Fisheye/Crucible
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;
- Start Fisheye/Crucible
Last modified on Nov 2, 2018
Powered by Confluence and Scroll Viewport.