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 Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.51 OS:Any (Unknown host OS)
Assigned to: CPU Architecture:Any
Tags: stored procedure, Stored Program, UUID, Violates PK

[11 Dec 2009 15:28] Andy Hurst
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'


How to repeat:
CREATE PROCEDURE spGenerateCouponCode(
 IN inCouponProgramUID int, 
 IN inXML varchar(4000)
 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)
       SELECT proposedCouponCode as CouponCode;

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.