Alexander Barkov added a comment - 2021-10-14 11:17 - edited This is a combination of multiple issues. The trigger was actually wrong. MariaDB does not support yet Oracle's multi-event triggers like CREATE TRIGGER .. AFTER DELETE OR UPDATE and therefore does not support syntax IF DELETING or IF UPDATING inside the trigger. This task in on our TODO list: MDEV-10164 So DELETING is considered as a local undeclared variable. In 10.3 the CREATE PROCEDURE/FUNCTION/TRIGGER statement accepted undeclared variables, but the error later happened during the routine execution time. That was wrong. The trigger with the reported body should not have worked in 10.3 too - it should have returned the error during the execution time. In 10.4 we implemented a stricter control for undeclared variables. So 10.4 returns the error at CREATE time. I can confirm the empty trigger name in INFORMATION_SCHEMA.TRIGGERS output. I run this script in 10.3: CREATE OR REPLACE TABLE t1 (a INT ); INSERT INTO t1 VALUES (1); CREATE OR REPLACE TABLE t2 (a INT ); DELIMITER $$ CREATE OR REPLACE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN IF DELETING THEN INSERT INTO t2 VALUES (OLD.a); END IF ; END ; $$ DELIMITER ; DELETE FROM t1 WHERE a=1; Then stop the 10.3 server and start the 10.4 server on top of the same database directory and run this statement: SELECT trigger_name, action_statement FROM information_schema.triggers WHERE event_object_table= 't1' \G trigger_name: action_statement: CREATE DEFINER=``@`localhost` TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN IF DELETING THEN INSERT INTO t2 VALUES (OLD.a); END IF; END 1 row in set (0.004 sec) Notice, the trigger_name value is empty. The same problem is repeatable with SHOW TRIGGERS . I can also reproduce that 10.4 cannot now drop the trigger: DROP TRIGGER test.tr1; ERROR 1360 (HY000): Trigger does not exist This is certainly a bug. A simple workaround to drop the trigger: open the trigger definition file (e.g. $DBDIR/test/t1.TRG in my case) in a text editor fix the wrong syntax to a correct syntax: I changed IF DELETING to IF 1 run the DROP TRIGGER statement again Then you can recreate the trigger with a correct 10.4 syntax to avoid the undeclared variable error.

Alexander Barkov added a comment - 2022-01-13 07:05 - edited More observation: I run this script in 10.4, with a correct trigger definition: CREATE OR REPLACE TABLE t1 (a INT ); INSERT INTO t1 VALUES (1); CREATE OR REPLACE TABLE t2 (a INT ); DELIMITER $$ CREATE OR REPLACE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW BEGIN IF OLD.a THEN INSERT INTO t2 VALUES (OLD.a); END IF ; END ; $$ DELIMITER ; Now I edit $DATADIR/test/tr1.TRG and change: IF OLD.a THEN INSERT INTO t2 VALUES (OLD.a); END IF ; to IF DELETING THEN INSERT INTO t2 VALUES (OLD.a); END IF ; pretending that this trigger was created by some earlier MariaDB version. As a result, the trigger definition becomes incorrect (intentionally). Now I run: FLUSH TABLES; DROP TABLE t1; Then check the database directory: Opps. The file $DATADIR/test/tr1.TRN was not deleted! After that it's not possible to create triggers for t1 any more. An attempt to re-run the above script returns an error when the CREATE OR REPLACE TRIGGER is being executed: MariaDB [test]> DELIMITER $$ MariaDB [test]> CREATE OR REPLACE TRIGGER tr1 AFTER DELETE ON t1 FOR EACH ROW -> BEGIN -> IF OLD.a -> THEN -> INSERT INTO t2 VALUES (OLD.a); -> END IF; -> END; -> $$ ERROR 1360 (HY000): Trigger does not exist MariaDB [test]> DELIMITER

Alexander Barkov added a comment - 2022-01-13 10:58 Hi shulga , can you please review a patch: https://github.com/MariaDB/server/commit/ced4354e40f9cae86731d0b993022c0921babbd3 Thanks

Dmitry Shulga added a comment - 2022-01-13 13:19 Looks good for me