Migration Won't Work (Innodb With Safeup)

Here is my code using safeUp


<?php


use yii\db\Schema;


class m140309_090452_add_payment_inform extends \yii\db\Migration

{

	public function safeUp()

	{

    $this->createTable("tbl_bank", [

      'bank_id' => 'smallint(6) NOT NULL AUTO_INCREMENT',

      'bank_name' => Schema::TYPE_STRING,

      'PRIMARY KEY (`bank_id`)'

    ]);

//    $this->addPrimaryKey("bank_id_pk", "tbl_bank", "bank_id");

    

    $this->batchInsert("tbl_bank", ['bank_name'], 

  [['ไทยพาณิชย์'], ['กสิกรไทย'], ['กรุงเทพ'], ['ทหารไทย'], ['กรุงไทย'], ['ออมสิน'], ['ธนชาติ']]);

    

    

    $this->createTable("tbl_bank_account", [

      'bank_account_id' => 'int(11) NOT NULL AUTO_INCREMENT',

      'account_name' => Schema::TYPE_STRING,

      'bank_id' => Schema::TYPE_SMALLINT,

      'account_number' => Schema::TYPE_INTEGER,

      'uid' => 'integer',

      'PRIMARY KEY (`bank_account_id`)'

    ]);

//    $this->addPrimaryKey("bank_account_id_pk", "tbl_bank_account", "bank_account_id");

    $this->createIndex("bank_id_account_index", "tbl_bank_account", "bank_id");

    $this->addForeignKey("bank_id_account_fk", "tbl_bank_account", "bank_id", "tbl_bank", "bank_id", 'RESTRICT', 'RESTRICT');

    $this->createIndex("uid_bank_account_index", "tbl_bank_account", "uid");

    $this->addForeignKey("uid_bank_account_fk", "tbl_bank_account", "uid", "tbl_user", "uid", 'RESTRICT', 'RESTRICT');

    

    $this->createTable("tbl_payment", [

      'payment_id' => 'int(11) NOT NULL AUTO_INCREMENT',

      'order_id' => Schema::TYPE_SMALLINT,

      'account_name' => Schema::TYPE_STRING,

      'bank_id' => Schema::TYPE_SMALLINT,

      'account_number' => Schema::TYPE_SMALLINT,

      'payer_id' => Schema::TYPE_SMALLINT,

      'payee_id' => Schema::TYPE_SMALLINT,

      'amount' => Schema::TYPE_FLOAT,

      'pay_date' => Schema::TYPE_DATETIME,

      'remark' => Schema::TYPE_STRING,

      'PRIMARY KEY (`payment_id`)'

    ]);

//    $this->addPrimaryKey("payment_id_pk", "tbl_payment", "payment_id");

    $this->createIndex("payment_order_index", "tbl_payment", "order_id");

    $this->addForeignKey("payment_order_fk", "tbl_payment", "order_id", "tbl_order", "order_id", 'RESTRICT', 'RESTRICT');

    $this->createIndex("payer_user_index", "tbl_payment", "payer_id");

    $this->addForeignKey("payer_user_fk", "tbl_payment", "payer_id", "tbl_user", "uid", 'RESTRICT', 'RESTRICT');

    $this->createIndex("payee_user_index", "tbl_payment", "payee_id");

    $this->addForeignKey("payee_user_fk", "tbl_payment", "payee_id", "tbl_user", "uid", 'RESTRICT', 'RESTRICT');

    

    $this->createTable("tbl_shipment", [

      'shipment_id' => 'int(11) NOT NULL AUTO_INCREMENT',

      'order_id' => Schema::TYPE_SMALLINT,

      'receiver_name' => Schema::TYPE_STRING,

      'address' => Schema::TYPE_STRING,

      'postal_code' => Schema::TYPE_INTEGER,

      'sender_id' => Schema::TYPE_SMALLINT,

      'receiver_id' => Schema::TYPE_SMALLINT,

      'track_code' => Schema::TYPE_FLOAT,

      'shipment_method' => Schema::TYPE_SMALLINT,

      'send_date' => Schema::TYPE_DATETIME,

      'remark' => Schema::TYPE_STRING,

      'PRIMARY KEY (`shipment_id`)'

    ]);

//    $this->addPrimaryKey("shipment_id_pk", "tbl_shipment", "shipment_id");

    $this->createIndex("shipment_order_index", "tbl_shipment", "order_id");

    $this->addForeignKey("shipment_order_fk", "tbl_shipment", "order_id", "tbl_order", "order_id", 'RESTRICT', 'RESTRICT');

    $this->createIndex("sender_user_index", "tbl_shipment", "sender_id");

    $this->addForeignKey("sender_user_fk", "tbl_shipment", "sender_id", "tbl_user", "uid", 'RESTRICT', 'RESTRICT');

    $this->createIndex("receiver_user_index", "tbl_shipment", "receiver_id");

    $this->addForeignKey("receiver_user_fk", "tbl_shipment", "receiver_id", "tbl_user", "uid", 'RESTRICT', 'RESTRICT');

    


	}


	public function safeDown()

	{

    $this->delete("tbl_bank_account");

    $this->delete("tbl_payment");

    $this->delete("tbl_shipment");

    $this->delete("tbl_bank");

	}

}

but the table also still appear, (it should be rollbacked ?)

Here is the result in my console…

Please advise,

Well only half of the message appears in the console where is the rest paste the full error message its hard to figure out what is the error

So sorry about that,

The table have insert 2 tables (tbl_bank, tbl_bank_account’) then has an error while addForeignKey…

After he migration fail, i’ve check the phpAdmin and still saw the 2 tables that has been created.

Thank you.




PorMacbook:himaparn por$ ./yii migrate

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


Total 1 new migration to be applied:

    m140309_090452_add_payment_inform


Apply the above migration? (yes|no) [no]:yes

*** applying m140309_090452_add_payment_inform

    > create table tbl_bank ... done (time: 0.026s)

    > insert into tbl_bank ... done (time: 0.008s)

    > create table tbl_bank_account ... done (time: 0.009s)

    > create index bank_id_account_index on tbl_bank_account (bank_id) ... done (time: 0.018s)

    > add foreign key bank_id_account_fk: tbl_bank_account (bank_id) references tbl_bank (bank_id) ... done (time: 0.015s)

    > create index uid_bank_account_index on tbl_bank_account (uid) ... done (time: 0.022s)

    > add foreign key uid_bank_account_fk: tbl_bank_account (uid2) references tbl_user (id) ...Exception: SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'uid2' doesn't exist in table

The SQL being executed was: ALTER TABLE `tbl_bank_account` ADD CONSTRAINT `uid_bank_account_fk` FOREIGN KEY (`uid2`) REFERENCES `tbl_user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT (/Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/db/Command.php:286)

#0 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/db/Migration.php(370): yii\db\Command->execute()

#1 /Applications/MAMP/htdocs/himaparn/console/migrations/m140309_090452_add_payment_inform.php(32): yii\db\Migration->addForeignKey('uid_bank_accoun...', 'tbl_bank_accoun...', 'uid2', 'tbl_user', 'id', 'RESTRICT', 'RESTRICT')

#2 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/db/Migration.php(66): m140309_090452_add_payment_inform->safeUp()

#3 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/console/controllers/MigrateController.php(502): yii\db\Migration->up()

#4 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/console/controllers/MigrateController.php(179): yii\console\controllers\MigrateController->migrateUp('m140309_090452_...')

#5 [internal function]: yii\console\controllers\MigrateController->actionUp(0)

#6 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/base/InlineAction.php(53): call_user_func_array(Array, Array)

#7 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/base/Controller.php(128): yii\base\InlineAction->runWithParams(Array)

#8 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/console/Controller.php(82): yii\base\Controller->runAction('', Array)

#9 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/base/Module.php(586): yii\console\Controller->runAction('', Array)

#10 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/console/Application.php(172): yii\base\Module->runAction('migrate', Array)

#11 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/console/Application.php(140): yii\console\Application->runAction('migrate', Array)

#12 /Applications/MAMP/htdocs/himaparn/vendor/yiisoft/yii2-dev/framework/base/Application.php(289): yii\console\Application->handleRequest(Object(yii\console\Request))

#13 /Applications/MAMP/htdocs/himaparn/yii(30): yii\base\Application->run()

#14 {main}

*** failed to apply m140309_090452_add_payment_inform (time: 0.109s)




Migration failed. The rest of the migrations are canceled.



Well, you’re trying to create constraint on non-existing tbl_bank_account.uid2 as said in the error.

Sorry that i stated not clear, the problem is about that the database won’t rollback to the original.

the table that has been created during SafeUp should be rollback and disappear if they encounter any error right?

Thank you.

Well yes and no.

Quote from documentation:

I can’t add link to my post but go to mysql manual and search for term: implicit commit

Since the up() function has not complete,

doing migrate down in console will migrate the previous version (not the one that fail)

So, could you suggest how should i do, beside going delete myself in sqlAdmin ?

P.S. I thougt innoDB have transaction, isn’t it ?

MySQL doesn’t support transactions for schema changes.

I see, thank for your information.

Do you have any work around on this to drop the table automatically?

No.