MySQL Bugs: #65652: created function, crashed

Bug #65652	created function, crashed
Submitted:	18 Jun 2012 11:06	Modified:	13 Sep 2012 20:55
Reporter:	Chris Morris	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.40	OS:	Windows (Microsoft Windows 7 Service Pack 1 (build 7601), 64-bit)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
took a couple of attempts to make a function - syntax error the first time round. When it worked this crash occured

How to repeat:
took a couple of attempts to make a function - syntax error the first time round. When it worked this crash occured

Hello Chris.
Can you please provide more data about this issue, in order to try to reproduce it?
Something like: 
- The MySQL Version that you are using.
- The Server is in your local machine or you are connecting to a remote server?
  - Server Host OS.
  - Connection type (SSH, TCP)
- A sample (create code) of a function where you can reproduce this.
Thanks.

Hi Armando,

To answer your questions in order:
Version is: '5.5.25'
I am indeed using my local machine as the server (product is still very in development), so that's as per the bug report windows 7 64 bit
Connection is TCP

The last one is a bit harder, because I didn't make a note of it, I suspect, based solely on the day I had the issue, it was the function I've included below.

Like I say I unhelpfully didn't really take notes at the time and I can't reproduce it now - I didn't get a warning telling me that my syntax was wrong, I just hit apply and then got a message telling me that the statement had failed to execute. It was descriptive enough I could find me mistake, so I corrected it, hit apply again and that's when the error report was triggered. I'm still teaching my self MySQL (Previous with a different database) and so, as the attached query no doubt conveys it's possible I did just about anything wrong!

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `machineLogin`(user varchar(30),pwdhash varbinary(512),machineID Int) RETURNS bigint(20)
BEGIN
DECLARE cid,SessionID  Int;

SET cid := (SELECT IFNULL(client.`ClientID`,-1)
FROM `papzt`.`tblclients` client 
        INNER JOIN `papzt`.`tblMachines` Machine ON client.ClientID = Machine.ClientID
WHERE client.PasswordHash = pwdhash 
        and DisplayName= user
        and Machine.MachineID = machineID);
        
IF (IFNULL(cid,-1) <=0) THEN 
    BEGIN
        RETURN -1 ;
    END;
ELSE
BEGIN
    UPDATE tblSessions 
    SET `Active` = False 
    WHERE `clientID`= cid
            and MachineID = machineID;
    INSERT INTO tblSessions(`clientID`,`MachineID`,`Active`) 
    VALUES (cid,machineID,True);
    RETURN LAST_INSERT_ID();
END;
END IF;
END

Kind Regards

Chris Morris

Thanks a lot for the info Chris.
In the new WB 5.2.42 a new way to manage the functions, can you please check this new version and try to reproduce this crash?
http://dev.mysql.com/downloads/workbench/5.2.html
Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".