Bug #49625 Stored procedure that calls UUID() calls resuting Insert Statement multipe times

• Submitted: 11 Dec 2009 15:28
• Modified: 14 Dec 2009 18:03
• Reporter: Andy Hurst
• Status: Not a Bug
• Category: MySQL Server: Stored Routines
• Severity: S1 (Critical)
• Version: 5.0.51
• OS: Any (Unknown host OS)
• CPU Architecture: Any
• Tags: stored procedure, Stored Program, UUID, Violates PK

[11 Dec 2009 15:28] Andy Hurst

Description:
I have a simple stored proc that creates a GUID, then inserts a row into a table.  If I use UUID() to generate the GUID within the proc the insert statement is called multiple times(count seems to vary, 3-4 times).  However if if I comment out the 'UUID' line and uncomment the 'test' line in the provided proc, the insert only runs once.  However this completely makes it useless.  

When the insert happens multiple times it will actually violate the PK's unique clause several times without errors. 

There are no triggers on the table.
I'm using Toad for MySQL as my editor, running the proc directly from there. 
Table engine is 'InnoDB'

http://stackoverflow.com/questions/1888488/mysql-stored-procedure-is-running-an-insert-3x

How to repeat:
DROP PROCEDURE IF EXISTS spGenerateCouponCode;
CREATE PROCEDURE spGenerateCouponCode(
 IN inCouponProgramUID int, 
 IN inXML varchar(4000)
)
BEGIN
 Declare proposedCouponCode varchar(50);
                      
         SELECT UUID() into proposedCouponCode;     
        -- select 'test' into proposedCouponcode;       
	
     -- unique is generated by now insert a new row 
      Insert into Coupon (CouponCode,CouponProgramUID,CreateDate,ExpirationDate,XML)
      Values      
     (
        proposedCouponCode,
        inCouponProgramUID,
        '01-01-2001',
        '09-09-2009',       
          inXML
     );
     
       SELECT proposedCouponCode as CouponCode;
END;

Suggested fix:
Something is up with how the UUID is generated.

[11 Dec 2009 15:33] Valeriy Kravchuk

Please, check with a newer version, 5.0.88, and inform about the results.

[14 Dec 2009 16:06] Andy Hurst

Unfortunately I cannot upgrade the db version at this time. However I've found more info about the issue:

Calling proc as such inserts multiple rows:
call dbname.procName(blah);

However calling proc like this works fine:
select the correct db first and then 
call procName(blah);

[14 Dec 2009 18:03] Andy Hurst

So now I'm pretty sure the hole stupid thing is a result of the usage of Toad for MySQL client.