Bug #31036 Using order by with archive table crashes server
Submitted: 14 Sep 2007 21:34 Modified: 19 Nov 2007 5:07
Reporter: Andy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.23-beta-debug OS:Linux
Assigned to: Gleb Shchepa CPU Architecture:Any
Tags: archive, bfsm_2007_10_18, order by, server crash

[14 Sep 2007 21:34] Andy
Description:
Selecting from an archive table using the "order by" clause causes the server to crash.

This bug seems a case of a buffer overflow, or some other means of memory getting overwritten.  The problem does not seem to occur if the column lengths are small. For example in the test I sent with this bug report, a column length of 510 crashes the server or causes an empty set to return.  However, a column length 509 works fine.  During my testing I have also see the number of rows matter, but the column length is more reliable at creating the problem.

The problem does not occur with a subselect (see below)

How to repeat:
I will be attaching the data file I used for this test.

Once the table has the data a

SELECT * FROM test_table ORDER BY v;

will crash the server, if you run the command after the server restarts itself, it will return an empty set.

However,

SELECT * FROM (SELECT * FROM test_table) AS a ORDER BY v;

works.

Suggested fix:
IMHO, there is no reason that ORDER BY should not be supported by archive tables.  And from the looks of it, it looks like it the problem is due to memory corruption issues.
[14 Sep 2007 21:35] Andy
create and load the test_table for this bug

Attachment: test_table.sql (text/x-sql), 55.84 KiB.

[15 Sep 2007 3:58] Miguel Solorzano
Thank you for the bug report. Verified as described.

mysql> show create table test_table\G
*************************** 1. row ***************************
       Table: test_table
Create Table: CREATE TABLE `test_table` (
  `v` varchar(510) DEFAULT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM test_table ORDER BY v;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[15 Sep 2007 10:38] Shane Bester
Version: '5.1.23-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  yes
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'sql_base.cc:862'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'filesort.cc:328'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'sql_select.cc:6451'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'lock.cc:389'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'my_alloc.c:64'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'my_alloc.c:355'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'sql_string.h:190'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'sql_string.cc:51'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'my_alloc.c:64'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'my_alloc.c:355'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'lock.cc:845'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'sql_select.cc:13042'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'filesort.cc:365'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'mf_cache.c:64'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'mf_cache.c:65'
Error: Memory allocated at my_alloc.c:201 was overrun, discovered at 'mf_iocache.c:238'
mysqld: handler.h:1164: int handler::ha_rnd_end(): Assertion `inited==RND' failed.
070915 12:20:04 - mysqld got signal 6;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388572
read_buffer_size=131072
max_used_connections=8
max_threads=151
threads_connected=7
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337620 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x8e1ddd8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x4283f8d4, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x820109b handle_segfault + 541
0xffffe410 _end + -142171936
0x400edb75 _end + 932550725
0x400e5903 _end + 932517331
0x82ed378 find_all_keys(st_sort_param*, SQL_SELECT*, unsigned char**, st_io_cache*, st_io_cache*, st_io_cache*) + 2154
0x82ec311 filesort(THD*, st_table*, st_sort_field*, unsigned int, SQL_SELECT*, unsigned long long, bool, unsigned long long*) + 1201
Stack trace seems successful - bottom reached
Please read http://dev.mysql.com/doc/refman/5.1/en/resolve-stack-dump.html
and follow instructions on how to resolve the stack trace.
Resolved stack trace is much more helpful in diagnosing the
problem, so please do resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8e2f990 = SELECT * FROM test_table ORDER BY v
thd->thread_id=22
[26 Oct 2007 16:28] 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/36442

ChangeSet@1.2582, 2007-10-26 21:26:06+05:00, gshchepa@gleb.loc +4 -0
  Fixed bug #31036: Using order by with archive table crashes server.
  
  1. Memory overrun have been fixed.
  2. Server failure on assertion has been fixed.
[16 Nov 2007 9:33] Bugs System
Pushed into 5.1.23-rc
[16 Nov 2007 9:35] Bugs System
Pushed into 6.0.4-alpha
[19 Nov 2007 5:07] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.