Stored Procedure inserting duplicate Rows
Posted by: Derek McKinnon
Date: July 14, 2022 01:58AM
Date: July 14, 2022 01:58AM
I have just inherited a system from the previous developer who has left the company. We have a bug where very occasionally a stored procedure (shown below) will create two entries into a table instead of one.
His verbal report to me when leaving is that he suspects it has to do with Atomisation, which I do not know much about.
It does seem to come in waves. So this week, we had it happen 3 times within 30 inserts (all running within one cursor - see below). Prior to that it hadn't happened for several months - which is probably 5000 inserts.
I suspect I need to use a transaction. However I have very limited experience with Cursors, due to obsessively avoiding them in the past, and so am not sure how best to use a transaction with a cursor.
Here is the stored procedure. If you can see anything else that is wrong, please let me know.
DELIMITER $$
CREATE PROCEDURE `ProcessCSSPayment`(
IN ipClearingDocumentDate DATE,
IN ipChildID VARCHAR(7)
)
BEGIN
DECLARE vPaymentItemID INT;
DECLARE vFamilyIDNo VARCHAR(7);
DECLARE vChildId VARCHAR(7);
DECLARE vChildIdInteger INT;
DECLARE vDetail VARCHAR(120) DEFAULT "Direct Deposit CCS Payment";
DECLARE vShortDescription VARCHAR(1) DEFAULT "G";
DECLARE vClearingDocumentDate DATETIME;
DECLARE vClearingDocumentNumber VARCHAR(12);
DECLARE vPaymentFiscalYear VARCHAR(4);
DECLARE vAmount DECIMAL(12,2);
DECLARE vWEID INT;
DECLARE vWeekEnding DATE;
DECLARE vdr02ID INT;
DECLARE vDone INT DEFAULT 0;
DECLARE curPaymentItem CURSOR FOR
SELECT PH.ClearingDocumentNumber,
PH.ClearingDocumentDate,
PH.PaymentFiscalYear,
PI.id AS PaymentItemID,
PI.Amount,
CH.FamilyIDNo,
CH.childid,
CH.id AS childIdInteger,
WE.WEID,
WE.WeekEnd AS WeekEnding
FROM CCSPaymentItem PI
INNER JOIN CCSPayment PH
ON PI.PaymentID = PH.id
INNER JOIN CCSEnrolment ER
ON PI.EnrolmentID = ER.EnrolmentID
INNER JOIN children CH
ON ER.ChildID = CH.ID
LEFT JOIN weekendings WE
ON PI.SessionReportStartDate BETWEEN WE.WeekStart AND WE.WeekEnd
WHERE ISNULL(PI.dr02Id)
AND (ISNULL(ipClearingDocumentDate) OR PH.ClearingDocumentDate = ipClearingDocumentDate)
AND (ipChildID = "" OR CH.childId = ipChildID)
AND PI.EnrolmentID <> '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = 1;
OPEN curPaymentItem;
PaymentItem_LOOP: LOOP
-- Fetch one record from CURSOR and set to some variable
-- (If not found then done will be set to 1 by continue handler)
FETCH curPaymentItem INTO vClearingDocumentNumber,
vClearingDocumentDate,
vPaymentFiscalYear,
vPaymentItemID,
vAmount,
vFamilyIDNo,
vChildId,
vChildIdInteger,
vWEID,
vWeekEnding;
IF vDone THEN
-- If done set to 1 then exit the loop else continue
LEAVE PaymentItem_LOOP;
END IF;
IF EXISTS ( SELECT id
FROM dr02
WHERE ChildIdInteger = vChildIdInteger
AND childId = vChildId
AND PayId = vClearingDocumentNumber
AND DATE = vClearingDocumentDate
AND WEID = vWEID
AND WeekEnd = vWeekEnding
AND AMOUNT = vAmount * -1
AND USER = "Admin"
) THEN
ITERATE PaymentItem_LOOP;
END IF;
CALL Drsub_DrJnl(vFamilyIDNo,
vDetail,
vShortDescription,
vClearingDocumentDate,
0,
vAmount * -1,
0,
0,
"Admin", -- UserName
vdr02ID);
UPDATE dr02
SET ref = ID,
ChildIdInteger = vChildIdInteger,
childId = vChildId,
PayId = vClearingDocumentNumber,
WEID = vWEID,
WeekEnd = vWeekEnding
WHERE ID = vdr02ID;
CALL Drsub_UpdateChildGovtAmounts(vWEID, vChildId);
UPDATE CCSPaymentItem
SET dr02Id = vdr02ID
WHERE ID = vPaymentItemID;
END LOOP PaymentItem_LOOP;
-- Closing the cursor
CLOSE curPaymentItem;
-- Calculate the CCSHourly
SET vWEID = (SELECT WE.WEID
FROM weekendings WE
WHERE WE.WeekStart = (SELECT MAX(SR.SessionReportStartDate)
FROM CCSSessionReport SR
WHERE NOT ISNULL(SR.FeeReductionAmount)));
UPDATE children CH
INNER JOIN dr02 D21
ON CH.childid = D21.childid
AND D21.WEID = vWEID
AND D21.T1 = "L"
INNER JOIN dr02 D22
ON CH.childid = D22.childid
AND D22.WEID = D21.WEID - 1
AND D22.T1 = D21.T1
SET CCSHourly = (IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) + IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0)) /
CASE WHEN ((CASE WHEN IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) = 0 THEN 0 ELSE 1 END) +
(CASE WHEN IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0) = 0 THEN 0 ELSE 1 END)) = 0
THEN 1
ELSE ((CASE WHEN IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) = 0 THEN 0 ELSE 1 END) +
(CASE WHEN IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0) = 0 THEN 0 ELSE 1 END)) END;
END$$
His verbal report to me when leaving is that he suspects it has to do with Atomisation, which I do not know much about.
It does seem to come in waves. So this week, we had it happen 3 times within 30 inserts (all running within one cursor - see below). Prior to that it hadn't happened for several months - which is probably 5000 inserts.
I suspect I need to use a transaction. However I have very limited experience with Cursors, due to obsessively avoiding them in the past, and so am not sure how best to use a transaction with a cursor.
Here is the stored procedure. If you can see anything else that is wrong, please let me know.
DELIMITER $$
CREATE PROCEDURE `ProcessCSSPayment`(
IN ipClearingDocumentDate DATE,
IN ipChildID VARCHAR(7)
)
BEGIN
DECLARE vPaymentItemID INT;
DECLARE vFamilyIDNo VARCHAR(7);
DECLARE vChildId VARCHAR(7);
DECLARE vChildIdInteger INT;
DECLARE vDetail VARCHAR(120) DEFAULT "Direct Deposit CCS Payment";
DECLARE vShortDescription VARCHAR(1) DEFAULT "G";
DECLARE vClearingDocumentDate DATETIME;
DECLARE vClearingDocumentNumber VARCHAR(12);
DECLARE vPaymentFiscalYear VARCHAR(4);
DECLARE vAmount DECIMAL(12,2);
DECLARE vWEID INT;
DECLARE vWeekEnding DATE;
DECLARE vdr02ID INT;
DECLARE vDone INT DEFAULT 0;
DECLARE curPaymentItem CURSOR FOR
SELECT PH.ClearingDocumentNumber,
PH.ClearingDocumentDate,
PH.PaymentFiscalYear,
PI.id AS PaymentItemID,
PI.Amount,
CH.FamilyIDNo,
CH.childid,
CH.id AS childIdInteger,
WE.WEID,
WE.WeekEnd AS WeekEnding
FROM CCSPaymentItem PI
INNER JOIN CCSPayment PH
ON PI.PaymentID = PH.id
INNER JOIN CCSEnrolment ER
ON PI.EnrolmentID = ER.EnrolmentID
INNER JOIN children CH
ON ER.ChildID = CH.ID
LEFT JOIN weekendings WE
ON PI.SessionReportStartDate BETWEEN WE.WeekStart AND WE.WeekEnd
WHERE ISNULL(PI.dr02Id)
AND (ISNULL(ipClearingDocumentDate) OR PH.ClearingDocumentDate = ipClearingDocumentDate)
AND (ipChildID = "" OR CH.childId = ipChildID)
AND PI.EnrolmentID <> '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = 1;
OPEN curPaymentItem;
PaymentItem_LOOP: LOOP
-- Fetch one record from CURSOR and set to some variable
-- (If not found then done will be set to 1 by continue handler)
FETCH curPaymentItem INTO vClearingDocumentNumber,
vClearingDocumentDate,
vPaymentFiscalYear,
vPaymentItemID,
vAmount,
vFamilyIDNo,
vChildId,
vChildIdInteger,
vWEID,
vWeekEnding;
IF vDone THEN
-- If done set to 1 then exit the loop else continue
LEAVE PaymentItem_LOOP;
END IF;
IF EXISTS ( SELECT id
FROM dr02
WHERE ChildIdInteger = vChildIdInteger
AND childId = vChildId
AND PayId = vClearingDocumentNumber
AND DATE = vClearingDocumentDate
AND WEID = vWEID
AND WeekEnd = vWeekEnding
AND AMOUNT = vAmount * -1
AND USER = "Admin"
) THEN
ITERATE PaymentItem_LOOP;
END IF;
CALL Drsub_DrJnl(vFamilyIDNo,
vDetail,
vShortDescription,
vClearingDocumentDate,
0,
vAmount * -1,
0,
0,
"Admin", -- UserName
vdr02ID);
UPDATE dr02
SET ref = ID,
ChildIdInteger = vChildIdInteger,
childId = vChildId,
PayId = vClearingDocumentNumber,
WEID = vWEID,
WeekEnd = vWeekEnding
WHERE ID = vdr02ID;
CALL Drsub_UpdateChildGovtAmounts(vWEID, vChildId);
UPDATE CCSPaymentItem
SET dr02Id = vdr02ID
WHERE ID = vPaymentItemID;
END LOOP PaymentItem_LOOP;
-- Closing the cursor
CLOSE curPaymentItem;
-- Calculate the CCSHourly
SET vWEID = (SELECT WE.WEID
FROM weekendings WE
WHERE WE.WeekStart = (SELECT MAX(SR.SessionReportStartDate)
FROM CCSSessionReport SR
WHERE NOT ISNULL(SR.FeeReductionAmount)));
UPDATE children CH
INNER JOIN dr02 D21
ON CH.childid = D21.childid
AND D21.WEID = vWEID
AND D21.T1 = "L"
INNER JOIN dr02 D22
ON CH.childid = D22.childid
AND D22.WEID = D21.WEID - 1
AND D22.T1 = D21.T1
SET CCSHourly = (IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) + IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0)) /
CASE WHEN ((CASE WHEN IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) = 0 THEN 0 ELSE 1 END) +
(CASE WHEN IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0) = 0 THEN 0 ELSE 1 END)) = 0
THEN 1
ELSE ((CASE WHEN IFNULL((-IFNULL(D21.Govt, 0)/D21.Units), 0) = 0 THEN 0 ELSE 1 END) +
(CASE WHEN IFNULL((-IFNULL(D22.Govt, 0)/D22.Units), 0) = 0 THEN 0 ELSE 1 END)) END;
END$$
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.