Bug #31833 ORDER BY leads to wrong result when ARCHIVE, BLOB and table cache is full
Submitted: 25 Oct 2007 7:09 Modified: 10 Jan 2008 17:46
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Archive storage engine Severity:S3 (Non-critical)
Version:5.1.23-bk OS:Linux
Assigned to: Sergey Vojtovich CPU Architecture:Any

[25 Oct 2007 7:09] Dmitry Lenev
Description:
SELECT statement with ORDER BY clause applied to ARCHIVE table with BLOB column can produce a wrong result when table cache is full. See How-to-repeat section for details.

How to repeat:
# This is script for 'mysqltest' tool which reproduces the problem

connect (addconroot1, localhost, root,,test);

# We use 't1' table and huge 'lock tables' statement below to exceed the size of
# table cache (it is 64 by default) 
CREATE TABLE t1 (i int);
LOCK TABLES
t1 AS h1 READ, t1 AS h2 READ, t1 AS h3 READ, t1 AS h4 READ, t1 AS h5 READ, t1 AS h6 READ, t1 AS h7 READ, t1 AS h8 READ,
t1 AS j1 READ, t1 AS j2 READ, t1 AS j3 READ, t1 AS j4 READ, t1 AS j5 READ, t1 AS j6 READ, t1 AS j7 READ, t1 AS j8 READ,
t1 AS k1 READ, t1 AS k2 READ, t1 AS k3 READ, t1 AS k4 READ, t1 AS k5 READ, t1 AS k6 READ, t1 AS k7 READ, t1 AS k8 READ,
t1 AS l1 READ, t1 AS l2 READ, t1 AS l3 READ, t1 AS l4 READ, t1 AS l5 READ, t1 AS l6 READ, t1 AS l7 READ, t1 AS l8 READ,
t1 AS m1 READ, t1 AS m2 READ, t1 AS m3 READ, t1 AS m4 READ, t1 AS m5 READ, t1 AS m6 READ, t1 AS m7 READ, t1 AS m8 READ,
t1 AS n1 READ, t1 AS n2 READ, t1 AS n3 READ, t1 AS n4 READ, t1 AS n5 READ, t1 AS n6 READ, t1 AS n7 READ, t1 AS n8 READ,
t1 AS b1 READ, t1 AS b2 READ, t1 AS b3 READ, t1 AS b4 READ, t1 AS b5 READ, t1 AS b6 READ, t1 AS b7 READ, t1 AS b8 READ,
t1 AS v1 READ, t1 AS v2 READ, t1 AS v3 READ, t1 AS v4 READ, t1 AS v5 READ, t1 AS v6 READ, t1 AS v7 READ, t1 AS v8 READ,
t1 AS c1 READ, t1 AS c2 READ, t1 AS c3 READ, t1 AS c4 READ, t1 AS c5 READ, t1 AS c6 READ, t1 AS c7 READ, t1 AS c8 READ;

connection default;

CREATE TABLE `t2` (
`a` int(11) NOT NULL auto_increment,
b blob(12),
c int,
KEY  (`a`)
) engine=archive;

SELECT * FROM t2;
INSERT INTO t2 VALUES (NULL, "foo", NULL);
INSERT INTO t2 VALUES (NULL, "We the people", 5);
INSERT INTO t2 VALUES (NULL, "in order to form a more pefect union", 9);
INSERT INTO t2 VALUES (NULL, "establish justice", NULL);
INSERT INTO t2 VALUES (NULL, NULL, NULL);
INSERT INTO t2 VALUES (32, "ensure domestic tranquility", NULL);
INSERT INTO t2 VALUES (23, "provide for the common defense", 30);
INSERT INTO t2 VALUES (NULL, "fo fooo", 70);
INSERT INTO t2 VALUES (NULL, NULL, 98);
INSERT INTO t2 VALUES (NULL, "promote the general welfare", 50);
# This produces some rows in result
SELECT * FROM t2;
# And these statements are not... Which is a bug!
SELECT * FROM t2 ORDER BY a;
SELECT * FROM t2 ORDER BY a DESC;
[25 Oct 2007 10:32] Sveta Smirnova
Thank you for the report.

Verified as described.
[23 Nov 2007 13:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38372

ChangeSet@1.2641, 2007-11-23 17:51:12+04:00, svoj@mysql.com +3 -0
  BUG#31833 - ORDER BY leads to wrong result when ARCHIVE, BLOB and
              table cache is full
  
  After reading last record from freshly opened archive table
  (e.g. after flush table, or if there is no room in table cache),
  the table is reported as crashed.
  
  The problem was that azio wrongly invalidated azio_stream when it
  meets EOF.
[12 Dec 2007 23:00] Bugs System
Pushed into 6.0.5-alpha
[12 Dec 2007 23:02] Bugs System
Pushed into 5.1.23-rc
[10 Jan 2008 17:46] MC Brown
A note has been added tot he 6.0.5 and 5.1.23 changelogs: 

Using ORDER BY leads to the wrong result when using the ARCHIVE on a table with a BLOB when the table cache is full. The table may also be reported as crashed after the query has completed, even though the table data was in tact.