Lucid: Failed migrations are not rolled back

[simontabor]

Migrations should be run in transactions, just like in Knex, and they should be rolled back on failure.

My migration file is very simple...

const Schema = use('Schema')

class DocumentsSchema extends Schema {
  up () {
    this.create('documents', (table) => {
      table.increments()
      table.integer('user_id').unsigned().index().references('id').inTable('users')
    })
  }

  down () {
    this.drop('documents')
  }
}

module.exports = DocumentsSchema

I ran node ace migration:run. The migration failed with Cannot add foreign key constraint but the table was not deleted aka the transaction was not rolled back. The migration error itself is fine, but why wasn't the migration rolled back?

Discord discussion:

[simontabor]

After the failed migration, I just ran node ace migration:rollback and it ran ALL (~50) of the down() migrations (e.g. deleted 20 tables). After a successful migration this doesn't happen, so something is clearly wrong here.

[thetutlage]

So here are 2 questions, lets focus on the first one "Why migrations are not rolled back after error".

Yes it should

1. Can you share the database you are using?

Also it will just rollback that file and not all the commands executed by other migration files. Since Adonis allows you to use different database connections for each migration file, it is not possible to have a single transaction for multiple database connections.

So for each migration file, all the commands will be executed in a transaction and if something goes wrong, it should rollback. If not, it's a bug

[simontabor]

Database is MySQL, single connection (no different configuration across migration files)

So yes, I believe it's a bug that then also leaves the adonis_schema table in an invalid state, causing the rollback command to destroy everything.

[simontabor]

Experienced this again with a migration error relating to indexes. Database is left in an invalid state (between migrations).

[thetutlage]

@simontabor I tried it and all seems to be fine. Can you please the particular Schema file, that breaks the migration

[simontabor]

Is the schema I posted in the original issue comment alright?

[simontabor]

@thetutlage any updates?

[moltar]

MySQL is unable to transact DDL statements.

https://dev.mysql.com/doc/internals/en/transactions-notes-on-ddl-and-normal-transaction.html

[thetutlage]

Closing since not actionable

[zelucena]

I've just encountered this same issue (Adonis 4.1 Lucid 6.1.3) hence I wish it was disclosed in the docs. I'll looking for alternatives and will post here if I come up with anything actually useful.