[NXP-29137] Fix unique constraint error in SQL database logs when initializing a batch for upload - Nuxeo Issue Tracker

XML

Word

Printable

Details

Type: Improvement
Status: Resolved
Priority: Minor
Resolution: Fixed
Affects Version/s: 10.10
Fix Version/s: 10.10-HF27, 11.1, 2021.0
Component/s: Key/Value Store

Release Notes Summary:
Transient store unique constraint error in SQL database is handled.
Tags:
- SupCom
Backlog priority:
750
Team:
FG

Description

Steps to reproduce

set Nuxeo to use a SQL database (PostgreSQL for example)

call this command

 curl -s -k -u Administrator:Administrator -X POST http://localhost:8080/nuxeo/api/v1/upload

it returns a batch

observe this message in PostgreSQL logs

2020-05-20 19:16:38.286 UTC [683] ERROR:  duplicate key value violates unique constraint "kv_transient_batchmanagercache_pk"
2020-05-20 19:16:38.286 UTC [683] DETAIL:  Key (key)=(batchId-00ff09f2-2577-4df0-9c37-af09e391df0c.completed) already exists.
2020-05-20 19:16:38.286 UTC [683] STATEMENT:  INSERT INTO "kv_transient_batchmanagercache"("key", "long", "string", "bytes", "ttl") VALUES ($1, $2, $3, $4, $5)

If JDBC logs are enabled in Nuxeo, you'll have this trace where there are 2 INSERT INTO queries for the "completed" key

2020-05-20T19:42:14,164 TRACE [pool-3-thread-14] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL: INSERT INTO "kv_transient_batchmanagercache" ("key", "long", "string", "bytes", "ttl") VALUES ('batchId-d004af94-dcc2-49e4-aeaa-7fb217ea3012.completed', NULL, 'false', NULL, 1590010934) ON CONFLICT ("key") DO UPDATE SET "long" = NULL, "string" = 'false', "bytes" = NULL, "ttl" = 1590010934
2020-05-20T19:42:14,167 TRACE [pool-3-thread-14] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL: INSERT INTO "kv_transient_batchmanagercache" ("key", "long", "string", "bytes", "ttl") VALUES ('batchId-d004af94-dcc2-49e4-aeaa-7fb217ea3012.param.handler', NULL, 'default', NULL, 1590010934) ON CONFLICT ("key") DO UPDATE SET "long" = NULL, "string" = 'default', "bytes" = NULL, "ttl" = 1590010934
2020-05-20T19:42:14,170 TRACE [pool-3-thread-14] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL: DELETE FROM "kv_transient_batchmanagercache" WHERE "key" = 'batchId-d004af94-dcc2-49e4-aeaa-7fb217ea3012.param.handler__format'
2020-05-20T19:42:14,171 TRACE [pool-3-thread-15] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL: SELECT "long", "string", "bytes" FROM "kv_transient_batchmanagercache" WHERE "key" = 'batchId-d004af94-dcc2-49e4-aeaa-7fb217ea3012.paraminfo'
2020-05-20T19:42:14,172 TRACE [pool-3-thread-15] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL:   -> null
2020-05-20T19:42:14,172 TRACE [pool-3-thread-15] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL: INSERT INTO "kv_transient_batchmanagercache"("key", "long", "string", "bytes", "ttl") VALUES ('batchId-d004af94-dcc2-49e4-aeaa-7fb217ea3012.paraminfo', NULL, '["handler"]', NULL, 1590010934)
2020-05-20T19:42:14,175 TRACE [pool-3-thread-15] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL:   -> SET
2020-05-20T19:42:14,177 TRACE [pool-3-thread-14] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL: INSERT INTO "kv_transient_batchmanagercache"("key", "long", "string", "bytes", "ttl") VALUES ('batchId-d004af94-dcc2-49e4-aeaa-7fb217ea3012.completed', NULL, 'false', NULL, 1590010934)
2020-05-20T19:42:14,178 TRACE [pool-3-thread-14] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL:   -> FAILED
2020-05-20T19:42:14,678 TRACE [Nuxeo-Expire-KeyValueStore-migration] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (migration) SQL: DELETE FROM "kv_migration" WHERE "ttl" < 1589996534
2020-05-20T19:42:16,149 TRACE [Nuxeo-Expire-KeyValueStore-transient_BatchManagerCache] [org.nuxeo.ecm.core.storage.sql.jdbc.JDBCLogger] (transient_BatchManagerCache) SQL: DELETE FROM "kv_transient_batchmanagercache" WHERE "ttl" < 1589996536

Note that the first INSERT INTO contains a clause to manage the conflict, but it is the first query to be run ...

However, my first observation shows that this error has no impact on Nuxeo.