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

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_before_insert` BEFORE INSERT ON `table1`
       FOR EACH ROW BEGIN
      	insert into table2(`id`, `name`) values (NEW.`id`, NEW.`name`);
      END
      

      import mariadb
      conn=mariadb.connect(user="test", database="test")
      cursor=conn.cursor()
      vals = [(1,'John'), (2,'Smith'), (3,'Carol')]
      cursor.executemany("INSERT INTO table1 VALUES (?,?)", vals)
      cursor.execute("SELECT * FROM table1")
      rows= cursor.fetchall()
      print(rows)
      

      Expected output:
      [(1, 'John', 1, 'John'), (2, 'Smith', 1, 'John'), (3, 'Carol', 1, 'John'), (1, 'John', 2, 'Smith'), (2, 'Smith', 2, 'Smith'), (3, 'Carol', 2, 'Smith'), (1, 'John', 3, 'Carol'), (2, 'Smith', 3, 'Carol'), (3, 'Carol', 3, 'Carol')]

      Current output:
      [(1, 'John', 1, 'John'), (1, 'John', 1, 'John')]

      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"

      Running insert on mariadb console, works correctly:
      insert into Table1 values(1,'John'),(2,'Smith'),(3,'Carol');

      Attachments

        Issue Links

          Activity

            People

              shulga Dmitry Shulga
              MarcT Marcelo Titonelli Pio da Cruz
              Votes:
              7 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.