Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.5.28, 10.6.21
-
None
-
None
-
FreeBSD 14.2
Description
After upgrading MariaDB on two different servers (10.5.26 -> 10.5.28 and 10.6.19 -> 10.6.21), both from FreeBSD 14.2 packages, an after insert trigger fails to add a record in another table. Exact same database and code on both servers.
The error :
SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`csp`.`reunion_item`, CONSTRAINT `reunion_item_ibfk_1` FOREIGN KEY (`reunion_id`) REFERENCES `reunion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
|
The trigger :
BEGIN
|
DECLARE tri INTEGER; |
|
|
INSERT INTO journal_assemblee (assemblee_id, texte) VALUES (NEW.assemblee_id, CONCAT('CREATION REUNION ', NEW.id, ' : ', NEW.type_reunion_id, '|', NEW.endroit_id, '|', NEW.debut, '|', IFNULL(NEW.complete, ''), '|', IFNULL(NEW.complete_par, ''), '|', IFNULL(NEW.odj_modifie, ''), '|', IFNULL(NEW.odj_mod_resolution, ''))); |
|
|
SELECT tri_service INTO tri FROM type_reunion WHERE id = NEW.type_reunion_id; |
|
|
IF (tri = 1) THEN |
INSERT INTO service_reunion (reunion_id, service_id, ordre) SELECT NEW.id, service_id, ordre_defaut FROM assemblee_service WHERE assemblee_id = NEW.assemblee_id; |
INSERT INTO reunion_item (reunion_id, description, ordre, service_id, preambule) SELECT NEW.id, description, ordre_defaut, service_id, preambule FROM modele_item WHERE assemblee_id = NEW.assemblee_id AND type_reunion_id = NEW.type_reunion_id; |
ELSE |
INSERT INTO reunion_item (reunion_id, description, ordre, categorie_id, preambule) SELECT NEW.id, description, ordre_defaut, categorie_id, preambule FROM modele_item WHERE assemblee_id = NEW.assemblee_id AND type_reunion_id = NEW.type_reunion_id; |
END IF; |
END |
Tables :
CREATE TABLE `reunion` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`type_reunion_id` int(11) NOT NULL, |
`debut` datetime NOT NULL, |
`assemblee_id` int(11) NOT NULL, |
`endroit_id` int(11) NOT NULL, |
`complete` datetime DEFAULT NULL, |
`complete_par` varchar(64) DEFAULT NULL, |
`odj_modifie` datetime DEFAULT NULL, |
`odj_mod_resolution` varchar(16) DEFAULT NULL, |
`revision` datetime DEFAULT NULL, |
PRIMARY KEY (`id`), |
KEY `type_reunion_id` (`type_reunion_id`), |
KEY `assemblee_id` (`assemblee_id`), |
KEY `endroit_id` (`endroit_id`), |
KEY `complete_par` (`complete_par`), |
CONSTRAINT `reunion_ibfk_1` FOREIGN KEY (`type_reunion_id`) REFERENCES `type_reunion` (`id`) ON UPDATE CASCADE, |
CONSTRAINT `reunion_ibfk_2` FOREIGN KEY (`assemblee_id`) REFERENCES `assemblee` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `reunion_ibfk_3` FOREIGN KEY (`endroit_id`) REFERENCES `endroit` (`id`) ON UPDATE CASCADE, |
CONSTRAINT `reunion_ibfk_4` FOREIGN KEY (`complete_par`) REFERENCES `usager` (`id`) ON UPDATE CASCADE |
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
|
|
CREATE TABLE `reunion_item` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`reunion_id` int(11) NOT NULL, |
`description` varchar(1024) NOT NULL, |
`service_id` int(11) DEFAULT NULL, |
`categorie_id` int(11) DEFAULT NULL, |
`ordre` int(11) NOT NULL DEFAULT 0, |
`ajout` int(11) NOT NULL DEFAULT 0, |
`preambule` int(11) NOT NULL DEFAULT 0, |
`publication` timestamp NOT NULL DEFAULT current_timestamp(), |
`retire` int(11) NOT NULL DEFAULT 0, |
PRIMARY KEY (`id`), |
KEY `reunion_id` (`reunion_id`), |
KEY `service_id` (`service_id`), |
KEY `categorie_id` (`categorie_id`), |
CONSTRAINT `reunion_item_ibfk_1` FOREIGN KEY (`reunion_id`) REFERENCES `reunion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `reunion_item_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`) ON UPDATE CASCADE, |
CONSTRAINT `reunion_item_ibfk_3` FOREIGN KEY (`categorie_id`) REFERENCES `categorie` (`id`) ON UPDATE CASCADE |
) ENGINE=InnoDB AUTO_INCREMENT=3556 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci |
A rollback to 10.5.26 / 10.6.19 fixes the problem.
Attachments
Activity
CREATE TABLE `service_reunion` ( |
`reunion_id` int(11) NOT NULL, |
`service_id` int(11) NOT NULL, |
`ordre` int(11) NOT NULL DEFAULT 0, |
`heure` time NOT NULL DEFAULT '00:00:00', |
PRIMARY KEY (`reunion_id`,`service_id`), |
KEY `service_id` (`service_id`), |
CONSTRAINT `service_reunion_ibfk_1` FOREIGN KEY (`reunion_id`) REFERENCES `reunion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `service_reunion_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`) ON UPDATE CASCADE |
CREATE TABLE `modele_item` ( |
`id` int(11) NOT NULL AUTO_INCREMENT, |
`assemblee_id` int(11) NOT NULL, |
`type_reunion_id` int(11) NOT NULL, |
`description` varchar(1024) NOT NULL, |
`service_id` int(11) DEFAULT NULL, |
`categorie_id` int(11) DEFAULT NULL, |
`ordre_defaut` int(11) NOT NULL DEFAULT 99, |
`preambule` int(11) NOT NULL DEFAULT 0, |
PRIMARY KEY (`id`), |
KEY `assemblee_id` (`assemblee_id`), |
KEY `type_reunion_id` (`type_reunion_id`), |
KEY `service_id` (`service_id`), |
KEY `categorie_id` (`categorie_id`), |
CONSTRAINT `modele_item_ibfk_1` FOREIGN KEY (`assemblee_id`) REFERENCES `assemblee` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `modele_item_ibfk_2` FOREIGN KEY (`type_reunion_id`) REFERENCES `type_reunion` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `modele_item_ibfk_3` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`) ON UPDATE CASCADE, |
CONSTRAINT `modele_item_ibfk_4` FOREIGN KEY (`categorie_id`) REFERENCES `categorie` (`id`) ON UPDATE CASCADE |
CREATE TABLE `assemblee_service` ( |
`assemblee_id` int(11) NOT NULL, |
`service_id` int(11) NOT NULL, |
`ordre_defaut` int(11) NOT NULL DEFAULT 0, |
PRIMARY KEY (`assemblee_id`,`service_id`), |
KEY `service_id` (`service_id`), |
CONSTRAINT `assemblee_service_ibfk_1` FOREIGN KEY (`assemblee_id`) REFERENCES `assemblee` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, |
CONSTRAINT `assemblee_service_ibfk_2` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`) ON UPDATE CASCADE |
This has been confirmed to be related to when there are records in the modele_item table, to be inserted by the trigger in reunion_item. When there are no related records in this table, the original insert completes successfully.
Could you please provide also SHOW CREATE TABLE for the other tables, that are involved?