DbManager::removeAll() MSSQL error.

[sdlins]

I have this migration for RBAC:

    public function safeUp()
    {
        /** @var \yii\rbac\DbManager $rbac */
        $rbac = \Yii::$app->get('authManagerAlteracaoDeCronograma');

        $rbac->removeAll(); // <-- results in error
         ...
    }

Line $rbac->removeAll(); results in:

SQLSTATE[21000]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The SQL being executed was: DELETE FROM [ALTERACAOCRONO_RBAC_ITEM] (C:\...\vendor\yiisoft\yii2\db\Schema.php:664)
#0 C:\...\vendor\yiisoft\yii2\db\Command.php(1263): yii\db\Schema->convertException(Object(PDOException), 'DELETE FROM [AL...')
#1 C:\...\vendor\yiisoft\yii2\db\Command.php(1075): yii\db\Command->internalExecute('DELETE FROM [AL...')
#2 C:\...\vendor\yiisoft\yii2\rbac\DbManager.php(901): yii\db\Command->execute()
#3 C:\...\common\migrations\alteracao-de-cronograma\m180704_153354_rbac_permissoes_e_regramento.php(18): yii\rbac\DbManager->removeAll()
#4 C:\...\vendor\yiisoft\yii2\db\Migration.php(114): m180704_153354_rbac_permissoes_e_regramento->safeUp()
#5 C:\...\vendor\yiisoft\yii2\console\controllers\BaseMigrateController.php(725): yii\db\Migration->up()
#6 C:\...\vendor\yiisoft\yii2\console\controllers\BaseMigrateController.php(199): yii\console\controllers\BaseMigrateController->migrateUp('m180704_153354_...')
#7 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#8 C:\...\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#9 C:\...\vendor\yiisoft\yii2\base\Controller.php(157): yii\base\InlineAction->runWithParams(Array)
#10 C:\...\vendor\yiisoft\yii2\console\Controller.php(148): yii\base\Controller->runAction('', Array)
#11 C:\...\vendor\yiisoft\yii2\base\Module.php(528): yii\console\Controller->runAction('', Array)

#12 C:\...\vendor\yiisoft\yii2\console\Application.php(180): yii\base\Module->runAction('migrate-alterac...', Array)
#13 C:\...\vendor\yiisoft\yii2\console\Application.php(147): yii\console\Application->runAction('migrate-alterac...', Array)
#14 C:\...\vendor\yiisoft\yii2\base\Application.php(386): yii\console\Application->handleRequest(Object(yii\console\Request))
#15 C:\...\yii(23): yii\base\Application->run()
#16 {main}
*** failed to apply m180704_153354_rbac_permissoes_e_regramento (time: 0.043s)

0 from 1 migrations were applied.

Migration failed. The rest of the migrations are canceled.

The problem happens inside DbManager:

    public function removeAll()
    {
        $this->removeAllAssignments(); // works fine
        $this->db->createCommand()->delete($this->itemChildTable)->execute(); // works fine
        $this->db->createCommand()->delete($this->itemTable)->execute(); // <-- error
        $this->db->createCommand()->delete($this->ruleTable)->execute();
        $this->invalidateCache();
    }

Tryed to run the generated query DELETE FROM [ALTERACAOCRONO_RBAC_ITEM] right in MSSQL manager and the error was the same. Probably it has something to do with the TRIGGER created by yii rbac migration.

Additional info

Q	A
Yii version	2.0.15.1
PHP version	7.1.14
Operating system	Win 10
MSSQL	2016

[sdlins]

This could be related to #16479.

[achretien]

Sorry to dig into an old issue.

The problem come from the rbac trigger (trigger_auth_item_child) in msssql

On delete there is a variable assignation

https://github.com/yiisoft/yii2/blob/13c14292c2bee07e6a1766262bbbae35289d9874/framework/rbac/migrations/m140506_102106_rbac_init.php#L107

and the SELECT name FROM deleted return the names of the planned deleted lines

A solution could be to split into two triggers, one for delete and one for update (this is what we did in our projects) @samdark would you like a pull request ?

[samdark]

Yes.

[achretien]

Done