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

Server cursor is broken with blobs in the select list, ORDER BY does not work

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.0.4, 5.5.32, 5.1.67, 5.2.14, 5.3.12
    • Fix Version/s: 5.5.33, 10.0.6, 5.1.73, 5.2.15, 5.3.13
    • Component/s: None
    • Labels:
      None

      Description

      Initially reported at Maria-discuss (https://lists.launchpad.net/maria-discuss/msg00986.html)

      Paul J Stevens wrote:

      MariaDB appears to
      mess up the server cursor when a blob is in the result.

      I'm attaching a test-case and with a schema and data for you to use.

      libzdb uses mysql_stmt_fetch to move the server cursor forward. This has
      worked flawlessly until now for MySQL, SQLite, PostgreSQL and Oracle.
      With MariaDB we seem to hit a problem as soon as a blob column is in the columns.

      The original test case by Paul can be found in the mailing list.
      Here is a testcase without libzdb. It uses the same dataset and query, only simplified ones.

      #include <my_global.h>
      #include <mysql.h>
       
      int main(int argc, char **argv)
      {  
        MYSQL *con = mysql_init(NULL);
        MYSQL_STMT *stmt = mysql_stmt_init(con);
        MYSQL_BIND    bind[2];
        unsigned long length[2];
        int           int_data;
        my_bool       is_null[2];
        my_bool       error[2];
        char          str_data[2048];
       
        if (con == NULL) 
        {
            fprintf(stderr, "%s\n", mysql_error(con));
            exit(1);
        }
       
        if (mysql_real_connect(con, "127.0.0.1", "root", "", 
                "test", 0, NULL, 0) == NULL) 
        {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        if (mysql_query(con,
          "DROP TABLE IF EXISTS dbmail_mimeparts"))
        {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        if (mysql_query(con,
          "CREATE TABLE dbmail_mimeparts ( "
            "id bigint(20), "
            "data longblob NOT NULL "
          ") ENGINE=InnoDB"))
        {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        if (mysql_query(con,
          "INSERT INTO `dbmail_mimeparts` VALUES "
          "(62,REPEAT('b',89)),"
          "(63,REPEAT('c',1251)),"
          "(64,REPEAT('d',1355)),"
          "(69,REPEAT('f',907)),"
          "(72,REPEAT('h',1355))"))
        {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        if (mysql_query(con,
          "DROP TABLE IF EXISTS dbmail_partlists"))
        {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        if (mysql_query(con,
          "CREATE TABLE dbmail_partlists ("
            "part smallint(6), "
            "part_id bigint(20) "
          ") ENGINE=InnoDB"))
        {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        if (mysql_query(con,
          "INSERT INTO dbmail_partlists VALUES "
          "(4,63),(5,64),(9,69),(11,64),(12,72),(15,62)"))
        {
            fprintf(stderr, "%s\n", mysql_error(con));
            mysql_close(con);
            exit(1);
        }
       
        if (mysql_stmt_prepare(stmt,
          "SELECT l.part,p.data "
            "from dbmail_mimeparts p "
            "join dbmail_partlists l on p.id=l.part_id "
            "order by l.part", 102)) 
        {
          fprintf(stderr, "%s\n", mysql_error(con));
          mysql_close(con);
          exit(1);
        }
       
        unsigned long cursor = CURSOR_TYPE_READ_ONLY;
        mysql_stmt_attr_set(stmt, STMT_ATTR_CURSOR_TYPE, &cursor);
       
        if (mysql_stmt_execute(stmt))
        {
          fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
          exit(1);
        }
       
        bind[0].buffer_type= MYSQL_TYPE_LONG;
        bind[0].buffer= (char *)&int_data;
        bind[0].buffer_length= 64;
        bind[0].is_null= &is_null[0];
        bind[0].length= &length[0];
        bind[0].error= &error[0];
       
        bind[1].buffer_type= MYSQL_TYPE_STRING;
        bind[1].buffer= (char *)str_data;
        bind[1].buffer_length= 2048;
        bind[1].is_null= &is_null[1];
        bind[1].length= &length[1];
        bind[1].error= &error[1];
       
        if (mysql_stmt_bind_result(stmt, bind))
        {
          fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
          exit(1);
        }
       
        while (!mysql_stmt_fetch(stmt))
        {
          fprintf(stdout, " part: %d\n", int_data);
          if (mysql_stmt_bind_result(stmt, bind))
          {
            fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
            exit(1);
          }
        }
       
        mysql_close(con);
        exit(0);
      }
       

      It returns

       part: 4
       part: 5
       part: 9
       part: 11
       part: 15
       part: 12

      which is obviously a wrong order.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                svoj Sergey Vojtovich
                Reporter:
                elenst Elena Stepanova
              • Votes:
                1 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: