3 ms·
>preferably inside a transaction? MySQL does not support transactional DDL, unfortunately.
by brobinson 7y ago
>preferably inside a transaction?
MySQL does not support transactional DDL, unfortunately.
- zerg2k 7y agoThat’s no longer true since MySQL8 https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html
- brlewis 7y agoMySQL 8 was extremely new at the time this comment was posted. It's likely that mysql limitations were a big part of the thinking. The mention of pt-online-schema-change at the bottom makes me think so.
- etaioinshrdlu 7y agoSadly I don't think this is actually true. From that page: 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. Whereas with real transactional DDL, the DDL can be done within large transactions, multiple ALTER's could be rolled back, no changes are visible outside the transaction...
- evanelias 7y agoMySQL 8 DDL is atomic at the statement level, but cannot be combined with other SQL DDL or DML statements in transactions yet.
- shawnz 7y agoSo then don't do it inside a transaction. It still wouldn't be any less safe than their current approach of just not using FKs at all
- evanelias 7y agoAdding a foreign key to a massive table can take a long time, since all existing row data must be validated against the constraint. With logical replication, long operations like this can be quite disruptive, even with a multi-threaded logical replication scheme.