distal-attribute
distal-attribute
distal-attribute
distal-attribute

Can't edit function: "You have an error in your SQL syntax"

lemon_juice posted 3 years ago in General
Today I wanted to make a stored function deterministic and Heidi has a problem with that. After checking the "Deterministic" checkbox and pressing "Save" I get this error:

/* SQL Error (1064): 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 'COLLATE utf8_polish_ci

BEGIN
DECLARE segm VARCHAR(10);
DECLARE out_segm
' at line 8 */



And this is the offending SQL as copied from the log:

CREATE DEFINER=`interfoto2`@`%` FUNCTION `HeidiSQL_temproutine_1`(`path` VARCHAR(255), `delim` VARCHAR(30))
RETURNS varchar(1024)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Zwraca pelna sciezke do kategorii w postaci tekstowej'
COLLATE utf8_polish_ci

BEGIN
DECLARE segm VARCHAR(10);
DECLARE out_segm VARCHAR(255);
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE i TINYINT UNSIGNED DEFAULT 0;
IF delim IS NULL THEN SET delim = ' > '; END IF;
REPEAT
SET segm = SUBSTRING_INDEX(path, '/', 1);
SET path = SUBSTR(path, LENGTH(segm)+2);

SET out_segm = (SELECT name FROM sklep_kategorie WHERE cat_id=segm LIMIT 1);

IF i > 0 THEN
SET result = CONCAT(result, delim);
END IF;
SET result = CONCAT(result, out_segm);
SET i = i + 1;
UNTIL path='' OR path IS NULL OR i > 100 END REPEAT;
RETURN result;
END;



I think the problem is with the COLLATE definition which seems to be not allowed in CREATE FUNCTION syntax.
ansgar posted 3 years ago
You have the "COLLATE utf8_polish_ci" in your SQL body I guess? How did that come in there?
lemon_juice posted 3 years ago
Well, "COLLATE utf8_polish_ci" is not in the function body - I thought you would know how it got in there :). OK, more information: "COLLATE utf8_polish_ci" is the specified collation of the "RETURNS varchar(1024)" value and I think Heidi got confused by that. Here is the original function definition as returned by "SHOW CREATE FUNCTION GET_CAT_PATH_AS_TEXT":

CREATE DEFINER=`interfoto2`@`%` FUNCTION `GET_CAT_PATH_AS_TEXT`(`path` VARCHAR(255), `delim` VARCHAR(30)) RETURNS varchar(1024) CHARSET utf8 COLLATE utf8_polish_ci
READS SQL DATA
COMMENT 'Zwraca pelna sciezke do kategorii w postaci tekstowej'
BEGIN
DECLARE segm VARCHAR(10);
DECLARE out_segm VARCHAR(255);
DECLARE result VARCHAR(2000) DEFAULT '';
DECLARE i TINYINT UNSIGNED DEFAULT 0;
IF delim IS NULL THEN SET delim = ' > '; END IF;
REPEAT
SET segm = SUBSTRING_INDEX(path, '/', 1);
SET path = SUBSTR(path, LENGTH(segm)+2);

SET out_segm = (SELECT name FROM sklep_kategorie WHERE cat_id=segm LIMIT 1);

IF i > 0 THEN
SET result = CONCAT(result, delim);
END IF;
SET result = CONCAT(result, out_segm);
SET i = i + 1;
UNTIL path='' OR path IS NULL OR i > 100 END REPEAT;
RETURN result;
END



So "CHARSET utf8 COLLATE utf8_polish_ci" is there but Heidi puts it in the wrong place and strips "CHARSET utf8".
lemon_juice posted 3 years ago
One more thing: when I view the "Routine body" in Heidi then I can see "COLLATE utf8_polish_ci" as the first line of the body, before the "BEGIN" statement. So I think it's already wrong at that stage - no sql should be present before "BEGIN", right?

PS. Don't be confused by READS SQL DATA instead of CONTAINS SQL in my post above - I changed it myself.
lemon_juice posted 3 years ago
Have you been able to look into this? I'm using the latest nightly and still I'm not able to edit this function in Heidi.

lemon_juice posted 3 years ago
I can create a bug report so it doesn't get lost if you don't have time to work on this now.
ansgar posted 3 years ago
Didn't have the time yet. No bug report please. Here's the place with all details. Trying to find some time soon.
lemon_juice posted 3 years ago
Okay, no problem!
ansgar posted 3 years ago
Fixed in r4115
lemon_juice posted 3 years ago
Thanks, works very well now!

Please login to leave a reply, or register at first.