dbdeploy error with --// in deploy-file

Question

I am generating deployment-files for my mysql-database with phing and dbdeploy.

The output of a dbdeploy-file that is generated looks like the following:

    -- Fragment begins: 8 --
INSERT INTO changelog
                                (change_number, delta_set, start_dt, applied_by, description) VALUES (8, 'Main', NOW(), 'dbdeploy', '8-add_tracking_code.sql');
--//
ALTER TABLE `order` ADD `tracking_code` VARCHAR(255) NOT NULL;
UPDATE changelog
                             SET complete_dt = NOW()
                             WHERE change_number = 8
                             AND delta_set = 'Main';
-- Fragment ends: 8 --

The Problem is the --// before the ALTER Statement. The database got an error with it. If I remove the --// the hole file ist correct.

Here is a piece of my phing build-script so that you can see how I am generating the .sql-File with dbdeploy:

<target name="dbdeploy-migrate-all">

        <!-- load the dbdeploy task -->
        <taskdef name="dbdeploy" classname="phing.tasks.ext.dbdeploy.DbDeployTask"/>

        <echo message="Loading deltas from ${build.dbdeploy.alters_dir}" />

        <property name="build.dbdeploy.deployfile" value="${build.dbdeploy.deploy_dir}/deploy-${DSTAMP}${TSTAMP}.sql" />
        <property name="build.dbdeploy.undofile" value="${build.dbdeploy.undo_dir}/undo-${DSTAMP}${TSTAMP}.sql" />

        <!-- generate the deployment scripts -->
        <dbdeploy 
            url="mysql:host=${db.host};dbname=${db.name}"
            userid="${db.user}" 
            password="${db.pass}" 
            dir="${build.dbdeploy.alters_dir}" 
            outputfile="${build.dbdeploy.deployfile}" 
            undooutputfile="${build.dbdeploy.undofile}" />

        <!-- execute the SQL - Use mysql command line to avoid trouble with large files or many statements and PDO -->
        <property name="mysql.command" value="${progs.mysql} -h${db.host} -u${db.user} -p${db.pass} ${db.name} &lt; ${build.dbdeploy.deployfile}" />
        <echo message="Executing command: ${mysql.command}" />
        <exec
            command="${mysql.command}"
            dir="${base.path}"
            checkreturn="true" />

    </target>

Why does dbdeploy generate a corrupt file?

Thans for your help!

Answer 1

A long time has passed since this question was asked, however I ran into the same problem and have managed to work out where Niels is coming from on this one.

I think we both ran into the problem because we both followed the popular tutorial on phing and dbdeploy by Dave Marshall here: http://davedevelopment.co.uk/2008/04/14/how-to-simple-database-migrations-with-phing-and-dbdeploy.html

In his example sql delat file he includes --// at the top which, if replaced with a comment in /* .... */ format instead, avoids this problem!

So I would say this is a bug in the tutorial, which is 8 years old now. Dave notes at the top of the tutorial that about 4 years ago he moved onto a different method, so it is kind of understandable that there is now a bug in the tutorial! I will submit a comment requesting an update though, because his page is a top ranking search result when searching for the topic so it would be good if we can save people the same problem we've had!

It is a shame phing didn't give a more detailed error report in the form of the SQL exception - there's an idea for a contribution to the dbdeploy script!