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

Trigger doesn't work correctly with bulk insert

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.5.8
    • Fix Version/s: 10.5
    • Component/s: Binary Protocol
    • Labels:
      None
    • Environment:
      MariaDB 10.5.4
      Windows 10 64 bits

      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

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            MarcT Marcelo Titonelli Pio da Cruz
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated: