When database migration fails, it partly applies changes

[karh]

When a database migration (change) fails, it applies part of the knex.
For example I had faulty Knex that threw an error that a column doesn’t exist.

It did however apply other lines which created new columns in the table.

However the migration isn’t applied since it failed.

After fixing the initial error, it will cause new errors because it’s trying to create columns that already were created in the failing migration…

Is there any way to prevent this? I don’t know much about Knex but can’t imagine that this is default behaviour.

Now the only fix is to manually undo the schema changes using an external db manager.

[George]

Well usually such things are done with database transactions.
But unfortunately transactions are not supported in database mutation statements. So you can’t undo the already executed statements.

Maybe we can generate more enhanced knex migration script that has an error checking around each statement to execute but then it has to keep track of the ones executed above and undo them in case of error…

[Apple]

If a migration fails, run the revert part

[tbvgl]

This. I will automate that in your CI/CD pipeline @karh

[karh]

@Apple @tbvgl
The revert part can’t be executed, at least not using the UI.
This button only shows up for migrations that didn’t error

.

I haven’t tried using the terminal. I also doubt that will work as expected because imagine there are 2 linesi. the ‘up’ statement: and line 2 fails…
Then the ‘down’ statement tries to also undo whatever line 2 was and fail.

@George Hmm what can also help is better Knex generation in general, I will report the next errors I get that are due to the the generation logic

[tbvgl]

@karh this is not a Wappler issue but a limitation when you use knex with mysql. https://stackoverflow.com/questions/59425666/knex-migration-fail-but-doesnt-revert-changes

Let’s finally switch to Postgres

[Apple]

@karh

The revert part can’t be executed, at least not using the UI.

I replied to George, not sure if Discourse registered that - I was meaning he didn’t have to do lots of work

@karh

I also doubt that will work as expected because imagine there are 2 linesi. the ‘up’ statement: and line 2 fails…
Then the ‘down’ statement tries to also undo whatever line 2 was and fail.

Your thinking is correct, it would error, but it would also successfully partially revert too

[karh]

@Apple

@karh

I also doubt that will work as expected because imagine there are 2 linesi. the ‘up’ statement: and line 2 fails…

Then the ‘down’ statement tries to also undo whatever line 2 was and fail.

Your thinking is correct, it would error, but it would also successfully partially revert too

Okay then I will have to get better with the command line and reverting like that
Or hope @George can build this into Wappler.

@tbvgl

Let’s finally switch to Postgres

So generous of you to donate your time to our charity and make this switch completely free of charge

[George]

In general it is advisable to keep the changes small, so when one fails it can be just reverted.

If you have many changes in a single change file (migration) and some of them are successful but other fails, it will be difficult to recover.

So keep it small and atomic.