Yii Framework Forum: Migration Roll Back Failed - Yii Framework Forum

Jump to content

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

Migration Roll Back Failed

#1 User is offline   timkan 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 6
  • Joined: 20-December 12

Posted 02 March 2013 - 05:25 AM

Hello All,

I migrated create_issue_user_and_assignment_tables successful, but want to roll back and type ./yiic migrate down.
It failed.

code fyi.

migration file:
<?php

class m130302_100145_create_issue_user_and_assignment_tables extends CDbMigration
{
	/*public function up()
	{

	}

	public function down()
	{
		echo "m130302_100145_create_issue_user_and_assignment_tables does not support migration down.\n";
		return false;
	}*/

	
	// Use safeUp/safeDown to do migration with transaction
	public function safeUp()
	{
            //create the issue table
            $this->createTable('tbl_issue', array(
                'id' => 'pk',
                'name' => 'string NOT NULL',
                'description' => 'text',
                'project_id' => 'int(11) DEFAULT NULL',
                'type_id' => 'int(11) DEFAULT NULL',
                'status_id' => 'int(11) DEFAULT NULL',
                'owner_id' => 'int(11) DEFAULT NULL',
                'requester_id' => 'int(11) DEFAULT NULL',
                'create_time' => 'datetime DEFAULT NULL',
                'create_user_id' => 'int(11) DEFAULT NULL',
                'update_time' => 'datetime DEFAULT NULL',
                'update_user_id' => 'int(11) DEFAULT NULL',
                ), 'ENGINE=InnoDB');
            //create the user table
            $this->createTable('tbl_user', array(
                'id' => 'pk',
                'username' => 'string NOT NULL',
                'email' => 'string NOT NULL',
                'password' => 'string NOT NULL',
                'last_login_time' => 'datetime DEFAULT NULL',
                'create_time' => 'datetime DEFAULT NULL',
                'create_user_id' => 'int(11) DEFAULT NULL',
                'update_time' => 'datetime DEFAULT NULL',
                'update_user_id' => 'int(11) DEFAULT NULL',
                ), 'ENGINE = InnoDB');
            //create the assignment table that allows for many-to-many
            //relationship between projects and users
            $this->createTable('tbl_project_user_assignment', array(
                'project_id' => 'int(11) NOT NULL',
                'user_id' => 'int(11) NOT NULL',
                'PRIMARY KEY(`project_id`,`user_id`)',
                ), 'ENGINE=InnoDB');
            
            //foreign key relationships

            //the tbl_issue.project_id is a reference to tbl_project.id
            $this->addForeignKey("fk_issue_project", "tbl_issue", "project_id", "tbl_project", "id", "CASCADE", "RESTRICT");

            //the tbl_issue.owner_id is a reference to tbl_user.id
            $this->addForeignKey("fk_issue_owner", "tbl_issue", "owner_id", "tbl_user", "id", "CASCADE", "RESTRICT");

            //the tbl_issue.requester_id is a reference to tbl_user.id
            $this->addForeignKey("fk_issue_requester", "tbl_issue", "requester_id", "tbl_user", "id", "CASCADE", "RESTRICT");

            //the tbl_project_user_assignment.project_id is a reference to tbl_project.id
            $this->addForeignKey("fk_project_user", "tbl_project_user_assignment", "project_id", "tbl_project", "id", "CASCADE", "RESTRICT");

            //the tbl_project_user_assignment.user_id is a reference to tbl_user.id
            $this->addForeignKey("fk_user_project", "tbl_project_user_assignment", "user_id", "tbl_user", "id", "CASCADE", "RESTRICT");

	}

	public function safeDown()
	{
            $this->truncateTable('tbl_project_user_assignment');
            $this->truncateTable('tbl_issue');
            $this->truncateTable('tbl_user');
            $this->dropTable('tbl_project_user_assignment');
            $this->dropTable('tbl_issue');
            $this->dropTable('tbl_user');

	}
	
}


result:
dami@ubuntu:/var/www/trackstar2/protected$ ./yiic migrate down

Yii Migration Tool v1.0 (based on Yii v1.1.13-dev)

Total 1 migration to be reverted:
    m130302_100145_create_issue_user_and_assignment_tables

Revert the above migration? (yes|no) [no]:yes
*** reverting m130302_100145_create_issue_user_and_assignment_tables
    > truncate table tbl_project_user_assignment ... done (time: 0.108s)
    > truncate table tbl_issue ... done (time: 0.100s)
    > truncate table tbl_user ...Exception: CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`trackstar2`.`tbl_issue`, CONSTRAINT `fk_issue_requester` FOREIGN KEY (`requester_id`) REFERENCES `trackstar2`.`tbl_user` (`id`)). The SQL statement executed was: TRUNCATE TABLE `tbl_user` (/var/www/htdocs/yii/framework/db/CDbCommand.php:357)
#0 /var/www/htdocs/yii/framework/db/CDbCommand.php(1356): CDbCommand->execute()
#1 /var/www/htdocs/yii/framework/db/CDbMigration.php(265): CDbCommand->truncateTable('tbl_user')
#2 /var/www/trackstar2/protected/migrations/m130302_100145_create_issue_user_and_assignment_tables.php(79): CDbMigration->truncateTable('tbl_user')
#3 /var/www/htdocs/yii/framework/db/CDbMigration.php(77): m130302_100145_create_issue_user_and_assignment_tables->safeDown()
#4 /var/www/htdocs/yii/framework/cli/commands/MigrateCommand.php(410): CDbMigration->down()
#5 /var/www/htdocs/yii/framework/cli/commands/MigrateCommand.php(145): MigrateCommand->migrateDown('m130302_100145_...')
#6 [internal function]: MigrateCommand->actionDown(Array)
#7 /var/www/htdocs/yii/framework/console/CConsoleCommand.php(172): ReflectionMethod->invokeArgs(Object(MigrateCommand), Array)
#8 /var/www/htdocs/yii/framework/console/CConsoleCommandRunner.php(67): CConsoleCommand->run(Array)
#9 /var/www/htdocs/yii/framework/console/CConsoleApplication.php(91): CConsoleCommandRunner->run(Array)
#10 /var/www/htdocs/yii/framework/base/CApplication.php(169): CConsoleApplication->processRequest()
#11 /var/www/htdocs/yii/framework/yiic.php(33): CApplication->run()
#12 /var/www/trackstar2/protected/yiic.php(7): require_once('/var/www/htdocs...')
#13 /var/www/trackstar2/protected/yiic(4): require_once('/var/www/tracks...')
#14 {main}
*** failed to revert m130302_100145_create_issue_user_and_assignment_tables (time: 0.225s)


Migration failed. All later migrations are canceled.


pls help how to proceed and thx a lot :)
0

#2 User is offline   dalers 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 07-February 13

Posted 06 March 2013 - 06:28 PM

View Posttimkan, on 02 March 2013 - 05:25 AM, said:

I migrated create_issue_user_and_assignment_tables successful, but want to roll back and type ./yiic migrate down. It failed.


I had the same problem, which seems to result from using MySQL 5.5+ (I am using MySQL 5.5.27 in XAMPP 1.8.1). I found this note in the MySQL 5.1 manual, Section 13.1.34 "TRUNCATE TABLE Syntax" (the MySQL 5.5 manual has the same syntax description in Section 13.1.133, but the note is only in the 5.1 manual):

Quote

In MySQL 5.5and higher, TRUNCATE TABLE is not allowed for InnoDB tables referenced by foreign keys. For ease of upgrading, rewrite such statements to use DELETE instead.


To correct the error, replace the following lines in the SafeDown() method:
$this->truncateTable('tbl_project_user_assignment');
$this->truncateTable('tbl_issue');
$this->truncateTable('tbl_user'); 


with:
$this->delete('tbl_project_user_assignment');
$this->delete('tbl_issue');
$this->delete('tbl_user'); 

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