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

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_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

          causes

          Bug - A problem which impairs or prevents the functions of the product. CONJ-1086 Batch insert inserts only the first row when there is a trigger

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. CONJS-182 Batch inserts with duplicate treatment only perform 1 insert

          • Major - Major loss of function.
          • Closed
          relates to

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

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

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

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

          Activity

            Ascending order - Click to sort in descending order
            MarcT Marcelo Titonelli Pio da Cruz created issue -
            MarcT Marcelo Titonelli Pio da Cruz made changes -
            Field Original Value New Value
            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_before_insert` BEFORE INSERT ON `table1`
             FOR EACH ROW BEGIN
            insert into table2(`id`, `name`) values (NEW.`id`, NEW.`name`);
            END
            {code}


            {code:java}
            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)
            {code}


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


            {code:java}
            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)
            {code}


            *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');
            georg Georg Richter made changes -
            Component/s Binary Protocol [ 16120 ]
            Component/s Other [ 16504 ]
            Key CONPY-135 MDEV-24411
            Affects Version/s 10.5.8 [ 25023 ]
            Affects Version/s 1.0.5 [ 25126 ]
            Project MariaDB Connector/Python [ 11400 ] MariaDB Server [ 10000 ]
            georg Georg Richter made changes -
            Assignee Georg Richter [ georg ] Oleksandr Byelkin [ sanja ]
            georg Georg Richter made changes -
            Priority Critical [ 2 ] Blocker [ 1 ]
            georg Georg Richter made changes -
            Priority Blocker [ 1 ] Critical [ 2 ]
            georg Georg Richter made changes -
            Summary Trigger doesn't work correctly with executemany Trigger doesn't work correctly with bulk insert
            elenst Elena Stepanova made changes -
            Fix Version/s 10.5 [ 23123 ]
            diego dupin Diego Dupin made changes -
            Labels CONNECTOR_RELATED
            diego dupin Diego Dupin made changes -
            Labels CONNECTOR_RELATED
            sanja Oleksandr Byelkin made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 116872 ] MariaDB v4 [ 144580 ]
            diego dupin Diego Dupin made changes -
            Labels CONNECTOR_RELATED
            diego dupin Diego Dupin made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.6 [ 24028 ]
            Fix Version/s 10.7 [ 24805 ]
            diego dupin Diego Dupin made changes -
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Dmitry Shulga [ JIRAUSER47315 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 10.2 [ 14601 ]
            serg Sergei Golubchik made changes -
            Priority Critical [ 2 ] Major [ 3 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.7 [ 24805 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s 10.3 [ 22126 ]
            diego dupin Diego Dupin made changes -
            diego dupin Diego Dupin made changes -
            diego dupin Diego Dupin made changes -
            diego dupin Diego Dupin made changes -
            diego dupin Diego Dupin made changes -
            JIraAutomate JiraAutomate made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            pandi.gurusamy Pandikrishnan Gurusamy made changes -
            Assignee Dmitry Shulga [ JIRAUSER47315 ] Pandikrishnan Gurusamy [ JIRAUSER52131 ]
            pandi.gurusamy Pandikrishnan Gurusamy made changes -
            Assignee Pandikrishnan Gurusamy [ JIRAUSER52131 ] Oleksandr Byelkin [ sanja ]
            pandi.gurusamy Pandikrishnan Gurusamy made changes -
            Priority Critical [ 2 ] Blocker [ 1 ]
            serg Sergei Golubchik made changes -
            Priority Blocker [ 1 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Assignee Oleksandr Byelkin [ sanja ] Dmitry Shulga [ JIRAUSER47315 ]
            shulga Dmitry Shulga made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            shulga Dmitry Shulga made changes -
            Assignee Dmitry Shulga [ JIRAUSER47315 ] Oleksandr Byelkin [ sanja ]
            Status In Progress [ 3 ] In Review [ 10002 ]
            sanja Oleksandr Byelkin made changes -
            Assignee Oleksandr Byelkin [ sanja ] Dmitry Shulga [ JIRAUSER47315 ]
            Status In Review [ 10002 ] Stalled [ 10000 ]
            shulga Dmitry Shulga made changes -
            Fix Version/s 10.5.26 [ 29832 ]
            Fix Version/s 10.6.19 [ 29833 ]
            Fix Version/s 10.11.9 [ 29834 ]
            Fix Version/s 11.1.6 [ 29835 ]
            Fix Version/s 11.2.5 [ 29836 ]
            Fix Version/s 11.4.3 [ 29837 ]
            Fix Version/s 11.5.2 [ 29838 ]
            Fix Version/s 11.6.0 [ 29839 ]
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.5 [ 23123 ]
            Fix Version/s 10.6 [ 24028 ]
            shulga Dmitry Shulga made changes -
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 201572
            Zendesk active tickets 201572
            shulga Dmitry Shulga made changes -
            Resolution Fixed [ 1 ]
            Status Closed [ 6 ] Stalled [ 10000 ]
            shulga Dmitry Shulga made changes -
            Status Stalled [ 10000 ] In Progress [ 3 ]
            shulga Dmitry Shulga made changes -
            shulga Dmitry Shulga made changes -
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            diego dupin Diego Dupin made changes -

            People

              shulga Dmitry Shulga
              MarcT Marcelo Titonelli Pio da Cruz
              Votes:
              9 Vote for this issue
              Watchers:
              16 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.