Comment by theptip | Hacker News Reader

Comment by theptip | Hacker News Reader

Parent Full thread

This would be great if so, but it doesn't look like it:

https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html

> DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement. This means that DDL statements cannot be performed within another transaction, within transaction control statements such as START TRANSACTION ... COMMIT, or combined with other statements within the same transaction.

Sounds like it just means that single ALTER/ADD/REMOVE statements now cannot fail in an intermediate state, but a sequence cannot be run in a transaction, so there's still a manual process to unpick a migration that failed half-way through. (That's always fun in production...)

Perhaps the groundwork has been laid though, and it'll be possible in 9.0.

This is correct. DDLs are (still) auto committed, but they now cannot fail in an intermediate state. Discussing full transactional DDL should also take into account DDL being Replicated and on Online Schema changes (perhaps more important for Web-scale). Then it starts to become complicated supporting full transactional DDL (at the same time). But transactional DDL is absolutely a desired feature. Geir

> DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement.

That sounds horrifying. Implicitly committing transactions could very well leave the database in an undefined / inconsistent state, no? That is, from the applications view, if I do:

   BEGIN;
     STATEMENT A;
     STATEMENT B;
   END;

I would expect either both or neither to succeed, but that paragraph would suggest that A could succeed, and the transaction be committed. (And who knows after that?)

Am I missing something?

> That sounds horrifying.

That means any previous version could fail during DDL execution and leave your metadata in an inconsistent state that you couldn't recover.

Now MySQL is about as good as any commercial database.

Reply on news.ycombinator.com