List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:October 12 1999 9:38pm
Subject:RE: Bus Error when using ORDER BY with 0 records (Solaris 2.6)
View as plain text  
>>>>> "David" == David Adams <da@stripped> writes:

David> Attached are two files: bugdb.sql and bugquery.sql (sorry about the names!)
David> Create a fresh database "testdb" and run
David> mysql testdb < bugdb.sql

David> This creates three tables with a couple hundred rows in each.

David> Running
David> mysql testdb < bugquery.sql

David> crashes mysqld.

David> The query is:

David> select w.userid
David> from tree t, treedescriptions td,  weightings w
David> where td.treeid = t.id
David> and w.typeid=3
David> and (w.userid=0 or w.userid = 1)
David> and w.itemid=t.id
David> and t.childid=0
David> order by w.userid

David> Changing almost anything makes the query NOT crash the server.  Removing the
David> order by, any of the where clauses or even moving "treedescriptions" after
David> "weightings" in the from clause: all will allow the query to then work.

David> I didn't post this to the list because I am unsure about the etiquette
David> regarding the attachments.

Hi!

Thanks for the test!   Yes, attachments doesn't go to the list.  I did 
however cc this to the list as someone else may have use for the
patch.


The problem with this query is that there isn't any real good keys
that MySQL can use to solve the query:
(Note that MySQL 3.23 can solve the query a bit better and because of
this doesn't get killed because of the same bug).

mysql> explain select w.userid
    -> from tree t, treedescriptions td,  weightings w
    -> where td.treeid = t.id
    -> and w.typeid=3
    -> and (w.userid=0 or w.userid = 1)
    -> and w.itemid=t.id
    -> and t.childid=0
    -> order by w.userid
    -> go
+-------+-------+----------------+-----------+---------+------+------+---------------+
| table | type  | possible_keys  | key       | key_len | ref  | rows | Extra         |
+-------+-------+----------------+-----------+---------+------+------+---------------+
| w     | ref   | weights_b      | weights_b |       4 | ???  |    2 | ; Using index |
| td    | ALL   | NULL           | NULL      |    NULL | NULL |  150 |               |
| t     | range | PRIMARY,tree_2 | tree_2    |    NULL | NULL |    1 |               |
+-------+-------+----------------+-----------+---------+------+------+---------------+

The problem was a bug in the code that caches rows when doing a full
join. Here is a fix for this:

*** /my/monty/master/mysql-3.22.26a/sql/sql_select.cc	Fri Sep  3 00:39:51 1999
--- ./sql_select.cc	Wed Oct 13 00:21:59 1999
***************
*** 3095,3101 ****
--- 3095,3105 ----
    }
   /* read through all records */
    if ((error=join_init_read_record(join_tab)) < 0)
+   {
+     reset_cache(&join_tab->cache);
+     join_tab->cache.records=0; join_tab->cache.ptr_record= (uint) ~0;
      return 1;				/* No records (not fatal) */
+   }
  
    for (JOIN_TAB *tmp=join->join_tab; tmp != join_tab ; tmp++)
    {
***************
*** 4242,4248 ****
    length=cache->length;
    if (cache->blobs)
      length+=used_blob_length(cache->blob_ptr);
!   if ((last_record=(length > (uint) (cache->end - pos))))
      cache->ptr_record=cache->records;
  
    /*
--- 4246,4252 ----
    length=cache->length;
    if (cache->blobs)
      length+=used_blob_length(cache->blob_ptr);
!   if ((last_record=(length+cache->length > (uint) (cache->end - pos))))
      cache->ptr_record=cache->records;
  
    /*


(The same patch will be included in MySQL 3.23.5)

Regards,
Monty
Thread
Bus Error when using ORDER BY with 0 records (Solaris 2.6)dadams7 Oct
  • Bus Error when using ORDER BY with 0 records (Solaris 2.6)Michael Widenius8 Oct
    • RE: Bus Error when using ORDER BY with 0 records (Solaris 2.6)David Adams12 Oct
RE: Bus Error when using ORDER BY with 0 records (Solaris 2.6)Michael Widenius12 Oct