August 27, 2016 at 12:52 pm
Hello Guys,
Below is a piece of code i'm working on to improve the efficiency of a purge, but i'm getting this error and need help to figure it out. ANY help would be appreciated
Thanks
The DELETE statement conflicted with the REFERENCE constraint "FK_ESS_FUEL_MSG". The conflict occurred in database "ExactFuel_Purge", table "dbo.FUEL_ESS_PUBLISH_RECORD", column 'msg_id'.
USE [ExactFuel_Purge]
GO
/****** Object: StoredProcedure [dbo].[usp_purge_fuel_message2] Script Date: 8/27/2016 1:41:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
----------------------------------------------------------------------------------------------------
-- OBJECT NAME : usp_purge_fuel_message
--
-- AUTHOR : mzhu
-- DATE : September 2012
--
-- INPUTS : @purgeDate
--
-- : @batchSize - how many records to delete per transaction
-- :
-- OUTPUTS : @totalRowsPurged - how many rows deleted
-- :
-- DEPENDENCIES :
--
-- DESCRIPTION : delete records from Fuel_Message.
----------------------------------------------------------------------------------------------------
CREATE PROCEDURE [dbo].[usp_purge_fuel_message2](@purgeDate datetime, @batchSize int, @totalRecordsPurged int OUTPUT)
AS
BEGIN
SET NOCOUNT ON;-- prevents extra result sets from interfering with SELECT statements.
------------------------------------------------------------------------------
-- initialize varibles
------------------------------------------------------------------------------
DECLARE @rowCount int, @retries tinyint, @maxRetries int, @errorNumber int, @errorMessage nvarchar(4000), @errorSeverity int, @errorState int
DECLARE @myAcct bigint, @myUA bigint, @HealthID bigint, @HealthInsert int -- used by new logic S.Watt
SELECT @rowCount = 1, @retries = 0, @maxRetries = 10, @totalRecordsPurged = 0
INSERT [dbo].[HEALTH_INFO] (category_name, category_step, created_date, status, event_note)
VALUES ('Purge', 'FUEL_MESSAGE', GETDATE(),'Started', 'Start of procedure timestamp')
-- Load temp table with all Accounts and UAs, use count to create distinct list (hack)
SELECT [account_id], [ua], count(*) as [count]
INTO #FUEL_MESSAGE_Purge
FROM [dbo].[FUEL_MESSAGE]
WHERE msg_process_time < @purgeDate
GROUP BY [account_id], [ua]
ORDER BY 1,2
-- INSERT progress record into HEALTH LOG to create a record to update status while running.
INSERT [dbo].[HEALTH_INFO] (category_name, category_step, created_date, status, event_note)
VALUES ('Purge', 'FUEL_MESSAGE', GETDATE(),'Progress', 'Start')
SELECT @HealthID = @@IDENTITY
-- Get Account and UA to start the purge off
SELECT @myAcct = MIN([account_id]) FROM #FUEL_MESSAGE_Purge
SELECT @myUA = MIN([ua]) FROM #FUEL_MESSAGE_Purge WHERE [account_id] = @myAcct
-- SELECT @myAcct, @myUA, @purgeDate as [purge date], 'Acct/UA before loop' -- debug
SELECT @rowCount = 1 -- setup varible to run purge loop
WHILE @myAcct IS NOT NULL
BEGIN
WHILE @myUA IS NOT NULL
BEGIN
--
--SELECT@rowCount as [RowCount],
--@retries as [Retries],
--@maxRetries as [MaxRetry],
--@myAcct as [MyAcct],
--@myUA as [UA],
--@totalRecordsPurged as [records purged], 'new Acct/UA via Acct/UA loop'
---- DEBUG
-- Original Purge before 8/16/2016
--SELECT@rowCount as [RowCount],
--@retries as [Retries],
--@maxRetries as [MaxRetry],
--@myAcct as [MyAcct],
--@myUA as [UA],
--@totalRecordsPurged as [records purged], 'Acct/UA via Acct/UA IN loop'
---- DEBUG
WHILE @rowCount > 0 AND @retries <= @maxRetries
BEGIN TRY
DELETE TOP (@batchSize)
FROM [dbo].[FUEL_MESSAGE]
WHERE id IN (SELECT ID FROM [dbo].FUEL_MESSAGE
WHERE msg_process_time < @purgeDate and [account_id] = @myAcct and [ua] = @myUA)
SELECT @rowCount = @@ROWCOUNT
SELECT @totalRecordsPurged = @totalRecordsPurged + ISNULL(@rowCount,0)
-- report progress
IF @HealthInsert > 25
BEGIN
UPDATE [dbo].[HEALTH_INFO]
SET [STATUS] = 'Progress',
[CREATED_DATE] = getdate(),
[EVENT_NOTE] = 'Acct_ID: ' + convert(varchar, ISNULL(@myAcct,0)) + ', ' + convert(varchar, ISNULL(@totalRecordsPurged,0)) + ' rows purged.'
WHERE [ID] = @HealthID
SELECT @HealthInsert = 0
END
SELECT @HealthInsert = @HealthInsert + 1
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 1205 AND @retries < @maxRetries -- 1205 is deadlock error
BEGIN
SET @retries = @retries + 1
END
ELSE -- some other error or done retrying
BEGIN
SELECT @errorNumber = ERROR_NUMBER(), @errorMessage = ERROR_MESSAGE(), @errorSeverity = ERROR_SEVERITY(), @errorState = ERROR_STATE()
INSERT [dbo].[HEALTH_INFO] (category_name, category_step, created_date, status, event_note)
VALUES ('Purge', 'FUEL_MESSAGE', GETDATE(), 'Failed', @errorMessage)
RAISERROR (@errorNumber, @errorSeverity, @errorState);
RETURN
END
END CATCH
-- Get next UA within an account
SELECT @myUA = MIN([UA]) FROM #FUEL_MESSAGE_Purge WHERE [account_id] = @myAcct and [ua] > @myUA
SELECT @rowCount = 1
-- SELECT @myAcct, @myUA, @totalRecordsPurged as [records], 'new UA via UA loop' -- debug
END
-- Get next Account_ID and then first UA for that account_id
SELECT @myAcct = MIN([account_id]) FROM #FUEL_MESSAGE_Purge WHERE [account_id] > @myAcct
SELECT @myUA = MIN([ua]) FROM #FUEL_MESSAGE_Purge WHERE [account_id] = @myAcct
--SELECT @rowCount as [rowCount], @myAcct as [MyAcct], @myUA as [UA], @totalRecordsPurged as [records purged], 'new Acct/UA via Acct/UA loop' -- debug
SELECT @rowCount = 1
-- Debug code
--IF @myAcct > 14 or @totalRecordsPurged > 10000
--BEGIN
--SELECT @myAcct = NULL
--PRINT 'Force Stop'
--END
END
END
-- SELECT @myAcct as [MyAcct], @myUA as [UA], @totalRecordsPurged as [records purged], 'End Acct/UA loop' -- debug
DROP TABLE #FUEL_MESSAGE_Purge
--INSERT [dbo].[HEALTH_INFO] (category_name, category_step, created_date, status, event_note)
--VALUES ('Purge', 'FUEL_ESS_PUBLISH_RECORD', GETDATE(),'Completed', convert(varchar, ISNULL(@totalRecordsPurged,0)) + ' rows purged.')
UPDATE [dbo].[HEALTH_INFO]
SET [STATUS] = 'Completed',
[CREATED_DATE] = getdate(),
[EVENT_NOTE] = convert(varchar, ISNULL(@totalRecordsPurged,0)) + ' rows purged.'
WHERE [ID] = @HealthID
RETURN
GO
August 27, 2016 at 4:41 pm
I only see the one delete so does the error happen when deleting from FUEL_MESSAGE?
From the error message and that one delete, it looks like there is a Relationship between FUEL_ESS_PUBLISH_RECORD and FUEL_MESSAGE. FUEL_ESS_PUBLISH_RECORD has FK relationship to FUEL_MESSAGE. They are related on the msg_id column.
So Fuel_message can have one or more related rows Fuel_ess_publish_record. The rows in Fuel_ess_publish_record would be orphaned as there wouldn't be the related msg_id row in FUEL_MESSAGE.
Sue
August 28, 2016 at 5:11 pm
Yes Sue, the error occurs deleting from the fuel_message table
August 28, 2016 at 7:56 pm
david.foli7 (8/28/2016)
Yes Sue, the error occurs deleting from the fuel_message table
Okay. I think I explained the relationship, why you get the error and how you would end up with orphaned records.
So you need to not delete records in the fuel_message table if they have the related records in FUEL_ESS_PUBLISH_RECORD. Or you need to delete the records in FUEL_ESS_PUBLISH_RECORD prior to deleting them in the fuel_message table. You have to delete the children before deleting the parent or don't delete the parent records that have children records.
Cascading deletes would delete the child records when a parent record is deleted but if you don't know what's going on when you delete a record, that just seems to be a disaster in the making.
You need to understand the data model, the relationship between the tables and understand the business requirements for this process to know what you need to do to address it.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply