MySQL Bugs: #103434: Bug in MySQL Role MySQL community 8.0.20.23

Bug #103434	Bug in MySQL Role MySQL community 8.0.20.23
Submitted:	22 Apr 2021 16:56	Modified:	23 Apr 2021 18:10
Reporter:	lokesh singhal	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.23	OS:	Ubuntu (18.4)
Assigned to:		CPU Architecture:	Any
Tags:	MySQL

Description:
I have faced a issue when I create a procedure in MySQL it works well.

after some days procedure not working as expected.

How to repeat:
just create procedure :

CREATE  PROCEDURE `test_procedure`(IN _ID int)
sp_Exit: BEGIN
DECLARE MaxB2CPID int;
DECLARE  _debacctNumber VARCHAR(30);

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SHOW ERRORS;  
ROLLBACK;
        CALL sp_test_update();
RESIGNAL;
   
END;

DROP temporary table IF EXISTS `tblRemittanceCount`;
DROP temporary table IF EXISTS `tblAdjustmentCount`;
SET SQL_SAFE_UPDATES = 0;

   
INSERT INTO test (ServiceName, ClassName, MethodName, SP_Name, DataString, ErrorMessage, ErrorStackTrace, ErrorDataTime)
values('Database','Procedure','','test_procedure', CONCAT('FileID: ',_ID),   'Procedure log..Line 20', '', NOW());
  

IF EXISTS(SELECT PID FROM Ps WHERE FileID = _ID Limit 1)
THEN
call `usp_LogError`('Database','Procedure','','test_procedure', CONCAT('FileID: ',_ID),   'SP Exit - Ps', '');
LEAVE sp_Exit;
END IF;

IF EXISTS(SELECT PR.PID FROM Premittances PR
			INNER JOIN Ps P
				ON (P.PID = PR.PID AND P.ClientID = PR.ClientID) WHERE P.FileID = _ID LIMIT 1)
THEN
call `usp_LogError`('Database','Procedure','','test_procedure', CONCAT('FileID: ',_ID),   'SP Exit - Premittances', '');
LEAVE sp_Exit;
END IF;
    
IF EXISTS(SELECT PR.AdjustmentID FROM Premittanceadjustments PR		
			INNER JOIN Ps P
				ON (P.PID = PR.PID AND P.ClientID = PR.ClientID) WHERE P.FileID = _ID LIMIT 1)
THEN
call `usp_LogError`('Database','Procedure','','test_procedure', CONCAT('FileID: ',_ID),   'SP Exit - Premittanceadjustments', '');
LEAVE sp_Exit;
END IF;

IF EXISTS(SELECT PD.PID FROM Pdeductions PD
			INNER JOIN Ps P
				ON (P.PID = PD.PID AND P.ClientID = PD.ClientID) WHERE P.FileID = _ID LIMIT 1)
THEN
call `usp_LogError`('Database','Procedure','','test_procedure', CONCAT('FileID: ',_ID),   'SP Exit - Pdeductions', '');
LEAVE sp_Exit;
END IF;
	
IF EXISTS(SELECT GS.FileID FROM gsdata GS
			INNER JOIN file P
				ON GS.fileID = P.FileID WHERE P.FileID = _ID LIMIT 1)
THEN
call `usp_LogError`('Database','Procedure','','test_procedure', CONCAT('FileID: ',_ID),   'SP Exit - gsdata', '');
LEAVE sp_Exit;
END IF;

IF EXISTS(SELECT PTN.PID FROM Ptransnumbers PTN
			INNER JOIN Ps P
				ON (P.PID = PTN.PID AND P.ClientID = PTN.ClientID) WHERE P.FileID = _ID LIMIT 1)
THEN
call `usp_LogError`('Database','Procedure','','test_procedure', CONCAT('FileID: ',_ID),   'SP Exit - Ptransnumbers', '');
LEAVE sp_Exit;
END IF;

   

START trans;

CALL sp_test_update(_ID, 62, 0, 'cd', 1, 0, 0);

SELECT  P.ClientID, P.ProcessingFlags,  P.FileName,  P.FileType, ifnull(CGPC.CustomFileTypeID, 0)
INTO @ClientID, @ProcessingFlags, @FileName, @PFileType, @CustomFileTypeID
FROM file P
INNER JOIN client_db.client_generalprofileconfiguration CGPC ON P.ClientID = CGPC.ClientID
WHERE FileID =  _ID;

SELECT IFNULL(MAX(PID), 0)
INTO @MaxPID
FROM Ps
WHERE ClientID = @ClientID;

SELECT COUNT(FileID)
INTO @TempPCount
FROM temp_Ps
WHERE FileID = _ID;

SELECT COUNT(FileID)
INTO @TempPRemmittanceCount
FROM temp_Premittances
WHERE FileID = _ID;

SELECT COUNT(FileID)
INTO @TempPAdjustmentsCount
FROM temp_Premittanceadjustments
WHERE FileID = _ID;

SELECT COUNT(FileID)
INTO @TempPDeductionsCount
FROM temp_Pdeductions
WHERE FileID = _ID;

SELECT COUNT(FileID)
INTO @TempGsDataCount
FROM temp_gsdata
WHERE FileID = _ID;

SELECT COUNT(FileID)
INTO @TempPtransNumbersCount
FROM temp_Ptransnumbers
WHERE FileID = _ID;

INSERT INTO Ps (
ClientID,
PID,
CreatedAt,
FileID,
StatusID,
debacctNumber,
PayeeName,
PIdentifier,
RemitTo,
Amount,
ValueDateRequested,
ExceptionFlags,
Notes,
RequestedPTypeID,
RemittanceDeliveryStatusID,
transControlNumber,
ReturnStatusID
,GroupControlNumber
,CurrencyCode
,CardAccountNumber
,IndexNo
,BufferIndex
,PayerCompanyName
,TypeID
,CheckAddress
,TraceNumber
,FormID
,CheckNumber
,AmountRequested
,CHKAddress1
,CHKAddress2
,CHKCityName
,CHKStateProvince
,CHKZipCode
,CHKCountryISO
)
SELECT
@ClientID,
@MaxPID + BP.PSeqNum,
NOW(),
_ID,
0,
UDF_encrypt(BP.debacctNumber),
BP.PayeeName,
BP.PIdentifier,
IFNULL(REPLACE(BP.RemitTo,'&amp;','&'),''),
BP.Amount,
BP.ValueDateRequested,
0,
LEFT(BP.Notes,1000),
PS.PTypeID,
1,
BP.transControlNumber,
1
,GroupControlNumber
,CASE WHEN BP.PType in ('CHK','CDM') THEN ifnull(nullif(BP.CurrencyCode,''),'USD') ELSE NULL END
,UDF_encrypt(BP.debacctNumber)
,IndexNo
,BufferIndex
,PayerCompanyName
,CASE WHEN PS.PTypeID IN (8,32) THEN NULL ELSE PS.PTypeID END
,CheckAddress
,BP.TraceNumber
,BP.FormID
,BP.CheckNumber
,BP.Amount
,BP.CHKAddress1
,BP.CHKAddress2
,BP.CHKCityName
,BP.CHKStateProvince
,BP.CHKZipCode
,BP.CHKCountryISO
FROM temp_Ps BP
LEFT JOIN lkp_Ptypes PS
ON (BP.PType = PS.PCode AND  PS.LocaleID  = 1)
WHERE BP.FileID = _ID;
       
SELECT COUNT(PID)
INTO @PCount
FROM Ps
WHERE FileID = _ID;

IF (@PCount > 0)
THEN
INSERT INTO Premittances(
ClientID,
PID,
RemittanceID,
PRDocType,
PRDocNumber ,
PRDocDate,
PRDocRef ,
PEDocType,
PEDocNumber,
PEDocDate,
Amount,
AmountPaid,
AmountOfDiscount,
AdjustmentReasonCode,
AdjustmentAmount,
Notes )
SELECT  @ClientID,
@MaxPID + PSeqNum,
RemittanceSeqNum,
PRDocType,
PRDocNumber,
PRDocDate,
PRDocRef,
PEDocType,
PEDocNumber,
PEDocDate,
Amount,
AmountPaid ,
AmountOfDiscount,
AdjustmentReasonCode ,
AdjustmentAmount ,
RTRIM(LEFT(Notes,1000))
FROM temp_Premittances WHERE FileID = _ID;
END IF;
 
SELECT COUNT(PR.PID)
            INTO @PRemmittanceCount
            FROM Premittances PR
INNER JOIN Ps P ON P.PID = PR.PID AND P.ClientID = PR.ClientID
            WHERE P.FileID = _ID;
           
IF EXISTS (SELECT PsCustomDataID FROM Ps_customdatasettings WHERE ClientID = @ClientID AND PsCustomDataID = 1 LIMIT 1)
THEN
INSERT INTO Ps_customdata
(
ClientID
,PID
,FileID
,Field1
)
SELECT
@ClientID
,@MaxPID + BP.PSeqNum
,FileID
,REFDescription
FROM temp_Ps BP
WHERE BP.FileID = _ID;
END IF;
       
INSERT INTO Pdeductions(
ClientID,
PID,
DeductionID,
DEDType,
CourtOrderNumber ,
CaseNumber,
PDate ,
Amount,
EmployeeSSNNumber,
MedicalSupportIndicator,
EmployeeName,
FIPSCode,
EmploymentTerminationIndicator)
SELECT  @ClientID,
@MaxPID + PSeqNum,
DEDSeqNum,
DEDType,
CourtOrderNumber ,
CaseNumber,
PDate ,
Amount,
EmployeeSSNNumber,
MedicalSupportIndicator,
EmployeeName,
FIPSCode,
EmploymentTerminationIndicator
FROM temp_Pdeductions WHERE FileID = _ID;

SELECT COUNT(PD.PID)
            INTO @PDeductionsCount
            FROM Pdeductions PD
INNER JOIN Ps P ON P.PID = PD.PID AND P.ClientID = PD.ClientID
            WHERE P.FileID = _ID;
           
INSERT INTO gsdata (
FileID,
PSeqNum,
FunctionalIdentifierCode,
ApplicationSenderCode,
ApplicationReceiverCode,
GSdate,
GStime,
GroupControlNumber,
ResponsibleAgencyCode,
Version )
SELECT
FileID,
PSeqNum,
FunctionalIdentifierCode,
ApplicationSenderCode,
ApplicationReceiverCode,
GSdate,
GStime,
GroupControlNumber,
ResponsibleAgencyCode,
Version
FROM temp_gsdata WHERE FileID = _ID;

SELECT COUNT(GS.FileID)
            INTO @GsDataCount
            FROM gsdata GS
            WHERE GS.FileID = _ID;

INSERT INTO Ptransnumbers (
ClientID
,PID
,InterchangeControlNumber
,BatchControlNumber
,transSetControlNumber
,MsgCreationDate
,FileID
)
SELECT
@ClientID
,@MaxPID + PSeqNum
,InterchangeControlNumber
,BatchControlNumber
,transSetControlNumber
,MsgCreationDate
,FileID
FROM temp_Ptransnumbers WHERE FileID = _ID;

SELECT COUNT(PTN.PID)
            INTO @PtransNumbersCount
            FROM Ptransnumbers PTN
INNER JOIN Ps P ON P.PID = PTN.PID AND P.ClientID = PTN.ClientID
            WHERE P.FileID = _ID;

INSERT INTO Premittanceadjustments(
ClientID,
PID,
RemittanceID ,
AdjustmentID,
Amount,
ReasonCode,
RefIDQualifier,
ReferenceID)
SELECT @ClientID,
@MaxPID + PSeqNum,
RemittanceSeqNum,
AdjustmentSeqNum,
Amount,
ReasonCode,
RefIDQualifier,
ReferenceID
FROM temp_Premittanceadjustments WHERE FileID = _ID;
           
SELECT COUNT(PR.AdjustmentID)
INTO @PAdjustmentsCount
FROM Premittanceadjustments PR
INNER JOIN Ps P ON P.PID = PR.PID AND P.ClientID = PR.ClientID
WHERE P.FileID = _ID;
           

UPDATE file SET PCount = @PCount WHERE FileID = _ID;
CREATE TEMPORARY TABLE tblRemittanceCount (
PID INT NOT NULL Primary Key,
RemittanceCount INT NULL);

INSERT INTO tblRemittanceCount
( PID, RemittanceCount )
SELECT PR.PID, COUNT(PR.RemittanceID)
FROM Ps P
INNER JOIN Premittances PR
ON (P.PID = PR.PID AND P.ClientID = PR.ClientID)
WHERE P.FileID = _ID
GROUP BY PR.PID;

CREATE TEMPORARY TABLE tblAdjustmentCount  (
PID INT NOT NULL Primary Key,
ADXCount INT NULL);
INSERT INTO tblAdjustmentCount
( PID, ADXCount )
SELECT PA.PID, COUNT(PA.AdjustmentID)
FROM Ps P
INNER JOIN Premittanceadjustments PA
ON (P.PID = PA.PID AND P.ClientID = PA.ClientID)
WHERE P.FileID = _ID
GROUP BY PA.PID;
       
UPDATE Ps  
INNER JOIN tblRemittanceCount
ON Ps.PID = tblRemittanceCount.PID
            SET Ps.RemittanceCount = tblRemittanceCount.RemittanceCount
WHERE Ps.FileID = _ID;

UPDATE Ps  
INNER JOIN tblAdjustmentCount
ON Ps.PID = tblAdjustmentCount.PID
            SET Ps.AdjustmentCount = tblAdjustmentCount.ADXCount
WHERE Ps.FileID = _ID;
  
IF(@TempPCount = @PCount AND @TempPRemmittanceCount = @PRemmittanceCount AND
@TempPDeductionsCount = @PDeductionsCount AND @TempGsDataCount = @GsDataCount AND
@TempPAdjustmentsCount = @PAdjustmentsCount
) THEN
CALL sp_test_update(_ID, 70, 0, 'cd', 1, 0, 0);
ELSE
CALL sp_test_update(_ID, 63, 0, 'cd', 1, 0, 0);
INSERT INTO test (ServiceName, ClassName, MethodName, SP_Name, DataString, ErrorMessage, ErrorStackTrace, ErrorDataTime)
        values('Database','Procedure','','test_procedure',
        CONCAT('Temp P Count:' , @TempPCount , ' P Count:' , @PCount ,
        'Temp P ramu Count:' , @TempPRemmittanceCount , ' P ramu Count:' , @PRemmittanceCount ,
        'Temp P Deductions Count:' , @TempPDeductionsCount , ' P Deductions Count:' , @PDeductionsCount ,
        'Temp GS Data Count:' , @TempGsDataCount , ' GS Data Count:' , @GsDataCount ,
        'Temp P Adjustments Count:' , @TempPAdjustmentsCount , ' P Adjustments Count:' , @PAdjustmentsCount),
        'Error in transdb.test_procedure procedure',         
        '', NOW());
END IF;
COMMIT;
   
INSERT INTO test (ServiceName, ClassName, MethodName, SP_Name, DataString, ErrorMessage, ErrorStackTrace, ErrorDataTime)
values('Database','Procedure','','test_procedure', CONCAT('FileID: ',_ID),   'Procedure log..Line 431', '', NOW());
  
END sp_Exit

Suggested fix:
just drop and create again works well for few days.

continue facing in production.

Hi Mr. singhai,

Thank you for your bug report.

However, I am afraid that we can not process your report at all.

We need test case that will show the problem in one or two runs. We can not run your production on our systems, because it is not feasible. In principle, we need all tables involved in this huge procedure and all other stored routines (including triggers) involved. 

This is simply not possible.

Hence, simply create a fully comprehensive test case that we can run once, twice or thrice and observe the error occurring.

This could be your coding error, in which case, we can not help you, because this is not a forum which provides help to users with the errors in their code.

Can't repeat.

Please find simplified testcase:

create table test1( FileID int);

DELIMITER $$
CREATE  PROCEDURE `test_proc`(IN _FileID int)
sp_Exit: BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SHOW ERRORS;  
ROLLBACK;
        select "some thing went wron with procedure";
RESIGNAL;
   END;
SET SQL_SAFE_UPDATES = 0;
IF EXISTS(select * from test1 where FileID=_FileID )
THEN
select " data exists";
LEAVE sp_Exit;
END IF;

START TRANSACTION;
INSERT INTO test1(FileID ) values (_FileID);
COMMIT;
   

END sp_Exit

$$ delimiter ;

Issue:

When execute procedure sometimes it return data exists for new value.
 but when we checked "select * from test1 where fileid="""new value"" ;
 data were not exists in the table.

If MySQL routine procedure  have any kind of cache to clear. Please suggest.

Hi Mr. singhal,

I have made some slight changes to your procedures and it worked just fine.