Overview
ETL jobs have started failing, with one of the following error messages appearing in the logs:
- ERROR com.jivesoftware.community.analytics.etl.IdDrivenCopyETLTask - Error running ETL: DocumentStatusETL
java.sql.BatchUpdateException: Batch entry - ERROR com.jivesoftware.community.analytics.etl.impl.ETLTaskExecutorImpl - Error executing tasks: ORA-00001: unique constraint (JIVE_ANA.JIVEDW_OS_STG_PK) violated
- ERROR com.jivesoftware.community.analytics.etl.IdDrivenCopyETLTask - org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "jivedw_os_stg_pk"
This article describes the solution for this issue, as well as a workaround.
Environment
Jive Hosted and On-Premise versions prior to 9.0.6.
Solution
This issue occurs when a unique index on the jiveDocVersion table is violated. To permanently resolve this issue, please upgrade to the latest version of Jive.
A temporary workaround can be applied by following these steps:
- Make a backup of the application database.
- Run the following queries on the application database (adjusting them based on your application database type):
- Query-1
SELECT v1.internalDocID AS id, v1.creationDate, COUNT(*)
FROM (SELECT internalDocID, MIN(versionId) AS min_v, MAX(versionId) AS max_v, creationDate, modificationDate
FROM jiveDocVersion WHERE status = 2 AND modificationDate > 0 AND internalDocID >= 0 AND internalDocID <= 10000000
GROUP BY internalDocID, creationDate, modificationDate) v1
LEFT JOIN (SELECT internalDocID, MAX(versionId) AS max_v, modificationDate
FROM jiveDocVersion WHERE status = 11 GROUP BY internalDocID, modificationDate) v2
ON(v1.internalDocID = v2.internalDocID)
GROUP BY id, creationDate
HAVING COUNT(*) > 1 - Query-2
SELECT v1.internalDocID AS id, COALESCE(v2.modificationDate, -1), COUNT(*)
FROM (SELECT internalDocID, MIN(versionId) AS min_v, MAX(versionId) AS max_v, creationDate, modificationDate
FROM jiveDocVersion WHERE status = 2 AND modificationDate > 0 AND internalDocID >= 0 AND internalDocID <= 10000000
GROUP BY internalDocID, creationDate, modificationDate) v1
LEFT JOIN (SELECT internalDocID, MAX(versionId) AS max_v, modificationDate
FROM jiveDocVersion WHERE status = 11 GROUP BY internalDocID, modificationDate) v2
ON(v1.internalDocID = v2.internalDocID)
GROUP BY id, v2.modificationDate
HAVING COUNT(*) > 1
- Query-1
- Identify which ids have multiple rows.
- For each id that has multiple rows, follow the below guidance:
- Where all results are from the same
userid
: Leave the result with the latest modification date and delete the rest. - When results return multiple statuses: Leave the result with the latest modification date and delete the rest. Alternatively, you can also delete the oldest of duplicate entries for the same status.
- If the results return multiple
userids
: Treat eachuserid
separately using the treatment from the two bullets above
- Where all results are from the same
- Start the ETL (Extract, Transform, and Load) job again.
<supportagent>
Support agents: For on-premise instances, Step 4 should only be done by the customer.
</supportagent>
Comments
0 comments
Article is closed for comments.