search

microsoft / fhir-server

A service that implements the FHIR standard
MIT License
1.15k stars 492 forks source link

SQL Sever schema migration 76->77 attempts to drop types still in use #3829

Closed tswelsh closed 2 months ago

tswelsh commented 2 months ago

Describe the bug When running the SQL Server migration scripts, 77.diff.sql attempts to drop a number of "unused" user defined types. For an old enough starting schema, this tries to drop at least one type that is still in use. Specifically, there was an error dropping dbo.BulkCompartmentAssignmentTableType_1, which is in use by the procedure dbo.ReindexResource. This procedure is not dropped first in any migration script.

FHIR Version? Stu3

Data provider? SQL Server

To Reproduce Steps to reproduce the behavior:

  1. Initialise a SQL Server database at a sufficiently low version - I tried starting from the lowest recorded in our version control which was schema version 7.
  2. Run the schema migrations (.diff.sql) in order up to at least 77.

Expected behavior All schema migration scripts should complete without error.

Actual behavior On executing 77.diff.sql, SQL Server returns an error:

"Cannot drop type 'dbo.BulkCompartmentAssignmentTableType_1' because it is being referenced by object 'ReindexResource'. There may be other objects that reference this type."

AB#119628

tswelsh commented 2 months ago

Just to add, I pretty much stopped at the first error it ran into - there could be other things referencing types that the script wants to drop.

EXPEkesheth commented 2 months ago

@SergeyGaluzo - is this something you can help look into?

SergeyGaluzo commented 2 months ago

@EXPEkesheth I will take a look.

SergeyGaluzo commented 2 months ago

Fixed by https://github.com/microsoft/fhir-server/pull/3830 Please re-test.

tswelsh commented 2 months ago

This isn't fully fixed - the type is also still in use by dbo.BulkReindexResources. Removing that procedure as well (which doesn't appear in the latest schema I don't think) appears to fix the issue, according to my test outlined earlier.

SergeyGaluzo commented 2 months ago

Added drop of this stored procedure in this PR https://github.com/microsoft/fhir-server/pull/3833

SergeyGaluzo commented 2 months ago

Fixed by https://github.com/microsoft/fhir-server/pull/3833. Please re-test.

tswelsh commented 2 months ago

Thanks @SergeyGaluzo, this looks to be fixed now.

SergeyGaluzo commented 2 months ago

@tswelsh Thanks for your patience.