Yii Framework Forum: Migration Won't Work (Innodb With Safeup) - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Migration Won't Work (Innodb With Safeup)

#1 User is offline   relaxpor 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 08-February 14

Posted 09 March 2014 - 12:03 PM

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,

Posted Image
0

#2 User is offline   alirz23 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,082
  • Joined: 08-August 12
  • Location:Durban, South Africa

Posted 09 March 2014 - 03:12 PM

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
0

#3 User is offline   relaxpor 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 08-February 14

Posted 11 March 2014 - 12:05 AM

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.

0

#4 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 11 March 2014 - 06:54 AM

Well, you're trying to create constraint on non-existing tbl_bank_account.uid2 as said in the error.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#5 User is offline   relaxpor 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 08-February 14

Posted 15 March 2014 - 03:34 AM

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.
0

#6 User is offline   Renka 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 12
  • Joined: 06-April 12

Posted 15 March 2014 - 04:36 AM

View Postrelaxpor, on 15 March 2014 - 03:34 AM, said:

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:

Quote

Note: Not all DBMS support transactions. And some DB queries cannot be put into a transaction. In this case, you will have to implement up() and down(), instead. And for MySQL, some SQL statements may cause implicit commit.


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

#7 User is offline   relaxpor 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 08-February 14

Posted 15 March 2014 - 08:38 AM

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 ?
0

#8 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 17 March 2014 - 05:02 AM

MySQL doesn't support transactions for schema changes.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

#9 User is offline   relaxpor 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 08-February 14

Posted 22 March 2014 - 08:26 AM

View Postsamdark, on 17 March 2014 - 05:02 AM, said:

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?
0

#10 User is offline   samdark 

  • Having fun
  • Yii
  • Group: Yii Dev Team
  • Posts: 3,778
  • Joined: 17-January 09
  • Location:Russia

Posted 22 March 2014 - 04:05 PM

No.
Yii 1.1 Application Development Cookbook

Enjoying Yii? Star us at github: 1.1 and 2.0.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users