VPN (Victorian Poetry Network) trigger bug (fixed, we hope)
Editing and saving an author was throwing the following error:
Error: syntax error Source File: https://hcmc.uvic.ca/people/martin/vpn/live/editor/save_rec.php Line: 2, Column: 1 Source Code: Could not successfully run query (UPDATE `authors` SET `au_displayName` = 'Balfour, Marie Clothilde' , `au_surname` = 'Balfour' , `au_firstnames` = 'Marie Clothilde' , `au_honorific` = '' , `au_quals` = '' , `au_birth` = '' , `au_death` = '' , `au_gender` = '2' , `au_nationality` = '' , `au_links` = '' , `au_notes` = '' WHERE `au_id` = 466) from DB: Incorrect number of arguments for PROCEDURE vpn.author_poem_loop; expected 3, got 2
It looks as though this error has been there since JN made his trigger changes a week last Friday. I did a full dump of the db, so that it included the procedures and triggers, and found the following:
-- -- Triggers `authors` -- DROP TRIGGER IF EXISTS `vpn`.`author_update`; DELIMITER // CREATE TRIGGER `vpn`.`author_update` AFTER UPDATE ON `vpn`.`authors` FOR EACH ROW BEGIN DECLARE author_poem_content text; CALL author_poem_loop(new.au_id, author_poem_content); END // DELIMITER ; DROP TRIGGER IF EXISTS `vpn`.`author_delete`; DELIMITER // CREATE TRIGGER `vpn`.`author_delete` BEFORE DELETE ON `vpn`.`authors` FOR EACH ROW BEGIN DECLARE author_poem_content text; CALL author_poem_loop(old.au_id, author_poem_content, old.au_id); END // DELIMITER ;
The first trigger calls author_poem_loop with only two arguments; the second uses three, and the function signature expects three. The third argument is "exclude_id", which is passed to the author_loop function, where its function seems to be to allow you to avoid processing an author which you're actually in the process of deleting. When we're doing an update rather than a delete, presumably this does not apply, so we decided that passing -1, which would not match any author_id, would be the safest behaviour here. Accordingly, we modified the trigger. We did that at the MySQL command line: ssh hcmc@mysqldev, then mysql -u hcmc -p vpn, which logs you into that db. Finally, this is what we ran:
DROP TRIGGER IF EXISTS `vpn`.`author_update`; DELIMITER // CREATE TRIGGER `vpn`.`author_update` AFTER UPDATE ON `vpn`.`authors` FOR EACH ROW BEGIN DECLARE author_poem_content text; CALL author_poem_loop(new.au_id, author_poem_content, -1); END // DELIMITER ;
This appears to have solved the immediate problem -- we can now save changes to authors -- but we should watch out for any other issues that might have arisen out of the re-introduction of triggers.