Describe the bug Grate successfully connects to the DB, checks what scripts to run, successfully runs procedures and indices scripts but then timeouts when running permissions scripts. This started happening when we upgraded from 1.6.2 to 1.7.0. Downgrading back to 1.6.2 solved the issue. Target database is an Azure SQL Database and authentication is via access token.

To Reproduce I will continue to investigate to come up with exact steps but so far what I know is that it timeouts on permissions scripts after upgrading to 1.7.0.

Expected behavior Permission scripts are run successfully since they were run successfully in 1.6.2.

Desktop (please complete the following information):

OS: Windows Server 2019
Version: 1.7.0

I tested this against a local MS SQL Server and it worked correctly but with Azure SQL Database and access token authentication grate seems to go into a loop. It runs through the scripts in the given folder X and then automagically starts running scripts from A under the Windows temp folder, then proceeds to folder B in the temp folder, etc.

Here's an excerpt from the output:

> dotnet grate --connectionstring "Server=***.database.windows.net;Database=kimtest" --accesstoken $sqltoken -f .\scripts\
Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest.
Looking in .\scripts\ for scripts to run.
Please press enter when ready to kick...

================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest.
Looking in C:\Users\***\AppData\Local\Temp\tmplrbkc2.tmp for scripts to run.
================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating kimtest from version 1.7.0 to 1.7.0.
 Versioning kimtest database with version 1.7.0.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "C:\Users\***\AppData\Local\Temp\tmplrbkc2.tmp\up". These should be one time only scripts.
--------------------------------------------------------------------------------
 No sql run, either an empty folder, or all files run against destination previously.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest)! You are now at version 1.7.0. All changes and backups can be found at "C:\Users\******\gratetest\output\migrations\kimtest\***.database.windows.net\2024-04-30T12_28_58.2587764_03_00".

Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest.
Looking in C:\Users\***\AppData\Local\Temp\tmp5425os.tmp for scripts to run.
================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating kimtest from version 1.7.0 to 1.7.0.
 Versioning kimtest database with version 1.7.0.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "C:\Users\***\AppData\Local\Temp\tmp5425os.tmp\up". These should be one time only scripts.
--------------------------------------------------------------------------------
 No sql run, either an empty folder, or all files run against destination previously.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest)! You are now at version 1.7.0. All changes and backups can be found at "C:\Users\******\gratetest\output\migrations\kimtest\***.database.windows.net\2024-04-30T12_28_59.4789308_03_00".

Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest.

...

Interestingly, the first time I ran grate, it seemed to run some internal migrations also:

> dotnet grate --connectionstring "Server=***.database.windows.net;Database=kimtest" --accesstoken $sqltoken
Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest.
Looking in . for scripts to run.
Please press enter when ready to kick...

================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest.
Looking in C:\Users\***\AppData\Local\Temp\tmpeicmwq.tmp for scripts to run.
================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating kimtest from version 0.0.0.0 to 1.7.0.
 Versioning kimtest database with version 1.7.0.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "C:\Users\***\AppData\Local\Temp\tmpeicmwq.tmp\up". These should be one time only scripts.
--------------------------------------------------------------------------------
  Running 'grate-internal/01_create_schema_grate.ENV.GrateInternalBoostrap-a61456d0-e00a-4933-b692-c6a5d7d51539.sql'.
  Running 'grate-internal/02_create_scripts_run_table.sql'.
  Running 'grate-internal/03_create_scripts_run_errors_table.sql'.
  Running 'grate-internal/04_create_version_table.sql'.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest)! You are now at version 1.7.0. All changes and backups can be found at "C:\Users\******\gratetest\scripts\output\migrations\kimtest\***.database.windows.net\2024-04-30T12_26_42.3776439_03_00".

Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest.
Looking in C:\Users\***\AppData\Local\Temp\tmpdh34or.tmp for scripts to run.
================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating kimtest from version 0.0.0.0 to 1.7.0.
 Versioning kimtest database with version 1.7.0.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "C:\Users\***\AppData\Local\Temp\tmpdh34or.tmp\up". These should be one time only scripts.
--------------------------------------------------------------------------------
  Running '02_create_scripts_run_table.sql'.
  Running '03_create_scripts_run_errors_table.sql'.
  Running '04_create_version_table.sql'.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest)! You are now at version 1.7.0. All changes and backups can be found at "C:\Users\******\gratetest\scripts\output\migrations\kimtest\***.database.windows.net\2024-04-30T12_26_44.3836389_03_00".

Initializing connections.

...

Hi, @kimjamia .

I understand this can be a bit confusing. I intended to hide the logs for the internal migrations/bootstrapping. As part of #482 , I've started using grate to handle the grate internal migration tables as well, to be able to evolve this structure as well. And, what tool best to handle table migrations than grate?

It's expected that there will be two internal migrations the first time you run grate 1.7.0+. This creates:

A set of "grate internal" tables that handle the versioning of the grate* tables
The grate tables, if they do not exist, using a migration that stores the history in the tables created above.

These will indeed be run from two different folders in the tmp directory.

After the two internal migrations has run, does not the grate migration (of your own database scripts) run as normal?

For what it's worth, I'm seeing the same behavior with permissions scripts on 1.7.0 against Azure SQL Managed Instance using normal SQL Authentication. Downgrading to 1.6.2 resolves the issue.

Thanks for chiming in, @DonalLOConnor !

To help me understand better, could you please give me some details about how you run grate? Is it the normal command-line version? Or docker? Are you also on Windows?

Could you please provide me with the command-line you use, and, also the connection string (without the secrets of course). I'm very interested in which type of authentication mechanism you are using. Is it Azure AD (aka Microsoft Entra) auth, or SQL server username and password (ref e.g. https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16#setting-microsoft-entra-authentication)

Additionally, after you see the output from the two "internal grate migrations", which are expected, does your permission scripts not run too? Does it hang? Timeout? What happens? Could you please provide me with a full output of the run (you can of course anonymise it if you have anything you don't want to share with the whole world)? It will make it easier to understand what happens, and solve the problem.

Is the problem there in grate 1.7.2 also (released yesterday)?

Erik,

I’m running grate as part of an Azure DevOps pipeline on a Microsoft-hosted build agent running on Microsoft’s latest Ubuntu image, with the following steps.

Install grate: dotnet tool install –global grate

Ensure the shell script checked out of git is runnable: chmod 775 ./dev.noninteractive.sh

Run the shell script containing the grate command: ./dev.noninteractive.sh

The contents of the shell script, dev.noninteractive.sh, are below.

!/bin/bash

if [[ -n "$GR_DEV_IDSERVICETOCDBDATAPIPELINE_USERNAME" && -n "$GR_DEV_IDSERVICETOCDBDATAPIPELINE_PASSWORD" && -n "$GR_DEV_DB_ADMIN_USERNAME" && -n "$GR_DEV_DB_ADMIN_PASSWORD" && -n "$GR_DEV_DB_DBNAME" && -n "$GR_DEV_DB_PUBLIC_ENDPOINT" ]]; then echo "All needed environment variables are defined. Grate will be run." grate --connectionstring="Server=$GR_DEV_DB_PUBLIC_ENDPOINT;Database=$GR_DEV_DB_DBNAME;User Id=$GR_DEV_DB_ADMIN_USERNAME;Password=$GR_DEV_DB_ADMIN_PASSWORD;" \ --commandtimeout=3600 \ --sqlfilesdirectory="./../IdServiceToCDbDataPipeline" \ --environment=dev \ --noninteractive \ --usertokens="GR_DEV_IDSERVICETOCDBDATAPIPELINE_USERNAME=$GR_DEV_IDSERVICETOCDBDATAPIPELINE_USERNAME" \ --usertokens="GR_DEV_IDSERVICETOCDBDATAPIPELINE_PASSWORD=$GR_DEV_IDSERVICETOCDBDATAPIPELINE_PASSWORD" \ --warnononetimescriptchanges \ --transaction else if [ -z "$GR_DEV_IDSERVICETOCDBDATAPIPELINE_USERNAME" ]; then echo "The environment variable GR_DEV_IDSERVICETOCDBDATAPIPELINE_USERNAME is not defined. Grate will not be run." fi if [ -z "$GR_DEV_IDSERVICETOCDBDATAPIPELINE_PASSWORD" ]; then echo "The environment variable GR_DEV_IDSERVICETOCDBDATAPIPELINE_PASSWORD is not defined. Grate will not be run." fi if [ -z "$GR_DEV_DB_ADMIN_USERNAME" ]; then echo "The environment variable GR_DEV_DB_ADMIN_USERNAME is not defined. Grate will not be run." fi if [ -z "$GR_DEV_DB_ADMIN_PASSWORD" ]; then echo "The environment variable GR_DEV_DB_ADMIN_PASSWORD is not defined. Grate will not be run." fi if [ -z "$GR_DEV_DB_DBNAME" ]; then echo "The environment variable GR_DEV_DB_DBNAME is not defined. Grate will not be run." fi if [ -z "$GR_DEV_DB_PUBLIC_ENDPOINT" ]; then echo "The environment variable GR_DEV_DB_PUBLIC_ENDPOINT is not defined. Grate will not be run." fi fi

The output of running the shell script is below.

2024-05-09T00:35:06.1697994Z ##[section]Starting: CmdLine 2024-05-09T00:35:06.1702054Z ============================================================================== 2024-05-09T00:35:06.1702179Z Task : Command line 2024-05-09T00:35:06.1702247Z Description : Run a command line script using Bash on Linux and macOS and cmd.exe on Windows 2024-05-09T00:35:06.1702383Z Version : 2.237.1 2024-05-09T00:35:06.1702443Z Author : Microsoft Corporation 2024-05-09T00:35:06.1702518Z Help : https://docs.microsoft.com/azure/devops/pipelines/tasks/utility/command-line 2024-05-09T00:35:06.1702634Z ============================================================================== 2024-05-09T00:35:06.3481699Z Generating script. 2024-05-09T00:35:06.3492768Z Script contents: 2024-05-09T00:35:06.3493296Z ./dev.noninteractive.sh 2024-05-09T00:35:06.3493634Z ========================== Starting Command Output =========================== 2024-05-09T00:35:06.3510748Z [command]/usr/bin/bash --noprofile --norc /home/vsts/work/_temp/64302b49-9dc5-49dd-9790-8b4bcbe04eee.sh 2024-05-09T00:35:06.3593266Z All needed environment variables are defined. Grate will be run. 2024-05-09T00:35:06.5799318Z [1m[34m[39m[22mInitializing connections. 2024-05-09T00:35:06.6427618Z Running grate v1.7.2 (build date 05/08/2024 06:03:24) against - Dev. 2024-05-09T00:35:06.6450597Z Looking in ./../IdServiceToCDbDataPipeline for scripts to run. 2024-05-09T00:35:06.6460115Z ================================================================================ 2024-05-09T00:35:06.6460608Z Setup, Backup, Create/Restore/Drop 2024-05-09T00:35:06.6461581Z ================================================================================ 2024-05-09T00:35:07.2916557Z ================================================================================ 2024-05-09T00:35:07.2916844Z Grate Structure 2024-05-09T00:35:07.2917035Z ================================================================================ 2024-05-09T00:35:07.3487021Z Initializing connections. 2024-05-09T00:35:07.3488446Z Running grate v1.7.2 (build date 05/08/2024 06:03:24) against - Dev. 2024-05-09T00:35:07.3494714Z Looking in /tmp/tmpbTIbdb.tmp for scripts to run. 2024-05-09T00:35:07.3495582Z ================================================================================ 2024-05-09T00:35:07.3495879Z Setup, Backup, Create/Restore/Drop 2024-05-09T00:35:07.3496085Z ================================================================================ 2024-05-09T00:35:07.3496337Z ================================================================================ 2024-05-09T00:35:07.3496519Z Grate Structure 2024-05-09T00:35:07.3496725Z ================================================================================ 2024-05-09T00:35:07.3500042Z ================================================================================ 2024-05-09T00:35:07.3500530Z Versioning 2024-05-09T00:35:07.3501055Z ================================================================================ 2024-05-09T00:35:07.3850114Z Migrating Dev from version 1.7.0 to 1.7.2. 2024-05-09T00:35:07.4546245Z Versioning Dev database with version 1.7.2. 2024-05-09T00:35:07.4554465Z ================================================================================ 2024-05-09T00:35:07.4555135Z Migration Scripts 2024-05-09T00:35:07.4555412Z ================================================================================ 2024-05-09T00:35:07.7563996Z Skipping 'BeforeMigration', beforeMigration does not exist. 2024-05-09T00:35:07.8023613Z Skipping 'AlterDatabase', alterDatabase does not exist. 2024-05-09T00:35:07.8027165Z Skipping 'Run After Create Database', runAfterCreateDatabase does not exist. 2024-05-09T00:35:07.8027867Z Skipping 'Run Before Update', runBeforeUp does not exist. 2024-05-09T00:35:07.8028835Z 2024-05-09T00:35:07.8029239Z Looking for Update scripts in "/tmp/tmpbTIbdb.tmp/up". These should be one time only scripts. 2024-05-09T00:35:07.8030127Z -------------------------------------------------------------------------------- 2024-05-09T00:35:07.8670789Z No sql run, either an empty folder, or all files run against destination previously. 2024-05-09T00:35:07.8673956Z -------------------------------------------------------------------------------- 2024-05-09T00:35:07.8674472Z 2024-05-09T00:35:07.8675336Z Skipping 'Run First After Update', runFirstAfterUp does not exist. 2024-05-09T00:35:07.8675664Z Skipping 'Functions', functions does not exist. 2024-05-09T00:35:07.8675980Z Skipping 'Views', views does not exist. 2024-05-09T00:35:07.8676222Z Skipping 'Stored Procedures', sprocs does not exist. 2024-05-09T00:35:07.8676478Z Skipping 'Triggers', triggers does not exist. 2024-05-09T00:35:07.8676710Z Skipping 'Indexes', indexes does not exist. 2024-05-09T00:35:07.8677040Z Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist. 2024-05-09T00:35:07.9095905Z Skipping 'Permissions', permissions does not exist. 2024-05-09T00:35:07.9509680Z Skipping 'AfterMigration', afterMigration does not exist. 2024-05-09T00:35:08.0413422Z 2024-05-09T00:35:08.0413889Z 2024-05-09T00:35:08.0415679Z grate v1.7.2 (build date 05/08/2024 06:03:24) has grated your database (Dev)! You are now at version 1.7.2. All changes and backups can be found at "/home/vsts/.local/share/grate/grate-internal-bootstrap/migrations/Dev/anonymized.database.windows.net_3342/2024-05-09T00_35_07.3480485_00_00". 2024-05-09T00:35:08.0416312Z 2024-05-09T00:35:08.0811480Z Initializing connections. 2024-05-09T00:35:08.0815512Z Running grate v1.7.2 (build date 05/08/2024 06:03:24) against - Dev. 2024-05-09T00:35:08.0816083Z Looking in /tmp/tmptdIf0p.tmp for scripts to run. 2024-05-09T00:35:08.0818731Z ================================================================================ 2024-05-09T00:35:08.0819899Z Setup, Backup, Create/Restore/Drop 2024-05-09T00:35:08.0820971Z ================================================================================ 2024-05-09T00:35:08.0822185Z ================================================================================ 2024-05-09T00:35:08.0823118Z Grate Structure 2024-05-09T00:35:08.0824209Z ================================================================================ 2024-05-09T00:35:08.0825205Z ================================================================================ 2024-05-09T00:35:08.0826149Z Versioning 2024-05-09T00:35:08.0827183Z ================================================================================ 2024-05-09T00:35:08.1155727Z Migrating Dev from version 1.7.0 to 1.7.2. 2024-05-09T00:35:08.1566144Z Versioning Dev database with version 1.7.2. 2024-05-09T00:35:08.1569773Z ================================================================================ 2024-05-09T00:35:08.1570553Z Migration Scripts 2024-05-09T00:35:08.1570872Z ================================================================================ 2024-05-09T00:35:08.1902495Z Skipping 'BeforeMigration', beforeMigration does not exist. 2024-05-09T00:35:08.2230407Z Skipping 'AlterDatabase', alterDatabase does not exist. 2024-05-09T00:35:08.2231590Z Skipping 'Run After Create Database', runAfterCreateDatabase does not exist. 2024-05-09T00:35:08.2232937Z Skipping 'Run Before Update', runBeforeUp does not exist. 2024-05-09T00:35:08.2235063Z 2024-05-09T00:35:08.2236255Z Looking for Update scripts in "/tmp/tmptdIf0p.tmp/up". These should be one time only scripts. 2024-05-09T00:35:08.2237370Z -------------------------------------------------------------------------------- 2024-05-09T00:35:08.4131959Z No sql run, either an empty folder, or all files run against destination previously. 2024-05-09T00:35:08.4133337Z -------------------------------------------------------------------------------- 2024-05-09T00:35:08.4133971Z 2024-05-09T00:35:08.4134473Z Skipping 'Run First After Update', runFirstAfterUp does not exist. 2024-05-09T00:35:08.4138395Z Skipping 'Functions', functions does not exist. 2024-05-09T00:35:08.4139086Z Skipping 'Views', views does not exist. 2024-05-09T00:35:08.4139422Z Skipping 'Stored Procedures', sprocs does not exist. 2024-05-09T00:35:08.4139711Z Skipping 'Triggers', triggers does not exist. 2024-05-09T00:35:08.4139996Z Skipping 'Indexes', indexes does not exist. 2024-05-09T00:35:08.4140343Z Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist. 2024-05-09T00:35:08.4558291Z Skipping 'Permissions', permissions does not exist. 2024-05-09T00:35:08.5034408Z Skipping 'AfterMigration', afterMigration does not exist. 2024-05-09T00:35:08.5763252Z 2024-05-09T00:35:08.5763917Z 2024-05-09T00:35:08.5765435Z grate v1.7.2 (build date 05/08/2024 06:03:24) has grated your database (Dev)! You are now at version 1.7.2. All changes and backups can be found at "/home/vsts/.local/share/grate/grate-internal/migrations/Dev/anonymized.database.windows.net_3342/2024-05-09T00_35_08.0815716_00_00". 2024-05-09T00:35:08.5766026Z 2024-05-09T00:35:08.5787823Z Initializing connections. 2024-05-09T00:35:08.5788980Z Running grate v1.7.2 (build date 05/08/2024 06:03:24) against - Dev. 2024-05-09T00:35:08.5789605Z Looking in /tmp/tmpaUofAf.tmp for scripts to run. 2024-05-09T00:35:08.5790290Z ================================================================================ 2024-05-09T00:35:08.5790557Z Setup, Backup, Create/Restore/Drop 2024-05-09T00:35:08.5790806Z ================================================================================ 2024-05-09T00:35:08.5791107Z ================================================================================ 2024-05-09T00:35:08.5791540Z Grate Structure 2024-05-09T00:35:08.5791772Z ================================================================================ 2024-05-09T00:35:08.5792058Z ================================================================================ 2024-05-09T00:35:08.5792274Z Versioning 2024-05-09T00:35:08.5792496Z ================================================================================ 2024-05-09T00:35:08.6115897Z Migrating Dev from version 1.7.0 to 1.7.2. 2024-05-09T00:35:08.6487407Z Versioning Dev database with version 1.7.2. 2024-05-09T00:35:08.6488015Z ================================================================================ 2024-05-09T00:35:08.6488570Z Migration Scripts 2024-05-09T00:35:08.6489397Z ================================================================================ 2024-05-09T00:35:08.6815109Z Skipping 'BeforeMigration', beforeMigration does not exist. 2024-05-09T00:35:08.7147064Z Skipping 'AlterDatabase', alterDatabase does not exist. 2024-05-09T00:35:08.7147883Z Skipping 'Run After Create Database', runAfterCreateDatabase does not exist. 2024-05-09T00:35:08.7148398Z Skipping 'Run Before Update', runBeforeUp does not exist. 2024-05-09T00:35:08.7148808Z 2024-05-09T00:35:08.7149283Z Looking for Update scripts in "/tmp/tmpaUofAf.tmp/up". These should be one time only scripts. 2024-05-09T00:35:08.7166944Z -------------------------------------------------------------------------------- 2024-05-09T00:35:08.7827069Z No sql run, either an empty folder, or all files run against destination previously. 2024-05-09T00:35:08.7827741Z -------------------------------------------------------------------------------- 2024-05-09T00:35:08.7828069Z 2024-05-09T00:35:08.7828366Z Skipping 'Run First After Update', runFirstAfterUp does not exist. 2024-05-09T00:35:08.7828677Z Skipping 'Functions', functions does not exist. 2024-05-09T00:35:08.7829280Z Skipping 'Views', views does not exist. 2024-05-09T00:35:08.7829558Z Skipping 'Stored Procedures', sprocs does not exist. 2024-05-09T00:35:08.7829971Z Skipping 'Triggers', triggers does not exist. 2024-05-09T00:35:08.7830238Z Skipping 'Indexes', indexes does not exist. 2024-05-09T00:35:08.7830600Z Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist. 2024-05-09T00:35:08.8241282Z Skipping 'Permissions', permissions does not exist. 2024-05-09T00:35:08.8654989Z Skipping 'AfterMigration', afterMigration does not exist. 2024-05-09T00:35:08.9383891Z 2024-05-09T00:35:08.9384525Z 2024-05-09T00:35:08.9386030Z grate v1.7.2 (build date 05/08/2024 06:03:24) has grated your database (Dev)! You are now at version 1.7.2. All changes and backups can be found at "/home/vsts/.local/share/grate/grate-internal-bootstrap/migrations/Dev/anonymized.database.windows.net_3342/2024-05-09T00_35_08.5784940_00_00". 2024-05-09T00:35:08.9388693Z 2024-05-09T00:35:08.9393858Z Initializing connections. 2024-05-09T00:35:08.9397305Z Running grate v1.7.2 (build date 05/08/2024 06:03:24) against - Dev. 2024-05-09T00:35:08.9397586Z Looking in /tmp/tmpIw2R3X.tmp for scripts to run. 2024-05-09T00:35:08.9397861Z ================================================================================ 2024-05-09T00:35:08.9398106Z Setup, Backup, Create/Restore/Drop 2024-05-09T00:35:08.9398339Z ================================================================================ 2024-05-09T00:35:08.9398630Z ================================================================================ 2024-05-09T00:35:08.9398836Z Grate Structure 2024-05-09T00:35:08.9399071Z ================================================================================ 2024-05-09T00:35:08.9399359Z ================================================================================ 2024-05-09T00:35:08.9399559Z Versioning 2024-05-09T00:35:08.9399793Z ================================================================================ 2024-05-09T00:35:08.9728446Z Migrating Dev from version 1.7.0 to 1.7.2. 2024-05-09T00:35:09.0095288Z Versioning Dev database with version 1.7.2. 2024-05-09T00:35:09.0095647Z ================================================================================ 2024-05-09T00:35:09.0095920Z Migration Scripts 2024-05-09T00:35:09.0096159Z ================================================================================ 2024-05-09T00:35:09.0426488Z Skipping 'BeforeMigration', beforeMigration does not exist. 2024-05-09T00:35:09.0763311Z Skipping 'AlterDatabase', alterDatabase does not exist. 2024-05-09T00:35:09.0767384Z Skipping 'Run After Create Database', runAfterCreateDatabase does not exist. 2024-05-09T00:35:09.0767748Z Skipping 'Run Before Update', runBeforeUp does not exist. 2024-05-09T00:35:09.0771897Z 2024-05-09T00:35:09.0772145Z Looking for Update scripts in "/tmp/tmpIw2R3X.tmp/up". These should be one time only scripts. 2024-05-09T00:35:09.0773109Z -------------------------------------------------------------------------------- 2024-05-09T00:35:09.1486255Z No sql run, either an empty folder, or all files run against destination previously. 2024-05-09T00:35:09.1487237Z -------------------------------------------------------------------------------- 2024-05-09T00:35:09.1487967Z 2024-05-09T00:35:09.1488446Z Skipping 'Run First After Update', runFirstAfterUp does not exist. 2024-05-09T00:35:09.1488766Z Skipping 'Functions', functions does not exist. 2024-05-09T00:35:09.1489043Z Skipping 'Views', views does not exist. 2024-05-09T00:35:09.1489321Z Skipping 'Stored Procedures', sprocs does not exist. 2024-05-09T00:35:09.1489616Z Skipping 'Triggers', triggers does not exist. 2024-05-09T00:35:09.1489880Z Skipping 'Indexes', indexes does not exist. 2024-05-09T00:35:09.1490592Z Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist. 2024-05-09T00:35:09.1898875Z Skipping 'Permissions', permissions does not exist. 2024-05-09T00:35:09.2318244Z Skipping 'AfterMigration', afterMigration does not exist. 2024-05-09T00:35:09.3247863Z 2024-05-09T00:35:09.3248133Z 2024-05-09T00:35:09.3249349Z grate v1.7.2 (build date 05/08/2024 06:03:24) has grated your database (Dev)! You are now at version 1.7.2. All changes and backups can be found at "/home/vsts/.local/share/grate/grate-internal/migrations/Dev/anonymized***.database.windows.net_3342/2024-05-09T00_35_08.9390750_00_00". 2024-05-09T00:35:09.3249821Z 2024-05-09T00:35:09.3250035Z ================================================================================ 2024-05-09T00:35:09.3250254Z Versioning 2024-05-09T00:35:09.3250466Z ================================================================================ 2024-05-09T00:35:09.3594989Z Migrating Dev from version 0.0.0.1 to 0.0.0.1. 2024-05-09T00:35:09.3977345Z Versioning Dev database with version 0.0.0.1. 2024-05-09T00:35:09.3977643Z ================================================================================ 2024-05-09T00:35:09.3977875Z Migration Scripts 2024-05-09T00:35:09.3978090Z ================================================================================ 2024-05-09T00:35:09.4327401Z Skipping 'BeforeMigration', beforeMigration does not exist. 2024-05-09T00:35:09.4851738Z Skipping 'AlterDatabase', alterDatabase does not exist. 2024-05-09T00:35:09.4852647Z Skipping 'Run After Create Database', runAfterCreateDatabase does not exist. 2024-05-09T00:35:09.4853153Z Skipping 'Run Before Update', runBeforeUp does not exist. 2024-05-09T00:35:09.4855824Z 2024-05-09T00:35:09.4856198Z Looking for Update scripts in "./../IdServiceToCDbDataPipeline/up". These should be one time only scripts. 2024-05-09T00:35:09.4859020Z -------------------------------------------------------------------------------- 2024-05-09T00:35:09.6326200Z Running '000_IdSToCDbDP_CreateLogin.dev.env.sql'. 2024-05-09T00:35:09.7626165Z Running '001_IdSToCDbDP_CreateUser.dev.env.sql'. 2024-05-09T00:35:09.8683462Z Running '002_IdSToCDbDP_AlterRoles.dev.env.sql'. 2024-05-09T00:35:09.9691447Z Running '003_IdSToCDbDP_InsightlySchema.sql'. 2024-05-09T00:35:10.0682724Z Running '004_IdSToCDbDP_45.sql'. 2024-05-09T00:35:10.1773151Z -------------------------------------------------------------------------------- 2024-05-09T00:35:10.1773505Z 2024-05-09T00:35:10.1776647Z Skipping 'Run First After Update', runFirstAfterUp does not exist. 2024-05-09T00:35:10.1780641Z Skipping 'Functions', functions does not exist. 2024-05-09T00:35:10.1781345Z Skipping 'Views', views does not exist. 2024-05-09T00:35:10.1781513Z 2024-05-09T00:35:10.1783933Z Looking for Stored Procedures scripts in "./../IdServiceToCDbDataPipeline/sprocs". 2024-05-09T00:35:10.1784315Z -------------------------------------------------------------------------------- 2024-05-09T00:35:10.2114572Z Running 'IdSToCDbDP_AdvancedMD_sp_GetPatientsChangedOrWithoutROMTechId.sql'. 2024-05-09T00:35:10.3241701Z Running 'IdSToCDbDP_Okta_sp_GetUsersChangedOrWithoutROMTechId.sql'. 2024-05-09T00:35:10.3932469Z -------------------------------------------------------------------------------- 2024-05-09T00:35:10.3932780Z 2024-05-09T00:35:10.3933297Z Skipping 'Triggers', triggers does not exist. 2024-05-09T00:35:10.3933675Z Skipping 'Indexes', indexes does not exist. 2024-05-09T00:35:10.3934115Z Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist. 2024-05-09T00:35:10.4366341Z 2024-05-09T00:35:10.4367165Z Looking for Permissions scripts in "./../IdServiceToCDbDataPipeline/permissions". These scripts will run every time. 2024-05-09T00:35:10.4367970Z -------------------------------------------------------------------------------- 2024-05-09T00:35:10.4797510Z Running 'IdSToCDbDP_AdvancedMD_execute_perms.dev.env.sql'. 2024-05-09T00:36:10.3841251Z IdSToCDbDP_AdvancedMD_execute_perms.dev.env.sql: Cannot find the user 'dev_IdServiceToCDbDataPipeline', because it does not exist or you do not have permission. 2024-05-09T00:36:10.8047242Z Skipping 'AfterMigration', afterMigration does not exist. 2024-05-09T00:36:10.8142263Z An error occurred: Migration failed due to the following errors: 2024-05-09T00:36:10.8142685Z 2024-05-09T00:36:10.8144547Z Permissions ("permissions"): 2024-05-09T00:36:10.8145119Z -------------------------------------------------------------------------------- 2024-05-09T00:36:10.8209122Z IdSToCDbDP_AdvancedMD_execute_perms.dev.env.sql: Cannot find the user 'dev_IdServiceToCDbDataPipeline', because it does not exist or you do not have permission. 2024-05-09T00:36:10.8212576Z (0,0): grant execute on schema::AdvancedMD to {{GR_DEV_IDSERVICETOCDBDATAPIPELINE_USERNAME}} 2024-05-09T00:36:10.8213034Z ^ Cannot find the user 'dev_IdServiceToCDbDataPipeline', because it does not exist or you do not have permission. 2024-05-09T00:36:10.8305798Z 2024-05-09T00:36:10.8355171Z ##[error]Bash exited with code '1'. 2024-05-09T00:36:10.8379535Z ##[section]Finishing: CmdLine

The permissions script does run, but appears not to have access to the user created in the "Update" scripts. The problem manifests itself slightly differently in 1.7.2, with the permissions script timing out with the message "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." after about 30 seconds. Reverting to 1.6.2 resolves the issue.

If there's anything else you need, please let me know, and please understand that you have my (and likely many others') unrelenting gratitude for your work on RoundhousE and grate.

Thanks,

Dó

From: Erik A. Brandstadmoen @.> Sent: Thursday, May 9, 2024 6:26 AM To: erikbra/grate @.> Cc: DonalLOConnor @.>; Mention @.> Subject: Re: [erikbra/grate] Migrations keep looping / timeout on permissions scripts on v1.7.0 (Issue #508)

Thanks for chiming in, @DonalLOConnorhttps://github.com/DonalLOConnor !

To help me understand better, could you please give me some details about how you run grate? Is it the normal command-line version? Or docker? Are you also on Windows?

Is the problem there in grate 1.7.2 also (released yesterday)?

— Reply to this email directly, view it on GitHubhttps://github.com/erikbra/grate/issues/508#issuecomment-2102392218, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEB45XBW4CY3T256WZKL3GDZBNFMPAVCNFSM6AAAAABG7TENWSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCMBSGM4TEMRRHA. You are receiving this because you were mentioned.Message ID: @.**@.>>

Thanks a lot for this output, @DonalLOConnor !

Explanation

This output is, although a bit confusing, actually just as expected. For grate 1.7+, I run some internal grate migrations (two) on the beginning of each migration, to make sure the tables used for storing the history of the migrations are up-to-date.

The intent was that this output shouldn't show, as it is of no interest to anyone but the grate developers, but due to a bug, the logs hit the end-users as well. This is fixed in release 1.7.3.

To explain, the migrations after these lines:

2024-05-09T00:35:07.3494714Z Looking in /tmp/tmpbTIbdb.tmp for scripts to run.

and

2024-05-09T00:35:08.0816083Z Looking in /tmp/tmptdIf0p.tmp for scripts to run.

are the internal migrations. Your actual migration starts on top, from:

2024-05-09T00:35:06.6427618Z Running grate v1.7.2 (build date 05/08/2024 06:03:24) against *** - Dev.

up until the line:

2024-05-09T00:35:07.2916844Z Grate Structure

and, then again from:

2024-05-09T00:35:09.3250254Z Versioning

all between these lines are the internal grate migrations, and this output is removed in 1.7.3.

TLDR;

Now, to your actual issue, it's difficult to know whether this is a grate bug or not. It says is cannot find the user dev_IdServiceToCDbDataPipeline. Are you absolutely sure that is the name of the user created in the scripts where you create the user? Do you use tokens in the "create user" scripts? Could it be that the casing is different?

Could you please retry the scripts on grate 1.7.3, where we get rid of all the confusing logging, and see if you still think there is a grate error?

Could you please try grate 1.7.3 too, and see if you still experience any hangs/strange behaviour, @kimjamia ?

I created a new empty db and ran grate locally and waited until it exited (previously I killed it after I saw it ran multiple migrations). The DB is an Azure SQL Database and I'm using access token auth. It didn't time out, though. The timeout was happening in our Azure DevOps pipeline.

Basically it's running 5 sets of migrations here:

Create the grate schema and the tables.
Run them again (except 01_create_schema_grate.ENV.GrateInternalBoostrap).
Run the migrations that add a status column and unicode support.
Run them again.
Run my migrations.

The tables it created:

The output:

PS > dotnet grate --connectionstring "Server=***.database.windows.net;Database=kimtest2" --accesstoken $sqltoken -f .\scripts\
Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest2.
Looking in .\scripts\ for scripts to run.
Please press enter when ready to kick...

================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest2.
Looking in C:\Users\***\AppData\Local\Temp\tmpd2owzz.tmp for scripts to run.
================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating kimtest2 from version 0.0.0.0 to 1.7.0.
 Versioning kimtest2 database with version 1.7.0.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "C:\Users\***\AppData\Local\Temp\tmpd2owzz.tmp\up". These should be one time only scripts.
--------------------------------------------------------------------------------
  Running 'grate-internal/01_create_schema_grate.ENV.GrateInternalBoostrap-a61456d0-e00a-4933-b692-c6a5d7d51539.sql'.
  Running 'grate-internal/02_create_scripts_run_table.sql'.
  Running 'grate-internal/03_create_scripts_run_errors_table.sql'.
  Running 'grate-internal/04_create_version_table.sql'.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest2)! You are now at version 1.7.0. All changes and backups can be found at "C:\Users\***\gratetest\output\migrations\kimtest2\***.database.windows.net\2024-05-13T11_34_19.7164588_03_00".

Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest2.
Looking in C:\Users\***\AppData\Local\Temp\tmpqxg4hs.tmp for scripts to run.
================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating kimtest2 from version 0.0.0.0 to 1.7.0.
 Versioning kimtest2 database with version 1.7.0.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "C:\Users\***\AppData\Local\Temp\tmpqxg4hs.tmp\up". These should be one time only scripts.
--------------------------------------------------------------------------------
  Running '02_create_scripts_run_table.sql'.
  Running '03_create_scripts_run_errors_table.sql'.
  Running '04_create_version_table.sql'.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest2)! You are now at version 1.7.0. All changes and backups can be found at "C:\Users\***\gratetest\output\migrations\kimtest2\***.database.windows.net\2024-05-13T11_34_21.6520724_03_00".

Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest2.
Looking in C:\Users\***\AppData\Local\Temp\tmpaasrkt.tmp for scripts to run.
================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating kimtest2 from version 1.7.0 to 1.7.0.
 Versioning kimtest2 database with version 1.7.0.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "C:\Users\***\AppData\Local\Temp\tmpaasrkt.tmp\up". These should be one time only scripts.
--------------------------------------------------------------------------------
  Running 'grate-internal/01_version_add_status_column.sql'.
  Running 'grate-internal/02_scripts_run_unicode_support.sql'.
  Running 'grate-internal/03_scripts_run_errors_unicode_support.sql'.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest2)! You are now at version 1.7.0. All changes and backups can be found at "C:\Users\***\gratetest\output\migrations\kimtest2\***.database.windows.net\2024-05-13T11_34_22.8775005_03_00".

Initializing connections.
Running grate v1.7.0 (build date 04/29/2024 17:52:09) against ***.database.windows.net - kimtest2.
Looking in C:\Users\***\AppData\Local\Temp\tmp2d2iue.tmp for scripts to run.
================================================================================
Setup, Backup, Create/Restore/Drop
================================================================================
================================================================================
Grate Structure
================================================================================
================================================================================
Versioning
================================================================================
 Migrating kimtest2 from version 1.7.0 to 1.7.0.
 Versioning kimtest2 database with version 1.7.0.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.

Looking for Update scripts in "C:\Users\***\AppData\Local\Temp\tmp2d2iue.tmp\up". These should be one time only scripts.
--------------------------------------------------------------------------------
  Running '01_version_add_status_column.sql'.
  Running '02_scripts_run_unicode_support.sql'.
  Running '03_scripts_run_errors_unicode_support.sql'.
--------------------------------------------------------------------------------

Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.
Skipping 'Permissions', permissions does not exist.
Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest2)! You are now at version 1.7.0. All changes and backups can be found at "C:\Users\***\gratetest\output\migrations\kimtest2\***.database.windows.net\2024-05-13T11_34_24.2423268_03_00".

================================================================================
Versioning
================================================================================
 Migrating kimtest2 from version 0.0.0.0 to 0.0.0.1.
 Versioning kimtest2 database with version 0.0.0.1.
================================================================================
Migration Scripts
================================================================================
Skipping 'BeforeMigration', beforeMigration does not exist.
Skipping 'AlterDatabase', alterDatabase does not exist.
Skipping 'Run After Create Database', runAfterCreateDatabase does not exist.
Skipping 'Run Before Update', runBeforeUp does not exist.
Skipping 'Update', up does not exist.
Skipping 'Run First After Update', runFirstAfterUp does not exist.
Skipping 'Functions', functions does not exist.
Skipping 'Views', views does not exist.
Skipping 'Stored Procedures', sprocs does not exist.
Skipping 'Triggers', triggers does not exist.
Skipping 'Indexes', indexes does not exist.
Skipping 'Run after Other Anytime Scripts', runAfterOtherAnyTimeScripts does not exist.

Looking for Permissions scripts in ".\scripts\permissions". These scripts will run every time.
--------------------------------------------------------------------------------
  Running 'test.sql'.
--------------------------------------------------------------------------------

Skipping 'AfterMigration', afterMigration does not exist.

grate v1.7.0 (build date 04/29/2024 17:52:09) has grated your database (kimtest2)! You are now at version 0.0.0.1. All changes and backups can be found at "C:\Users\***\AppData\Local\grate\migrations\kimtest2\***.database.windows.net\2024-05-13T11_34_18.9493433_03_00".

@kimjamia, thank you. This makes perfect sense, and this is as expected (even though you shouldn't be exposed to it, as I planned to turn the logging off, which will be fixed in next release).

The tables that are called:

GrateScriptsRun
GrateScriptsErrors
GrateVersion are used for versioning the grate tables themselves (i.e. ScriptsRun, ScriptsRunErrors, and GrateVersion).

I thought about putting the "internal migrations" in the "normal" grate tables, but didn't want to interfere with people's "real" migrations.

So, as the internal folder structure for Grate evolves, the migrations of those tables will be in the Grate* tables.

There are two sets of migrations for the internal tables, one to update these tables above, and one that updates the Grate* tables that are used for user migrations. They should both evolve.

And, there is a bootstrapping the first time, to create the tables, and there is a migration that adds a column and changes some columns. So two times two internal migrations makes sense for the first 1.7 run.

You can look in the entries in the grate.GrateScriptsRun table too, it tells the same story. E.g:

@erikbra, after much experimentation, I was able to replicate the issue in version 1.6.2, and it's less an issue with grate than it is an issue with nasty auto-generated passwords with shell-unfriendly symbol characters. These characters likely prevented the "create login" script from completing properly, leaving a transaction hanging or in some sad state, and preventing the later permissions script from being able to find the database user created from that login. Running grate locally in a Git Bash shell succeeded, whereas running it in Azure DevOps's build pipeline running in a Ubuntu Bash shell failed, but the solution to the difference was to avoid shell-unfriendly characters in the password to begin with, so not a grate issue. I've adjusted the symbol usage in the auto-generated passwords, and that's resolved the issue. Thanks for the guidance.

Aha, @DonalLOConnor - that explains it. Great to hear you were able to solve your particular issue too! Shell escaping in Azure Devops tasks are a pain. I've experienced just having to give up some times, when you e.g. want to actually provide a string in quotes to a task (e.g. a list of tags to build a docker image from using dotnet publish to docker).

Anyway, I'm thinking, if you see any things that could be better if we make a custom-built native Devops-task for grate, please let me know. My personal experience is that some of the existing devops tasks out there are actually less convenient to work with that just using a shell, but your experience may differ. If you have any good ideas, please feel free to file an issue, or start a discussion around this!

I'm also thinking a bit around how to more easily configure grate using the environment (e.g. environment variables). I don't think System.CommandLine, which I'm using for parsing the arguments is going to be released in the current form at all. I want to wait a bit, and see if there is any movement around native command-argument parsing in the dotnet runtime coming in .NET 9 (coming in October). If not, I might rewrite it to use plain, old .NET Configuration providers. We'll see :)

@kimjamia - thanks a lot for you simple repro. That helped me a lot being able to understand what happened. Given my explanation, do you still think we have a bug, or was it just difficult to understand what happened?

Closing this, as I think we solved it, and I'm getting no more feedback.

erikbra / grate

Migrations keep looping / timeout on permissions scripts on v1.7.0 #508

!/bin/bash

Explanation

TLDR;