[3.x]: Database exception when saving new primary site

[jmauzyk]

What happened?

Description

Getting the database exception below (edited for brevity) when trying to set new site as primary. I believe this is due to a large number of Commerce orders (currently over 60,000).

SQLSTATE[HY000]: General error: 7 number of parameters must be between 0 and 65535
The SQL being executed was: DELETE FROM "elements_sites" WHERE ("elementId" IN (10982, 10979 ... 1019656, 1019698)) AND (NOT ("siteId"=1))

Steps to reproduce

1. From Settings > Sites, click a site that isn't currently primary.
2. In the site edit page, enable the Make this the primary site setting.
3. Click Save.

Expected behavior

Site should be saved as new primary site.

Actual behavior

Internal server error occurs and database exception is logged. After trying to reload the page or navigate elsewhere, you're redirected to the CP login page. Attempts to log in return error "Invalid email or password."

Craft CMS version

Pro 3.7.44

PHP version

7.4.28

Operating system and version

Linux 4.15.0-65-generic

Database type and version

PostgreSQL 14.2

Image driver and version

Imagick 3.4.4 (ImageMagick 6.9.7-4)

Installed plugins and versions

Asset Usage 2.2.1
AsyncQueue 2.3.0
Back In Stock 1.4.0
Braintree for Craft Commerce 2.4.3
Craft Commerce 3.4.15
Feed Me 4.5.3
Formie 1.6.0
Gift Voucher 2.7.1
Purchase Patterns 1.1.5
Redactor 2.10.8
Retour 3.1.72
SEOmatic 3.4.31
ShipStation Connect 1.3.7
Sprout Encode Email 2.0.7
SuperSort 3.1.0
TaxJar 1.0.5.3
Videos 2.1.0
Wordsmith 3.3.2

[brandonkelly]

Thanks for reporting this. It’s now fixed for the next Craft 3 and 4 releases.

To get the fix early for Craft 3, change your craftcms/cms requirement in composer.json to "v3.x-dev as 3.7.44" and run composer update.

[brandonkelly]

Craft 3.7.45 and 4.0.5 are out now with that fix.