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

Trigger prevents insert after update to 10.1.21-MariaDB-1~trusty

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Fixed
    • Affects Version/s: 10.1.21
    • Fix Version/s: 10.1.22, 10.2.5
    • Component/s: Server, Triggers
    • Labels:
      None
    • Environment:
      Ubuntu Trusty 14.04LTS
    • Sprint:
      10.1.22

      Description

      After upgrading to the latest version from repository (apt-get update && apt-get upgrade), I can no longer insert into tables that have a trigger.

      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 211
      Server version: 10.1.21-MariaDB-1~trusty mariadb.org binary distribution
       
      Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [(none)]> use devdb;
       
      MariaDB [devdb]> drop trigger token_user;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [devdb]> insert into user (email) values ("junk1@r.com");
      Query OK, 1 row affected, 4 warnings (0.01 sec)
      

      MariaDB [devdb]> describe user;
      | Field                    | Type             | Null | Key | Default             | Extra          |
      | user_id                  | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
      | created                  | timestamp        | YES  |     | CURRENT_TIMESTAMP   |                |
      | token                    | varchar(255)     | YES  |     |                     |                |
      ... There are 69 other rows ....
      72 rows in set (0.00 sec)
       
      MariaDB [devdb]> update user set token = SHA1(RAND()) where user_id = last_insert_id();
      Query OK, 1 row affected, 1 warning (0.00 sec)
      Rows matched: 1  Changed: 1  Warnings: 1
      

      MariaDB [devdb]> DELIMITER ;;
      MariaDB [devdb]> CREATE TRIGGER token_user
          -> BEFORE INSERT ON user
          -> FOR EACH ROW
          -> BEGIN
          -> SET NEW.token = SHA1(RAND());
          -> END ;;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [devdb]> DELIMITER ;
      MariaDB [devdb]> 
      MariaDB [devdb]> insert into user (email) values ("junk2@r.com");
      Query OK, 0 rows affected (0.00 sec)
      

      MariaDB [devdb]> drop trigger token_user;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [devdb]> insert into user (email) values ("junk2@r.com");
      Query OK, 1 row affected, 4 warnings (0.00 sec)
       
      The warnings don't appear relevant as they were there before:
       
      MariaDB [devdb]> show warnings;
      +---------+------+-------------------------------------------------------+
      | Level   | Code | Message                                               |
      +---------+------+-------------------------------------------------------+
      | Warning | 1364 | Field 'store' doesn't have a default value            |
      | Warning | 1364 | Field 'expiration_date' doesn't have a default value  |
      | Warning | 1364 | Field 'scope' doesn't have a default value            |
      | Warning | 1364 | Field 'type' doesn't have a default value |
      +---------+------+-------------------------------------------------------+
      4 rows in set (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                resultsathand Jonathan Brown
              • Votes:
                1 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: