SQL duplicate entry error

[tyssen]

I installed Trending Posts about a month ago on a site and then today made a change to another template file, completely unrelated to the plugin.

I deployed the change and then afterward every page that had {% do craft.trendingPosts.increment(entry.id) %} in it was throwing this sort of error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2147483647' for key 'PRIMARY' The SQL being executed was: INSERT INTO craft_trendingpostssummary (dateCreated, dateUpdated, siteId, entryId, totalviews, uid) VALUES ('2022-03-09 10:30:36', '2022-03-09 10:30:36', 1, 124967, 570, '3797a423-d6b9-4f56-8a37-a10e1294bf91')

Removing the increment function brought the pages back.

Do you have any idea why this might be occurring?

[tyssen]

I should mention that when deployed, the following are also being run:

migrate/all
project-config/sync
clear-caches/data
clear-caches/asset
clear-caches/asset-indexing-data
clear-caches/compiled-templates
clear-caches/cp-resources
clear-caches/temp-files
clear-caches/template-caches
clear-caches/route-map
clear-caches/transform-indexes
blitz/cache/clear
blitz/cache/flush

[tyssen]

And the associated log for the latest deployment:

[Wed Mar  9 10:20:38 GMT 2022]
===== COMPOSER =====
Installing dependencies from lock file (including require-dev)
Verifying lock file contents can be installed on current platform.
Your lock file does not contain a compatible set of packages. Please run composer update.

  Problem 1
    - psr/log is locked to version 1.1.4 and an update of this package was not requested.
    - You can only install one version of a package, so only one of these can be installed: psr/log[1.1.4].

===== CRAFT MIGRATE =====
Checking for pending migrations ...
No new migrations found. Your system is up to date.
===== CRAFT PROJECT CONFIG SYNC =====
project-config/sync has been renamed to project-config/apply. Running that instead...
Applying changes from your project config files ...
- updating plugins.blitz.settings ... done
- updating dateModified ... done
Finished applying changes
===== CRAFT CLEAR CACHES =====
Clearing cache: Data caches
Clearing cache: Asset caches
Clearing cache: Asset indexing data
Clearing cache: Compiled templates
Clearing cache: Temp files
Unknown command: clear-caches/template-caches
Clearing cache: Asset transform index
===== BLITZ CLEAR CACHES =====
Blitz cache successfully cleared.
Blitz cache successfully flushed.

[daltonrooney]

Thanks for the report. I can confirm that this plugin is working with the latest Craft with no problems on a couple of sites that we manage. I wonder if this could have anything to do with Blitz? Is there any way to exclude specific functions from being cached with Blitz? Do you have a dev or staging server you can test with Blitz turned off?

[tyssen]

Sorry for the slow reply. Yes, there is a staging environment and I've just tried testing pushing some changes to it that involve disabling Blitz's cache (the plugin is still enabled, it just doesn't cache pages). Everything is working as intended on staging.

[tyssen]

Following up on this again, both staging and production environments are on the same server. With Blitz cache enabled and {% do craft.trendingPosts.increment(entry.id) %} in place in templates on both, staging renders pages fine, but on production, the same templates produce the duplicate entry error reported initially.

[daltonrooney]

@tyssen Are your staging server & production server running the same database? Are you handy with MySQL? I'm just guessing here, but I think you somehow ended up with more than one summary record for a particular entry ID 2147483647. It's possible if you delete that summary record that would prevent the error in the future.

I'm not the one who originally wrote this code (I am in no way an SQL expert) but looking more closely at it I think I see some queries and functions that aren't very efficient. I'm worried this might cause some performance problems at scale, you may want to consider a different solution for tracking content performance on your site. I'm going to check around to see if I can find someone more experienced to take a look.

[tyssen]

No, different databases. The trendingpostssumary table only has one record in it. trendingposts has 190.

[tyssen]

Hi @daltonrooney any updates on this one.