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

Problem with INSERT SELECT on NOT NULL columns while having BEFORE UPDATE trigger

Details

    Description

      Hello,

      Until version 10.11.10 i had no problem with the following statements,
      but since 10.11.11 i get the error "ERROR 1048 (23000): Column 'ARTICLE_ID' cannot be null" on the last INSERT statement.
      The only way i was able to get over the error is by removing BEFORE UPDATE trigger (table2_before_upd_tr).

      DROP TABLE IF EXISTS table1;
      		 
      DROP TABLE IF EXISTS table2;
      		 
       
      		 
      CREATE TABLE table1 (
      		 
          ARTICLE_ID INT NOT NULL,  
          DEPOT_ID INT NOT NULL,  
          BARSY_ID INT NOT NULL  
      ) ENGINE=InnoDB;
      		 
       
      		 
      INSERT INTO table1 (ARTICLE_ID, DEPOT_ID, BARSY_ID)  
      VALUES(1, 2, 3), (4, 5, 6);
      		 
       
      		 
      CREATE TABLE `table2` (
      		 
        `ARTICLE_ID` int(11) NOT NULL,
      		 
        `DEPOT_ID` int(11) NOT NULL,
      		 
        `BARSY_ID` int(11) NOT NULL
      		 
      ) ENGINE=InnoDB;
      		 
       
      		 
      -- if i dont create the following trigger (table2_before_upd_tr), i get no error on the next INSERT ... SELECT
      		 
      DELIMITER ;;
      		 
      DROP TRIGGER  IF EXISTS `table2_before_upd_tr`;;
      		 
       
      		 
      CREATE TRIGGER `table2_before_upd_tr` BEFORE UPDATE ON `table2` FOR EACH ROW BEGIN
      		 
        
      END;;
      		 
      DELIMITER ;
      		 
       
      		 
      INSERT INTO table2 (ARTICLE_ID,DEPOT_ID,BARSY_ID)
      		 
      SELECT ARTICLE_ID, DEPOT_ID, BARSY_ID FROM table1;

      Attachments

        Issue Links

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19761 Before Trigger not processed for Not Null Columns if no explicit value and no DEFAULT

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36060 Warning: (1048) Column 'xxxxxxx' cannot be null

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36066 v10.11.11 introduces bug that breaks production code - INSERT SELECT and triggres not working

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36191 Multi-row insert fails on table with update before trigger

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36224 Unexpected behavior with triggers.

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36262 INSERT ... SELECT fails with BEFORE INSERT trigger in MariaDB 10.5.28 (NULL values for foreign-key fields)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36284 MariaDB: INSERT ... SELECT fails with "Column cannot be null" error when a BEFORE INSERT trigger modifies the column

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            dpankov David Pankov created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik added a comment - - edited

            Apparently, caused by MDEV-19761.
            Sorry for this, we'll fix it in the next release.

            serg Sergei Golubchik added a comment - - edited Apparently, caused by MDEV-19761 . Sorry for this, we'll fix it in the next release.
            serg Sergei Golubchik made changes -
            Assignee Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]
            Fix Version/s 11.8 [ 29921 ]
            Fix Version/s 11.7 [ 29815 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.7 [ 29815 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Blocker [ 1 ]
            serg Sergei Golubchik made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 11.7.2 [ 29914 ]
            Fix Version/s 11.8 [ 29921 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.5.29 [ 29996 ]
            Fix Version/s 10.6.22 [ 29997 ]
            Fix Version/s 10.11.12 [ 29998 ]
            Fix Version/s 11.4.6 [ 29999 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]
            serg Sergei Golubchik made changes -
            Resolution Fixed [ 1 ]
            Status In Testing [ 10301 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Component/s Triggers [ 10109 ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            elenst Elena Stepanova made changes -
            Roman Salis Roman Salis made changes -
            alice Alice Sherepa made changes -
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -

            People

              serg Sergei Golubchik
              dpankov David Pankov
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.