Menu
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?
492
MySQL does not support transactions for DDL changes. To understand the implications of this, we can turn to the MySQL documentation on the Transaction Life Cycle. The normal transaction is committed in a similar way (by going over all engines in thd->transaction.
Now, what does true “transactional DDL” mean? It means that all statements should be ACID, regardless of whether they are DML or DDL statements. In practice, with most databases, DDL statements break the transactionality of the enclosing transaction and cause anomalies.
In MySQL, the transactions begin with the statement BEGIN WORK and end with either a COMMIT or a ROLLBACK statement. The SQL commands between the beginning and ending statements form the bulk of the transaction.
MySQL supports the ACID properties for a transaction-safe Relational Database Management System. Let's see each of these properties in brief. A (Atomicity): Transactions support atomicity by running ALL or NONE – i.e. either all the statements of a transaction would be executed or NONE of them.
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.
94
One possibility is doing DDL changes in a non-destructive-manner, which would include:
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
