[5.x]: Can't switch primary store currency (Unknown column 'elements.dateDeleted')

[mandrasch]

What happened?

Description

I installed craft commerce v5, played around a bit and wanted to switch my primary store currency to EUR. I deleted all inactive carts and orders, also deleted the trashed ones in order to do so (permanently). Also ran ddev craft gc.

When I try switching from USD to EUR, I get

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'elements.dateDeleted' in 'where clause'
The SQL being executed was: SELECT EXISTS(SELECT *
FROM `ri_commerce_orders`
LEFT JOIN `ri_elements` ON `elements`.`id` = `commerce_orders`.`id`
WHERE (`storeId`=1) AND (`elements`.`dateDeleted` IS NULL))
Error Info: Array
(
    [0] => 42S22
    [1] => 1054
    [2] => Unknown column 'elements.dateDeleted' in 'where clause'
)

Steps to reproduce

1. Switch USD to EUR in Commerce > System Settings > Stores > Primary
2. Hit save

Expected behavior

Save successfull

Actual behavior

MySQL exception

Craft CMS version

5.2.3

Craft Commerce version

5.0.11.1

PHP version

8.2.20

Operating system and version

Mac OSX / DDEV

Database type and version

MySQL 8.0

Image driver and version

No response

Installed plugins and versions

-

[linear[bot]]

PT-1909 [5.x]: Can't switch primary store currency (Unknown column 'elements.dateDeleted')

[mandrasch]

Maybe related to https://github.com/craftcms/commerce/issues/3433?

[lukeholder]

Thanks for reporting. This is fixed for the next release.

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

"require": {
  "craftcms/commerce": "5.x-dev#0bf59809c239e1e906b2953cd8937bfec58f9304",
  "...": "..."
}

Then run composer update.

We will update this ticket once we cut the next release.

[nfourtythree]

Hi

Commerce version 5.0.12 has been released with this update included.

Thanks!

[mandrasch]

Hi @lukeholder @nfourtythree, thanks for your responses!

I updated to 5.0.12.2, but I'm still not able to switch currencies in Shop settings:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'elements.dateDeleted' in 'where clause'
The SQL being executed was: SELECT EXISTS(SELECT *
FROM `ri_commerce_orders`
LEFT JOIN `ri_elements` ON `elements`.`id` = `commerce_orders`.`id`
WHERE (`storeId`=1) AND (`elements`.`dateDeleted` IS NULL))

Let me know if I can assist in debugging this, thanks very much!

[mandrasch]

Okay, full error log helps:

in /var/www/html/vendor/craftcms/commerce/src/models/Store.php at line 201– craft\db\Query::exists()

In release https://github.com/craftcms/commerce/commit/0bf59809c239e1e906b2953cd8937bfec58f9304 another code line was fixed (StoresController)

(I did not test the hotfix but waited for the official release of it)

[nfourtythree]

Hi @mandrasch

Looking at the screenshot you posted from the stack trace:

It looks like this is the pre-fix code, could you make sure you are up to date with the latest version of Commerce and try again.

Thanks!

[mandrasch]

Hi @nfourtythree,

as far as I see:

In https://github.com/craftcms/commerce/commit/0bf59809c239e1e906b2953cd8937bfec58f9304, the src/controllers/StoresController.php is only fixed - but the error occurs in models/Store.php at line 201 (https://github.com/craftcms/commerce/blob/5.x/src/models/Store.php#L199)?

                $hasOrders = (new Query())
                    ->from(Table::ORDERS)
                    ->leftJoin(\craft\db\Table::ELEMENTS, '[[elements.id]] = [[commerce_orders.id]]')
                    ->where([
                        'storeId' => $this->id,
                        'elements.dateDeleted' => null,
                    ])
                    ->exists();

I should be up-to-date locally:

ddev composer show
craftcms/commerce                       5.0.12.2 Craft Commerce

ddev craft project-config/diff
No pending project config YAML changes.

ddev craft migrate
Checking for pending content migrations ...
No new migrations found. Your system is up-to-date.

ddev composer dumpautoload

[nfourtythree]

Hi @mandrasch

Thanks for the update and the info. Was a strange one as I wasn't actually able to replicate that issue even with that code in place in the store model.

However, we have now applied the fix to the store model and pushed it up. This fix will be included in the next release ofr Commerce.

To get this early, change your craftcms/commerce requirement in your project's composer.json to:

"require": {
  "craftcms/commerce": "5.x-dev#164e8e8f7c22ce1b3161aa4b5d25c11b32afc93e as 5.0.12.2",
  "...": "..."
}

Then run composer update.

Thanks!

[mandrasch]

Hi @nfourtythree,

thanks very much!

Oh, interesting. 🤔

Did you try with a database prefix or without a prefix?

My setup uses a prefix: DB_TABLE_PREFIX=ri_

Other settings I had locally in .ddev/config.yaml:

php_version: "8.2"
webserver_type: nginx-fpm
database:
    type: mysql
    version: "8.0"

[mandrasch]

Regarding hotfix - I used

ddev composer require  "craftcms/commerce":"5.x-dev#164e8e8f7c22ce1b3161aa4b5d25c11b32afc93e as 5.0.12.2"
ddev composer install

Switching the currency worked without an error now (locally).

But the weird thing is, that I see three main currencies listed after deploying 🤔 (I also saw the USD from before, but could delete it)

Remote database:

Locally in DDEV - I only see EUR once.

[lukeholder]

The fixes above were released in Craft Commerce 5.0.13

If you still have a problem with that release can you please create a new issue with further details.

Thanks!