MySQL 8.0.22 Trigger won’t run unless definer is logged in

MySQLtriggerUbuntu

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

Best Answer

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

Related Question