Integrity constraint violation

[roberttolton]

Hi,

I'm seeing a few more of these popup:

Integrity constraint violation: 1062 Duplicate entry '2963-13989' for key 'craft_blitz_elementcaches_cacheId_elementId_unq_idx'
The SQL being executed was: INSERT INTO `craft_blitz_elementcaches` (`cacheId`, `elementId`) VALUES (2963, 13989), (2963, 8190), (2963, 9937), (2963, 13990), (2963, 5510), (2963, 5512), (2963, 5513), (2963, 5514), (2963, 5515), (2963, 5516), (2963, 5518), (2963, 5519), (2963, 5520), (2963, 5521), (2963, 5522), (2963, 5527), (2963, 8828), (2963, 5523), (2963, 5528), (2963, 5530), (2963, 5531), (2963, 5532), (2963, 5535), (2963, 5536), (2963, 5537), (2963, 5538), (2963, 5539), (2963, 5540), (2963, 5541), (2963, 5542), (2963, 5543), (2963, 5545), (2963, 5547), (2963, 5550), (2963, 6992), (2963, 5546), (2963, 5551), (2963, 5549), (2963, 5552), (2963, 5553), (2963, 5554), (2963, 5555), (2963, 5556), (2963, 5557), (2963, 5558), (2963, 5559), (2963, 5560), (2963, 5561), (2963, 5562), (2963, 5563), (2963, 5210), (2963, 5209), (2963, 14037), (2963, 14038), (2963, 14036), (2963, 14039), (2963, 14032), (2963, 14033), (2963, 14034), (2963, 14031), (2963, 1029), (2963, 1030), (2963, 1031), (2963, 1032), (2963, 1026), (2963, 1027), (2963, 1028), (2963, 1021), (2963, 1022), (2963, 1023), (2963, 1024), (2963, 1025) 

Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`smartdoll_live`.`craft_blitz_elementquerycaches`, CONSTRAINT `craft_blitz_elementquerycaches_queryId_fk` FOREIGN KEY (`queryId`) REFERENCES `craft_blitz_elementqueries` (`id`) ON DELETE CASCA)
The SQL being executed was: INSERT INTO `craft_blitz_elementquerycaches` (`cacheId`, `queryId`) VALUES (2784, 3644), (2784, 3647), (2784, 3648), (2784, 3649), (2784, 3650), (2784, 3651), (2784, 3652), (2784, 3653), (2784, 3654), (2784, 3655), (2784, 3656), (2784, 3657), (2784, 3723), (2784, 3724), (2784, 4520), (2784, 3719), (2784, 3720), (2784, 3721), (2784, 3722) 

It happens for posts and other pages such as a posts listing. Usually right after, or when the cache is meant to be cleared. Any other information I can give to help?

-EDIT-

So I can randomly reproduce an issue which might be caused by the above once it occurs, and that's any edits I make to a post don't break the cache. I have to clear the cache manually, and then the cache-break-on-save works again until the next time it doesn't.

When the issue occurs, the row that represents the page in question is no longer in the craft_blitz_caches table, indicating it has yet to be cached. However, the static file still remains on disk - so going to the post in the browser loads up this file and not one with the changes. If I rename, move or delete this static file and visit the post, the changes show-up, a row is inserted and a new file is written to disk.

So perhaps it's the cache-warming process that's going wrong? But I'm not getting any errors, and it's really intermittent. I'm not sure if which the duration between hitting 'Save' on the post and trying to view it online makes a difference.

-EDIT-

Ok, so it seems to indeed be the Integrity constraint violation: 1062 Duplicate entry error which causes an Error 500 status on my front-end for that single request - it's just a matter of whether I got to the error first or the warming job did. Looking at my BugSnag reports, I can see the times that the warmer hit the error:

userAgent
Craft/3.0.40 GuzzleHttp/6.3.3 curl/7.58.0 PHP/7.2.15-0ubuntu0.18.04.1

And when I hit the error:

userAgent
Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_3) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/73.0.3683.75 Safari/537.36

Considering that it results in an Error 500 I'm figuring the warming job doesn't finish properly.

[putyourlightson]

Strangely I cannot replicate this and no other reports of it have come in. Are you using MySQL or Pestgres? Can you try uninstalling the plugin and installing it again to ensure that the tables are recreated with the correct cascading rules in place and retest?

[putyourlightson]

I also added some code to help prevent this in future in version 2.0.2.

[roberttolton]

It's MySQL. I haven't tried re-installing yet, but do you have an ETA on 2.0.2?

[putyourlightson]

2.0.3 is already available.

[raptureaid]

This seems to match the issues I have been troubleshooting, as documented in #69, and can confirm similar Integrity constraint violation: 1062 Duplicate entry messages and failure to break the cache. This is on version 1.11.5 - we're hoping to do an upgrade of Craft to 3.1+ and Blitz in early April and am hopeful that this issue will be resolved.

@roberttolton, curious to know if this has been fixed with the updates?

[roberttolton]

I forgot to mention I'm only on Craft 3.0.x and so my latest available version is 1.11.5.

[putyourlightson]

If you are still on Blitz 1 and this issue is still cropping up then you can check if the relationships are set up correctly in the database.

• There should be a foreign called blitz_caches_siteId_fk in the blitz_caches table.
• There should be a foreign called blitz_elementcaches_cacheId_fk and one called blitz_elementcaches_elementId_fkin the blitz_elementcaches table.

If any of these relationships do not exist then I would recommend uninstalling and reinstalling Blitz to ensure that the tables have the correct relationships in place.