Solved

Capturing an error to handle partial failures in a CodeIgniter migration

Posted on 2013-11-17
1
162 Views
Last Modified: 2015-05-06
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
Comment
Question by:Terry Woods
1 Comment
 
LVL 25

Accepted Solution

by:
Marcus Bointon earned 500 total points
ID: 39688002
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question