3 ms·

The best part is transactional ddl statements. You can do your database migration in a transaction, if something fails the transaction is rolled back compared t

by tpetry 6y ago

The best part is transactional ddl statements. You can do your database migration in a transaction, if something fails the transaction is rolled back compared to an invalid state with mysql.

chousuke 6y ago
Being used to PostgreSQL, this one actually bit me once during a production upgrade. A database migration failed due to a Galera transaction size limit that I unfortunately hadn't caught testing the migrations on a single database, and I had to restore from the pre-upgrade backup before resolving the issue and continuing. It wasn't a major issue (the upgrade finished well within the acceptable downtime window), but until then I had assumed that the migrations would be transactional because of course they should be, it's a database! Now I know better than to assume everything you do in a database is transactional. :P
- spuz 6y ago
  Which database are you talking about? Postgres or Mysql?
  - llarsson 6y ago
    Something that happened when they were on MySQL, before PostgreSQL. The phrasing at the start and "Galera" mention shows this.
derekperkins 6y ago
That was added a couple years ago in MySQL 8, as were window functions and arbitrary checks. The two databases continue to fill in the gaps that were historically reasons to choose one over the other.
- paulryanrogers 6y ago
  Are Mysql 8 DDL changes transactional across tables? If not then in what sense are they transactional? Because to this day I still have to deal with locking problems unless my changes are within a narrow window: last column, no on-update or on-delete, not changing type, etc.
- tpetry 6y ago
  Mysql 8 supports atomic ddl, which means if you drop two tables in a single DROP TABLE query either both are deleted or none. But it does not support postgresql‘s transactional ddl feature where you can start a transaction change the structure of multiple tables, change some data etc. and commit at the end. Mysql 8 males ddl changes atomic, but does not support transactional mode where you could rollback them.

by tpetry 6y ago

The best part is transactional ddl statements. You can do your database migration in a transaction, if something fails the transaction is rolled back compared to an invalid state with mysql.

chousuke 6y ago

Being used to PostgreSQL, this one actually bit me once during a production upgrade. A database migration failed due to a Galera transaction size limit that I unfortunately hadn't caught testing the migrations on a single database, and I had to restore from the pre-upgrade backup before resolving the issue and continuing. It wasn't a major issue (the upgrade finished well within the acceptable downtime window), but until then I had assumed that the migrations would be transactional because of course they should be, it's a database! Now I know better than to assume everything you do in a database is transactional. :P

spuz 6y ago
Which database are you talking about? Postgres or Mysql?
- llarsson 6y ago
  Something that happened when they were on MySQL, before PostgreSQL. The phrasing at the start and "Galera" mention shows this.

derekperkins 6y ago

That was added a couple years ago in MySQL 8, as were window functions and arbitrary checks. The two databases continue to fill in the gaps that were historically reasons to choose one over the other.

paulryanrogers 6y ago
Are Mysql 8 DDL changes transactional across tables? If not then in what sense are they transactional? Because to this day I still have to deal with locking problems unless my changes are within a narrow window: last column, no on-update or on-delete, not changing type, etc.
tpetry 6y ago
Mysql 8 supports atomic ddl, which means if you drop two tables in a single DROP TABLE query either both are deleted or none. But it does not support postgresql‘s transactional ddl feature where you can start a transaction change the structure of multiple tables, change some data etc. and commit at the end. Mysql 8 males ddl changes atomic, but does not support transactional mode where you could rollback them.