Migrations & SQL Server 2012 - cannot create a transactional migration

[jreyes-csppr]

After creating the migration table manually (see issue #3066), yii migrate/up crashes with the following error:

*** applying m140411_000001_create_test_table
Exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server N
ative Client 11.0][SQL Server]A transaction that was started in a MARS batch is
still active at the end of the batch. The transaction is rolled back.

Configuration as follows:

PHP 5.4.20 on Windows Server 2008 R2
MS SQL Server 2012
Yii2 2.0.0-alpha

config/db.php

    return [
    'class' => 'yii\db\Connection',
    'dsn'=>'sqlsrv:server=mydomain.com\sqlexpress;database=Test',
    'username' => 'sa',
    'password' => 'password',
    ];

create_test_table.php:

    use yii\db\Schema;
    class m140411_000001_create_test_table extends \yii\db\Migration
    {

    // Use safeUp/safeDown to do migration with transaction
    public function up()
    {
            $this->createTable('Test', [
            'ID' => 'pk',
            'Comment' => 'nvarchar(32) NOT NULL',
        ]);

         $this->insert('Test', [
         'Comment' => 'Test Table',
        ]);
    }

    public function down()
    {
        $this->dropTable('Test');
    }
    }

yii migrate/up results:

Yii Migration Tool (based on Yii v2.0.0-dev)

Total 1 new migration to be applied:
    m140411_000001_create_test_table

Apply the above migration? (yes|no) [no]:yes
*** applying m140411_000001_create_test_table
Exception 'PDOException' with message 'SQLSTATE[42000]: [Microsoft][SQL Server N
ative Client 11.0][SQL Server]A transaction that was started in a MARS batch is
still active at the end of the batch. The transaction is rolled back.'

in C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\db\mssql\PDO.php:36

Stack trace:
0 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\db\mssql\PDO.php(36): PDO->e
xec('BEGIN TRANSACTI...')
1 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\db\Transaction.php(73): yii\
db\mssql\PDO->beginTransaction()
2 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\db\Connection.php(425): yii\
db\Transaction->begin()
3 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\db\Migration.php(64): yii\db
\Connection->beginTransaction()
4 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\console\controllers\MigrateC
ontroller.php(511): yii\db\Migration->up()
5 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\console\controllers\MigrateC
ontroller.php(180): yii\console\controllers\MigrateController->migrateUp('m14041
1_000001_...')
6 [internal function]: yii\console\controllers\MigrateController->actionUp(0)
7 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\base\InlineAction.php(54): c
all_user_func_array(Array, Array)
8 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\base\Controller.php(147): yi
i\base\InlineAction->runWithParams(Array)
9 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\console\Controller.php(83):
yii\base\Controller->runAction('up', Array)
10 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\base\Module.php(423): yii\c
onsole\Controller->runAction('up', Array)
11 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\console\Application.php(160
): yii\base\Module->runAction('migrate/up', Array)
12 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\console\Application.php(136
): yii\console\Application->runAction('migrate/up', Array)
13 C:\inetpub\wwwroot\cspquotes\vendor\yiisoft\yii2\base\Application.php(353):
yii\console\Application->handleRequest(Object(yii\console\Request))
14 C:\inetpub\wwwroot\cspquotes\yii(23): yii\base\Application->run()

However, using up() instead of safeUp() works. Please look into it.

Thanks!

[qiangxue]

Could you try an example similar to http://www.yiiframework.com/doc-2.0/guide-database-basics.html#transactions to see if transaction works well with your MSSQL installation?

[qiangxue]

So your proposed fix in #3068 should fix this issue?

[jreyes-csppr]

Qiang:

The proposed fix in #3068 was tested by me and solves the transaction issue for migrations. I have yet to test it in other scenarios (I'm refactoring an existing Yii1 app into Yii2 so I'll get there) but I'm confident it will work as well because what it does is return the responsibility to the PDO driver where it should be, like it works for other DBMS.

It only applies to the SQLSRV driver version 3.0.1 (SQLSRV30.EXE which is required for PHP 5.4) which has been stable since October 2012. The PHP distribution doesn't include any Windows version after PHP 5.3; refer to http://www.php.net/manual/en/intro.mssql.php.

For compatibility with earlier versions or if if the app runs under Linux the existing MSSQL code should remain. For now I'm keeping the changes locally and will report back after further testing.

Thanks,

José

[samdark]

Should be fixed now.