Checksum warnings - "this migration has changed since it was deployed"

PeterDanielsPeterDaniels Posts: 89 Bronze 3
edited April 10, 2019 8:28PM in SQL Change Automation
I'm seeing a ton of warnings in the output for New-DatabaseReleaseArtifact that say:

"WARNING: C:\[...]\AppData\Local\Temp\DLM Automation\ifp4iatf.30a\Migrations\1.1.0-Changes\014_20190314-1432_[...].sql: This migration has changed since it was deployed to [localhost]. [TestSCA_QA]. These changes will not be applied to the target unless the database is dropped/redeployed."

I haven't modified any of these migrations.  Only thing I can think of is perhaps my git/github settings are off and maybe it added/removed newline characters during a pull.

How can I get SCA to not warn about these anymore...or get the right checksum into the migration log...or otherwise resolve?

TIA,

-Peter



Tagged:
· Share on Twitter

Answers

  • MondayMonday Posts: 77 Silver 3
    I found a bug a few weeks back that affected comparing white space. It was in the VS extension but maybe it is also in certain versions of the cmdlets. I was able to see this by using SQL compare to compare my VS project against the database. Make sure you have the setting in SQL compare to include white space. Might not be the issue but something to check.
    · Share on Twitter
  • PeterDanielsPeterDaniels Posts: 89 Bronze 3
    Thanks, @Monday.  Are you suggesting that I work with the:
    <SyncOptionIgnoreWhiteSpace>True</SyncOptionIgnoreWhiteSpace>
    setting in the sqlproj file?  Or something different?
    · Share on Twitter
  • MondayMonday Posts: 77 Silver 3
    No I had mine set to true in the sqlproj file but the VS extension was not honoring it. To see this I used SQL compare but in SQL compare you need to uncheck ignore white space to see it.  Check out this thread here.
    · Share on Twitter
  • PeterDanielsPeterDaniels Posts: 89 Bronze 3
    I'm a bit confused, @Monday .  How did you resolve the issue?
    · Share on Twitter
  • MondayMonday Posts: 77 Silver 3
    Before you figure out how to resolve it, make sure you know what the issue is. Did you compare your db to your project with sql compare and the only difference was white space? If so then you have the same issue that I had. (You are telling it to ignore white space and it is not) I fixed my issue by making the objects in the DB the same as the objects in the project by matching white space as a temp fix while they fixed the bug in the SCA code. Alternatively this may be a bug in the version of the cmdlet you have that was fixed or was just introduced?  To me, SCA thinks there is a difference for some reason, so the place to start is to find out what that difference is.
    · Share on Twitter
  • PeterDanielsPeterDaniels Posts: 89 Bronze 3
    @Monday, I think my issue is similar, but not the same.  Wondering how I could actually generate the script checksum of a migration file and compare to what I find in the __MigrationLog table.  
    · Share on Twitter
  • MondayMonday Posts: 77 Silver 3
    I think we need redgate to chime in with what algorithm they are using to produce the checksum. get-filehash  does not match any of the 7 it supports.

    · Share on Twitter
  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi

    The checksum generated is a SHA256 representation of the migration script at the time of the build.  This value is used to determine whether a migration has been changed since it was last deployed.  In the case of a programmable object script, a different checksum will cause the migration to be redeployed.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
  • PeterDanielsPeterDaniels Posts: 89 Bronze 3
    Thanks, Eddie.  I have been checking this by using powershell's Get-FileHash, and I'm not getting a match.  For example, in my __MigrationLog, one of my scripts (attached) shows a checksum of:
    E0F10404E1FDCBBEF178991783B426B5FCF4BA2A906DDF75CFB54DF025721EDB
    but using Get-FileHash -Algorithm SHA256 gives me:
    222A98897E8773854BED4584B70176BA74188E5EA1D28A8ADE8DB1E1A2B59941

    I've also checked my git history and none of my migrations that SCA was warning about has changed.
    · Share on Twitter
  • PeterDanielsPeterDaniels Posts: 89 Bronze 3
    None of the algorithms available in PS's GetFilehash match what I'm finding in the script_checksum column in __MigrationLog.
    · Share on Twitter
  • PeterDanielsPeterDaniels Posts: 89 Bronze 3
    @Eddie D Please see my previous post.  I am unable to match filehashes using PowerShell's Get-FileHash with the hash value in script_schecksum.  Can you tell me how I can get  matching hash, please?
    · Share on Twitter
  • Eddie DEddie D Posts: 1,803 Rose Gold 5
    Hi Peter

    I was under the impression that as long as there was an entry in the migration_id column that matched the id shown in the migration itself (and possibly the checksum to make sure the script hasn't been modified since it was deployed), that it wouldn't attempt to be deployed.

    Doing a trial of this, the entry in the database I deployed to is the same except for the complete_dt column value and the deployed value (being 1 instead of 2 since it was deployed to the target, but imported to the dev database).

    If I then delete the _MigrationLog table doing the deployment fails because target  needs to be baselined.

    Recreating the _MigrationLog table with the same values as are in the development database (but changing it to deployed 1 instead of 2) - I used SQL Compare to recreate the schema for _MigratinoLog and Data Compare to create a script to deploy the data (which I then changed the 2 to a 1 for the migration) - after building again, it then worked as there was nothing to deploy.

    Many Thanks
    Eddie
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
    · Share on Twitter
Sign In or Register to comment.