[5.x]: Generating catalog prices fails when using database table prefix

wanze commented 4 months ago

Description

The job CatalogPricing was not able to complete on one of our projects. Turns out the SQL queries executed at the step "Copying base prices to catalog pricing" in craft\commerce\services\CatalogPricing::generateCatalogPrices do not respect the configured table prefix via CRAFT_DB_TABLE_PREFIX.

Craft::$app->getDb()->createCommand()->setSql('
    INSERT INTO [[commerce_catalogpricing]] ([[price]], [[purchasableId]], [[storeId]], [[uid]], [[dateCreated]], [[dateUpdated]])
    SELECT [[basePrice]], [[purchasableId]], [[storeId]], ' . $uuidFunction . ', NOW(), NOW() FROM [[commerce_purchasables_stores]]
    WHERE [[purchasableId]] IN (' . implode(',', $purchasableIdsChunk) . ')
')->execute();

Craft::$app->getDb()->createCommand()->setSql('
    INSERT INTO [[commerce_catalogpricing]] ([[price]], [[purchasableId]], [[storeId]], [[isPromotionalPrice]], [[uid]], [[dateCreated]], [[dateUpdated]])
    SELECT [[basePromotionalPrice]], [[purchasableId]], [[storeId]], true, ' . $uuidFunction . ', NOW(), NOW() FROM [[commerce_purchasables_stores]]
    WHERE (NOT ([[basePromotionalPrice]] is null)) AND [[purchasableId]] IN (' . implode(',', $purchasableIdsChunk) . ')
')->execute();

The exception message:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.commerce_catalogpricing' doesn't exist
The SQL being executed was: 
    INSERT INTO `commerce_catalogpricing` (`price`, `purchasableId`, `storeId`, `uid`, `dateCreated`, `dateUpdated`)
    SELECT `basePrice`, `purchasableId`, `storeId`, UUID(), NOW(), NOW() FROM `commerce_purchasables_stores`
    WHERE `purchasableId` IN (2844453)

Steps to reproduce

Install Craft with a custom table prefix (env var CRAFT_DB_TABLE_PREFIX)
Create a product / variant and note that the job hangs at "40% - Clearing existing catalog prices"

Expected behavior

Job is able to complete with table prefixes :)

Craft CMS version

5.1.4

Craft Commerce version

5.0.6

PHP version

8.3

Operating system and version

No response

Database type and version

No response

Image driver and version

No response

Installed plugins and versions

-

linear[bot] commented 4 months ago

PT-1752 [5.x]: Generating catalog prices fails when using database table prefix

lukeholder commented 4 months ago

Thanks for reporting this. We have fixed it for the next release.

To get the fix early, change your craftcms/commerce requirement in composer.json to:

"require": {
  "craftcms/commerce": "5.x-dev#1c551d521e40d2775fcc728763e765adf4d5efeb as 5.0.6",
  "...": "..."
}

Then run composer update.

We will update this ticket when the release is out.

lukeholder commented 4 months ago

5.0.7 is now out and fixes this issue. Thanks!

craftcms / commerce