26

I was a little surprised to discover that DDL statements (alter table, create index etc) implicitly commit the current transaction in MySQL. Coming from MS SQL Server, the ability to do database alterations in a transaction locally (that was then rolled back) was an important part of my workflow. For continuous integration, the rollback was used if the migration hiccuped for any reason, so that at least we did not leave the database in a half-migrated state.

How do people solve these two problems when using MySQL with migrations and continuous integration?

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
sennett
  • 8,014
  • 9
  • 46
  • 69
  • 2
    Cross posted on DBA: http://dba.stackexchange.com/q/90794/18273 – sennett Feb 02 '15 at 13:08
  • 6
    Welcome to the wonderfull world of MySQL :) – David Soussan Mar 16 '15 at 05:36
  • Are you sure in SQL Server DDL command does not commit the transaction? because in oracle DDL command commit the transaction also. – Mostafa Vatanpour May 30 '16 at 20:05
  • 1
    WWWOOOOOOOOOOOWWW. Oracle/MySQL sucks if that's true. Unbelievable. And yes, we're sure DDL statements participate in the current transaction in Microsoft SQL Server and do not auto-commit the transaction after each statement (wow) like Oracle does. How do you think EntityFramework is able to model code-first migrations that can be applied and rolled back transactionally while including many DDL statements in them. That makes MySQL fundamentally incompatible with something like EntityFramework. Why do they even bother to integrate with it? – Triynko Mar 27 '19 at 21:04

2 Answers2

4

DDL statements cause an implicit commit and there is nothing you can do about it. There is no way to stop this behaviour.

Which DDL statements have this behaviour changes over time so you need to check for your version.

5.1 http://dev.mysql.com/doc/refman/5.1/en/implicit-commit.html
5.5 http://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html
5.6 http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

When we are just extending the schema, new tables/columns/views/procs/etc, that will not affect existing code then automation is OK, just check for errors and fix them.

When they will affect existing code then you need to devise a strategy on a case by case basis. Since there is no rollback you need your own backout plan and you need to test it thoroughly.

Since it is case-by-case there is not a lot that I can offer in the way of help for your particular situation.

David Soussan
  • 2,698
  • 1
  • 16
  • 19
2

One possibility is doing DDL changes in a non-destructive-manner, which would include:

  • split logic in DDL/DCL (+1 to reverse all) and DML
  • run only the DDL/DCL script adding columns, new tables, ..
  • depending on result:
    • on success, apply the DML changes,
    • on fail, apply reverse DDL/DCL script removing the stuff you wanted to add in second step (obviously with some errors "does not exist" depending on how far step 1 got)
  • remove what is not needed anymore, drop old columns/tables
Alim Özdemir
  • 2,396
  • 1
  • 24
  • 35