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

after Trigger doesn't work correctly with bulk insert

    XMLWordPrintable

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

          Activity

            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.