Commerce: Deleting a gateway doesn't remove its name from the unique index

[lazlo-bonin]

Description

Deleting a gateway doesn't remove its name from the unique index.
After trying to set another gateway to the name of that which was deleted, the following error occurs:
(For example with the 'PayPal' name):

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'PayPal' for key 'commerce_gateways_name_unq_idx' in /vendor/yiisoft/yii2/db/Command.php:1258
Stack trace:

0 /vendor/yiisoft/yii2/db/Command.php(1258): PDOStatement->execute()

1 /vendor/yiisoft/yii2/db/Command.php(1075): yii\db\Command->internalExecute('UPDATE `commerc...')

2 /vendor/yiisoft/yii2/db/ActiveRecord.php(281): yii\db\Command->execute()

3 /vendor/yiisoft/yii2/db/BaseActiveRecord.php(802): yii\db\ActiveRecord::updateAll(Array, Array)

4 /vendor/yiisoft/yii2/db/ActiveRecord.php(625): yii\db\BaseActiveRecord->updateInternal(NULL)

5 /vendor/yiisoft/yii2/db/BaseActiveRecord.php(673): yii\db\ActiveRecord->update(false, NULL)

6 /vendor/craftcms/commerce/src/services/Gateways.php(245): yii\db\BaseActiveRecord->save(false)

7 /vendor/craftcms/commerce/src/controllers/GatewaysController.php(137): craft\commerce\services\Gateways->saveGateway(Object(craft\commerce\paypal\gateways\PayPalExpress))

8 [internal function]: craft\commerce\controllers\GatewaysController->actionSave()

9 /vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)

10 /vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)

11 /vendor/craftcms/cms/src/web/Controller.php(103): yii\base\Controller->runAction('save', Array)

12 /vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('save', Array)

13 /vendor/craftcms/cms/src/web/Application.php(282): yii\base\Module->runAction('commerce/gatewa...', Array)

14 /vendor/craftcms/cms/src/web/Application.php(538): craft\web\Application->runAction('commerce/gatewa...', Array)

15 /vendor/craftcms/cms/src/web/Application.php(266): craft\web\Application->_processActionRequest(Object(craft\web\Request))

16 /vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))

17 /web/index.php(21): yii\base\Application->run()

18 {main}

Next yii\db\IntegrityException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'PayPal' for key 'commerce_gateways_name_unq_idx'
The SQL being executed was: UPDATE commerce_gateways SET name='PayPal', isFrontendEnabled=1, isArchived=0, dateUpdated='2018-09-11 21:23:30' WHERE id=4 in /vendor/yiisoft/yii2/db/Schema.php:664
Stack trace:

0 /vendor/yiisoft/yii2/db/Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'UPDATE `commerc...')

1 /vendor/yiisoft/yii2/db/Command.php(1075): yii\db\Command->internalExecute('UPDATE `commerc...')

2 /vendor/yiisoft/yii2/db/ActiveRecord.php(281): yii\db\Command->execute()

3 /vendor/yiisoft/yii2/db/BaseActiveRecord.php(802): yii\db\ActiveRecord::updateAll(Array, Array)

4 /vendor/yiisoft/yii2/db/ActiveRecord.php(625): yii\db\BaseActiveRecord->updateInternal(NULL)

5 /vendor/yiisoft/yii2/db/BaseActiveRecord.php(673): yii\db\ActiveRecord->update(false, NULL)

6 /vendor/craftcms/commerce/src/services/Gateways.php(245): yii\db\BaseActiveRecord->save(false)

7 /vendor/craftcms/commerce/src/controllers/GatewaysController.php(137): craft\commerce\services\Gateways->saveGateway(Object(craft\commerce\paypal\gateways\PayPalExpress))

8 [internal function]: craft\commerce\controllers\GatewaysController->actionSave()

9 /vendor/yiisoft/yii2/base/InlineAction.php(57): call_user_func_array(Array, Array)

10 /vendor/yiisoft/yii2/base/Controller.php(157): yii\base\InlineAction->runWithParams(Array)

11 /vendor/craftcms/cms/src/web/Controller.php(103): yii\base\Controller->runAction('save', Array)

12 /vendor/yiisoft/yii2/base/Module.php(528): craft\web\Controller->runAction('save', Array)

13 /vendor/craftcms/cms/src/web/Application.php(282): yii\base\Module->runAction('commerce/gatewa...', Array)

14 /vendor/craftcms/cms/src/web/Application.php(538): craft\web\Application->runAction('commerce/gatewa...', Array)

15 /vendor/craftcms/cms/src/web/Application.php(266): craft\web\Application->_processActionRequest(Object(craft\web\Request))

16 /vendor/yiisoft/yii2/base/Application.php(386): craft\web\Application->handleRequest(Object(craft\web\Request))

17 /web/index.php(21): yii\base\Application->run()

18 {main}

Additional Information:
Array
(
[0] => 23000
[1] => 1062
[2] => Duplicate entry 'PayPal' for key 'commerce_gateways_name_unq_idx'
)

Steps to reproduce

1. Create PayPal a gateway named 'PayPal'
2. Delete it
3. Create another PayPal gateway named 'PayPal'

Additional info

• Craft version: 3.0.23.1
• PHP version: 7.2 FPM
• Database driver & version: MySQL 5.7
• Plugins & versions: Commerce 2.0.0-beta.9

[lazlo-bonin]

In fact, having a closer look at my database, I realize it's worse: the actual Gateway doesn't even get deleted in the first place. The index is right: it still exists, but it's no longer visible from the CP.

[lukeholder]

Not deleting the gateway is on purpose, it is so you can still refund payment for existing orders etc.

Will update to work with the new soft delete feature and update the delete action to rename (append '_deleted') to for any the unique column constraints.

[lukeholder]

FYI we will be updating commerce to support Craft's new soft deletes: https://craftcms.com/news/craft-3-1-dev-preview

[lukeholder]

@andris-sevcenko this will be able to be closed once we push out project config support.

[andris-sevcenko]

Closed in fc3116a, going to be released with 2.0 GA most likely.