Options

Octopus Database deployment issue surrounding constraints - Guidance needed

casterlightcasterlight Posts: 5 New member
edited January 8, 2019 8:47PM in General Forum
The below error is received after the update script tries to execute as part of the "Redgate - Deploy from Package" step process in Octopus deploy. This is the last step to run. 

The update script that is created as part of the deploy step cannot work correctly due to those constraints. 



Applying update script to database 'Log' on server '####.####'.
December 21st 2018 13:55:57
Info
WARNING: The error 'The DELETE statement conflicted with the REFERENCE constraint "FK_Hit_Usage_Area". The conflict
December 21st 2018 13:55:57
Info
occurred in database "log", table "dbo.PM_CANDIDATE", column 'PM_USAGE_AREA_ID'.
December 21st 2018 13:55:57
Info
Delete 15 rows from [dbo].[REF_PM_USAGE_AREA]' occurred when executing the following SQL:
December 21st 2018 13:55:57
Info
PRINT(N'Delete 15 rows from [dbo].[REF_PM_USAGE_AREA]')
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 1
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 2
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 3
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 4
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 5
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 6
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 7
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 8
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 9
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 10
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 11
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 12
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 13
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 14
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_USAGE_AREA] WHERE [PM_USAGE_AREA_ID] = 15
December 21st 2018 13:55:57
Info
PRINT(N'Delete 3 rows from [dbo].[REF_PM_RESULT_TYPE]')
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_RESULT_TYPE] WHERE [PM_RESULT_TYPE_ID] = 1
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_RESULT_TYPE] WHERE [PM_RESULT_TYPE_ID] = 2
December 21st 2018 13:55:57
Info
DELETE FROM [dbo].[REF_PM_RESULT_TYPE] WHERE [PM_RESULT_TYPE_ID] = 3
December 21st 2018 13:55:57
Info
COMMIT TRANSACTION
December 21st 2018 13:55:57
Info
The database update failed
December 21st 2018 13:55:57
Error
Use-DlmDatabaseRelease : Applying update script failed: The DELETE statement conflicted with the REFERENCE constraint
December 21st 2018 13:55:57
Error
"FK_Hit_Usage_Area". The conflict occurred in database "log", table "dbo.PM_CANDIDATE", column 'PM_USAGE_AREA_ID'.
December 21st 2018 13:55:57
Error
Delete 15 rows from [dbo].[REF_PM_USAGE_AREA]
December 21st 2018 13:55:57
Error
Local parameters [connectionString = Data Source=######.#####\####;Initial Catalog=Log;Integrated
December 21st 2018 13:55:57
Error
Security=True;Application Name="Redgate DLM Automation"]
Tagged:
· Share on Twitter

Answers

  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    It looks like you are using static data and the changes caused by that static data would cause a Foreign Key to be violated: the table with static data is a parent in Foreign Key relation and one of the values that you are attempting to delete exists in the child table.

    You need to either adjust your static data so that it doesn't drop that value or adjust the data in the child table.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
    · Share on Twitter
  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    Also if you are not worried about static data changes and want to prioritize any schema changes, you can use the option to Ignore Static Data, which should enable you to deploy any pending schema changes but will, of course, ignore all static data changes.
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
    · Share on Twitter
  • Options
    casterlightcasterlight Posts: 5 New member
    edited January 8, 2019 8:29PM
    Thanks Sergio. The DBA team just went in and edited the RedgateDatabaseInfo.xml file. They updated the DataFileSet elements count to 2 from 0. They also added the qty 2 DataFile sql statements inside this "DataFileSet" element. We re-built and deployed with success. 

    Is their any reasoning why this would fix it? 
    · Share on Twitter
  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    The behavior around this changed a few months ago, what version of DLM Automation\SQL Change Automation Powershell are you using?
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
    · Share on Twitter
  • Options
    casterlightcasterlight Posts: 5 New member
    DLM Automation 2.0.32.1199
    · Share on Twitter
  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    I believe that on that version, if you have a data file in the data folder, and that file is not listed in the xml then all data on the target will be dropped when you deploy.

    I am not sure how you got in that situation, this usually only happens if the xml or the data folder were changed manually.

    Anyway this will not be an issue anymore if you upgrade: this behavior changed in version 13.6.0 of the SQL Compare engine which was merged into SQL Change Automation Powershell 3.0.7 (DLM Automation's new name).

    From SQL Compare version 13.6.0's release notes :
    • The DataFileSet node of the RedGateDatabaseInfo.xml file in a scripts folder has been deprecated and no longer affects static data deployment
    Please note that if you have a SQL Toolbelt license with non-expired Support and Upgrades, you can upgrade free of charge from DLM Automation to SQL Change Automation Powershell.

    Upgrading from DLM Automation 2.0.32 to SCA Powershell is just a case of updating the templates in Octopus Deploy and installing SCA Powershell (if the tentacles are able to connect to the Powershell Gallery, you won't even need to install SCA Powershell, since the template downloads and installs the latest version automatically). The process is similar for any other add-ons that you might be using.

    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
    · Share on Twitter
  • Options
    casterlightcasterlight Posts: 5 New member
    Thanks Sergio. Great feedback. From the RedGate documentation, I see that using SCA requires a different build template in TFS also which is expected but it requests a path to the SQL Change Automation project file (.sqlproj). The DLM Template points to the root path of the Database so this is different. Their is no solution or sqlproj file type available since this has been our standard up to this point. 
    · Share on Twitter
  • Options
    Sergio RSergio R Posts: 610 Rose Gold 5
    SCA supports 2 types of projects: SQL Change Automation and SQL Source Control Projects.

    SQL Source Control Projects are the project type that you are using and weare the only project type supported by DLM Automation 2.

    The reference to .sqlproj only applies to SQL Change Automation Projects, which are created with SCA Visual Studio (a Visual Studio add-on) rather than SQL Source Control.

    The build template is very similar, any changes exist to accommodate SQL Change Automation projects and to allow you to lock to a particular version of the tool (otherwise by default it will attempt to download and use the latest available version).

    SQL Source Control Projects (which is what you are using) work the same way they did in DLM Automation 2, and you don't need to change anything in the project (scripts folder in this case).

    If you select SQL Source Control Project as the type of operation you will find that's very similar to DLM A 2:
    https://documentation.red-gate.com/sca3/automating-database-changes/add-ons/visual-studio-team-services/use-the-vsts-build-extension-with-sql-source-control-project



    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
    · Share on Twitter
  • Options
    casterlightcasterlight Posts: 5 New member
    Tellem to give you a raise Sergio. Always a pleasure. Thanks for your expertise. 
    · Share on Twitter
Sign In or Register to comment.