要約

Crucible reports in the logs a database corruption when trying to insert review data into a table because the unique constraint uk_crurevision_spr has been violated:

2021-09-27 16:09:15,088 WARN  [qtp800735172-5979 nSeparateTransaction] org.hibernate.engine.jdbc.spi.SqlExceptionHelper SqlExceptionHelper-logExceptions - SQL Error: 0, SQLState: 23505
2021-09-27 16:09:15,089 ERROR [qtp800735172-5979 nSeparateTransaction] org.hibernate.engine.jdbc.spi.SqlExceptionHelper SqlExceptionHelper-logExceptions - ERROR: duplicate key value violates unique constraint "uk_crurevision_spr"
      Detail: Key (cru_source_name, cru_path, cru_revision)=(theRepoName, 139513, 4fdd51f7ad3eacbe392ad6cc31e82664adc300d7) already exists.
2021-09-27 16:09:15,089 WARN  [qtp800735172-5979 nSeparateTransaction] fisheye HibernateUtil-withRetriableTransaction - Caught ConstraintViolationException while executing:CrucibleRevisionCreator.createRevisionsInSeparateTransaction. Going to retry 0 more times
2021-09-27 16:09:15,090 ERROR [qtp800735172-5979 nSeparateTransaction] fisheye HibernateUtil-withRetriableTransaction - Unable to execute CrucibleRevisionCreator.createRevisionsInSeparateTransaction after 10 retries. Giving up
org.hibernate.exception.ConstraintViolationException: could not execute statement
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "uk_crurevision_spr"
      Detail: Key (cru_source_name, cru_path, cru_revision)=(theRepoName, 139513, 4fdd51f7ad3eacbe392ad6cc31e82664adc300d7) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) [postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) [postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) [postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) [postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) [postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153) [postgresql-42.2.14.jar:42.2.14]
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:119) [postgresql-42.2.14.jar:42.2.14]
	at com.jolbox.bonecp.PreparedStatementHandle.executeUpdate(PreparedStatementHandle.java:203) [bonecp-0.7.1.RELEASE.jar:0.7.1.RELEASE]
	at sun.reflect.GeneratedMethodAccessor334.invoke(Unknown Source) [?:?]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [?:1.8.0_265]
	at java.lang.reflect.Method.invoke(Method.java:498) [?:1.8.0_265]
	at com.cenqua.crucible.hibernate.trackingstatements.TrackingStatementFactory$1.handleInvocation(TrackingStatementFactory.java:25) [fisheye.jar:?]
	at com.google.common.reflect.AbstractInvocationHandler.invoke(AbstractInvocationHandler.java:87) [guava-18.0.jar:?]
	at com.sun.proxy.$Proxy86.executeUpdate(Unknown Source) [?:?]
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:186) [hibernate-core-4.2.13.Final.jar:4.2.13.Final]
	... 244 more

As can be seen above, the data Crucible tried to insert already exists in the database somehow.

環境

Crucible 4.8.6

診断

Connect to the database used by Crucible and verify that the data is already present. Following the example values showed in the log above, this SQL query should retrieve at least one result:

SELECT * FROM cru_revision WHERE cru_source_name = 'theRepoName' AND cru_path = 139513 AND cru_revision = '4fdd51f7ad3eacbe392ad6cc31e82664adc300d7'

Please remember to adjust the column values in the SQL query above according to your use case.

This problem may happen in both old and newly created reviews.

Running the review re-index process does not help.

原因

We've seen this happening when two Crucible instances (one of version 4.7.3 and the other of version 4.8.6), even though each one running on a different server, were using the very same database schema somehow. The 4.8.x instance has upgraded the database schema so that it matched the expectations of the Crucible 4.8.x version and somehow the old Crucible version was still writing data into that database it was no longer compatible with because that old instance was never stopped / started after the database schema was upgraded. It it were stopped, it would not be able to start because it validates the database schema version on startup.

ソリューション

It's unpredictable how much damaged the database is at this point, specially because it also depends on how long the two instances (and of different versions each, to make it worse) stayed up and running and connecting to the very same database schema. This database corruption may not be the only one. Therefore:

• The recommended solution is to restore the most recent healthy backup from the older instance, that is, restore the last backup from the old instance created before the new instance started connecting to it.
  Yes, this will mean losing all data produced in Crucible reviews and all instance configuration changes performed after the backup, however it is the only way to guarantee that the database is totally consistent and that this issue won't ever happen again.
  Yes, this will also mean that you will need to manually recreate / edit the reviews and perform all the instance configuration changes that will be missing after restoring the backup.
  The earlier the latest healthy backup is restored, the smaller the delta between the backup's restore date and now will be.

• If there is no healthy backup or you can't afford losing data, the SQL queries below will delete the existing entries from the database (mentioned in the log stack trace) in order to clean that specific database corruption only, which will allow Crucible to insert those entries the next time it needs to do so:

  -- CRU_REVISION x CRU_UPLOAD_ITEM
  DELETE FROM cru_upload_item, var WHERE cru_upload_id = (SELECT cru_upload_item FROM cru_revision WHERE cru_source_name = 'sourceName' AND cru_path = pathid AND cru_revision = 'revid');
  
  -- CRU_REVISION x CRU_FEINDEX_MSG
  DELETE FROM cru_feindex_msg, var WHERE cru_fr_id = (SELECT cru_revision_id FROM cru_revision WHERE cru_source_name = 'sourceName' AND cru_path = pathid AND cru_revision = 'revid');
  
  -- CRU_REVISION x CRU_FR_DETAIL
  DELETE FROM cru_fr_detail, var WHERE cru_revision_id = (SELECT cru_revision_id FROM cru_revision WHERE cru_source_name = 'sourceName' AND cru_path = pathid AND cru_revision = 'revid');
  
  -- CRU_FRX_REVISION x CRU_INLINE_COMMENT_TO_FRX_REV
  DELETE FROM cru_inline_comment_to_frx_rev, var WHERE cru_frx_rev_id = (SELECT cru_frx_rev_id FROM cru_frx_revision WHERE cru_revision = (SELECT cru_revision_id FROM cru_revision WHERE cru_source_name = 'sourceName' AND cru_path = pathid AND cru_revision = 'revid'));
  
  -- CRU_REVISION x CRU_FRX_REVISION
  DELETE FROM cru_frx_revision, var WHERE cru_revision = (SELECT cru_revision_id FROM cru_revision WHERE cru_source_name = 'sourceName' AND cru_path = pathid AND cru_revision = 'revid');
  
  -- CRU_REVISION x CRU_PATCH_REVISION
  DELETE FROM cru_patch_revision, var WHERE cru_revision_id = (SELECT cru_revision_id FROM cru_revision WHERE cru_source_name = 'sourceName' AND cru_path = pathid AND cru_revision = 'revid');
  
  -- CRU_REVISION
  DELETE FROM cru_revision, var WHERE cru_source_name = 'sourceName' AND cru_path = pathid AND cru_revision = 'revid';
  
  -- CRU_REVISION x CRU_STORED_PATH
  DELETE FROM cru_stored_path, var WHERE cru_path_id = (SELECT cru_path FROM cru_revision WHERE cru_source_name = 'sourceName' AND cru_path = pathid AND cru_revision = 'revid'); 
  

  
  Just replace sourceName, pathid and revid in the queries above by their corresponding cru_source_name, cru_path and cru_revision, respectively, mentioned in the error messages.