Bug #39984 Race condition in trigger ?
Submitted: 11 Oct 2008 16:13 Modified: 12 Nov 2008 9:29
Reporter: marcel casado Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.045 OS:Linux
Assigned to: CPU Architecture:Any

[11 Oct 2008 16:13] marcel casado
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.
[12 Oct 2008 9:29] Sveta Smirnova
Thank you for the report.

But version 5.0.45 is old. Please try with current version 5.0.67 and if problem still exists provide definition of function wchill and output of SHOW CREATE TABLE surface_data
[13 Nov 2008 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".