How to align RoundhousE to one-time scripts that have changed due to git line-ending normalisation?

Question

I'm using RoundhousE to manage database migrations, and, due to bad legacy git config, the scripts have been committed to git with inconsistent line endings. I want to normalise these line-endings and, of course, in doing so RoundhousE detects hash changes to these files.

I accept this as correct behaviour from a RoundhousE perspective, but I'm unsure of the best path to remediate. We're already in production with the database.

I'm at a bit of a loss of best way to proceed, so not much research done so far, happy to discuss in comments with people with prior experience.

---

Option #1

Commit an UPDATE script first that bootstraps the [RoundhousE].[ScriptsRun] table of hashes to the correct hash for the new one-time scripts that have changed. Then commit the one-time scripts. I really dislike this approach, it feels dirty and hacky.

Answer 1

There are two command line options that might help: WarnOnOneTimeScriptChanges in combination with Baseline.

-w, --warnononetimescriptchanges | WarnOnOneTimeScriptChanges - Instructs RH to execute changed one time scripts (DDL/DML in Up folder) that have previously been run against the database instead of failing. A warning is logged for each one time scripts that is rerun. Defaults to false.

This option would execute your migrations, which is probably not what you want. That's why you should also specify --baseline.

--baseline | Baseline - This instructs RH to create an insert for its recording tables, but not to actually run anything against the database. Use this option if you already have scripts that have been run through other means (and BEFORE you start the new ones).

I haven't tried this, but these two switches together should record the new script hashes without actually executing anything.

Answer 2

You want to use --warnandignoreononetimescriptchanges

WarnAndIgnoreOnOneTimeScriptChanges - Instructs RH to ignore and update the hash of changed one time scripts (DDL/DML in Up folder) that have previously been run against the database instead of failing. A warning is logged for each onetime scripts that is rerun. Defaults to false.

The first run will yeild the following output:

21-Apr-2020 22:23:52    2019-12-17_1100_SomeScript.sql is a one time script that has changed since it was run. build    
21-Apr-2020 22:23:43    BASELINING: Recording 2019-12-17_1100_SomeScript.sql on 10.0.0.14,1433 - DATABASENAME.

Subsequent runs will show "Skipped".

Answer 3

Although the solution documented in other answers is entirely correct its often difficult to run RoundhousE with the extra flags if you have pipelines into the Cloud running migrations. RoundhouseE tracks which scripts it has run and stores the SQL file contents and a hash of it in a database table in the roundhouse schema named scriptsrun. So if you delete the row for the script in question RoundhouseE doesn't know the script has ever been run. All you need to do is:

1. Connect to database with SQL tool
2. Run the following SQL command to get rid of the record:

delete from roundhouse.scriptsrun where script_name = 'my_script_name.sql';

• Note 1: This isn't a suitable solution for dealing with production systems but works great as part of the development process.
• Note 2: If you run have previously run a script and delete RoundhousE's record you are advised to undo the actions performed by the script to avoid mis-matched state.