[MDEV-4978] Server cursor is broken with blobs in the select list, ORDER BY does not work Created: 2013-09-02  Updated: 2013-09-10  Resolved: 2013-09-06

Status: Closed
Project: MariaDB Server
Component/s: None
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

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergey Vojtovich
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-5007 wrong order by moving from mysql to m... Closed

 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.



 Comments   
Comment by Arjen Lentz [ 2013-09-03 ]

How does stock MySQL get on with this test code?
Same or different results?

Comment by Elena Stepanova [ 2013-09-03 ]

Stock MySQL returns the correct result, both on Paul's original test case and on the simplified one.

Comment by Sergey Vojtovich [ 2013-09-05 ]

Even more simplified mtr test case:

CREATE TABLE t1(a INT, b BLOB);
INSERT INTO t1 VALUES(1,REPEAT('a',4835)),(2,'b'),(3,'c'),(4,'d'),(5,REPEAT('e',805)),(6,'f');

DELIMITER |;
CREATE PROCEDURE p1()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v1 INT;
DECLARE v2 BLOB;
DECLARE c1 CURSOR FOR SELECT * FROM t1 ORDER BY a;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN c1;
REPEAT
FETCH c1 INTO v1, v2;
IF NOT done THEN
SELECT v1;
END IF;
UNTIL done END REPEAT;
CLOSE c1;
END|
DELIMITER ;|

CALL p1;

DROP PROCEDURE p1;
DROP TABLE t1;

Comment by Sergey Vojtovich [ 2013-09-05 ]

The difference with stock MySQL is that MariaDB uses MARIA storage engine for temporary tables, which seem to shuffle records:

CREATE TABLE t1(a INT, b BLOB) ENGINE=MyISAM;
INSERT INTO t1 VALUES(1,REPEAT('a',4835)),(2,'b'),(3,'c'),(4,'d'),(5,REPEAT('e',805)),(6,'f');
SELECT a FROM t1;
a
1
2
3
4
5
6
DROP TABLE t1;

CREATE TABLE t1(a INT, b BLOB) ENGINE=MARIA;
INSERT INTO t1 VALUES(1,REPEAT('a',4835)),(2,'b'),(3,'c'),(4,'d'),(5,REPEAT('e',805)),(6,'f');
SELECT a FROM t1;
a
1
2
3
4
6
5
DROP TABLE t1;

Comment by Reindl Harald [ 2013-09-05 ]

well, no wonder that it works faster if it skips the most important things of a RDBMS which are reliability - may I ask why such basic operations like "order by" are not covered by any of the auto-tests?

Comment by Elena Stepanova [ 2013-09-05 ]

@ Reindl Harald

Why do you think that "basic operations like order by" are not covered by auto-tests?
Auto-tests are not a secret, they are a part of the server tree, so you can look at them and see what's there and what not.

The test case might look basic to you, because it's a result of simplification and further analysis; but in fact it represents a very particular situation.The numbers in the test are not purely random. Replace 805 with 800, and you won't get the failure. Replace 805 with 9805 – same story; etc.

No tests will ever imitate all possible combinations of real-life data. We needed an original erroneous scenario from someone who did hit the problem. Thanks to Paul J Stevens, we got it, so now it will be fixed.

Also, "shuffle" does not necessarily mean "skip ordering", at the very least you should wait for the real fix and check it to be able to claim that the performance advantage was caused by this bug.

Comment by Reindl Harald [ 2013-09-05 ]

maybe i sounded too rough - i was the guy treated Paul two days by testing dbmail-3.1, punsihed him all the time with the same test-message until we realized that he did not understand me because the problem did not exist and he realized after screenshots "ok, wait, a year ago MariaDB did not pass basic tests"

these things happened after both of the two of us worked day and night for weeks on other issues

well, by realize this now possible my life has been saved because in doubt i can cacnel the calendar entry on 2013/10/01 switch production to MariaDB after now have MySQL 5.5.33 on fedora 19 without all the mess Oracle did to the official Fedora packages by bring back their "community-mysql"

Comment by Sergey Vojtovich [ 2013-09-06 ]

Sergei, please review fix for this issue.

Comment by Sergey Vojtovich [ 2013-09-06 ]

Fixed in 5.5.33, revision-id: svoj@mariadb.org-20130906115919-w1xbf99iuyz82c8m

Comment by Reindl Harald [ 2013-09-06 ]

sounds good - thank you very much!

is there some place where i can get a replacement for mariadb-5.5.32.tar.gz in conext
of my RPM-SPEC to go ahead and play testmonkey - i am the one with the dbmail
VMware setup where a testmessage breaks horrible with 5.5.32

Comment by Elena Stepanova [ 2013-09-06 ]

You can download the packages from here http://hasky.askmonty.org/archive/pack/5.5/build-4167/
Not all binary packages have been built yet, but if you only need the source code, it's in http://hasky.askmonty.org/archive/pack/5.5/build-4167/kvm-tarbake-jaunty-x86/ (http://hasky.askmonty.org/archive/pack/5.5/build-4167/kvm-tarbake-jaunty-x86/mariadb-5.5.32.tar.gz)

Comment by Antonio Fernandes [ 2013-09-06 ]

Sorry for being such a newbie and a bit off topic but I've been tracking buildbot from MariaDB and, until now, never knew there were available to download builds... for example (since affects me), where is stored http://buildbot.askmonty.org/buildbot/builders/kvm-tarbake-jaunty-x86/builds/4169?

Thank you for great work (I've replaced MySQL when sub-queries were awful - MySQL 5.5 series)!

Comment by Elena Stepanova [ 2013-09-06 ]

It is being built now, but when the packages are baked, they'll be available at http://hasky.askmonty.org/archive/pack/5.5/build-4169/

Comment by Elena Stepanova [ 2013-09-06 ]

Please note that intermediate builds can have temporary regressions, please use them with caution.

Comment by Reindl Harald [ 2013-09-07 ]

the intermediate build (from source) solves the dbmail-problem at least with the message who went the upstream-developer and me crazy

for broader tests we will wait for 5.5.33 - after MySQL 5.5.33 is out for some time now and this bug is really serious i expect the release in a not so long future - after that we will verify dbmail again with unit-tests again MariaDB which hopefully will be an option for production

Generated at Thu Feb 08 07:00:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.