ArtleMaks ArtleMaks - 27 days ago 11
MySQL Question

MySQL - CREATE DEFINER syntax error

I am trying to update a stored function in our MySQL database. The update is to be released to multiple devices so I am doing it through an update.sql file.

Here is the function

DROP FUNCTION `STAFF_MPT`;
CREATE DEFINER=`jelena`@`%` FUNCTION `STAFF_MPT`(`par_stocktake_staff_id` INT) RETURNS DECIMAL(20,0) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN

DECLARE proc_total INT;
DECLARE proc_time INT;

SET proc_total = (SELECT SUM(quantity) FROM stocktake_scans WHERE stocktake_staff_id = par_stocktake_staff_id);

SET proc_time = (SELECT TIMESTAMPDIFF( SECOND , MIN( scan_date ) , MAX( scan_date ) ) AS area_time
FROM stocktake_scans
WHERE stocktake_staff_id = par_stocktake_staff_id
);

RETURN (proc_total/proc_time)*3600;

END


It was just reported to me by the test team that the report that uses this function did not generate properly. I tried to run the code in PMA SQL query window and got the following:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3


Can someone tell me what am I missing? According to this, line 3 is empty, so how could it possibly have a syntax error?

Answer

As of MySQL docs:

If you use the mysql client program to define a stored program containing semicolon characters, a problem arises. By default, mysql itself recognizes the semicolon as a statement delimiter, so you must redefine the delimiter temporarily to cause mysql to pass the entire stored program definition to the server.

To redefine the mysql delimiter, use the delimiter command.

@juergen_d hinted in the comment: you have to define your procedure with a delimiter:

DROP FUNCTION `STAFF_MPT`;

delimiter ||

CREATE DEFINER=`jelena`@`%` FUNCTION `STAFF_MPT`(`par_stocktake_staff_id`     INT) RETURNS DECIMAL(20,0) NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER BEGIN

DECLARE proc_total INT;
DECLARE proc_time INT;

SET proc_total = (SELECT SUM(quantity) FROM stocktake_scans WHERE stocktake_staff_id = par_stocktake_staff_id);

SET proc_time = (SELECT TIMESTAMPDIFF( SECOND , MIN( scan_date ) , MAX( scan_date ) ) AS area_time
    FROM stocktake_scans
    WHERE stocktake_staff_id = par_stocktake_staff_id
    );

RETURN (proc_total/proc_time)*3600;

END

||
delimiter ;
Comments