• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Capturing an error to handle partial failures in a CodeIgniter migration

Below is an example of the kind of migrations I'm using in CodeIgniter. Performing one migration can include several queries.

The trouble I'm having during development of such migration code is where at least one, but not all, of the queries are successful. Currently, this leaves the migration partially done, so that it's not re-runnable. It slows down development; I have to manually change the schema back to it's original state before I can retest the code.

Schema changes can't use database transactions, so any code achieving a roll back would need to be coded manually. If I was to restructure the code so that each SQL was grouped as a pair (an "up" query, and a "down" query), then an error could be caught, and the schema changes could be rolled back.

Can someone explain if it's possible for error handling to be set up to trigger a roll back, and how it would be done? Thanks

<?
defined('BASEPATH') OR exit('No direct script access allowed');

class Migration_Alter_table_files extends CI_Migration {

	public function __construct($config = array()) {
		parent::__construct($config);
	}

	public function up()
	{
		$sqls = array();
		$sqls[] = <<<END
ALTER TABLE files RENAME parent_file_id  TO parent_folder_id;
END;

		$sqls[] = <<<END
ALTER TABLE files 
ADD CONSTRAINT component_files_fk FOREIGN KEY (component_files_id)
      REFERENCES component_files (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION;
END;

		$sqls[] = <<<END
ALTER TABLE files 
ADD CONSTRAINT parent_folder_fk FOREIGN KEY (parent_folder_id)
      REFERENCES file_folders (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
END;

		foreach ($sqls as $sql) {
			$this->db->query($sql);
		}
	}

	public function down()
	{
		$table = "files";

		$sqls = array();
		$sqls[] = <<<END
ALTER TABLE files DROP CONSTRAINT parent_folder_fk;
END;
		
		$sqls[] = <<<END
ALTER TABLE files RENAME parent_folder_id  TO parent_file_id;
END;
		
		$sqls[] = <<<END
ALTER TABLE files DROP CONSTRAINT component_files_fk;
END;
		
		foreach ($sqls as $sql) {
			$this->db->query($sql);
		}
		#exit;

	}

}

Open in new window

0
Terry Woods
Asked:
Terry Woods
1 Solution
 
Marcus BointonCommented:
That approach is reasonable in general, but it's not always possible to go back - for example if you drop a table or field you can't undelete it.

You could copy the tables to be altered to different names, apply your migrations to the copies, then swap them into place by renaming them after they have all been created successfully. That can also have the benefit that you retain copies of the old tables.

The only issue with it is it's hard to retain foreign key constraints like that (if you're using them), so you might have to drop constraints and recreate them afterwards.

It's very difficult to retain any kind of transactional integrity through all of this, and it can take a long time if your tables are large.

Automated schema change scripts usually make the mistake of doing each change on a table in a separate ALTER TABLE statement. This multiples the time it needs to take by the number of alterations; you should combine multiple ALTER statements into a single one, and the alterations can all happen in one go. That said, I don't think it's possible to do multiple foreign key constraint changes at once, but most operations are ok.

You could look at the online schema change script that's part of Percona's MySQL toolkit: http://www.percona.com/software/percona-toolkit
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now