SQLIntegrityConstraintViolationException: herddb.model.DuplicatePrimaryKeyException

[hamadodene]

Sometimes I see this error in the logs:

Sep 18, 2025 9:16:59 AM org.carapaceproxy.cluster.impl.ZooKeeperGroupMembershipHandler executeInMutex
SEVERE: Failed to acquire lock for executeInMutex (mutexId: dynamic-certificates-manager, peerId: carapace1)
org.carapaceproxy.configstore.ConfigurationStoreException: java.sql.SQLIntegrityConstraintViolationException: java.sql.SQLIntegrityConstraintViolationException: herddb.model.DuplicatePrimaryKeyException: key 3259464e734d427a645a35796c3672416e7253477762345a4d737537474a65444752494770465a4b5f366f, decoded as 2YFNsMBzdZ5yl6rAnrSGwb4ZMsu7GJeDGRIGpFZK_6o, already exists in table acme_challenge_tokens
        at org.carapaceproxy.configstore.HerdDBConfigurationStore.saveAcmeChallengeToken(HerdDBConfigurationStore.java:527)
        at org.carapaceproxy.server.certificates.DynamicCertificatesManager.createOrderAndChallengesForCertificate(DynamicCertificatesManager.java:454)
        at org.carapaceproxy.server.certificates.DynamicCertificatesManager.startCertificateProcessing(DynamicCertificatesManager.java:399)
        at org.carapaceproxy.server.certificates.DynamicCertificatesManager.certificatesLifecycle(DynamicCertificatesManager.java:298)
        at org.carapaceproxy.cluster.impl.ZooKeeperGroupMembershipHandler.executeInMutex(ZooKeeperGroupMembershipHandler.java:299)
        at org.carapaceproxy.server.certificates.DynamicCertificatesManager.run(DynamicCertificatesManager.java:279)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
        at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358)
        at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
        at java.base/java.lang.Thread.run(Thread.java:1583)
Caused by: java.sql.SQLIntegrityConstraintViolationException: java.sql.SQLIntegrityConstraintViolationException: herddb.model.DuplicatePrimaryKeyException: key 3259464e734d427a645a35796c3672416e7253477762345a4d737537474a65444752494770465a4b5f366f, decoded as 2YFNsMBzdZ5yl6rAnrSGwb4ZMsu7GJeDGRIGpFZK_6o, already exists in table acme_challenge_tokens
        at herddb.jdbc.utils.SQLExceptionUtils.wrapException(SQLExceptionUtils.java:42)
        at herddb.jdbc.HerdDBPreparedStatement.doExecuteLargeUpdateWithParameters(HerdDBPreparedStatement.java:571)
        at herddb.jdbc.HerdDBPreparedStatement.executeLargeUpdate(HerdDBPreparedStatement.java:558)
        at herddb.jdbc.HerdDBPreparedStatement.executeUpdate(HerdDBPreparedStatement.java:547)
        at org.carapaceproxy.configstore.HerdDBConfigurationStore.saveAcmeChallengeToken(HerdDBConfigurationStore.java:524)
        ... 11 more

It seems that some records in the acme_challenge_tokens table are not deleted after a certificate is renewed or created.

The workaround that works for me is to manually delete the corresponding key from the table

[NiccoMlt]

The call chain starts in DynamicCertificatesManager, inside createOrderAndChallengesForCertificate:

store.saveAcmeChallengeToken(http01Challenge.getToken(), http01Challenge.getAuthorization());

In HerdDBConfigurationStore.saveAcmeChallengeToken, the INSERT uses no upsert logic (unlike saveCertificate which tries UPDATE first, then INSERT). If the token ID already exists, the INSERT fails with a SQLException, which gets wrapped and rethrown as a ConfigurationStoreException (unchecked).

createOrderAndChallengesForCertificate is called from startCertificateProcessing, which is called from certificatesLifecycle. The catch block in certificatesLifecycle is:

} catch (AcmeException | IOException | GeneralSecurityException | IllegalStateException ex) {
    LOG.log(Level.SEVERE, "Error while handling dynamic certificate for domain " + domain, ex);
}

ConfigurationStoreException extends RuntimeException, so it's not caught by any of those listed exception types. It propagates out of certificatesLifecycle, which is called via:

groupMembershipHandler.executeInMutex(THREAD_NAME, period > 0 ? period : 1, this::certificatesLifecycle);

Since saveAcmeChallengeToken is a store operation that can fail on duplicate keys, HerdDBConfigurationStore should implement an upsert (try UPDATE first, then INSERT), similar to how saveCertificate already works, so that retrying a token doesn't throw at all.