Description:
I wrote a trigger (before insert ) and a stored function that is called by the trigger before each insert. The function does a select SUM() where the clause is based on the parameters passed by the trigger on the same table. I got a perl script that does a select statement on anther table and the retrieved data is then inserted on the trigger table. So the problem comes when only one row is returned from the select and then inserted this row it's not included on the select statement of the function since has not been inserted yet on the table so everything is fine at this point but when the select statement returns a bunch of rows and an insert statement is issued for each row the function called by the trigger gets the not yet inserted row in it's select statement. So it looks like the is race condition of some kind. But I'm totally lost since I don't understand how the not yet inserted row can be selected by the function called by the trigger.
How to repeat:
DELIMITER $$
DROP TRIGGER IF EXISTS `mir`.`calc_derived_precip_fields`$$
CREATE TRIGGER `mir`.`calc_derived_precip_fields` BEFORE INSERT on surface_data
FOR EACH ROW
BEGIN
DECLARE precip24hr DOUBLE;
DECLARE precip03hr DOUBLE;
DECLARE precip15min DOUBLE;
IF (NEW.temp > -200) THEN
IF (NEW.wspd >= 0) THEN
SET NEW.wchill = wchill(NEW.temp,NEW.wspd);
END IF;
IF (NEW.wspd2 >= 0) THEN
SET NEW.wchill2 = wchill(NEW.temp,NEW.wspd2);
END IF;
IF (NEW.wspd3 >= 0) THEN
SET NEW.wchill2 = wchill(NEW.temp,NEW.wspd3);
END IF;
END IF;
IF(NEW.report_type = 3 ) THEN
SET precip24hr = derived_precip(NEW.report_type, NEW.unique_site_id, NEW.obs_time, '23:59');
IF ( precip24hr >= 0) THEN
IF (NEW.precip_cur >= 0 ) THEN
SET NEW.precip_24hr = NEW.precip_cur + precip24hr;
ELSE
SET NEW.precip_24hr = precip24hr;
END IF;
ELSE
IF (NEW.precip_cur >= 0 ) THEN
SET NEW.precip_24hr = NEW.precip_cur;
ELSE
SET NEW.precip_24hr = precip24hr;
END IF;
END IF;
END IF;
IF(NEW.report_type = 3 ) THEN
SET precip03hr = derived_precip(NEW.report_type, NEW.unique_site_id, NEW.obs_time, '02:59');
IF ( precip03hr >= 0) THEN
IF (NEW.precip_cur >= 0 ) THEN
SET NEW.precip_03hr = NEW.precip_cur + precip03hr;
ELSE
SET NEW.precip_03hr = precip03hr;
END IF;
ELSE
IF (NEW.precip_cur >= 0 ) THEN
SET NEW.precip_03hr = NEW.precip_cur;
ELSE
SET NEW.precip_03hr = precip03hr;
END IF;
END IF;
END IF;
IF(NEW.report_type = 3 ) THEN
SET precip15min = derived_precip(NEW.report_type, NEW.unique_site_id, NEW.obs_time, '00:14');
IF ( precip15min >= 0) THEN
IF (NEW.precip_cur >= 0 ) THEN
SET NEW.precip_15min = NEW.precip_cur + precip15min;
ELSE
SET NEW.precip_15min = precip15min;
END IF;
ELSE
IF (NEW.precip_cur >= 0 ) THEN
SET NEW.precip_15min = NEW.precip_cur;
ELSE
SET NEW.precip_15min = precip15min;
END IF;
END IF;
END IF;
END$$
DELIMITER ;
DELIMITER $$
DROP FUNCTION IF EXISTS `mir`.`derived_precip`$$
CREATE FUNCTION `mir`.`derived_precip` (reporttype TINYINT(2) , uid VARCHAR(11) , obstime DATETIME, hh_mm TIME ) RETURNS DOUBLE
READS SQL DATA
BEGIN
DECLARE bv DECIMAL;
DECLARE precip DOUBLE;
DECLARE hourminute VARCHAR(5);
SELECT var_value INTO bv FROM mir_globals WHERE var_name='bad_value';
SET hourminute = TIME_FORMAT(hh_mm, '%H:%i');
SELECT SUM(precip_cur) INTO precip FROM surface_data WHERE unique_site_id = uid AND report_type = reporttype AND obs_time BETWEEN DATE_SUB(obstime, INTERVAL hourminute HOUR_MINUTE ) AND obstime
AND precip_cur between (select min_value from wxdb_fields where field_name = "precip_cur") and (select max_value from wxdb_fields where field_name = "precip_cur");
IF (precip IS NULL) THEN
SET precip = bv;
END IF;
RETURN precip;
END$$
DELIMITER ;
Suggested fix:
No clue yet.
Description: I wrote a trigger (before insert ) and a stored function that is called by the trigger before each insert. The function does a select SUM() where the clause is based on the parameters passed by the trigger on the same table. I got a perl script that does a select statement on anther table and the retrieved data is then inserted on the trigger table. So the problem comes when only one row is returned from the select and then inserted this row it's not included on the select statement of the function since has not been inserted yet on the table so everything is fine at this point but when the select statement returns a bunch of rows and an insert statement is issued for each row the function called by the trigger gets the not yet inserted row in it's select statement. So it looks like the is race condition of some kind. But I'm totally lost since I don't understand how the not yet inserted row can be selected by the function called by the trigger. How to repeat: DELIMITER $$ DROP TRIGGER IF EXISTS `mir`.`calc_derived_precip_fields`$$ CREATE TRIGGER `mir`.`calc_derived_precip_fields` BEFORE INSERT on surface_data FOR EACH ROW BEGIN DECLARE precip24hr DOUBLE; DECLARE precip03hr DOUBLE; DECLARE precip15min DOUBLE; IF (NEW.temp > -200) THEN IF (NEW.wspd >= 0) THEN SET NEW.wchill = wchill(NEW.temp,NEW.wspd); END IF; IF (NEW.wspd2 >= 0) THEN SET NEW.wchill2 = wchill(NEW.temp,NEW.wspd2); END IF; IF (NEW.wspd3 >= 0) THEN SET NEW.wchill2 = wchill(NEW.temp,NEW.wspd3); END IF; END IF; IF(NEW.report_type = 3 ) THEN SET precip24hr = derived_precip(NEW.report_type, NEW.unique_site_id, NEW.obs_time, '23:59'); IF ( precip24hr >= 0) THEN IF (NEW.precip_cur >= 0 ) THEN SET NEW.precip_24hr = NEW.precip_cur + precip24hr; ELSE SET NEW.precip_24hr = precip24hr; END IF; ELSE IF (NEW.precip_cur >= 0 ) THEN SET NEW.precip_24hr = NEW.precip_cur; ELSE SET NEW.precip_24hr = precip24hr; END IF; END IF; END IF; IF(NEW.report_type = 3 ) THEN SET precip03hr = derived_precip(NEW.report_type, NEW.unique_site_id, NEW.obs_time, '02:59'); IF ( precip03hr >= 0) THEN IF (NEW.precip_cur >= 0 ) THEN SET NEW.precip_03hr = NEW.precip_cur + precip03hr; ELSE SET NEW.precip_03hr = precip03hr; END IF; ELSE IF (NEW.precip_cur >= 0 ) THEN SET NEW.precip_03hr = NEW.precip_cur; ELSE SET NEW.precip_03hr = precip03hr; END IF; END IF; END IF; IF(NEW.report_type = 3 ) THEN SET precip15min = derived_precip(NEW.report_type, NEW.unique_site_id, NEW.obs_time, '00:14'); IF ( precip15min >= 0) THEN IF (NEW.precip_cur >= 0 ) THEN SET NEW.precip_15min = NEW.precip_cur + precip15min; ELSE SET NEW.precip_15min = precip15min; END IF; ELSE IF (NEW.precip_cur >= 0 ) THEN SET NEW.precip_15min = NEW.precip_cur; ELSE SET NEW.precip_15min = precip15min; END IF; END IF; END IF; END$$ DELIMITER ; DELIMITER $$ DROP FUNCTION IF EXISTS `mir`.`derived_precip`$$ CREATE FUNCTION `mir`.`derived_precip` (reporttype TINYINT(2) , uid VARCHAR(11) , obstime DATETIME, hh_mm TIME ) RETURNS DOUBLE READS SQL DATA BEGIN DECLARE bv DECIMAL; DECLARE precip DOUBLE; DECLARE hourminute VARCHAR(5); SELECT var_value INTO bv FROM mir_globals WHERE var_name='bad_value'; SET hourminute = TIME_FORMAT(hh_mm, '%H:%i'); SELECT SUM(precip_cur) INTO precip FROM surface_data WHERE unique_site_id = uid AND report_type = reporttype AND obs_time BETWEEN DATE_SUB(obstime, INTERVAL hourminute HOUR_MINUTE ) AND obstime AND precip_cur between (select min_value from wxdb_fields where field_name = "precip_cur") and (select max_value from wxdb_fields where field_name = "precip_cur"); IF (precip IS NULL) THEN SET precip = bv; END IF; RETURN precip; END$$ DELIMITER ; Suggested fix: No clue yet.