MySQL - CREATE DEFINER syntax error

Question

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?

You forgot the delimiter. See an example here http://stackoverflow.com/a/40467504/575376 — juergen d, Nov 07 '16 at 14:57

Kevin Kopf · Accepted Answer · 2016-11-07T15:12:26.037

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 ;

Thank you! I had to put in the delimiter after the drop function part, but it does work now :) — ArtleMaks, Nov 07 '16 at 15:08
oh yes, you're right, it had to be after the `drop` call. I fixed it. — Kevin Kopf, Nov 07 '16 at 15:10
I'm being picky now, but also a space here delimiter; then you're answer is 101% — ArtleMaks, Nov 07 '16 at 15:11

MySQL - CREATE DEFINER syntax error

1 Answers1