Restore From Sql Dump 'glitch' For Stored Procedure

[Simeon]

6.05 Community, MySQL5.0.26

Create the SP by running the following code in the Editor:

DELIMITER $$

/*!50003 CREATE PROCEDURE `validateUserSession`(IN sessionKeyIn text, IN PHPSessionIDIn text)

BEGIN

— Created by Simeon for LLB (20070706)

— Returns 1 if session is valid, else 0.

SET @permissionMask = 0;

SELECT permissionMask, userid

INTO @permissionMask, @userid

FROM user_log

WHERE sessionkey = sessionKeyIn

AND phpsessionid = PHPSessionIDIn

AND logoutdate is NULL

AND (lastaccessed > date_add(now(), INTERVAL -20 MINUTE)

OR logindate > date_add(now(), INTERVAL -20 MINUTE));

— Logins last accessed > 20 minutes ago will have expired session

IF @permissionMask != 0

THEN UPDATE user_log

SET lastaccessed = now()

WHERE sessionkey = sessionKeyIn

AND phpsessionid = PHPSessionIDIn;

SELECT 1 sessionStatus, 'Success, Session Valid.' validationStatus, @permissionMask permissionMask,

logonname, firstname

FROM user

WHERE user.userid = @userid; — Success, Session Valid.

ELSE SELECT 0 sessionStatus, 'This session has expired. Please logon again before continuing.' validationStatus; — Session not valid.

END IF;

END */$$

DELIMITER ;

Now generate a SQL Dump, and attempt to restore it.:

Error occured at:2007-08-17 08:04:40

Line no.:116

Error Code: 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 '' at line 3

I have a handfull of SP's affected by this, and can not figure out where the problem is. The scripts for the affected SPs run fine (as do the SPs) if I run that section of code in the editor, but the 'Restore' functionality seems to have issue with something?

[peterlaursen]

We had an almost similar report 20 hours ago! We are testing the fix for this now!

I think the problem the “comment inside conditional comment” conctruction inside the CREATE PROCEDURE statement like

/*!50003 …

….

— a comment

…

*/

Coul you please try to remove '– a comment' and verify if that solves the issue?

[Simeon]

peterlaursen wrote on Aug 17 2007, 08:54 AM:

We had an almost similar report 20 hours ago! We are testing the fix for this now!

I think the problem the “comment inside conditional comment” conctruction inside the CREATE PROCEDURE statement like

/*!50003 …

….

— a comment

…

*/

Coul you please try to remove '– a comment' and verify if that solves the issue?

It's not that! Almost all of my SPs have comments inside (I like to call it 'documentation'!) and only a few of them have this problem. I guess it could be to do with the position of the comment somehow?

I have tried to see any common features between the affected SPs, but whatever it is, it's not obvious..

[Author]

Posts