Transactional migrations are applied partially if fail in the middle

[vimmerru]

Describe the bug
I have the following mikro-orm.config.ts

export default {
    ...config,
    entities: Object.values(entities),
    migrations: {
        tableName: 'migrations', // name of database table with log of executed transactions
        path: './migrations', // path to the folder with migrations
        transactional: true, // wrap each migration in a transaction
        disableForeignKeys: true, // wrap statements with `set foreign_key_checks = 0` or equivalent
        allOrNothing: true, // wrap all migrations in master transaction
    },
};

I don't override isTransactional in migration classes

I noticed on debugging migrations that if i have an error in some statement in the middle of migration previous statements are still applied to the database. All It indicates that there is no migration transaction and master transaction too.

[B4nan]

Are you using the latest alpha? There are tests that check fired queries, the transaction is definitely there.

[vimmerru]

"mikro-orm": {
      "version": "3.0.0-alpha.31",
      "resolved": "https://registry.npmjs.org/mikro-orm/-/mikro-orm-3.0.0-alpha.31.tgz",

In the lock

[vimmerru]

Seems the problem cause is MySQL does not support transactions for DDL changes.

[B4nan]

Then please try latest, should be 32. Allornothing was not implemented in 31 afaik.

Here is snapshot from latest tests:

https://github.com/mikro-orm/mikro-orm/blob/dev/tests/__snapshots__/Migrator.test.ts.snap#L85

[vimmerru]

I will re-check, but anyway seems MySQL 5.7 i use can't rollback DDL changes at all. In 8.0 there can be some enhancements.

[B4nan]

Right, didn't know about that, apparently there is no way to rollback DDL changes so they are forcing implicit commit and running DDL statements in dedicated transactions (one per query).

I guess there is nothing we can do about it, will add a note to migrations docs at least.

Here is docs about it: https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html

(8.0 docs states the same: https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html)

[B4nan]

Added a note to the migrations docs.

[SantoJambit]

@B4nan I just stumbled over this, it seems MariaDB suffers from the same issue: https://mariadb.com/kb/en/start-transaction/#ddl-statements

It may be worth adding that to the docs as well. (Haven't tried it, just read the page).