Search code examples
mysqlsqldatabaserollbacktransactional

Transactional ALTER statements in MySQL

I'm doing an update to MySQL Database which includes MySQL scripts that make ALTER TABLE sentences, as well as DIU sentences (delete, insert, update).

The idea is to make a transactional update, so if a sentence fails, a rollback is made, but if I put ALTER TABLE sentences or others specified in http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html an implicit commit is made, so I can't make a complete rollback, because the indicated operations remains commited.

I tried to use mysqldump to make a backup which is used in case of error (mysql returns distinct to zero), but it is too slow and can fail too.

What can I do? I need this to ensure that future updates are safe and not too slow, because databases contains between 30-100 GB of data.

Solution

  • dump and reload might be your best options instead of alter table.

    From mysql prompt or from the database script:

    select * from mydb.myt INTO OUTFILE '/var/lib/mysql/mydb.myt.out';
    drop table mydb.myt;
    create table    myt(your table ddl here)
    load data infile '/var/lib/mysql/mydb.myt.out' INTO TABLE mydb.myt;

    Check this out:

    http://everythingmysql.ning.com/profiles/blogs/whats-faster-than-alter

    I think it offers good guidance on "alternatives to alter".