1

I have the following tigger that used to work before upgrading to MySQL 8 from MySQL 5. Now the trigger will only run if the definer is logged in via phpmyadmin or some other database admin tool. The trigger is setup as timing After and event Insert.

 CREATE TRIGGER `countupdate` AFTER INSERT ON `status`
 FOR EACH ROW BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`);
       IF @COUNT > 0 THEN 
        IF NEW.`status` = 'UP'
        THEN 
        UPDATE `updowncount`
        SET `upcount` = `upcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        ELSE
        UPDATE `updowncount`
        SET `downcount` = `downcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        END IF;
    END IF;
 END

I have tried deleting the trigger then creating it again and setting the definer to root@%. I also tried definer root@localhost.

Running on Ubuntu 20.04.1

UPDATE: Also it appears that I have to go back in and re-save the trigger or it won't trigger.

Log file during an insert that did not update data based on the tigger:

2020-12-14T02:06:09.569622Z   794 Query INSERT INTO status (systemname, status, lastchecked) VALUES ('Linuxdb2', 'Up', '2020-12-13 20:06:09.569394')
2020-12-14T02:06:09.569808Z   794 Query SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`)
2020-12-14T02:06:09.570132Z   794 Query SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`)
2020-12-14T02:06:09.574412Z   794 Query COMMIT

When status table has a new row inserted two tiggers run. The one above that has stopped working and one to add the system to the updowncount table. If the systemname doesn't exist in updowncount table. This is a before for insert trigger.

 CREATE TRIGGER `countsystemadd` BEFORE INSERT ON `status`
 FOR EACH ROW BEGIN
       SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`);
       IF @COUNT=0 THEN
        INSERT INTO `updowncount` (upcount, downcount, systemname, `lastupdate`) VALUES (0, 0, NEW.`systemname`, NOW());
       END IF;
END

Thirteen new rows are all added with in seconds of each other by different python scripts.

Status table:

CREATE TABLE `status` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `systemname` char(40) DEFAULT NULL,
  `status` char(40) DEFAULT NULL,
  `lastchecked` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8

Updowncount table:

CREATE TABLE `updowncount` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `upcount` int DEFAULT NULL,
  `downcount` int DEFAULT NULL,
  `systemname` text,
  `lastupdate` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8

UPDATE: I removed the countsystemadd trigger and things started working. But once the data in the status table gets over 1,000 records the it stops working. This what shows up in the log.

2021-01-08T15:00:01.829948Z  1348 Query INSERT INTO status (systemname, status, lastchecked) VALUES ('msmccam', 'Up', '2021-01-08 09:00:01.829712')
2021-01-08T15:00:01.830210Z  1348 Query SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`)
2021-01-08T15:00:01.831031Z  1348 Query COMMIT
9
  • so when you loged ln as another user and insert a row, it doesn't run the trigger? without an error or log entry?
    – nbk
    Dec 13, 2020 at 23:51
  • @nbk No, I have updated the question with additional information based on your question.
    – MwcsMac
    Dec 14, 2020 at 2:12
  • Please provide complete and unchanged trigger creation script, not the body only. Also provide DLL for the tables involved and the statement which must fire the trigger.
    – Akina
    Dec 14, 2020 at 4:48
  • everything is running, you have zwei trigger s one for staus and one for updowncount that look similar, and i think the ne for updowncount skips the update
    – nbk
    Dec 14, 2020 at 10:03
  • @Akina I don't have creation script as I used phpmyadmin add trigger to create it. I am not sure what you mean by DLL for the tables. See update.
    – MwcsMac
    Dec 15, 2020 at 16:36

1 Answer 1

1

Everything is fine, with your triggers, as long as you don't have also a Trgger on updowncount

CREATE TABLE `status` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `systemname` char(40) DEFAULT NULL,
  `status` char(40) DEFAULT NULL,
  `lastchecked` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=444 DEFAULT CHARSET=utf8
CREATE TABLE `updowncount` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `upcount` int DEFAULT NULL,
  `downcount` int DEFAULT NULL,
  `systemname` text,
  `lastupdate` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
 CREATE TRIGGER `countsystemadd` BEFORE INSERT ON `status`
 FOR EACH ROW BEGIN
       SET @COUNT=(SELECT COUNT(*) FROM `updowncount` WHERE updowncount.`systemname`= NEW.`systemname`);
       IF @COUNT=0 THEN
        INSERT INTO `updowncount` (upcount, downcount, systemname, `lastupdate`) VALUES (0, 0, NEW.`systemname`, NOW());
       END IF;
END
 CREATE TRIGGER `countupdate` AFTER INSERT ON `status`
 FOR EACH ROW BEGIN
    SET @COUNT=(SELECT COUNT(*) FROM `status` WHERE status.`systemname`= NEW.`systemname`);
       IF @COUNT > 0 THEN 
        IF NEW.`status` = 'UP'
        THEN 
        UPDATE `updowncount`
        SET `upcount` = `upcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        ELSE
        UPDATE `updowncount`
        SET `downcount` = `downcount` +1, lastupdate = NOW()
        WHERE systemname = NEW.`systemname`;
        END IF;
    END IF;
 END
INSERT INTO status (`systemname`,`status`) VALUES  ('SystemA','UP')
SELECT * FROM updowncount
id | upcount | downcount | systemname | lastupdate         
-: | ------: | --------: | :--------- | :------------------
18 |       1 |         0 | SystemA    | 2020-12-15 19:18:43

db<>fiddle here

2
  • See update as the trigger has stopped working.
    – MwcsMac
    Jan 8, 2021 at 16:20
  • I run the code on my workstation, with 1848 INSERTS and it runs withouit a problem. so you must search for anathor reason like timeouts
    – nbk
    Jan 8, 2021 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.