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

after Trigger doesn't work correctly with bulk insert

Details

    Description

      CREATE TABLE `table1` (
      		 
       `id` int(11) DEFAULT NULL,
      		 
       `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
      		 
      ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1;
      		 
       
      CREATE TABLE `table2` (
      		 
       `id` int(11) DEFAULT NULL,
      		 
       `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
      		 
      ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1;
      		 
       
      CREATE TRIGGER `table1_after_insert` AFTER INSERT ON `table1`
      		 
       FOR EACH ROW insert into table2(`id`, `name`) values (NEW.`id`, NEW.`name`);

      When using bulk:

      try (PreparedStatement prep = sharedConnBinary.prepareStatement("INSERT INTO table1 VALUES (?,?)")) {
      		 
            prep.setInt(1, 1);
      		 
            prep.setString(2, "John");
      		 
            prep.addBatch();
      		 
       
      		 
            prep.setInt(1, 2);
      		 
            prep.setString(2, "Smith");
      		 
            prep.addBatch();
      		 
       
      		 
            prep.setInt(1, 3);
      		 
            prep.setString(2, "Carol");
      		 
            prep.addBatch();
      		 
       
      		 
            prep.executeBatch();
      		 
          }

      Expected rows on tables:
      Table1:
      "1" "John"
      "2" "Smith"
      "3" "Carol"

      Table2:
      "1" "John"
      "2" "Smith"
      "3" "Carol"

      Current rows on tables:
      Table1:
      "1" "John"

      Table2:
      "1" "John"
      "1" "John"

      This is exactly the same result than before MDEV-24411. It would seems MDEV-24411 only correct trigger BEFORE INSERT, not AFTER INSERT

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-24411 Trigger doesn't work correctly with bulk insert

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

          Activity

            Ascending order - Click to sort in descending order
            diego dupin Diego Dupin created issue -
            diego dupin Diego Dupin made changes -
            Field Original Value New Value
            diego dupin Diego Dupin made changes -
            Description
            {code:java}
            CREATE TABLE `table1` (
             `id` int(11) DEFAULT NULL,
             `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
            ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1;
             
            CREATE TABLE `table2` (
             `id` int(11) DEFAULT NULL,
             `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
            ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1;
             
            CREATE TRIGGER `table1_after_insert` AFTER INSERT ON `table1`
             FOR EACH ROW insert into table2(`id`, `name`) values (NEW.`id`, NEW.`name`);
            {code}

            When using bulk:

            {code:java}
            try (PreparedStatement prep = sharedConnBinary.prepareStatement("INSERT INTO table1 VALUES (?,?)")) {
                  prep.setInt(1, 1);
                  prep.setString(2, "John");
                  prep.addBatch();

                  prep.setInt(1, 2);
                  prep.setString(2, "Smith");
                  prep.addBatch();

                  prep.setInt(1, 3);
                  prep.setString(2, "Carol");
                  prep.addBatch();

                  prep.executeBatch();
                }
            {code}

            Expected rows on tables:
            Table1:
            "1" "John"
            "2" "Smith"
            "3" "Carol"

            Table2:
            "1" "John"
            "2" "Smith"
            "3" "Carol"

            Current rows on tables:
            Table1:
            "1" "John"

            Table2:
            "1" "John"
            "1" "John"

            This is exactly the same result than before MDEV-24411. It would seems MDEV-24411 only correct trigger BEFORE INSERT, not AFTER INSERT             		{code:java}
            CREATE TABLE `table1` (
             `id` int(11) DEFAULT NULL,
             `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
            ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1;
             
            CREATE TABLE `table2` (
             `id` int(11) DEFAULT NULL,
             `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL
            ) ENGINE=Aria DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci PAGE_CHECKSUM=1;
             
            CREATE TRIGGER `table1_after_insert` AFTER INSERT ON `table1`
             FOR EACH ROW insert into table2(`id`, `name`) values (NEW.`id`, NEW.`name`);
            {code}

            When using bulk:

            {code:java}
            try (PreparedStatement prep = sharedConnBinary.prepareStatement("INSERT INTO table1 VALUES (?,?)")) {
                  prep.setInt(1, 1);
                  prep.setString(2, "John");
                  prep.addBatch();

                  prep.setInt(1, 2);
                  prep.setString(2, "Smith");
                  prep.addBatch();

                  prep.setInt(1, 3);
                  prep.setString(2, "Carol");
                  prep.addBatch();

                  prep.executeBatch();
                }
            {code}

            Expected rows on tables:
            Table1:
            "1" "John"
            "2" "Smith"
            "3" "Carol"

            Table2:
            "1" "John"
            "2" "Smith"
            "3" "Carol"

            Current rows on tables:
            Table1:
            "1" "John"

            Table2:
            "1" "John"
            "1" "John"

            This is exactly the same result than before MDEV-24411. It would seems MDEV-24411 only correct trigger BEFORE INSERT, not AFTER INSERT
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Assignee Dmitry Shulga [ JIRAUSER47315 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]
            julien.fritsch Julien Fritsch made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            julien.fritsch Julien Fritsch made changes -
            Labels triage
            shulga Dmitry Shulga made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            shulga Dmitry Shulga added a comment -

            The branch for review is bb-10.6-MDEV-34958

            shulga Dmitry Shulga added a comment - The branch for review is bb-10.6- MDEV-34958
            shulga Dmitry Shulga made changes -
            Assignee Dmitry Shulga [ JIRAUSER47315 ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin added a comment -

            shulga Is it tested on 10.5? (if yes why there is not labels?)

            sanja Oleksandr Byelkin added a comment - shulga Is it tested on 10.5? (if yes why there is not labels?)
            sanja Oleksandr Byelkin added a comment -

            I checed, the bug present on 10.5 and fix fixes it.

            sanja Oleksandr Byelkin added a comment - I checed, the bug present on 10.5 and fix fixes it.
            sanja Oleksandr Byelkin made changes -
            Affects Version/s 10.5 [ 23123 ]
            sanja Oleksandr Byelkin made changes -
            Fix Version/s 10.5 [ 23123 ]
            sanja Oleksandr Byelkin added a comment -

            OK to push but in 10.5

            sanja Oleksandr Byelkin added a comment - OK to push but in 10.5
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Dmitry Shulga [ JIRAUSER47315 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            shulga Dmitry Shulga made changes -
            Component/s Triggers [ 10109 ]
            Fix Version/s 10.5.28 [ 29952 ]
            Fix Version/s 10.6.21 [ 29953 ]
            Fix Version/s 10.11.11 [ 29954 ]
            Fix Version/s 11.4.5 [ 29956 ]
            Fix Version/s 11.7.2 [ 29914 ]
            Fix Version/s 11.8.0 [ 29960 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.11 [ 27614 ]
            Fix Version/s 11.4 [ 29301 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]

            People

              shulga Dmitry Shulga
              diego dupin Diego Dupin
              Votes:
              1 Vote for this issue
              Watchers:
              9 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.