Migrations hang on permissions scripts since v1.7.0

[kimjamia]

Describe the bug
Ever since v1.7.0 permissions scripts will just hang if a view script exists and when running with --trx on SQL Server. It doesn't even have to run it in the same migration. It's enough that the view script exists and has previously been run.

I originally reported this as #508 but then I couldn't correctly pinpoint the issue and we ended up on the wrong track.

To Reproduce

1. Install 1.7.0 or 1.8.0 with dotnet tool: dotnet tool update -g grate --allow-downgrade --version 1.8.0.
2. Create views\01.sql
3. Create permissions\Create.sql
4. Run grate --connectionstring "Server=localhost,1433;Database=***;TrustServerCertificate=True;User Id=sa;Password=***" --files . --trx
5. Observe that grate hangs on Running 'Create.sql'.

Example scripts:
views\01.sql:

CREATE VIEW [myview] AS
SELECT 1 as [Number]

permissions\Create.sql:

DROP USER IF EXISTS [myuser];
CREATE USER [myuser] WITHOUT LOGIN

You can also leave out the Create.sql, run migrations, then add the Create.sql and try to run migrations. The view has been created but Create.sql will hang.

Expected behavior
Create.sql completes and the user is created in the db.

Desktop (please complete the following information):

• OS: Windows
• Version: 1.7.0 or 1.8.0
• Installed with dotnet tool

[kimjamia]

FYI, I debugged this a bit and it hangs after the permissions scripts have run when AnsiSqlDatabase.InsertScriptRun(..) calls ExecuteAsync(..).

[wokket]

Hey @kimjamia, thanks for re-reporting this with a simple repro, it looks likely to be the same issue in #647 by @ronniehicks.

I've had similar problems (either changes committed on rollback, or deadlocks) all the way back to roundhouse :(

The nature of trying to make a transactional migration, potentially using two different SQL connections (one to the migrated database, one to the master or equivalent), on different DBMSs, when not all SQL is transactional (creating logins in older versions of MSSQL from memory?) means this is a hard problem.

In your specific example, I'd normally expect to see a create user in an up script that only runs once... the permissions scripts are normally for the equivalent of grant select on myview to myuser;... that doesn't change the fact there's a problem here, but might let you work around it in your real app.

[kimjamia]

Hey @wokket, I'm not entirely sure if that's the exact same issue. In my case grate hangs and then eventually fails with a timeout.

I understand it's a difficult problem but I've actually never had this issue before and I've been using RoundhousE and grate for years. And this thing is clearly regression from 1.6.3 to 1.7.0 because the same scripts work with 1.6.3 but not in 1.7.0.

Regarding my example, the script drops the user first and then creates it. This is very good way to ensure that the permissions of the user are as expected because they're rewired every time the migrations run.

I tested this issue a bit further. It seems that it can reproduced with any(?) folder as the first folder as long as the second folder is either permissions or afterMigration. The issue won't occur with e.g. indexes and runAfterOtherAnyTimeScripts but it will occur with runAfterOtherAnyTimeScripts and afterMigration.

[wokket]

I can't afford to kick users/disconnect servers by dropping the logins everytime I run a migration, but obviously everyone has different constraints :)

The 1.7.0 release rewrote huge amounts of stuff, there were some issues even at release time (1.6.3 was actually a back-port to the 1.6 branch because of those issues).

I still suspect it's the same underlying issues (or at least, in the same area of the code) as #647 because:

• as pointed out in that issue, the permissions folder is configured to not participate in the transaction, for reasons I can't determine from the code (along with BeforeMigration, AlterDatabase and AfterMigration, which all make sense), and
• that issue is on postgres/AWS, so I'm not suprised your symptoms for the issue (hang/deadlock vs permissions completing outside a failed trx)... there's every chance a bug was fixed for PgSql that broke MsSql or something, I just don't know.

[wokket]

I knew your issue sounded familiar, I hit it in v1.3.2 (with AlterDatabase), and raised it as #232 .

One suggested fix from Erik after some work (as part of #450, committed in #451) suggested using a config feature I wasn't aware of:

You can pass --folders=runAfterCreateDatabase=transactionHandling:autonomous or similar on the command line to alter transaction behaviour of individual folders from the default... you could also try disabling connection pooling in your connection string (pooling=false) which was the workaround previously, although the performance will drop (possibly significantly) as a result.

[kimjamia]

I think I figured out the issue. Thanks for pointing out that the permissions folder doesn't participate in the transaction.

When executing e.g. the views folder, it then writes to the ScriptsRun table using the "default connection" with a transaction open.
When it comes to the permissions folder it opens a new connection without committing the previous transaction. This keeps the ScriptsRun table locked and the new connection can't write to it. I did a really ugly hack where I just closed the connection, completed the scope and disposed it just before the permissions scripts run and it worked.

It would probably be a really good idea to understand why do permissions need to run in a different connection/transaction.

Also, I noticed that if you call AnsiSqlDatabase.OpenNewActiveConnection() when there is already an active connection, it will just forget about the old connection and there's probably no way to close it anymore. The permissions scripts call this function to create a new connection.