Page 1 of 1
Help me debug this Stored Procedure
Posted: Tue May 08, 2012 4:10 pm
by Benjamin
Code: Select all
DROP PROCEDURE IF EXISTS get_atomic_timeslot;
DELIMITER $$
CREATE PROCEDURE `get_atomic_timeslot` (paramKey VARCHAR(255), paramNow DECIMAL(20, 4), INOUT paramDelay DECIMAL(20, 4))
BEGIN
DECLARE lastRunTime DECIMAL(20, 4) DEFAULT NULL;
DECLARE nextRunTime DECIMAL(20, 4) DEFAULT NULL;
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;
START TRANSACTION;
SELECT `value` INTO lastRunTime FROM `apc` WHERE `key` = paramKey;
IF lastRunTime IS NULL THEN
SET lastRunTime = paramNow - paramDelay;
INSERT INTO `apc`
(`key`, `value`)
VALUES
(paramKey, lastRunTime)
ON DUPLICATE KEY UPDATE
`value` = lastRunTime;
END IF;
SET paramDelay = 555;
SET nextRunTime = lastRunTime + paramDelay;
UPDATE `apc` SET `value` = nextRunTime WHERE `key` = paramKey;
SET paramDelay = nextRunTime - lastRunTime;
COMMIT;
END $$
DELIMITER ;
paramDelay is always set to 555. It doesn't seem like anything after that line is running. Not sure what the issue is.
Re: Help me debug this Stored Procedure
Posted: Tue May 08, 2012 10:15 pm
by mikosiko
Not sure why you think that the result is wrong... just looking the last few lines of your SP...
Code: Select all
// Let assume that
// lastRuntime=10
SET paramDelay = 555;
SET nextRunTime = lastRunTime + paramDelay;
// THEN nextRuntime = 10 + 555 ----> = 565
// LET ASSUME THAT THIS UPDATE IS EXECUTED CORRECTLY
// UPDATE `apc` SET `value` = nextRunTime WHERE `key` = paramKey;
SET paramDelay = nextRunTime - lastRunTime;
// paramDelay = 565 - 10 ----->>>> 555 ....
so I don't see any problem regarding to the result, however seems that your code is generating a SQLWARNING (didn't have time to test why)... test the procedure just commenting that EXIT HANDLER and see what happen... hope this help
Re: Help me debug this Stored Procedure
Posted: Wed May 09, 2012 4:53 am
by Benjamin
I'll give that a shot - I think I already tried that but will try it again.
I had the "SET paramDelay = 555;" sprinkled all over the place with different numbers to try to get an idea of what was happening. It was being set to different values below those lines but the corresponding values were never returned. Without the "SET paramDelay = 555;" line in there, it comes back out unchanged.
I'll do some more digging and see if maybe it's getting changed back to 555 even with the other code in there and also check to make sure the default values of NULL aren't screwing anything up.
Re: Help me debug this Stored Procedure
Posted: Wed May 09, 2012 6:13 am
by mikosiko
Without the "SET paramDelay = 555;" line in there, it comes back out unchanged.
for what I see that is absolutely correct with that code... take out completely the SET paramdelay=555 (or assign any value to it) and you always are going to get the same value back.
I did test your code commenting the SQLWARNING line and it does what it is supposed to do (with or without changing the paramdelay in the procedure)