MySQL存储过程执行报错[1644][45000]：无法将MESSAGE_TEXT变量赋值为NULL的问题排查求助

I’ve dug into your stored procedure code and spotted a few key issues causing this frustrating "second run fails" problem. Let’s break it down and fix it step by step:

Root Causes #

1. Uninitialized Error Message Variable: Your _ErrorMessage is declared without a default value, so it starts as NULL. When certain SELECT INTO queries return no results, it stays NULL, which breaks the SIGNAL command later.
2. Unsafe Error Handling: If GET DIAGNOSTICS fails to retrieve a valid error message (possible on repeated runs), _ErrorMessage becomes NULL, and trying to set MESSAGE_TEXT to NULL triggers the 1644 error.
3. Flawed NULL Check Logic: Your existing condition doesn’t properly handle NULL values, allowing them to reach the SIGNAL statement.

Step-by-Step Fixes #

1. Initialize Error Message with Default Value #

Change the declaration to start with an empty string instead of NULL:

DECLARE _ErrorMessage VARCHAR(500) DEFAULT '';

2. Safeguard SELECT INTO Queries #

The problematic SELECT INTO that checks for existing runsheet drivers can leave _ErrorMessage as NULL if no matching rows exist. Wrap it in an IF EXISTS check to only assign a value when results are found:

-- Reset error message first
SET _ErrorMessage = '';
IF EXISTS(
    SELECT 1 FROM DRSHeader DH
    INNER JOIN UserMaster UM ON DH.DriverId = UM.Id AND UM.RoleId=1
    INNER JOIN tempDRSJson TR ON TR.RunsheetNo=DH.RunsheetNo AND NOT DH.DriverId=IFNULL(TR.DriverId,0)
) THEN
    SELECT CONCAT('Runsheet created for driver: ' , ' - ',COALESCE(cast( UM.UserId as char))) 
    INTO _ErrorMessage
    FROM DRSHeader DH
    INNER JOIN UserMaster UM ON DH.DriverId = UM.Id AND UM.RoleId=1
    INNER JOIN tempDRSJson TR ON TR.RunsheetNo=DH.RunsheetNo AND NOT DH.DriverId=IFNULL(TR.DriverId,0) 
    LIMIT 1 ;
END IF;

3. Strengthen the EXIT HANDLER #

Add COALESCE to ensure MESSAGE_TEXT never gets set to NULL, even if GET DIAGNOSTICS returns nothing:

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1 _ErrorMessage = COALESCE(Message_Text, 'Unknown SQL exception occurred');
    ROLLBACK;
    SET SQL_SAFE_UPDATES = 0;
    INSERT INTO SqlExceptionData(SPName,ErrorMessage,Parameters,InsertedOn,InsertedBy)
    VALUES ('OrderStatusInsert',COALESCE(_ErrorMessage, 'Unknown error'),_json,NOW(),'SYSTEM');
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = COALESCE(_ErrorMessage, 'Unknown error occurred');
END;

4. Simplify the NULL Check Logic #

Replace your complex condition with a straightforward check for non-empty strings:

IF _ErrorMessage <> '' THEN
    BEGIN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = _ErrorMessage;
    END;
ELSE
    -- Rest of your logic here
END IF;

Modified Full Procedure Code #

CREATE PROCEDURE OrderStatusInsert(IN _json JSON)
BEGIN
    DECLARE _tempDocNo varchar(50);
    DECLARE _Flag VARCHAR(50);
    DECLARE _AWBNo VARCHAR(50);
    DECLARE _FirstActionDate VARCHAR(50);
    DECLARE _DriverId VARCHAR(25);
    DECLARE _RunsheetNo varchar(100);
    DECLARE _InsertedBy INT;
    DECLARE _ShelveId VARCHAR(45);
    DECLARE _ShelveCode VARCHAR(45);
    DECLARE _LocationCode VARCHAR(20);
    DECLARE _VehicleNo VARCHAR(25);
    DECLARE _DriverDetail VARCHAR(200);
    DECLARE _LineCount INT default 0;
    DECLARE _ErrorMessage VARCHAR(500) DEFAULT ''; -- Initialize with empty string
    DECLARE _UniqueId VARCHAR(100) DEFAULT DATE_FORMAT(NOW(),'%Y%m%d%H%i%s');
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1 _ErrorMessage = COALESCE(Message_Text, 'Unknown SQL exception occurred');
        ROLLBACK;
        SET SQL_SAFE_UPDATES = 0;
        INSERT INTO SqlExceptionData(SPName,ErrorMessage,Parameters,InsertedOn,InsertedBy)
        VALUES ('OrderStatusInsert',COALESCE(_ErrorMessage, 'Unknown error'),_json,NOW(),'SYSTEM');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = COALESCE(_ErrorMessage, 'Unknown error occurred');
    END;
    
    SET SQL_SAFE_UPDATES = 0;
    DROP TEMPORARY TABLE IF EXISTS tempDRSJson;
    CREATE TEMPORARY TABLE tempDRSJson
    SELECT JSON_UNQUOTE(JSON_EXTRACT(_json,'$.Flag')) as Flag
        ,JSON_UNQUOTE(JSON_EXTRACT(_json,'$.RunsheetNo'))as RunsheetNo
        ,JSON_UNQUOTE(JSON_EXTRACT(_json,'$.LocationCode')) as LocationCode
        ,JSON_UNQUOTE(JSON_EXTRACT(_json,'$.VehicleNo')) as VehicleNo
        ,IFNULL(JSON_UNQUOTE(JSON_EXTRACT(_json,'$.DriverId')),0) as DriverId
        ,JSON_UNQUOTE(JSON_EXTRACT(_json,'$.AWBNo')) as AWBNo
        ,IFNULL(JSON_UNQUOTE(JSON_EXTRACT(_json,'$.InsertedBy')),0) as InsertedBy ;
    
    SELECT Flag, RunsheetNo, InsertedBy, DriverId INTO _Flag, _RunsheetNo, _InsertedBy, _DriverId FROM tempDRSJson ;
    
    DROP TEMPORARY TABLE IF EXISTS tempDRS;
    CREATE TEMPORARY TABLE tempDRS (
        SRNO int(11) NOT NULL AUTO_INCREMENT
        ,Id int(11)
        ,RunsheetNo varchar(50) DEFAULT NULL
        ,AWBNo varchar(50) DEFAULT NULL
        ,City varchar(70) DEFAULT NULL
        ,DriverId varchar(50) DEFAULT NULL
        ,DriverName varchar(60) DEFAULT NULL
        ,Status varchar(50) DEFAULT NULL
        ,DriverPhoneNo varchar(50) DEFAULT NULL
        ,PaymentMethod varchar(50) DEFAULT NULL
        ,CollectableAmount INT default 0
        ,FacilityAddress varchar(100) DEFAULT NULL
        ,PhoneNumber1 varchar(50) DEFAULT NULL
        ,Consignee varchar(50) DEFAULT NULL
        ,ConsigneeAddress1 varchar(250) DEFAULT NULL
        ,Latitude varchar(50) DEFAULT '0'
        ,Longitude varchar(50) DEFAULT '0'
        ,InsertedOn varchar(50) DEFAULT NULL
        ,InsertedBy varchar(50) DEFAULT NULL
        ,VehicleNo varchar(25) DEFAULT NULL
        ,LocationCode varchar(25) DEFAULT NULL
        ,PRIMARY KEY (SRNO), KEY IDX_RunsheetNo (RunsheetNo)
    );
    
    IF (UPPER(_Flag)='INSERTLINE') THEN
        BEGIN
            IF NOT EXISTS(SELECT DISTINCT 1 FROM LocationMaster LM INNER JOIN tempDRSJson TR ON TR.LocationCode=LM.LocationCode LIMIT 1 ) THEN
                BEGIN
                    SET _ErrorMessage = CONCAT('Invalid location') ;
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = _ErrorMessage;
                END;
            ELSEIF NOT EXISTS(SELECT DISTINCT 1 FROM ShipmentData SD INNER JOIN tempDRSJson TR ON TR.AWBNo=SD.AWBNo LIMIT 1 ) THEN
                BEGIN
                    SET _ErrorMessage = 'Invalid box' ;
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = _ErrorMessage;
                END;
            ELSEIF(SELECT DISTINCT 1 FROM DRSLine DL INNER JOIN tempDRSJson TR ON TR.AWBNo=DL.AWBNo AND DL.DriverId != IFNULL(TR.DriverId,0)) THEN
                BEGIN
                    SELECT CONCAT('Box: ', TR.AWBNo, ' is already scanned by driver : ', DM.Name , ' in RunsheetNo: ', DL.RunsheetNo) INTO _ErrorMessage
                    FROM DRSLine DL
                    INNER JOIN UserMaster DM ON DL.DriverId = DM.Id
                    INNER JOIN tempDRSJson TR ON TR.AWBNo=DL.AWBNo AND DL.DriverId != TR.DriverId;
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = _ErrorMessage;
                END;
            ELSEIF EXISTS(SELECT DISTINCT 1 FROM DRSLine DL INNER JOIN tempDRSJson TR ON TR.AWBNo=DL.AWBNo ) THEN
                BEGIN
                    SELECT CONCAT('Box: ', AWBNo, ' is already scanned') INTO _ErrorMessage FROM tempDRSJson;
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = _ErrorMessage;
                END;
            ELSEIF EXISTS(SELECT 1 FROM RunsheetDetail DRS INNER JOIN tempDRSJson TR ON TR.AWBNo=DRS.AWBNo LIMIT 1) THEN
                BEGIN
                    SELECT DISTINCT concat('Box already scanned in another Runsheet: ', RunsheetNo) into _ErrorMessage FROM RunsheetDetail WHERE AWBNo = _AWBNo ;
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = _ErrorMessage;
                END;
            ELSEIF EXISTS(SELECT 1 FROM ReScheduleDetails RD INNER JOIN tempDRSJson TR ON TR.AWBNo=RD.AWBNo LIMIT 1) THEN
                BEGIN
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT ='AWB has Reschedule without date.' ;
                END;
            END IF;
            
            -- Fixed: Wrap SELECT INTO with EXISTS check to avoid NULL assignment
            SET _ErrorMessage = '';
            IF EXISTS(
                SELECT 1 FROM DRSHeader DH
                INNER JOIN UserMaster UM ON DH.DriverId = UM.Id AND UM.RoleId=1
                INNER JOIN tempDRSJson TR ON TR.RunsheetNo=DH.RunsheetNo AND NOT DH.DriverId=IFNULL(TR.DriverId,0)
            ) THEN
                SELECT CONCAT('Runsheet created for driver: ' , ' - ',COALESCE(cast( UM.UserId as char))) 
                INTO _ErrorMessage
                FROM DRSHeader DH
                INNER JOIN UserMaster UM ON DH.DriverId = UM.Id AND UM.RoleId=1
                INNER JOIN tempDRSJson TR ON TR.RunsheetNo=DH.RunsheetNo AND NOT DH.DriverId=IFNULL(TR.DriverId,0) 
                LIMIT 1 ;
            END IF;
            
            -- Fixed: Simplified NULL/empty check
            IF _ErrorMessage <> '' THEN
                BEGIN
                    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = _ErrorMessage;
                END;
            ELSE
                BEGIN
                    INSERT INTO DRSLine(RunsheetNo, ShelvingId, DriverId, AWBNo, Status, InsertedBy, InsertedOn, LocationCode)
                    SELECT RunsheetNo, 0, DriverId, AWBNo, 'Shipped', InsertedBy, NOW(), LocationCode FROM tempDRSJson ;
                    
                    UPDATE DRSHeader DH INNER JOIN tempDRSJson TR ON DH.RunsheetNo = TR.RunsheetNo
                    SET DH.DriverId = TR.DriverId, DH.VehicleNo = TR.VehicleNo;
                    
                    INSERT INTO tempDRS (RunsheetNo, DriverId, FacilityAddress, Latitude, Longitude )
                    SELECT _RunsheetNo, _DriverId, CONCAT(Address1, ', ',City)Facility, Latitude, Longitude FROM LocationMaster LIMIT 1;
                    
                    INSERT INTO tempDRS ( Id, RunsheetNo, AWBNo, City , DriverId, DriverName , Status , DriverPhoneNo , PaymentMethod, CollectableAmount, FacilityAddress, PhoneNumber1 , Consignee, ConsigneeAddress1, Latitude, Longitude, InsertedOn, InsertedBy,LocationCode, VehicleNo )
                    SELECT DL.Id, DH.RunsheetNo, DL.AWBNo, IFNULL(SD.City, '') AS City
                        , DL.DriverId, concat ( DL.DriverId ,' - ',DM.Name )as DriverName, DL.Status
                        , DM.PhoneNo as DriverPhoneNo, '' AS PaymentMethod
                        , 0 AS CollectableAmount, '' AS FacilityAddress
                        , '' PhoneNumber1, '' Consignee
                        , concat(SD.City, ', Block: ',SD.Block, ', Street: ',SD.Street, ', Country: ', SD.Country) ConsigneeAddress1
                        , IFNULL(SD.Latitude,'0')Latitude, IFNULL(SD.Longitude,'0')Longitude
                        , DL.InsertedOn, DH.InsertedBy, DL.LocationCode, DH.VehicleNo
                    FROM DRSHeader DH
                    INNER JOIN DRSLine DL ON DL.RunsheetNo = DH.RunsheetNo
                    INNER JOIN tempDRSJson TR ON TR.RunsheetNo= DH.RunsheetNo
                    INNER JOIN UserMaster DM ON DL.DriverId = DM.Id
                    INNER JOIN ShipmentData SD ON DL.AWBNO = SD.AWBNo
                    ORDER BY SD.Longitude ASC;
                    
                    SELECT SRNO, Id, RunsheetNo, AWBNo, City , Consignee, ConsigneeAddress1, IFNULL(Latitude,0)Latitude
                        , IFNULL(Longitude,0)Longitude, PhoneNumber1
                        , DriverId, DriverName , DriverPhoneNo , Status
                        , PaymentMethod, CollectableAmount, FacilityAddress
                        , InsertedOn, InsertedBy, LocationCode, VehicleNo
                    FROM tempDRS ORDER BY SRNO;
                END;
            END IF;
        END;
    END IF;
END;

Why This Works #

• The default empty string ensures _ErrorMessage never starts as NULL.
• Wrapping SELECT INTO with IF EXISTS prevents accidental NULL assignments when no rows are found.
• COALESCE in the exit handler guarantees we always have a valid error message for SIGNAL.
• The simplified condition correctly triggers errors only when _ErrorMessage has actual content.

This should eliminate the 1644 error on repeated runs—no more need to add blank spaces to "reset" the procedure.

内容的提问来源于stack exchange，提问作者Tech Student