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

COM_STMT_BULK_EXECUTE with RETURNING insert wrong values

Details

    Description

      COM_STMT_BULK_EXECUTE permits to use batching.
      Having the results using the new 10.5 RETURNING clause might be very interesting to retrieve all ids.

      Return resulset work well. Problem is the data inserted doesn't correspond to command.

      to reproduced :

      CREATE TABLE batch_bulk(id int NOT NULL AUTO_INCREMENT, val varchar(250), PRIMARY KEY (id));
      

      sending a COM_STMT_BULK_EXECUTE

      INSERT INTO `batch_bulk`(val) values (?)
      

      with data 'test1', 'test2', 'test3' works well, with 3 new rows inserted.

      but same query with returning clause :

      INSERT INTO `batch_bulk`(val) values (?)  returning id
      

      with result in having 15 new rows with value 'test1', and no rows for 'test2', 'test3'
      The returned resultset show the 15 new ids;

      To confirm commands follow protocol:

      ==> conn:125 BatchBulk(0,58)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 36 00 00 00 16 49 4E 53  45 52 54 20 49 4E 54 4F | 6....INSERT INTO |
      | 20 60 62 61 74 63 68 5F  62 75 6C 6B 60 28 76 61 |  `batch_bulk`(va |
      | 6C 29 20 76 61 6C 75 65  73 20 28 3F 29 20 72 65 | l) values (?) re |
      | 74 75 72 6E 69 6E 67 20  69 64                   | turning id       |
      +--------------------------------------------------+------------------+
       
      ==> conn:125 BatchBulk(0,34)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 1E 00 00 00 FA FF FF FF  FF 80 00 0F 00 00 05 74 | ...............t |
      | 65 73 74 31 00 05 74 65  73 74 32 00 05 74 65 73 | est1..test2..tes |
      | 74 33                                            | t3               |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readPrepareResultPacket (0,12)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 0C 00 00 01 00 01 00 00  00 00 00 01 00 00 00 00 | ................ |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.skipParameterPacket (0,23)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 17 00 00 02 03 64 65 66  00 00 00 01 3F 00 0C 3F | .....def....?..? |
      | 00 00 00 00 00 06 80 00  00 00 00                | ...........      |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResponsePacket (0,1)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 01 00 00 01 01                                   | .....            |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readColumn (0,51)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 33 00 00 02 03 64 65 66  05 74 65 73 74 6E 0A 62 | 3....def.testn.b |
      | 61 74 63 68 5F 62 75 6C  6B 0A 62 61 74 63 68 5F | atch_bulk.batch_ |
      | 62 75 6C 6B 02 69 64 02  69 64 0C 3F 00 0B 00 00 | bulk.id.id.?.... |
      | 00 03 03 42 00 00 00                             | ...B...          |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 03 00 00 01 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 04 00 00 02 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 05 00 00 03 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 06 00 00 04 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 07 00 00 05 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 08 00 00 06 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 09 00 00 07 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 0A 00 00 08 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 0B 00 00 09 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 0C 00 00 0A 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 0D 00 00 0B 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 0E 00 00 0C 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 0F 00 00 0D 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 10 00 00 0E 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,6)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 06 00 00 11 00 00 0F 00  00 00                   | ..........       |
      +--------------------------------------------------+------------------+
       
      <== conn:125 BatchBulk.readResultSetRow (0,7)
      +--------------------------------------------------+
      |  0  1  2  3  4  5  6  7   8  9  a  b  c  d  e  f |
      +--------------------------------------------------+------------------+
      | 07 00 00 12 FE 00 00 02  00 00 00                | ...........      |
      +--------------------------------------------------+------------------+
      
      

      Attachments

        Issue Links

          Activity

            DELETE RETURNING shoud be brocken it the same way

            (reading and writing the same buffer)

            sanja Oleksandr Byelkin added a comment - DELETE RETURNING shoud be brocken it the same way (reading and writing the same buffer)
            sanja Oleksandr Byelkin added a comment - - edited

            Yes, there is the same problem:

            static void test_bulk_delete_returning()
            {
              int rc;
              MYSQL_STMT *stmt;
              MYSQL_BIND bind[2], res_bind[2];
              MYSQL_ROW  row;
              MYSQL_RES *result;
              int        i,
                         id[]= {1, 2, 3, 4},
                         count= sizeof(id)/sizeof(id[0]);
              unsigned long length[1];
              my_bool       is_null[1];
              my_bool       error[1];
              int32_t       res[1];
             
              rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1");
              myquery(rc);
              rc= mysql_query(mysql, "CREATE TABLE t1 (id int not null primary key)");
              myquery(rc);
              rc= mysql_query(mysql, "insert into t1 values (1), (2), (3), (4)");
              myquery(rc);
              verify_affected_rows(4);
             
              stmt= mysql_stmt_init(mysql);
              rc= mysql_stmt_prepare(stmt, "DELETE FROM t1 WHERE id=? RETURNING id", -1);
              check_execute(stmt, rc);
             
              memset(bind, 0, sizeof(bind));
              bind[0].buffer_type = MYSQL_TYPE_LONG;
              bind[0].buffer = (void *)id;
              bind[0].buffer_length = 0;
             
              mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, (void*)&count);
              rc= mysql_stmt_bind_param(stmt, bind);
              check_execute(stmt, rc);
             
              rc= mysql_stmt_execute(stmt);
              myquery(rc);
             
              memset(bind, 0, sizeof(res_bind));
              res_bind[0].buffer_type= MYSQL_TYPE_LONG;
              res_bind[0].buffer= (char *)&res[0];
              res_bind[0].is_null= &is_null[0];
              res_bind[0].length= &length[0];
              res_bind[0].error= &error[0];
              rc= mysql_stmt_bind_result(stmt, res_bind);
              myquery(rc);
              rc= mysql_stmt_store_result(stmt);
              myquery(rc);
             
              i= 0;
              while (!mysql_stmt_fetch(stmt))
              {
                i++;
                DIE_IF(is_null[0]);
                DIE_IF(res[0] != i);
              }
              DIE_IF(i != 4);
             
              mysql_stmt_close(stmt);
             
              rc= mysql_query(mysql, "SELECT id FROM t1");
              myquery(rc);
             
              result= mysql_store_result(mysql);
              mytest(result);
             
              i= 0;
              while ((row= mysql_fetch_row(result)))
              {
                i++;
              }
              DIE_IF(i != 0 );
              mysql_free_result(result);
             
             
              rc= mysql_query(mysql, "DROP TABLE t1");
              myquery(rc);
            }
            

            sanja Oleksandr Byelkin added a comment - - edited Yes, there is the same problem: static void test_bulk_delete_returning() { int rc; MYSQL_STMT *stmt; MYSQL_BIND bind[2], res_bind[2]; MYSQL_ROW row; MYSQL_RES *result; int i, id[]= {1, 2, 3, 4}, count= sizeof(id)/sizeof(id[0]); unsigned long length[1]; my_bool is_null[1]; my_bool error[1]; int32_t res[1];   rc= mysql_query(mysql, "DROP TABLE IF EXISTS t1"); myquery(rc); rc= mysql_query(mysql, "CREATE TABLE t1 (id int not null primary key)"); myquery(rc); rc= mysql_query(mysql, "insert into t1 values (1), (2), (3), (4)"); myquery(rc); verify_affected_rows(4);   stmt= mysql_stmt_init(mysql); rc= mysql_stmt_prepare(stmt, "DELETE FROM t1 WHERE id=? RETURNING id", -1); check_execute(stmt, rc);   memset(bind, 0, sizeof(bind)); bind[0].buffer_type = MYSQL_TYPE_LONG; bind[0].buffer = (void *)id; bind[0].buffer_length = 0;   mysql_stmt_attr_set(stmt, STMT_ATTR_ARRAY_SIZE, (void*)&count); rc= mysql_stmt_bind_param(stmt, bind); check_execute(stmt, rc);   rc= mysql_stmt_execute(stmt); myquery(rc);   memset(bind, 0, sizeof(res_bind)); res_bind[0].buffer_type= MYSQL_TYPE_LONG; res_bind[0].buffer= (char *)&res[0]; res_bind[0].is_null= &is_null[0]; res_bind[0].length= &length[0]; res_bind[0].error= &error[0]; rc= mysql_stmt_bind_result(stmt, res_bind); myquery(rc); rc= mysql_stmt_store_result(stmt); myquery(rc);   i= 0; while (!mysql_stmt_fetch(stmt)) { i++; DIE_IF(is_null[0]); DIE_IF(res[0] != i); } DIE_IF(i != 4);   mysql_stmt_close(stmt);   rc= mysql_query(mysql, "SELECT id FROM t1"); myquery(rc);   result= mysql_store_result(mysql); mytest(result);   i= 0; while ((row= mysql_fetch_row(result))) { i++; } DIE_IF(i != 0 ); mysql_free_result(result);     rc= mysql_query(mysql, "DROP TABLE t1"); myquery(rc); }

            there is more problem with DELETE ... RETURNING due to protocol.

            sanja Oleksandr Byelkin added a comment - there is more problem with DELETE ... RETURNING due to protocol.

            commit f854ac4d9e0a2ab534fb5e9bbdb2c3338a1ab57e (HEAD > bb-10.5MDEV-21916, origin/bb-10.5-MDEV-21916)
            Author: Oleksandr Byelkin <sanja@mariadb.com>
            Date: Mon Jun 22 18:21:21 2020 +0200

            MDEV-21916: COM_STMT_BULK_EXECUTE with RETURNING insert wrong values

            To allocate new net buffer to avoid changing bufer we are reading.

            sanja Oleksandr Byelkin added a comment - commit f854ac4d9e0a2ab534fb5e9bbdb2c3338a1ab57e (HEAD > bb-10.5 MDEV-21916 , origin/bb-10.5- MDEV-21916 ) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Mon Jun 22 18:21:21 2020 +0200 MDEV-21916 : COM_STMT_BULK_EXECUTE with RETURNING insert wrong values To allocate new net buffer to avoid changing bufer we are reading.
            sanja Oleksandr Byelkin added a comment - - edited

            commit 27060eb6ba514e00ed30e939556b14bcdb4aa89e (HEAD -> bb-10.5-MDEV-21916, origin/bb-10.5-MDEV-21916)
            Author: Oleksandr Byelkin <sanja@mariadb.com>
            Date: Mon Jun 22 18:21:21 2020 +0200

            MDEV-21916: COM_STMT_BULK_EXECUTE with RETURNING insert wrong values

            To allocate new net buffer to avoid changing bufer we are reading.

            sanja Oleksandr Byelkin added a comment - - edited commit 27060eb6ba514e00ed30e939556b14bcdb4aa89e (HEAD -> bb-10.5- MDEV-21916 , origin/bb-10.5- MDEV-21916 ) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Mon Jun 22 18:21:21 2020 +0200 MDEV-21916 : COM_STMT_BULK_EXECUTE with RETURNING insert wrong values To allocate new net buffer to avoid changing bufer we are reading.

            commit a6e96ce3c9686a9e7a926c9ca20de66daa3143fb (HEAD > bb-10.5MDEV-21916, origin/bb-10.5-MDEV-21916)
            Author: Oleksandr Byelkin <sanja@mariadb.com>
            Date: Mon Jun 22 18:21:21 2020 +0200

            MDEV-21916: COM_STMT_BULK_EXECUTE with RETURNING insert wrong values

            The problem is that array binding uses net buffer to read parameters for each
            execution while each execiting with RETURNING write in the same buffer.

            Solution is to allocate new net buffer to avoid changing buffer we are reading
            from.

            sanja Oleksandr Byelkin added a comment - commit a6e96ce3c9686a9e7a926c9ca20de66daa3143fb (HEAD > bb-10.5 MDEV-21916 , origin/bb-10.5- MDEV-21916 ) Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Mon Jun 22 18:21:21 2020 +0200 MDEV-21916 : COM_STMT_BULK_EXECUTE with RETURNING insert wrong values The problem is that array binding uses net buffer to read parameters for each execution while each execiting with RETURNING write in the same buffer. Solution is to allocate new net buffer to avoid changing buffer we are reading from.

            People

              sanja Oleksandr Byelkin
              diego dupin Diego Dupin
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.