Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-40436

Trigger using a package-defined type is silently lost during mariadb-dump / restore

    XMLWordPrintable

Details

    • Not for Release Notes
    • Q3/2026 Server Maintenance

    Description

      Trigger using a package-defined type is silently lost during mariadb-dump / restore.

      MTR Test

      SET sql_mode=ORACLE;
      CREATE DATABASE db1;
      USE db1;
      DELIMITER $$;
      CREATE PACKAGE pkg AS
        TYPE rec IS RECORD (a INT, b VARCHAR(20));
      END;
      $$
      DELIMITER ;$$
      CREATE TABLE t1 (id INT, val VARCHAR(20));
      DELIMITER $$;
      CREATE TRIGGER trg BEFORE INSERT ON t1
      FOR EACH ROW
      DECLARE
        r pkg.rec;
      BEGIN
        :NEW.val := CONCAT('r-', :NEW.id);
      END;
      $$
      DELIMITER ;$$
      --exec $MYSQL_DUMP --skip-comments --routines --triggers --databases db1 > $MYSQLTEST_VARDIR/tmp/i004.sql
      DROP DATABASE db1;
      --exec $MYSQL --force < $MYSQLTEST_VARDIR/tmp/i004.sql 2>&1
      --echo # trigger_count should be 1 but is 0 -> trigger lost
      SELECT COUNT(*) AS trigger_count FROM information_schema.triggers
      WHERE event_object_schema='db1' AND trigger_name='trg';
      --remove_file $MYSQLTEST_VARDIR/tmp/i004.sql
      DROP DATABASE db1;
      

      MTR output

      SET sql_mode=ORACLE;
      CREATE DATABASE db1;
      USE db1;
      CREATE PACKAGE pkg AS
      TYPE rec IS RECORD (a INT, b VARCHAR(20));
      END;
      $$
      CREATE TABLE t1 (id INT, val VARCHAR(20));
      CREATE TRIGGER trg BEFORE INSERT ON t1
      FOR EACH ROW
      DECLARE
      r pkg.rec;
      BEGIN
      :NEW.val := CONCAT('r-', :NEW.id);
      END;
      $$
      DROP DATABASE db1;
      --------------
      /*!50003 CREATE*/ /*!50017 DEFINER="root"@"localhost"*/ /*!50003 TRIGGER trg BEFORE INSERT ON t1
      FOR EACH ROW
      DECLARE
      r pkg.rec;
      BEGIN
      :NEW.val := CONCAT('r-', :NEW.id);
      END
      */
      --------------
       
      ERROR 4161 (HY000) at line 42: Unknown data type: '`pkg`.`rec`'
      # trigger_count should be 1 but is 0 -> trigger lost
      SELECT COUNT(*) AS trigger_count FROM information_schema.triggers
      WHERE event_object_schema='db1' AND trigger_name='trg';
      trigger_count
      0
      

      Attachments

        1. dump.txt
          4 kB
          Ramesh Sivaraman

        Issue Links

          Activity

            People

              bar Alexander Barkov
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 4.7h Original Estimate - 4.7h
                  4.7h
                  Remaining:
                  Time Spent - 0.5h Remaining Estimate - 4.7h
                  4.7h
                  Logged:
                  Time Spent - 0.5h Remaining Estimate - 4.7h
                  0.5h

                  Git Integration

                    Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.