synchronization - mySql error #2014 - SET FOREIGN_KEY_CHECKS = ON; - Commands out of sync; you can't run this command no

admin管理员组

文章数量:1122846

I have read many posts on stackoverflow regarding this error, but could not find my answer. I have created this procedure on MySQL 8.0 Standard via phpMyAdmin on a shared server :

DELIMITER //
CREATE PROCEDURE CreateUpdateCarrierRange (
    IN idCarrier INT,
    IN idZone INT,
    IN iWeightMin DECIMAL,
    IN iWeightMax DECIMAL,
    IN price DECIMAL,
    IN display INT
)
BEGIN
    SET @iCount :=  0;
    SELECT @iCount, idCarrier, iWeightMin, iWeightMax;
    -- Est-ce que la plage existe ? → count retourne une valeur > 0
    SELECT @iCount := COUNT(RANGE_WEIGHT.id_carrier)
    FROM range_weight as RANGE_WEIGHT
    WHERE
        RANGE_WEIGHT.id_carrier = idCarrier 
        AND RANGE_WEIGHT.delimiter1 = iWeightMin 
        AND RANGE_WEIGHT.delimiter2 = iWeightMax;
    
    IF display = 1 THEN
        SELECT @iCount as 'Nombre enregistrements range_weight';
    END IF;

END //

DELIMITER ;

I call in phpMyAdmin my stored procedure like this :

CALL CreateUpdateCarrierRange(128, 1, 4.000, 5.000, 10.75, 1) 

but I get the error

Static Analysis :

1 error found during the analysis

**Missing expression. (near "ON" at position 25)
SET FOREIGN_KEY_CHECKS = ON;
#2014 - Commands out of sync; you can't run this command now**

In the posts I have read, they speak about missing BEGIN END, missing delimiter, multiple threads (for 1 query ??), ... I must have written something wrong, but I don't see it...

Thanks for your help.

The SHOW CREATE PROCEDURE CreateUpdateCarrierRange; in phpMyAdmin returns the following :

Procedure : CreateUpdateCarrierRange
**sql_mode :
Create Procedure : CREATE DEFINER=o13084985@% PROCEDURE CreateUpdateCarrierRange
haracter_set_client : utf8mb4
collation_connection : utf8mb4_unicode_ci
Database Collation : utf8mb4_general_ci 

I have read many posts on stackoverflow regarding this error, but could not find my answer. I have created this procedure on MySQL 8.0 Standard via phpMyAdmin on a shared server :

DELIMITER //
CREATE PROCEDURE CreateUpdateCarrierRange (
    IN idCarrier INT,
    IN idZone INT,
    IN iWeightMin DECIMAL,
    IN iWeightMax DECIMAL,
    IN price DECIMAL,
    IN display INT
)
BEGIN
    SET @iCount :=  0;
    SELECT @iCount, idCarrier, iWeightMin, iWeightMax;
    -- Est-ce que la plage existe ? → count retourne une valeur > 0
    SELECT @iCount := COUNT(RANGE_WEIGHT.id_carrier)
    FROM range_weight as RANGE_WEIGHT
    WHERE
        RANGE_WEIGHT.id_carrier = idCarrier 
        AND RANGE_WEIGHT.delimiter1 = iWeightMin 
        AND RANGE_WEIGHT.delimiter2 = iWeightMax;
    
    IF display = 1 THEN
        SELECT @iCount as 'Nombre enregistrements range_weight';
    END IF;

END //

DELIMITER ;

I call in phpMyAdmin my stored procedure like this :

CALL CreateUpdateCarrierRange(128, 1, 4.000, 5.000, 10.75, 1) 

but I get the error

Static Analysis :

1 error found during the analysis

**Missing expression. (near "ON" at position 25)
SET FOREIGN_KEY_CHECKS = ON;
#2014 - Commands out of sync; you can't run this command now**

In the posts I have read, they speak about missing BEGIN END, missing delimiter, multiple threads (for 1 query ??), ... I must have written something wrong, but I don't see it...

Thanks for your help.

The SHOW CREATE PROCEDURE CreateUpdateCarrierRange; in phpMyAdmin returns the following :

Procedure : CreateUpdateCarrierRange
**sql_mode :
Create Procedure : CREATE DEFINER=o13084985@% PROCEDURE CreateUpdateCarrierRange
haracter_set_client : utf8mb4
collation_connection : utf8mb4_unicode_ci
Database Collation : utf8mb4_general_ci 

• mysql
• synchronization
• command
• out

Share Improve this question Follow edited Nov 24, 2024 at 12:20 Dominique Delcourt asked Nov 22, 2024 at 19:12 Dominique DelcourtDominique Delcourt 111 bronze badge 2

• This is a command which is added by phpMyAdmin (it is absent in your code). Check that SHOW CREATE PROCEDURE CreateUpdateCarrierRange; returns the code which matches your source code. If so then search where this statement is added during your CALL query processing. – Akina Commented Nov 22, 2024 at 19:36
• Thank you Akina for trying to help me. I have added the information I got from phpMyAdmin with the SHOW command. Can you please precise what you mean by the 'command added by phpMyAdmin' ? 'returns the code which matches your source code' : the SHOW command only display few information (and not the source code). – Dominique Delcourt Commented Nov 24, 2024 at 12:17

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Can you try below and check if this helps.

I tried replacing SET with declaring variables as to eliminate the possibility of conflicting SET or session-changing commands (like SET FOREIGN_KEY_CHECKS = ON) in the context where the procedure is being called

DELIMITER //

CREATE PROCEDURE CreateUpdateCarrierRange (
    IN idCarrier INT,
    IN idZone INT,
    IN iWeightMin DECIMAL,
    IN iWeightMax DECIMAL,
    IN price DECIMAL,
    IN display INT
)
BEGIN
    DECLARE iCount INT DEFAULT 0;

    -- Check if the range exists
    SELECT COUNT(RANGE_WEIGHT.id_carrier)
    INTO iCount
    FROM range_weight AS RANGE_WEIGHT
    WHERE RANGE_WEIGHT.id_carrier = idCarrier 
    AND RANGE_WEIGHT.delimiter1 = iWeightMin 
    AND RANGE_WEIGHT.delimiter2 = iWeightMax;

    -- If display is set to 1, show the count of records in range_weight
    IF display = 1 THEN
        SELECT iCount AS 'Nombre enregistrements range_weight';
    END IF;

    -- Optionally, you could add logic here to insert or update records in range_weight based on iCount.
END //

DELIMITER ;

本文标签：