Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-29137

Fix unique constraint error in SQL database logs when initializing a batch for upload

    Details

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

      Description

      Steps to reproduce

      1. set Nuxeo to use a SQL database (PostgreSQL for example)
      2. call this command
         curl -s -k -u Administrator:Administrator -X POST http://localhost:8080/nuxeo/api/v1/upload
      3. it returns a batch
      4. 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.

        Attachments

          Activity

            People

            • Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 4 hours
                4h

                  PagerDuty

                  Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.