0

I wrote a tool for our project, for applying sql update files that were committed, to the DB. Whenever run (on deployment), it calculates the list of update files which need to be applied, and applies them iniside a transaction.

Recently I became aware of an issue: mysql would implicitly commit a transaction, whenever DDL statements (like create) are executed. http://dev.mysql.com/doc/refman/5.0/en/implicit-commit.html

This is an issue for me, as sometimes an sql update file contains several statements, which as I understand will result in committing the transaction in the middle of executing the update file. This is a problem, because whenever a subsequent update will fail (which happens from time to time) I want to be able to rollback the transaction, or at least track which update files where applied (completely) and which were not.

Is there a way around the implicit transactions issue? I.e. is there a way to rollback a sequence of DDL statements whenever one of them fail?

Any other suggestions how I can handle the issue?

Thanks

Gidi

shealtiel
  • 8,020
  • 18
  • 50
  • 82

1 Answers1

2

No. MySQL does not support transactional DDL. You either need to separate your DDL statements from DML statements, or perhaps try to use migration tool like RuckUsing

Mchl
  • 61,444
  • 9
  • 118
  • 120
  • [RuckUsing](https://github.com/ruckus/ruckusing-migrations) is now hosted at [GitHub](https://github.com/ruckus/ruckusing-migrations). – trejder Apr 02 '14 at 09:12
  • 1
    Thanks @trejder , I've updated the link in my answer – Mchl Apr 02 '14 at 11:32