[MDEV-25659] trigger name is empty after upgrade to 10.4 Created: 2021-05-12  Updated: 2022-01-14  Resolved: 2022-01-14

Status: Closed
Project: MariaDB Server
Component/s: Stored routines, Triggers
Affects Version/s: 10.4, 10.5
Fix Version/s: 10.4.23, 10.5.14, 10.6.6, 10.7.2, 10.8.1

Type: Bug Priority: Critical
Reporter: Muhammad Irfan Assignee: Alexander Barkov
Resolution: Fixed Votes: 1
Labels: None


 Description   

Created trigger on 10.3 installation with oracle mode.

MariaDB [information_schema]> SELECT * FROM TRIGGERS\G
 
*************************** 1. row ***************************
 
           TRIGGER_CATALOG: def
 
            TRIGGER_SCHEMA: test
 
              TRIGGER_NAME: tr_sez
 
        EVENT_MANIPULATION: DELETE
 
      EVENT_OBJECT_CATALOG: def
 
       EVENT_OBJECT_SCHEMA: test
 
        EVENT_OBJECT_TABLE: sez
 
              ACTION_ORDER: 1
 
          ACTION_CONDITION: NULL
 
          ACTION_STATEMENT: declare akon char(1);
 
begin
 
akon := 'U';
 
if deleting then akon := 'D';
 
end if;
 
delete from sachb_z
 
where mand = :old.mand
 
and id_role = :old.id_role
 
and sach_nr in (select sach_nr from sach_b
 
where mand = :old.mand
 
and id_se = :old.id_se);
 
end
 
        ACTION_ORIENTATION: ROW
 
             ACTION_TIMING: AFTER
 
ACTION_REFERENCE_OLD_TABLE: NULL
 
ACTION_REFERENCE_NEW_TABLE: NULL
 
  ACTION_REFERENCE_OLD_ROW: OLD
 
  ACTION_REFERENCE_NEW_ROW: NEW
 
                   CREATED: 2021-05-12 02:18:25.52
 
                  SQL_MODE: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
 
                   DEFINER: root@localhost
 
      CHARACTER_SET_CLIENT: latin1
 
      COLLATION_CONNECTION: latin1_swedish_ci
 
        DATABASE_COLLATION: latin1_swedish_ci
 
1 row in set (0.004 sec)

And after upgrade to 10.4, TRIGGER_NAME column is empty.

MariaDB [test]> select * from information_schema.triggers\G
 
*************************** 1. row ***************************
 
           TRIGGER_CATALOG: def
 
            TRIGGER_SCHEMA: test
 
              TRIGGER_NAME: 
        EVENT_MANIPULATION: DELETE
 
      EVENT_OBJECT_CATALOG: def
 
       EVENT_OBJECT_SCHEMA: test
 
        EVENT_OBJECT_TABLE: sez
 
              ACTION_ORDER: 1
 
          ACTION_CONDITION: NULL
 
          ACTION_STATEMENT: CREATE DEFINER="root"@"localhost" trigger tr_sez
 
after delete on sez
 
for each row 
declare akon char(1);
 
begin
 
akon := 'U';
 
if deleting then akon := 'D';
 
end if;
 
delete from sachb_z
 
where mand = :old.mand
 
and id_role = :old.id_role
 
and sach_nr in (select sach_nr from sach_b
 
where mand = :old.mand
 
and id_se = :old.id_se);
 
end
 
        ACTION_ORIENTATION: ROW
 
             ACTION_TIMING: AFTER
 
ACTION_REFERENCE_OLD_TABLE: NULL
 
ACTION_REFERENCE_NEW_TABLE: NULL
 
  ACTION_REFERENCE_OLD_ROW: OLD
 
  ACTION_REFERENCE_NEW_ROW: NEW
 
                   CREATED: 2021-05-12 02:18:25.52
 
                  SQL_MODE: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT
 
                   DEFINER: 
      CHARACTER_SET_CLIENT: latin1
 
      COLLATION_CONNECTION: latin1_swedish_ci
 
        DATABASE_COLLATION: latin1_swedish_ci
 
1 row in set (0.002 sec)

And can't find way to drop trigger or write to table in question. Tried to set same sql_mode as for trigger and bounce server but no luck.

MariaDB> INSERT INTO test.sez (mand,id_se,id_role) VALUES (1,2,5);
 
ERROR 1064 (42000): Unknown trigger has an error in its body: 'Undeclared variable: deleting'



 Comments   
Comment by Alexander Barkov [ 2021-10-14 ]

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.

Comment by Alexander Barkov [ 2022-01-13 ]

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

Comment by Alexander Barkov [ 2022-01-13 ]

Hi shulga, can you please review a patch:
https://github.com/MariaDB/server/commit/ced4354e40f9cae86731d0b993022c0921babbd3

Thanks

Comment by Dmitry Shulga [ 2022-01-13 ]

Looks good for me

Generated at Thu Feb 08 09:39:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.