Transaction not completely rolled back when combining options --transaction-mode Rollback with --out-of-order

[tuan-tu-tran]

Hello,

It seems the options --transaction-mode RollbackAll and --out-of-order are not working well together and it's not completely rolling back everything.

Say you have applied versions 2 and 3 and you add versions 1 and 4.
Running evolve migrate --transaction-mode RollbackAll --out-of-order [...] will report that it applied 1 and 4 and that it then rolled them back.
But in reality, only version 4 was rolled back and version 1 was not: it was applied and recorded in the changelog table.

That's the gist of it. Hereunder are a bunch of additional details, with a powershell script that reproduces and illustrates the issue

Thanks!

---

Working base scenario

Consider this base scenario where Evolve behaves as expected.

First, you have 2 versioned migrations applied: 2 and 3

Then you create a versioned migration 1 i.e. with a version number lower than 2 and 3.
Trying to apply this migration results in an error, something along the lines of

Validation failed: script V1__some_script.sql not found in the metadata table of applied migrations.

Because the database is already at version 3, it considers it's an anomaly that all of a sudden a script for version 1 appears.

You can instruct Evolve to apply version 1 anyway, using the --out-of-order switch. From the documentation:

Allows migrations to be run “out of order”. If you already have versions 1 and 3 applied, and now a version 2 is found, it will be applied too instead of being ignored.

Note: in this case, it's not really "being ignored" but it actually generates an error, but that's not the point.

So running:

evolve migrate --out-of-order true [the rest]

Will work as expected and Evolve will apply the version 1.

Incorrect behaviour

Imagine that after applying versions 2 and 3, before applying version 1, you want to test your migrations using the --transaction-mode RollbackAll option.

So running:

evolve migrate --out-of-order true --transaction-mode RollbackAll [the rest]

The expected behaviour is that the version 1 would be applied then rolled back.

But the observed behaviour is that:

• Evolve reports that version 1 was rolled back
• but in reality, it was applied

Bug reproduction

Here's a powershell script that illustrates this

$dbHost="(localdb)\MSSQLLocalDB"
$database="test_evolve"
$migrations="migrations"

$connectionString="Server=$dbHost;Database=$database"
# prepare an empty db and an empty migrations folder
Write-Output "Clean up and init"
sqlcmd -S $dbHost -Q "DROP DATABASE IF EXISTS $database"
sqlcmd -S $dbHost -Q "CREATE DATABASE $database"
Remove-Item -r -ErrorAction Ignore $migrations
mkdir $migrations > $null

# add V2 and V3 : create a table and insert a value
Write-Output "====================================================================="
Write-Output "add V2 and V3 : create a table and insert a value"
Write-Output "CREATE TABLE dbo.foo(Version decimal(2,1) NOT NULL)" > $migrations\V2__Create_table.sql
Write-Output "INSERT INTO foo VALUES (3)" > $migrations\V3__Insert_3.sql
evolve migrate sqlserver -c $connectionString -l $migrations
sqlcmd -S $dbHost -d $database -Q "SELECT * FROM foo"
<# Output
=====================================================================
add V2 and V3 : create a table and insert a value
Executing Migrate...
Evolve initialized.
Mark schema dbo as empty.
No metadata found.
Successfully applied migration V2__Create_table.sql in 10 ms.
Successfully applied migration V3__Insert_3.sql in 1 ms.
Database migrated to version 3. 2 migration(s) applied in 11 ms.
Version
-------
    3.0

(1 rows affected)
#>

# add V1: it fails without --out-of-order
Write-Output "====================================================================="
Write-Output "add V1: it fails without --out-of-order"
Write-Output "INSERT INTO foo VALUES (1)" > $migrations\V1__Insert_1.sql
evolve migrate sqlserver -c $connectionString -l $migrations
sqlcmd -S $dbHost -d $database -Q "SELECT * FROM foo"
<# Output:
=====================================================================
add V1: it fails without --out-of-order
Executing Migrate...
Evolve initialized.
Validation failed: script V1__Insert_1.sql not found in the metadata table of applied migrations.
Version
-------
    3.0

(1 rows affected)
#>

# retry V1 with --out-of-order and --transaction-mode RollbackAll : observe that it's not rolled back
Write-Output "====================================================================="
Write-Output "retry V1 with --out-of-order and --transaction-mode RollbackAll : observe that it's not rolled back"
evolve migrate --out-of-order true --transaction-mode RollbackAll sqlserver -c $connectionString -l $migrations
sqlcmd -S $dbHost -d $database -Q "SELECT * FROM foo"
<# Output:
=====================================================================
retry V1 with --out-of-order and --transaction-mode RollbackAll : observe that it's not rolled back
Executing Migrate...
Evolve initialized.
Successfully executed migration V1__Insert_1.sql in 12 ms.
Metadata validated.
Rollback migration V1__Insert_1.sql.
Database migration tested to version 3. 0 migration applied. 1 migration(s) tested in 12 ms.
Version
-------
    3.0
    1.0

(2 rows affected)
#>

# It says the migration was rolled back, but it wasn't: it was applied and recorded
Write-Output "====================================================================="
Write-Output "evolve info shows it was indeed applied"
evolve info sqlserver -c $connectionString
<# Output:
=====================================================================
evolve info shows it was indeed applied
+----+---------+-------------+--------------------------+---------------------+------------------+---------+----------------------------------+
| Id | Version | Category    | Description              | Installed on        | Installed by     | Success | Checksum                         |
+----+---------+-------------+--------------------------+---------------------+------------------+---------+----------------------------------+
| 1  | 0       | EmptySchema | Empty schema found: dbo. | 15/12/2021 13:23:34 | [redacted]       | True    |                                  |
+----+---------+-------------+--------------------------+---------------------+------------------+---------+----------------------------------+
| 2  | 2       | Migration   | Create table (12 ms)     | 15/12/2021 13:23:34 | [redacted]       | True    | EB601493474A8DFCD926CF785B69FF4F |
+----+---------+-------------+--------------------------+---------------------+------------------+---------+----------------------------------+
| 3  | 3       | Migration   | Insert 3 (13 ms)         | 15/12/2021 13:23:34 | [redacted]       | True    | E1E2B0DC52F97F638F45C4F113E6C9C8 |
+----+---------+-------------+--------------------------+---------------------+------------------+---------+----------------------------------+
| 4  | 1       | Migration   | Insert 1 (12 ms)         | 15/12/2021 13:23:36 | [redacted]       | True    | 19D29C94C3F0615EF17697C7FEFF282F |
+----+---------+-------------+--------------------------+---------------------+------------------+---------+----------------------------------+
#>

# if you try again: there's nothing to apply
Write-Output "====================================================================="
Write-Output "if you try again: there's nothing to apply"
evolve migrate sqlserver -c $connectionString -l $migrations
<# Output:
=====================================================================
if you try again: there's nothing to apply
Executing Migrate...
Evolve initialized.
Metadata validated.
Database is up to date. No migration needed.
#>

# For info: only the "lower" versions are not properly rolled back (as part of metadata validation) and the "higher" versions are indeed rolled back
# For example: add 1.1 and 4 : 1.1 will not be rolled back, but 4 will
Write-Output "====================================================================="
Write-Output "add V1.1 and V4 with --out-of-order and --transaction-mode RollbackAll : observe that 1.1 is not rolled back but 4 is"
Write-Output "INSERT INTO foo VALUES (1.1)" > $migrations\V1_1__Insert_1_1.sql
Write-Output "INSERT INTO foo VALUES (4)" > $migrations\V4__Insert_4.sql
evolve migrate --out-of-order true --transaction-mode RollbackAll sqlserver -c $connectionString -l $migrations
sqlcmd -S $dbHost -d $database -Q "SELECT * FROM foo"

<# Output:
=====================================================================
add V1.1 and V4 with --out-of-order and --transaction-mode RollbackAll : observe that 1.1 is not rolled back but 4 is
Executing Migrate...
Evolve initialized.
Successfully executed migration V1_1__Insert_1_1.sql in 12 ms.
Metadata validated.
Successfully executed migration V4__Insert_4.sql in 0 ms.
Rollback migration V4__Insert_4.sql.
Rollback migration V1_1__Insert_1_1.sql.
Database migration tested to version 4. 0 migration applied. 2 migration(s) tested in 12 ms.
Version
-------
    3.0
    1.0
    1.1

(3 rows affected)
#>

[lecaillon]

A real pleasure to read an issue so well written. thanks @tuan-tu-tran I'm gonna check

[lecaillon]

Ok i confirm your analysis. In fact out of order migration are executed out of the transaction scope. They are never rollbacked then. I'm gonna fix that in the next version of Evolve

[dacardona]

Thanks @lecaillon
Do you have an idea when version 3.1 will be released?