CODEWITHSUNDEEP
mysqltriggers
nullp01nter
nullp01nter

Reputation: 521

Odd MySQL trigger behaviour

I manage my home automation system with a Raspberry Pi and collect a lot of data (temperatures, water levels, energy usage, ...), mostly for every minute. Since the Pi is not very powerful, I did optimize my web frontend queries by implementing cache tables which aggregate hourly and daily values to speed up everything. My latest addition was to use triggers for automatic propagation of INSERT and UPDATE statements. Everytime a value is inserted (or updated) into the water_level table, its trigger fires and correctly calculates the beginning and end of the hour coming with the trigger event. Furthermore, the min and max values are correctly calculated and inserted in the water_level_hourly table. When looking at the table, the min and max values appear as expected.

Problems start with the second trigger wich I added to the table water_level_hourly. Its purpose is to aggregate all hourly values into a daily value everytime something is inserted or updated into the hourly table. I copy/pasted the trigger from the first table, but changed the calculation of the current day (which is a cast to DATE of the time of the inserted/updated rows). Somehow, everytime the trigger fires, it does not correctly query the values of min and max from the hourly table and always inserts -1 into the daily table. What do I need to change to achieve the desired behaviour of automatic calculation of daily values?

I attached an example below (I know I could combine the triggers into a stored procedure, but wanted to find the problem first, so please bear with me):

CREATE DATABASE IF NOT EXISTS `homebusdata` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `homebusdata`;

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `water_level` (
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `level` int(11) NOT NULL,
  PRIMARY KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------
DROP TRIGGER IF EXISTS `trg_water_level_insert`;
DELIMITER //
CREATE TRIGGER `trg_water_level_insert` AFTER INSERT ON `water_level`
 FOR EACH ROW BEGIN
    DECLARE t1, t2 TIMESTAMP;
    DECLARE min, max FLOAT;

    # calculate start and end of current hour
    SET @t1 := DATE_FORMAT(NEW.`time`, '%Y-%m-%d %H:00:00');
    SET @t2 := DATE_ADD(@t1, INTERVAL 1 HOUR);

    # fetch min/max values of current hour
    SELECT MIN(`level`), MAX(`level`) 
    INTO @min, @max
    FROM `water_level`
    WHERE `water_level`.`time` BETWEEN @t1 AND @t2;

    # care for empty values at beginning of each hour
    IF @min IS NULL THEN
        SET @min := 0;
    END IF;
    IF @max IS NULL THEN
        SET @max := 0;
    END IF;

    # write min/max to hourly table
    INSERT INTO `water_level_hourly` (`time`, `min`, `max`) 
    VALUES (@t1, @min, @max) 
    ON DUPLICATE KEY UPDATE `min`=@min, `max`=@max;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trg_water_level_update`;
DELIMITER //
CREATE TRIGGER `trg_water_level_update` AFTER UPDATE ON `water_level`
 FOR EACH ROW BEGIN
    DECLARE t1, t2 TIMESTAMP;
    DECLARE min, max FLOAT;

    # calculate start and end of current hour
    SET @t1 := DATE_FORMAT(NEW.`time`, '%Y-%m-%d %H:00:00');
    SET @t2 := DATE_ADD(@t1, INTERVAL 1 HOUR);

    # fetch min/max values of current hour
    SELECT MIN(`level`), MAX(`level`) 
    INTO @min, @max
    FROM `water_level`
    WHERE `water_level`.`time` BETWEEN @t1 AND @t2;

    # care for empty values at beginning of each hour
    IF @min IS NULL THEN
        SET @min := 0;
    END IF;
    IF @max IS NULL THEN
        SET @max := 0;
    END IF;

    # write min/max to hourly log
    INSERT INTO `water_level_hourly` (`time`, `min`, `max`) 
    VALUES (@t1, @min, @max) 
    ON DUPLICATE KEY UPDATE `min`=@min, `max`=@max;
END
//
DELIMITER ;

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `water_level_hourly` (
  `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `min` int(11) NOT NULL,
  `max` int(11) NOT NULL,
  PRIMARY KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Trigger `water_level_hourly`
--
DROP TRIGGER IF EXISTS `trg_water_level_hourly_insert`;
DELIMITER //
CREATE TRIGGER `trg_water_level_hourly_insert` AFTER INSERT ON `water_level_hourly`
 FOR EACH ROW BEGIN
    DECLARE t DATE;
    DECLARE min, max FLOAT;

    # create date value for current day
    SET @t := DATE(NEW.`time`);

    # get min/max value for current day
    SELECT MIN(`min`), MAX(`max`) 
    INTO @min, @max
    FROM `water_level_hourly`
    WHERE DATE(`water_level_hourly`.`time`) = @t;

    # care for empty values at beginning of each day
    IF @min IS NULL THEN
        SET @min := -1;
    END IF;
    IF @max IS NULL THEN
        SET @max := -1;
    END IF;

    # write min/max into daily log
    INSERT INTO `water_level_daily` (`time`, `min`, `max`) 
    VALUES (@t, @min, @max) 
    ON DUPLICATE KEY UPDATE `min`=@min, `max`=@max;
END
//
DELIMITER ;

DROP TRIGGER IF EXISTS `trg_water_level_hourly_update`;
DELIMITER //
CREATE TRIGGER `trg_water_level_hourly_update` AFTER UPDATE ON `water_level_hourly`
 FOR EACH ROW BEGIN
    DECLARE t DATE;
    DECLARE min, max FLOAT;

    # create date value for current day
    SET @t := DATE(NEW.`time`);

    # get min/max value for current day
    SELECT MIN(`min`), MAX(`max`) 
    INTO @min, @max
    FROM `water_level_hourly`
    WHERE DATE(`water_level_hourly`.`time`) = @t;

    # care for empty values at beginning of each day
    IF @min IS NULL THEN
        SET @min := -1;
    END IF;
    IF @max IS NULL THEN
        SET @max := -1;
    END IF;

    # write min/max into daily log
    INSERT INTO `water_level_daily` (`time`, `min`, `max`) 
    VALUES (@t, @min, @max) 
    ON DUPLICATE KEY UPDATE `min`=@min, `max`=@max;
END
//
DELIMITER ;

-- --------------------------------------------------------
CREATE TABLE IF NOT EXISTS `water_level_daily` (
  `time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `min` int(11) NOT NULL,
  `max` int(11) NOT NULL,
  PRIMARY KEY (`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The following statement should create '42' in all three tables, but does only in the first two:

USE `homebusdata`;
INSERT INTO `water_level` (`time`, `level`) VALUES (NOW(), 42);

Upvotes: 0

Views: 270

Answers (2)

user6188895
user6188895

Reputation: 1

I think this behavoir is caused by naming variables MIN and MAx because MIN and MAx are name of agreggate functions in mysql. So when you call MIN() function in your instruction, the resolution stack have a scalar variable with these name, not a function.

Upvotes: 0

Vatev
Vatev

Reputation: 7590

After some fiddling I managed to get it working by renaming the variables min and max to something else. The other changes are probably not necessary.

BEGIN
    DECLARE min_, max_ FLOAT;
    DECLARE t DATE DEFAULT NULL;
    SET t = DATE(new.time);
    # get min/max value for current day
    SELECT MIN(`min`), MAX(`max`) 
    FROM `water_level_hourly`
    WHERE DATE(`water_level_hourly`.`time`) = t INTO min_, max_;

    # care for empty values at beginning of each day
    IF min_ IS NULL THEN
        SET min_ := -1;
    END IF;
    IF max_ IS NULL THEN
        SET max_ := -1;
    END IF;

    # write min/max into daily log
    INSERT INTO `water_level_daily` (`time`, `min`, `max`) 
    VALUES (t, min_, max_) 
    ON DUPLICATE KEY UPDATE `min`=VALUES(min), `max`=VALUES(max);
END

Unfortunately I'm not sure why that is a problem. I guess it gets confused by the SELECT ... INTO having the same field names as the variables.

Upvotes: 1

Related Questions