Menu

1 / 3

Transactional DDL in PostgreSQL, MySQL and CockroachDB: Which Failed Migrations Roll Back?

DEV Community: postgres·Son Tran·10 days ago
#2KVDyCuJ
Reading 0:00
15s threshold

Disclosure: I build Schemity , a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: If the third statement of a migration fails, PostgreSQL, SQL Server and SQLite undo the whole migration, and MySQL and CockroachDB do not. MySQL commits before (and after) every DDL statement, so earlier statements and any data changes made before them stay. CockroachDB does the same by default through autocommit_before_ddl , and with that setting off it can still commit the data changes while undoing the schema change.

Whether a failed migration rolls back depends on the database as much as on the BEGIN around it. With the migration in one transaction and a rollback on error, PostgreSQL, SQL Server and SQLite undo every statement when the third one fails. MySQL and CockroachDB keep the first two, and both also keep any data change the same transaction made before them.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.