Solved

Capturing an error to handle partial failures in a CodeIgniter migration

Posted on 2013-11-17
1
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

Question has a verified solution.

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

There is a huge demand for CodeIgniter among the PHP web developers due to its dynamic features and benefits these days. It is one of most popular and agile open source PHP framework for creating robust web applications in PHP web development field.…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

626 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