Schema Compare tool showing objects as changed but no differences displayed in the diff itself (potential line ending mismatch)

[sgarwood]

• Azure Data Studio Version:

  Version: 1.37.0
  Commit: d904740d93d7df76a0ba361f20e4351813b57645
  Date: 2022-06-14T00:52:49.854Z
  VS Code: 1.59.0
  Electron: 13.6.6
  Chrome: 91.0.4472.164
  Node.js: 14.16.0
  V8: 9.1.269.39-electron.0
  OS: Linux x64 5.13.4-051304-generic

• OS Version:

  NAME="Ubuntu"
  VERSION="20.04.4 LTS (Focal Fossa)"
  ID=ubuntu
  ID_LIKE=debian
  PRETTY_NAME="Ubuntu 20.04.4 LTS"
  VERSION_ID="20.04"
  KERNEL=5.13.4-051304-generic

Steps to Reproduce:

1. Right click on a Database Project
2. Select "Schema Compare" from dropdown menu.
3. In the open Schema Compare menu select a SQL Database as the source and Database Project as the Target
4. Observe that views and stored procedures appear with 'Change' in the Action column but the 'Compare Details' window below shows no material difference.

Could this be a line ending issue where procedures with CRLF line endings in the database compared the LF characters in the sqlproj files?

Does this issue occur when all extensions are disabled?: No - requires Databse Project Extension for Schema Compare

[kisantia]

@sgarwood can you check if this also happens in SSDT Schema Compare or if this is specific to ADS?

[sgarwood]

@kisantia I can confirm same database target and same branch of Database Project from Windows Visual Studio SSDT Schema Compare this isssue is not present suggesting this is specific to ADS.

I can also confirm that on Windows 10 with ADS version

Version: 1.37.0 (system setup)
Commit: d904740d93d7df76a0ba361f20e4351813b57645
Date: 2022-06-14T01:05:12.352Z
VS Code: 1.59.0
Electron: 13.6.6
Chrome: 91.0.4472.164
Node.js: 14.16.0
V8: 9.1.269.39-electron.0
OS: Windows_NT x64 10.0.19043

this issue is not present when I run a Schema Compare from the same database and same source code that Visual Studio SSDT Schema Compare was running against (and the same branch one I was using from the first screenshot). Suggesting this may only be affecting ADS Linux.

Output from ADS Schema Compare on Windows 10 appears to match SSDT Visual Studio Schema Compare

However ADS Schema Compare on Linux shows changes to more files:

When you look at the row for the Source Name tblWebUser it is the last table in the list of changed SqlTable objects in SSDT and ADT on Windows, the name of the first changes SqlView after that table is Invoice_LEDES_TAX_TOTAL. However comparing this the last table is tblWebUser and we see the first changed SqlView after that table is in fact __RP_001 not Invoice_LEDES_TAX_TOTAL. When __RP_001 is opened in the diff, no tangible changes can be seen in the compare details.

NB: long git hash is present at the bottom of the screenshots to confirm the same source code commit is checked out and I can confirm both working trees show as clean with no additional changes.

Hope this illustrates the issue a bit more.

Let me know if you need anything else, happy to provide further detail.

[sgarwood]

Defintely looking like line endings here. Windows ADS configured with CRLF and Linux ADS configured with LF.

When running the compare after switching to CRLF and running the Schema compare in the Linux ADS __RP_001 disappears from the differences. As displayed below.

Can we get a Option in the schema compare to ignore this and prevent these appearing as changes in the list?

[kisantia]

Thanks for all the additional info! Looks like this is something we'll have to look into fixing in DacFx.

[tlentine]

I have this same issue on macOS Monterey and it is dreadful - with a large database schema it is a ton of extra (manual) work to sift through and find the actual changes / differences vs those where it's just a line ending difference. A fix would be most welcome and helpful : )

Azure Data Studio Version: 1.39.1 Commit: 7553f799e175f471b7590302dd65c997b838b29b Date: 2022-08-30T01:12:48.559Z VS Code: 1.62.0 Electron: 13.6.6 Chrome: 91.0.4472.164 Node.js: 14.16.0 V8: 9.1.269.39-electron.0 OS: Darwin x64 21.6.0

[kisantia]

@sgarwood @tlentine do you have the "Ignore whitespace" option selected when doing schema compare?

Steps I did to try to repro:

1. Created a new sql project with a stored proc with CRLF
2. Published project to a new db
3. Changed the stored proc file in the sql project to have LF
4. Schema compared the project and db with the default schema compare options Result: no differences

When I changed the options to unselect "Ignore whitespace" and compared again, the stored proc showed as a difference

[sgarwood]

Thanks for this Kim.

Could you clarify where exactly this option appears in the GUI?

Seb

---

From: Kim Santiago @.> Sent: Friday, September 23, 2022 7:28:07 PM To: microsoft/azuredatastudio @.> Cc: Seb Garwood @.>; Mention @.> Subject: Re: [microsoft/azuredatastudio] Schema Compare tool showing objects as changed but no differences displayed in the diff itself (potential line ending mismatch) (Issue #20052)

@sgarwoodhttps://github.com/sgarwood @tlentinehttps://github.com/tlentine do you have the "Ignore whitespace" option selected when doing schema compare?

Steps I did to try to repro:

1. Created a new sql project with a stored proc with CRLF
2. Published project to a new db
3. Changed the stored proc file in the sql project to have LF
4. Schema compared the project and db with the default schema compare options Result: no differences

When I changed the options to unselect "Ignore whitespace" and compared again, the stored proc showed as a difference

— Reply to this email directly, view it on GitHubhttps://github.com/microsoft/azuredatastudio/issues/20052#issuecomment-1256537291, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ADI45R3T4OKM2EHDEZHADRDV7XZDPANCNFSM53WH43WA. You are receiving this because you were mentioned.Message ID: @.***>

[tlentine]

Yes, I have the ignore whitespace option checked and have this difficulty. Could the target database have an impact on this? I'm comparing against Azure SQL Databases.

[kisantia]

@tlentine It also wasn't reproing for me when comparing the project against an Azure SQL db with ignore whitespace checked :/ If you try the simple repro I did do you still see the differences? Maybe I'm missing something.

@sgarwood the options dialog can be opened from the toolbar:

[tlentine]

With a brand new project I can't replicate the issue. Are there any diagnostics we could tap into? The main project I see this with is a pretty large database, and the project itself has been migrated to the sdk style project from the older Visual Studio Database project format.

[sgarwood]

I'm afraid it's the same again here even with with the highlighted option ticked there are still changed detected while no visible changes can be seen in the diff window.

Similar situation- extremely large enterprise system migrated from Visual Studio SSDT.

Target database is an official MSSQL Docker container running on the local network.

[TommyLeng]

I got the same problem using visual studio 2022

[Benjin]

@sgarwood, @tlentine I'm having the darndest time reproing this issue. I think I've followed your setup to a T, but let me know if I missed any step:

1. [SSDT, Windows] Create new SQL project, and add a stored procedure to the project so that there's something with exact text preserved.
2. [SSDT, Windows] Check the project into git repo
3. [SSDT, Windows] Publish the project to a SQL Server 2019 Docker container
4. [ADS, Mac] Clone the repo
5. [ADS, Mac] Use Schema Compare to compare the project with the database running on the Docker container

After the last step, I see no differences, which is correct (non-buggy) behavior. If I disable the Ignore Whitespace option that @kisantia showed, the file shows up as a difference. If I leave that disabled and change the newline type in ADS on Mac to CRLF, then the difference goes away.

---

Can you provide a from-scratch repro of this bug? Complete steps, database schema, etc. Without the ability to repro this, I'm not sure how I can identify the issue and fix it. I'm also happy to hop on a call/screen share with you; if that interests you, please shoot me an email at benjind@microsoft.com to coordinate.

[sgarwood]

Hi @Benjin. Thanks for your efforts on this. I've only just had chance to attempt to recreate this from scratch. Following a very similar methodology to what you described above I am also unable to recreate this.

The project I am currently working on was an existing database that I have imported into SSDT but yet to rerun the publish on the production database. Would this have any impact do you think?

Thinking about this, perhaps a more accurate workflow would be:

1. [SSMS, Windows] Create a SQL Server Database and add a stored procedure to the database against a local SQL Server Instance (this is mimicking our production database we haven't run publish against yet)
2. [SSDT, Windows] create new SQL project, and import the database + stored procedure into the SQLproject
3. [SSDT, Windows] Check the project into git repo
4. [SSDT, Windows] Publish the project to a SQL Server 2019 Docker container
5. [ADS, Mac/Linux] Clone the repo
6. [ADS, Mac/Linux] Use Schema Compare to compare the project with the database running on the Docker container/local SQL Server instance

I've dropped you an email as well if you'd like to arrange a call/screen share- I am happy to facilitate.

[Benjin]

Thanks for chatting this morning, @sgarwood! That was very helpful. For everyone else following along, we think this bug is stemming from the newline characters (LF vs CRLF) in string literals. If a stored procedure (or anything else) is defined involving a multi-line string, the newlines in that string are not ignored by the Ignore Whitespace option... which makes sense because DacFx (the library that powers all these schema operations, like Schema Compare and projects) doesn't want to try to interpret when it's okay to convert a character inside of a user-defined string. Tracking down the chain of custody for a string (and therefore its newline characters) across an entire environment/flow can be tricky, 'cause it seems everyone along the way - SQL, Git, SSDT, ADS, Ubuntu, and Windows - all have opinions on what the newline should be and whether to convert it.

Given that, I think we have some options:

1. Introduce a new option to ignore white space in string literals, separate from Ignore Whitespace that already exists.
2. Enable whitespace visibility in the diff view (though this may land at VS Code's feet, as the diff view is upstream from ADS)
3. Document this behavior so that users can ensure their line endings are all the same.

Any other options I've missed? Any feedback on the ones I've listed?

[kisantia]

closing from lack of activity. Please reopen to continue the discussion