Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.9.3
-
k8s (Ubuntu 21.10 5.13.0-52-generic)
Description
The error is observed on the slave instance of mariadb.
Two databases are (ROW) replicated from the MySQL master.
Replication makes changes to table data. With each change, it executes a trigger that marks the day in the results table for which data needs to be recalculated. The trigger in the results table sees the modify label and recalculates the value for the updated day
The messages table has triggers for insert/update/delete to copy data to an analytics staging table like:
CREATE DEFINER=`root`@`%` TRIGGER messages_1_ins
AFTER INSERT
ON messages FOR EACH row
begin
INSERT INTO messages_1 (id,`Type`,cancelReason,addate) VALUES (NEW.id,NEW.TYPE,NEW.cancelReason,DATE(NEW.addate));
end
Table messages_1 has triggers to mark changed days in the results table like:
CREATE DEFINER=`root`@`%` TRIGGER messages_stats_ins
AFTER INSERT
ON messages_1 FOR EACH ROW
begin
update stats_time.baseDB e set `modify`=1, comment="auto" where e.`date`= DATE(NEW.addate);
end
Result table:
CREATE TABLE `baseDB` (
`date` varchar(100) NOT NULL,
`modify` tinyint(4) NOT NULL DEFAULT 0,
`comment` varchar(100) NOT NULL DEFAULT '',
`1_3_1` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
Trigger in results table like:
CREATE TRIGGER stats_upd
AFTER UPDATE
ON baseDB FOR EACH ROW
BEGIN
IF (NEW.modify = 1 AND NEW.comment="auto")
THEN
call stats_time.upd_all(NEW.`date`, NEW.`date`);
UPDATE baseDB e SET e.`modify` = 0, e.comment = "" WHERE e.`date` = NEW.`date`;
insert into stats_log(logs) values (concat("stats_upd:", NEW.`date`));
END IF;
END
CREATE PROCEDURE stats_time.upd_all(dateStart DATE, dateEnd DATE, newComment varchar(100))
BEGIN
call stats_time.x_1_3_1(dateStart, dateEnd, newComment);
insert into stats_log(logs) values (concat(newComment, ": ", dateStart));
END $$
CREATE DEFINER=`root`@`%` PROCEDURE stats_time.x_1_3_1(dateStart DATE, dateEnd DATE, newComment varchar(100))
BEGIN
DECLARE res INT;
ALTER TABLE stats_time.x ADD COLUMN IF NOT EXISTS 1_3_1 INT DEFAULT 0 NOT NULL;
WHILE dateStart <= dateEnd DO
SET res = stats_time.calc_x_1_3_1(dateStart);
INSERT INTO stats_time.x (`date`, `modify`, 1_3_1 ,comment)
VALUES (dateStart, 0, res, newComment) ON DUPLICATE KEY UPDATE
`modify` = 0, 1_3_1 = res, comment = newComment;
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END $$
CREATE DEFINER=`root`@`%` FUNCTION stats_time.calc_x_1_3_1(calcDate DATE)
RETURNS INT READS SQL DATA
BEGIN
DECLARE res INT;
Select COUNT(id) FROM dbdb.messages_1 WHERE addate=calcDate into res; #(1-2 ms)
RETURN res;
END $$
Also tried to stop replication when the stats_upd trigger is activated and put the trigger commands in a transaction - the same result.
Currently, the option to process marked lines is the scheduler, as well as their processing by a third-party (python) handler
Attachments
Issue Links
- duplicates
-
MDEV-29894 Calling a function from a different database in a slave side trigger crashes
- Closed