Fixture “User” loaded in MySQL but not loaded in MS SQL Server


(Aleksey Bykov) #1

I installed advanced application template, created two databases yii2advanced: one in the local MySQL another in the remote MS SQL Server:


'components' => [

    'db' => [

        'class' => '\yii\db\Connection',

        'dsn' => 'mysql:host=127.0.0.1;dbname=yii2advanced',

        'username' => 'root',

        'password' => 'root',

//    'dsn' => 'sqlsrv:Server=..;Database=yii2advanced;MultipleActiveResultSets=false',

//    'username' => 'sa',

//    'password' => 'sa',

        'charset' => 'utf8',

        'tablePrefix' => 'tbl_',

    ],

],

Applied the migration on both the databases:


yii migrate

Now both databases have tables named tbl_user and tbl_migration.

CREATE TABLEstatement for MS SQL Server:


CREATE TABLE [dbo].[tbl_user](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [username] [nvarchar](255) NOT NULL,

    [auth_key] [nvarchar](32) NOT NULL,

    [password_hash] [nvarchar](255) NOT NULL,

    [password_reset_token] [nvarchar](255) NULL,

    [email] [nvarchar](255) NOT NULL,

    [status] [smallint] NOT NULL,

    [created_at] [int] NOT NULL,

    [updated_at] [int] NOT NULL,

PRIMARY KEY CLUSTERED 

(

    [id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

UNIQUE NONCLUSTERED 

(

    [password_reset_token] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

UNIQUE NONCLUSTERED 

(

    [email] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],

UNIQUE NONCLUSTERED 

(

    [username] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE statement for MySQL:


CREATE TABLE `tbl_user` (

 `id` int(11) NOT NULL AUTO_INCREMENT,

 `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

 `auth_key` varchar(32) COLLATE utf8_unicode_ci NOT NULL,

 `password_hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

 `password_reset_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

 `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,

 `status` smallint(6) NOT NULL DEFAULT '10',

 `created_at` int(11) NOT NULL,

 `updated_at` int(11) NOT NULL,

 PRIMARY KEY (`id`),

 UNIQUE KEY `username` (`username`),

 UNIQUE KEY `email` (`email`),

 UNIQUE KEY `password_reset_token` (`password_reset_token`)

 ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Next, I loaded fixture into a MySQL table tbl_user:




C:\php-projects\advanced\tests\codeception\bin>yii fixture/load User

Fixtures namespace is:

        tests\codeception\common\fixtures


Global fixtures will be used:


        1. yii\test\InitDb


Fixtures below will be loaded:


        1. User


Be aware that:

Applying leads to purging of certain data in the database!


Load above fixtures? (yes|no) [no]:yes


Fixtures were successfully loaded from namespace:

        "tests\codeception\common\fixtures"


        1. yii\test\InitDbFixture

        2. tests\codeception\common\fixtures\UserFixture

Tried to load fixture into the MS SQL Server’s table [dbo].[tbl_user] but get exception:


C:\php-projects\advanced\tests\codeception\bin>yii fixture/load User

Fixtures namespace is:

        tests\codeception\common\fixtures


Global fixtures will be used:


        1. yii\test\InitDb


Fixtures below will be loaded:


        1. User


Be aware that:

Applying leads to purging of certain data in the database!


Load above fixtures? (yes|no) [no]:yes


Exception 'yii\base\InvalidParamException' with message 'Table not found: []'


in C:\php-projects\advanced\vendor\yiisoft\yii2\db\mssql\QueryBuilder.php:182


Stack trace:

0 C:\php-projects\advanced\vendor\yiisoft\yii2\db\Command.php(754): yii\db\mssql\QueryBuilder->checkIntegrity(false, '', '')

1 C:\php-projects\advanced\vendor\yiisoft\yii2\test\InitDbFixture.php(94): yii\db\Command->checkIntegrity(false, '')

2 C:\php-projects\advanced\vendor\yiisoft\yii2\test\InitDbFixture.php(76): yii\test\InitDbFixture->checkIntegrity(false)

3 C:\php-projects\advanced\vendor\yiisoft\yii2\test\FixtureTrait.php(114): yii\test\InitDbFixture->beforeUnload()

4 C:\php-projects\advanced\vendor\yiisoft\yii2\console\controllers\FixtureController.php(159): yii\console\controllers\FixtureController->unloadFixtures(Array)


5 [internal function]: yii\console\controllers\FixtureController->actionLoad('User')

6 C:\php-projects\advanced\vendor\yiisoft\yii2\base\InlineAction.php(55): call_user_func_array(Array, Array)

7 C:\php-projects\advanced\vendor\yiisoft\yii2\base\Controller.php(154): yii\base\InlineAction->runWithParams(Array)

8 C:\php-projects\advanced\vendor\yiisoft\yii2\console\Controller.php(119): yii\base\Controller->runAction('load', Array)

9 C:\php-projects\advanced\vendor\yiisoft\yii2\base\Module.php(454): yii\console\Controller->runAction('load', Array)

10 C:\php-projects\advanced\vendor\yiisoft\yii2\console\Application.php(176): yii\base\Module->runAction('fixture/load', Array)

11 C:\php-projects\advanced\vendor\yiisoft\yii2\console\Application.php(143): yii\console\Application->runAction('fixture/load', Array)

12 C:\php-projects\advanced\vendor\yiisoft\yii2\base\Application.php(375): yii\console\Application->handleRequest(Object(yii\console\Request))

13 C:\php-projects\advanced\tests\codeception\bin\yii(23): yii\base\Application->run()

14 {main}

Why the User fixture not loaded into a table in MS SQL Server? Maybe the reason is in the [dbo] scheme?


(Aleksey Bykov) #2

Tried to define the table name as described below, several variants, all the have not helped:


public static function tableName()

{

    return 'dbo..tbl_user'

}

...

public static function tableName()

{

    return 'dbo.sa.tbl_user'

}

...

public static function tableName()

{

    return '{{dbo.sa.%user}}'

}

...

And so on…