Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a bash script to run migrations on our MySQL databases.

Is it possible to wrap migrations within a single transaction when running multiple migration scripts from the command line?

Occasionally we've had schema changes that require some data manipulation to migrate that into the new structure and maintain referential integrity.

I'd love to do something like: (assume all vars are set accordingly)

mysql `mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "START TRANSACTION;"`
for i in $( ls $MIGRATIONS_FOLDER/migration-* ); do

    # check database for record of this file
    result=`mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e 
            "SELECT id,filename,ran FROM tbl_migrations WHERE filename='$i'" 
            --batch --raw | wc -l`

    # if there is a record in tbl_migrations for this file, skip it
    if (($result > 1)); then
        continue
    fi;

    echo "Importing $i";

    result=`mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME < $i`

    if [[summatz screwed up]]
    then
        mysql `mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "ROLLBACK;"`
        exit
    fi


    mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e 
            "INSERT INTO tbl_migrations (filename, ran) VALUES ('$i', 1)"

    if [ "$i" = "$END" ]; then
        echo "Ending migration at user specified file $i"
        break
    fi

done
mysql `mysql -h$DB_HOST -u$DB_USER -p$DB_PASS $DB_NAME -e "COMMIT;"`
share|improve this question
1  
Schema changes can't be rolled back, they're not "transactional". Most (all?) DDL will implicitly commit before it runs. –  Mat Oct 2 '12 at 15:54
    
thanks @Mat - I think I'll look at an alternative. tyvm! –  Ian Wood Oct 2 '12 at 16:01
1  
Use PostgreSQL if you're looking for transactional DDL, works as it should. –  Frank Heikens Oct 2 '12 at 18:08
add comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.