[4.x]: Migration failed upgrading to 4.4.0

[philipboomy]

What happened?

One of Craft Commerce’s migrations failed.

Database Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY sortOrder ASC, id ASC) as rownumber
FROM wd_commerce_discounts) b' at line 2
The SQL being executed was: UPDATE wd_commerce_discounts a
JOIN (SELECT id, sortOrder, ROW_NUMBER() OVER (ORDER BY sortOrder ASC, id ASC) as rownumber
FROM wd_commerce_discounts) b ON a.id = b.id
SET a.sortOrder = b.rownumber

Migration: craft\commerce\migrations\m231220_103739_ensure_discount_sort_order

Output:

Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY sortOrder ASC, id ASC) as rownumber
FROM wd_commerce_discounts) b' at line 2
The SQL being executed was: UPDATE wd_commerce_discounts a
JOIN (SELECT id, sortOrder, ROW_NUMBER() OVER (ORDER BY sortOrder ASC, id ASC) as rownumber
FROM wd_commerce_discounts) b ON a.id = b.id
SET a.sortOrder = b.rownumber (/Users/name/Sites/website/vendor/yiisoft/yii2/db/Schema.php:676)
#0 /Users/name/Sites/website/vendor/yiisoft/yii2/db/Command.php(1307): yii\db\Schema->convertException(Object(PDOException), 'UPDATE wd_com...')
#1 /Users/name/Sites/website/vendor/yiisoft/yii2/db/Command.php(1102): yii\db\Command->internalExecute('UPDATE wd_com...')
#2 /Users/name/Sites/website/vendor/craftcms/commerce/src/services/Discounts.php(888): yii\db\Command->execute()
#3 /Users/name/Sites/website/vendor/craftcms/commerce/src/migrations/m231220_103739_ensure_discount_sort_order.php(18): craft\commerce\services\Discounts->ensureSortOrder()
#4 /Users/name/Sites/website/vendor/craftcms/cms/src/db/Migration.php(49): craft\commerce\migrations\m231220_103739_ensure_discount_sort_order->safeUp()
#5 /Users/name/Sites/website/vendor/craftcms/cms/src/db/MigrationManager.php(233): craft\db\Migration->up(true)
#6 /Users/name/Sites/website/vendor/craftcms/cms/src/db/MigrationManager.php(149): craft\db\MigrationManager->migrateUp(Object(craft\commerce\migrations\m231220_103739_ensure_discount_sort_order))
#7 /Users/name/Sites/website/vendor/craftcms/cms/src/services/Updates.php(252): craft\db\MigrationManager->up()
#8 /Users/name/Sites/website/vendor/craftcms/cms/src/controllers/BaseUpdaterController.php(493): craft\services\Updates->runMigrations(Array)
#9 /Users/name/Sites/website/vendor/craftcms/cms/src/controllers/UpdaterController.php(203): craft\controllers\BaseUpdaterController->runMigrations(Array, 'restore-db')
#10 [internal function]: craft\controllers\UpdaterController->actionMigrate()
#11 /Users/name/Sites/website/vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)
#12 /Users/name/Sites/website/vendor/yiisoft/yii2/base/Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#13 /Users/name/Sites/website/vendor/yiisoft/yii2/base/Module.php(552): yii\base\Controller->runAction('migrate', Array)
#14 /Users/name/Sites/website/vendor/craftcms/cms/src/web/Application.php(305): yii\base\Module->runAction('updater/migrate', Array)
#15 /Users/name/Sites/website/vendor/craftcms/cms/src/web/Application.php(693): craft\web\Application->runAction('updater/migrate')
#16 /Users/name/Sites/website/vendor/craftcms/cms/src/web/Application.php(262): craft\web\Application->_processUpdateLogic(Object(craft\web\Request))
#17 /Users/name/Sites/website/vendor/yiisoft/yii2/base/Application.php(384): craft\web\Application->handleRequest(Object(craft\web\Request))
#18 /Users/name/Sites/website/web/index.php(12): yii\base\Application->run()
#19 /Users/name/.composer/vendor/laravel/valet/server.php(110): require('/Users/name/Site...')
#20 {main}

Craft CMS version

4.6.0

Craft Commerce version

4.3.3

PHP version

No response

Operating system and version

No response

Database type and version

MySQL 5.7.42

Image driver and version

No response

Installed plugins and versions

[engram-design]

Running into this as well, I assume this is due to the use of OVER not being supported in MySQL < 8.

commerce/src/services/Discounts.php

Line 882 in 69ca6b9

JOIN (SELECT id, [[sortOrder]], ROW_NUMBER() OVER (ORDER BY [[sortOrder]] ASC, id ASC) as rownumber

[engram-design]

I've modified the following:

UPDATE $table a
JOIN (SELECT id, [[sortOrder]], ROW_NUMBER() OVER (ORDER BY [[sortOrder]] ASC, id ASC) as rownumber
FROM $table) b ON a.id = b.id
SET [[a.sortOrder]] = b.rownumber

to

UPDATE $table a
JOIN (
    SELECT b.id, b.sortOrder, COUNT(*) + 1 as rownumber
    FROM $table b
    JOIN $table c ON (c.sortOrder, c.id) <= (b.sortOrder, b.id)
    GROUP BY b.id, b.sortOrder
) AS b ON a.id = b.id
SET [[a.sortOrder]] = b.rownumber;

I would send a PR, but my SQL is a bit rusty, so you might want to review this first.

[nfourtythree]

Hi All

Thank you for reporting this. We have just pushed out a release (4.4.1.1) that includes a fix for this issue.

Running composer update should get everything working for you, please let us know if you have any further issues.

Thanks!