Integrity constraint violation: 1062 Duplicate entry '1171043176' for key 'craft_blitz_elementqueries.craft_idx_wyjzxruzvkdhftefeorcyczbllfajyidnqjc'

[WHITE-developer]

Hi,
We get a lot of the error message in the blitz log file.
We are on the latest craft and blitz version.
Good to know that we are running a split db setup (1 master + 1 read)

Full error:

[2023-07-12 16:50:52] SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1171043176' for key 'craft_blitz_elementqueries.craft_idx_wyjzxruzvkdhftefeorcyczbllfajyidnqjc' The SQL being executed was: INSERT INTO `craft_blitz_elementqueries` (`index`, `type`, `params`) VALUES (1171043176, 'craft\\commerce\\elements\\Variant', '{\"productId\":254608,\"siteId\":1}')

Blitz settings:

blitz:
    edition: standard
    enabled: true
    licenseKey: 
    schemaVersion: 4.4.0
    settings:
      apiKey: ''
      cacheControlHeader: 'public, s-maxage=86400, max-age=0'
      cacheDuration: 86400
      cacheElementQueries: true
      cacheElements: true
      cacheGeneratorSettings:
        __assoc__:
          -
            - concurrency
            - '1'
      cacheGeneratorType: putyourlightson\blitz\drivers\generators\HttpGenerator
      cacheNonHtmlResponses: false
      cachePurgerType: putyourlightson\blitz\drivers\purgers\DummyPurger
      cacheStorageSettings:
        __assoc__:
          -
            - folderPath
            - '@webroot/cache/blitz'
          -
            - createGzipFiles
            - ''
          -
            - createBrotliFiles
            - ''
      cacheStorageType: putyourlightson\blitz\drivers\storage\FileStorage
      cachingEnabled: false
      debug: true
      deployerType: putyourlightson\blitz\drivers\deployers\DummyDeployer
      driverJobPriority: 100
      esiEnabled: false
      excludedQueryStringParams:
        -
          __assoc__:
            -
              - queryStringParam
              - gclid
        -
          __assoc__:
            -
              - queryStringParam
              - fbclid
      generatePagesWithQueryStringParams: true
      hintsEnabled: true
      includedQueryStringParams:
        -
          __assoc__:
            -
              - queryStringParam
              - '.*'
      includedUriPatterns:
        -
          __assoc__:
            -
              - uriPattern
              - '.*'
      injectScriptEvent: DOMContentLoaded
      integrations:
        - putyourlightson\blitz\drivers\integrations\FeedMeIntegration
        - putyourlightson\blitz\drivers\integrations\SeomaticIntegration
      maxRetryAttempts: 10
      mutexTimeout: 1
      outputComments: true
      purgeAssetImagesWhenChanged: true
      queryStringCaching: 2
      queueJobTtr: 3600
      refreshCacheAutomaticallyForGlobals: true
      refreshCacheJobPriority: 110
      refreshCacheWhenElementMovedInStructure: true
      refreshCacheWhenElementSavedNotLive: false
      refreshCacheWhenElementSavedUnchanged: false
      refreshMode: 3
      sendPoweredByHeader: false
      ssiEnabled: true
      trackElementQueries: true
      trackElements: true

[bencroker]

Blitz must acquire a mutex lock before it can write to the craft_blitz_elementqueries table, which is intended to prevent this situation from happening.

Is the site running in a load-balanced environment? If it is then you should probably switch to a different driver since Craft uses a file-based mutex driver by default, see https://craftcms.com/docs/4.x/config/app.html#mutex

[lukas-jansen]

The server itself is not load balanced, only the database is splitted

[bencroker]

How have you configured which database Craft should read versus write to?

[lukas-jansen]

'db' => function() {
                $config = App::dbConfig();

                if (App::parseBooleanEnv('$DB_REPLICA_ENABLED') ?? false) {
                    $config['replicaConfig'] = [
                        'username' => App::env('CRAFT_DB_USER'),
                        'password' => App::env('CRAFT_DB_PASSWORD'),
                        'tablePrefix' => App::env('CRAFT_DB_TABLE_PREFIX'),
                        'attributes' => [
                            PDO::ATTR_TIMEOUT => 10,
                        ],
                        'charset' => 'utf8'
                    ];

                    $config['replicas'] = [
                        ['dsn' => App::env('CRAFT_DB_READ_DSN_1')],
                    ];
                }

                return Craft::createObject($config);
            }

This inside the app.php

[bencroker]

Thanks. This is likely being caused by a slight delay in the time it takes to update the replica after the primary DB is updated. I’ve fixed it in 8f25718, which you can test by running composer require putyourlightson/craft-blitz:dev-develop as 4.4.7.

[elivz]

I was having the same issue (with a read-replica DB setup) and switching to the develop branch seems to have resolved it. 🎉

[bencroker]

Glad to hear it, should be able to release this early next week.

[bencroker]

Released in version 4.4.7.