We help IT Professionals succeed at work.

Unwanted increment in MySql stored function.

codevomit
codevomit used Ask the Experts™
on
Hi all,

I have a stored function which was causing me problems, I've wittled down my function to the offending parts, mysql seems to be incrementing by one when I UPDATE, the value I get on the next SELECT has gone up by 2 instead of one. Before you mention I should have "sends=sends+s" to increment by a value I have additional arithmetic being performed, what I'm giving you is a wittled down version which is enough to recreate the problem.

-- The table I'm using

CREATE TABLE IF NOT EXISTS `SentAndWraps` (
  `UDID` varchar(500) NOT NULL,
  `sends` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`UDID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `SentAndWraps` (`UDID`, `sends`) VALUES ('Test', 0);

-- What I run through netbeans, makes it easy to debug.

DROP FUNCTION AddWraps;

DELIMITER :
CREATE FUNCTION AddWraps(u VARCHAR(500), s INT(11), w INT(11))
    RETURNS VARCHAR(500)

    BEGIN
        DECLARE sen INT(11) unsigned;
	DECLARE rem INT(11) unsigned;

        SELECT `sends` INTO sen FROM `SentAndWraps` WHERE `UDID`=u FOR UPDATE;

        SET rem = sen + s;

	UPDATE `SentAndWraps` SET `sends`=rem WHERE `UDID`=u;  -- THIS LINE HERE INCREMENTS BY 1

	RETURN CONCAT('What is retrived from DB:',sen,' what Im adding:',s,' what should be on the next retrievle:',rem);
  
  END :
DELIMITER ;

SELECT AddWraps('Test',1,3);

Open in new window


Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Also tried this

[code]
UPDATE `SentAndWraps` SET sends=sends+1;
[/code]

This also increments by 2;
Just tried running SELECT AddWraps('Test',1.0,3); in phpMyAdmin and it's fine, must be a bug with NetBeans, probably executing my script twice when I hit run.