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

PS + array binding does not work with triggers

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.4.26, 10.6.9, 10.8.4, 10.11
    • 10.5, 10.6
    • None
    • None

    Description

      Hi Georg, whassup?

      I found another bug related to executemany. (Does not affect execute with explicit values)

      If my table has any trigger, only one row is inserted. But, the trigger is called n times (n rows passed) with the same data.

      import mariadb
      		 
       
      		 
      db = mariadb.connect(host='10.4.0.8', user='Administrador', password='XXXXX', database='tmp')
      		 
      qr = db.cursor(buffered=True)
      		 
      qr.execute('drop table if exists aaa')
      		 
      qr.execute("""
      		 
      CREATE TABLE `aaa` (
      		 
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `some_field` varchar(10) DEFAULT '',
      		 
        PRIMARY KEY (`id`)
      		 
      ) ENGINE=InnoDB
      		 
      """)
      		 
       
      		 
      to_be_inserted = [(None, f'aaa_{i}') for i in range(10)]
      		 
      qr.executemany('insert into aaa values (?, ?) returning id', to_be_inserted)
      		 
      inserted = qr.fetchall()
      		 
      print(f'Should be', len(to_be_inserted), 'got', len(inserted), '. What whas inserted:', inserted)
      		 
       
      		 
      # ----------------------
      		 
      qr.execute('drop table if exists aaa_spy')
      		 
      qr.execute("""
      		 
      CREATE TABLE `aaa_spy` (
      		 
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      		 
        `aaa_id` int(10) unsigned NOT NULL,
      		 
        `aaa_some_field` varchar(10) DEFAULT '',
      		 
        PRIMARY KEY (`id`)
      		 
      ) ENGINE=MyISAM
      		 
      """)
      		 
      qr.execute('drop trigger if exists aaa_after_insert')
      		 
      qr.execute("""
      		 
      CREATE TRIGGER aaa_after_insert
      		 
      AFTER INSERT
      		 
      ON aaa FOR EACH ROW
      		 
         insert into aaa_spy values (null, new.id, new.some_field)
      		 
      """)
      		 
       
      		 
      to_be_inserted = [(None, f'aaa_{i}') for i in range(10, 20)]
      		 
      qr.executemany('insert into aaa values (?, ?) returning id', to_be_inserted)
      		 
      inserted = qr.fetchall()
      		 
      print(f'Should be', len(to_be_inserted), 'got', len(inserted), '. What whas inserted:', inserted)  # Wrong!
      		 
       
      		 
      # What was inserted?
      		 
      qr.execute('select aaa_id, aaa_some_field from aaa_spy where aaa_id >= 11')
      		 
      # always the same data...
      		 
      for _id, f in qr:
      		 
          print(_id, f)

      This snippet returns this:

      Should be 10 got 10 . What whas inserted: [(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,), (10,)]
      		 
      Should be 10 got 1 . What whas inserted: [(16,)]
      		 
      16 aaa_10
      		 
      16 aaa_10
      		 
      16 aaa_10
      		 
      16 aaa_10
      		 
      16 aaa_10
      		 
      16 aaa_10
      		 
      16 aaa_10
      		 
      16 aaa_10
      		 
      16 aaa_10

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          lucianobarcaro Luciano Barcaro created issue -
          lucianobarcaro Luciano Barcaro made changes -
          Field Original Value New Value
          Description Hi Georg, whassup?

          I found another bug related to executemany.
          If my table has any trigger, only one row is inserted. But, the trigger is called _n_ times (n rows passed) with the same data.

          {code:python}
          import mariadb

          db = mariadb.connect(host='10.4.0.8', user='Administrador', password='XXXXX', database='tmp')
          qr = db.cursor(buffered=True)
          qr.execute('drop table if exists aaa')
          qr.execute("""
          CREATE TABLE `aaa` (
            `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
            `some_field` varchar(10) DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB
          """)

          to_be_inserted = [(None, f'aaa_{i}') for i in range(10)]
          qr.executemany('insert into aaa values (?, ?) returning id', to_be_inserted)
          inserted = qr.fetchall()
          print(f'Should be', len(to_be_inserted), 'got', len(inserted), '. What whas inserted:', inserted)

          # ----------------------
          qr.execute('drop table if exists aaa_spy')
          qr.execute("""
          CREATE TABLE `aaa_spy` (
            `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
            `aaa_id` int(10) unsigned NOT NULL,
            `aaa_some_field` varchar(10) DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=MyISAM
          """)
          qr.execute('drop trigger if exists aaa_after_insert')
          qr.execute("""
          CREATE TRIGGER aaa_after_insert
          AFTER INSERT
          ON aaa FOR EACH ROW
             insert into aaa_spy values (null, new.id, new.some_field)
          """)

          to_be_inserted = [(None, f'aaa_{i}') for i in range(10, 20)]
          qr.executemany('insert into aaa values (?, ?) returning id', to_be_inserted)
          inserted = qr.fetchall()
          print(f'Should be', len(to_be_inserted), 'got', len(inserted), '. What whas inserted:', inserted) # Wrong!

          # What was inserted?
          qr.execute('select aaa_id, aaa_some_field from aaa_spy where aaa_id >= 11')
          # always the same data...
          for _id, f in qr:
              print(_id, f)
          {code}

          This snippet returns this:

          {code:text}
          Should be 10 got 10 . What whas inserted: [(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,), (10,)]
          Should be 10 got 1 . What whas inserted: [(16,)]
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          {code}

          		Hi Georg, whassup?

          I found another bug related to executemany. (Does not affect execute with explicit values)

          If my table has any trigger, only one row is inserted. But, the trigger is called _n_ times (n rows passed) with the same data.

          {code:python}
          import mariadb

          db = mariadb.connect(host='10.4.0.8', user='Administrador', password='XXXXX', database='tmp')
          qr = db.cursor(buffered=True)
          qr.execute('drop table if exists aaa')
          qr.execute("""
          CREATE TABLE `aaa` (
            `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
            `some_field` varchar(10) DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=InnoDB
          """)

          to_be_inserted = [(None, f'aaa_{i}') for i in range(10)]
          qr.executemany('insert into aaa values (?, ?) returning id', to_be_inserted)
          inserted = qr.fetchall()
          print(f'Should be', len(to_be_inserted), 'got', len(inserted), '. What whas inserted:', inserted)

          # ----------------------
          qr.execute('drop table if exists aaa_spy')
          qr.execute("""
          CREATE TABLE `aaa_spy` (
            `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
            `aaa_id` int(10) unsigned NOT NULL,
            `aaa_some_field` varchar(10) DEFAULT '',
            PRIMARY KEY (`id`)
          ) ENGINE=MyISAM
          """)
          qr.execute('drop trigger if exists aaa_after_insert')
          qr.execute("""
          CREATE TRIGGER aaa_after_insert
          AFTER INSERT
          ON aaa FOR EACH ROW
             insert into aaa_spy values (null, new.id, new.some_field)
          """)

          to_be_inserted = [(None, f'aaa_{i}') for i in range(10, 20)]
          qr.executemany('insert into aaa values (?, ?) returning id', to_be_inserted)
          inserted = qr.fetchall()
          print(f'Should be', len(to_be_inserted), 'got', len(inserted), '. What whas inserted:', inserted) # Wrong!

          # What was inserted?
          qr.execute('select aaa_id, aaa_some_field from aaa_spy where aaa_id >= 11')
          # always the same data...
          for _id, f in qr:
              print(_id, f)
          {code}

          This snippet returns this:

          {code:text}
          Should be 10 got 10 . What whas inserted: [(1,), (2,), (3,), (4,), (5,), (6,), (7,), (8,), (9,), (10,)]
          Should be 10 got 1 . What whas inserted: [(16,)]
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          16 aaa_10
          {code}

          georg Georg Richter made changes -
          Key CONPY-219 MDEV-29218
          Affects Version/s 1.1.3 [ 28322 ]
          Project MariaDB Connector/Python [ 11400 ] MariaDB Server [ 10000 ]
          georg Georg Richter made changes -
          Attachment MDEV29218.c [ 64895 ]
          georg Georg Richter added a comment - - edited

          Moved from CONPY-219. Test case (in C) attached: While text protocol works ok, binary protocol fails.

          Output:

          # text protocol
          		 
          # affected_rows: 3
          		 
          # rows in t2: 3
          		 
          # binary protocol
          		 
          # affected_rows: 1
          		 
          # rows in t2: 2

          georg Georg Richter added a comment - - edited Moved from CONPY-219 . Test case (in C) attached: While text protocol works ok, binary protocol fails. Output: # text protocol # affected_rows: 3 # rows in t2: 3 # binary protocol # affected_rows: 1 # rows in t2: 2
          georg Georg Richter made changes -
          Assignee Georg Richter [ georg ] Oleksandr Byelkin [ sanja ]
          serg Sergei Golubchik made changes -
          Priority Blocker [ 1 ] Major [ 3 ]
          serg Sergei Golubchik added a comment -

          What server versions are affected?

          serg Sergei Golubchik added a comment - What server versions are affected?
          serg Sergei Golubchik made changes -
          Status Open [ 1 ] Needs Feedback [ 10501 ]
          georg Georg Richter added a comment -

          I reproduced it with 10.4 and 10.8 - other versions not tested yet.

          georg Georg Richter added a comment - I reproduced it with 10.4 and 10.8 - other versions not tested yet.
          georg Georg Richter made changes -
          Affects Version/s 10.4 [ 22408 ]
          Affects Version/s 10.8 [ 26121 ]
          georg Georg Richter made changes -
          Affects Version/s 10.4.26 [ 27511 ]
          Affects Version/s 10.8.4 [ 27503 ]
          Affects Version/s 10.4 [ 22408 ]
          Affects Version/s 10.8 [ 26121 ]
          georg Georg Richter made changes -
          Affects Version/s 10.6.9 [ 27507 ]
          lucianobarcaro Luciano Barcaro made changes -
          Summary Executemany does not work with triggers Multirow operation with triggers does not work with binary protocol
          lucianobarcaro Luciano Barcaro added a comment -

          Any news about this bug?

          lucianobarcaro Luciano Barcaro added a comment - Any news about this bug?
          serg Sergei Golubchik made changes -
          Status Needs Feedback [ 10501 ] Open [ 1 ]
          serg Sergei Golubchik made changes -
          Summary Multirow operation with triggers does not work with binary protocol PS + array binding does not work with triggers
          serg Sergei Golubchik made changes -
          Fix Version/s 10.4 [ 22408 ]
          Fix Version/s 10.5 [ 23123 ]
          Fix Version/s 10.6 [ 24028 ]
          Fix Version/s 10.7 [ 24805 ]
          Fix Version/s 10.8 [ 26121 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.7 [ 24805 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.8 [ 26121 ]
          lucianobarcaro Luciano Barcaro made changes -
          Affects Version/s 10.11 [ 27614 ]
          julien.fritsch Julien Fritsch made changes -
          Fix Version/s 10.4 [ 22408 ]

          People

            sanja Oleksandr Byelkin
            lucianobarcaro Luciano Barcaro
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.