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

            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.

            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.