Migration Roll Back Failed

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 :)

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):

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');