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

BEFORE INSERT triggers dont issue 1406 error

    XMLWordPrintable

Details

    • 10.1.7-1, 10.1.7-2, 10.1.8-1, 10.1.8-3, 10.1.8-4, 10.1.9-2, 10.1.9-3

    Description

      I created a BEFORE INSERT trigger which modifies a value making it too long. Despite the sql_mode, no error/warning appears.

      MariaDB [test]> USE test;
      Database changed
      MariaDB [test]> SET @@session.sql_mode = 'STRICT_ALL_TABLES,STRICT_TRANS_TABLES'
      ;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [test]> DROP TABLE IF EXISTS t1;
      Query OK, 0 rows affected (0.06 sec)
       
      MariaDB [test]> CREATE TABLE t1 (
          ->  c CHAR(1) NOT NULL
          -> ) ENGINE = InnoDB;
      Query OK, 0 rows affected (0.08 sec)
       
      MariaDB [test]> DELIMITER ||
      MariaDB [test]> CREATE TRIGGER t1_bi
          ->  BEFORE INSERT
          ->  ON t1
          ->  FOR EACH ROW
          -> BEGIN
          ->  SET NEW.c = 'www';
          -> END;
          -> ||
      Query OK, 0 rows affected (0.04 sec)
       
      MariaDB [test]> DELIMITER ;
      MariaDB [test]> INSERT INTO t1 VALUES ('a');
      Query OK, 1 row affected (0.00 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> SHOW WARNINGS;
      Empty set (0.00 sec)
       
      MariaDB [test]> SELECT * FROM t1;
      +---+
      | c |
      +---+
      | w |
      +---+
      1 row in set (0.00 sec)

      Sorry, i dont have a MySQL installed locally anymore, so i dont know if this bug is mainstream.

      Some notes...

      • only happens with before insert triggers, not with update triggers or procedures or functions
      • only happens when doing SET NEW.col, not when trying to UPDATE another table
      • only happens for long data, not if you try to set NULL a NOT NULL column

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              f_razzoli Federico Razzoli
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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