Getting Phing's dbdeploy task to automatically rollback on delta error

Question

I am using Phing's dbdeploy task to manage my database schema. This is working fine, as long as there is no errors in the queries of my delta files.

However, if there is an error, dbdeploy will just run the delta files up to the query with the error and then abort. This causes me some frustration, because I have to manually rollback the entry in the changelog table then. If I don't, dbdeploy will assume the migration was successful on a subsequent try, so any retries will do nothing.

So the question is, is there any way to get dbdeploy use transactions or can you suggest any other way to have phing rollback automatically when an error occurs?

Note: I'm not that proficient with Phing, so if this involves writing a custom task, any example code or a url with further information is highly appreciated. Thanks

I think phings dbdeploy at its current state is inferior to projects which concentrate on db-versioning only. See http://stackoverflow.com/questions/3324571/is-there-a-php-equivalent-of-rails-migrations for example — Christoph Strasen, Aug 02 '11 at 10:52

score 3 · Answer 1 · answered May 18 '12 at 14:50

I know, this is very old thread, but maybe it will be use full for someone else. You can use try->catch statements to implement a solution for that. My example :

<trycatch>
    <try>
        <exec
            command="${progs.mysql} -h${db.live.host} -u${db.live.user} -p${db.live.password} ${db.live.name} &lt; ${db.live.output}/${build.dbdeploy.deployfile}"
            dir="${project.basedir}"
            checkreturn="true" />
            <echo>Live  database was upgraded successfully</echo>
    </try>    
    <catch>
            <echo>Errors in upgrading database</echo>
            <exec
            command="${progs.mysql} -h${db.live.host} -u${db.live.user} -p${db.live.password} ${db.live.name} &lt; ${db.live.output}/${build.dbdeploy.undofile}"
            dir="${project.basedir}"
            checkreturn="true" />
    </catch>
    </trycatch>

score 3 · Accepted Answer · answered Apr 07 '11 at 20:02

(if you're still out there...) Regarding phing for a db dump task, use the db's dump utility and create a phing task. I use postgres mainly and have this in my phing build.xml:

<target name="db-dump" depends="">
    <php expression="date('Ymd-Hi')" returnProperty="phing.dump.ts"/>
    <exec command="pg_dump -h ${db.host} -U ${db.user} -O ${db.name} | gzip > ${db.dumppath}/${db.name}-${phing.dump.ts}.gz" />
</target>

Chris Suszyński · Answer 3 · 2012-01-10T12:06:53.697

3

The simplest way of solving your problem is to use pdoexec task which by default runs sql script in transaction. When error occurs the database engine will automatically rollback your changes (even those on change log table - database will be in previous state)

Example:

<pdosqlexec 
    url="pgsql:host=${db.host}
    dbname=${db.name}"
    userid="${db.user}"
    password="${db.pass}"
    src="${build.dbdeploy.deployfile}"
/>

edited Jan 10 '12 at 12:06

answered Dec 30 '11 at 22:35

Chris Suszyński

1,494
17
23

Does this work for DDL changing statements? At least in MySql those cannot be rolled back. – Gordon Apr 28 '14 at 16:07

score 1 · Answer 4 · answered Mar 16 '10 at 16:51

1

Why not write a series of undo deltas and add a phing task that runs on failure of the other task?

answered Mar 16 '10 at 16:51

Steve Robillard

13,445
3
36
32

Thanks. I do have undo deltas. But how could I run them automatically? How would the other phing task know how far to run back? – Gordon Mar 16 '10 at 17:35
in terms of knowing how far back to go you could set a property for the starting point. As for running automatically I am not sure you can do it automatically, but what about setting a task that checks the db versioning table and based on that runs the undo. You may want to look at xinc which has notifiers for success and failure with the ability to run some code logic based on success or failure. – Steve Robillard Mar 17 '10 at 00:23

score 1 · Answer 5 · answered Jun 28 '10 at 22:03

1

you really should take a look at capistrano. TomTom: you are missing something here: the backup before schema change of course has to be made - but what to do with the NEW data that was inserted meanwhile you were thinking that everything is ok? I do not say, tthat there is a good tool for this problem, but the problem exists in real life.

answered Jun 28 '10 at 22:03

Aborto Virtuale de la Veritano

21
1

Thanks for the suggestion. I'd like to stay in the PHP ecosystem if possible though. – Gordon Jun 28 '10 at 22:40

score -1 · Answer 6 · answered Mar 16 '10 at 11:33

-1

The "proper" way to do this is a backup before schema change, then rollback in case of error.

You dont say what db you use - but it would wonder me if all schema changes would be supported in transactions. Mos thigh end SQL databases (oracle, db2, sql server) dont do that in all cases for really good reasons. Transacitonal schema changes are REALLY hard and REALLY resouce intensive.

answered Mar 16 '10 at 11:33

TomTom

61,059
10
88
148

Thanks. Using MySQL but I am considering using this for managing some Views in an Oracle 10g as well. Any ideas how to do the backup with Phing then? – Gordon Mar 16 '10 at 11:41
Nope. Should not be done in there. it is the admins job to make backup before configuration / software changes. – TomTom Mar 16 '10 at 12:09
4

Umm, that kinda defeats the purpose of automatic deployment tools like phing, doesn't it? – Gordon Mar 16 '10 at 12:22

Getting Phing's dbdeploy task to automatically rollback on delta error

6 Answers6