Subquery Crashes Server

Hello All,
Sorry this is so long, but I have attempted to answer all that I could
before sending this.

First of all, the specs:
MySQL MAX 4.1.1   Installed using
"mysql-max-4.1.1-alpha-pc-linux-i686.tar.gz"
Red Hat Linux AS 2.1

Now the specifics:
I have 2 queries.  Both are more complex than what I have posted, as I have
isolated the incident to the particular section of each query that is
causing the crash. The CUSTOMER table is 150K rows, the PART table is 200K,
and PARTSUPP is 800K.  It appears that the server is crashing somewhere
around the Copy_field portion???

In trying to diagnose this, I download the debug version.  Strange thing
there as well, the server crashes, but does not restart like in the alpha
version. 

040510 16:39:29  mysqld restarted
040510 16:39:29  Can't start server: Bind on TCP/IP port: Address already in
use
040510 16:39:29  Do you already have another mysqld server running on port:
3306 ?
040510 16:39:29  Aborting 

There are no other servers running.
[root@hvcwy0918 data]# ps -ef |grep mysqld
root     24953 24852  0 16:48 pts/1    00:00:00 grep mysqld

I am at the end of my rope as to what to do next.  Any suggestions?

----------------------------------------------------------------------------
----
CREATE TABLE CUSTOMER (
   C_CUSTKEY      int(10),
   C_NAME         varchar(25),
   C_ADDRESS      varchar(40),
   C_NATIONKEY    int(10),
   C_PHONE        char(15),
   C_ACCTBAL      dec(10,2),
   C_MKTSEGMENT   char(10),
   C_COMMENT      varchar(117),
      PRIMARY KEY (C_CUSTKEY),
      INDEX C_NATIONKEY_INDX (C_NATIONKEY),
      FOREIGN KEY (C_NATIONKEY) REFERENCES NATION(N_NATIONKEY) ON DELETE NO
ACTION)
TYPE=INNODB;

select
        substring(c_phone from 1 for 2) as cntrycode,
        c_acctbal
from
        CUSTOMER
where
        substring(c_phone from 1 for 2) in
                ('13', '10', '16', '24', '20', '15', '31')
        and c_acctbal > (
                select
                        avg(c_acctbal)
                from
                        CUSTOMER
                where
                        c_acctbal > 0.00
                        and substring(c_phone from 1 for 2) in
                                ('13', '10', '16', '24', '20', '15', '31')
        )
                        

0x8106af3 handle_segfault + 423
0xb75c4df8 _end + -1358819568
0x171c7ccc _end + 247610852
0x81783cb general_fetch__11ha_innobasePcUiUi + 75
0x817860b rnd_next__11ha_innobasePc + 79
0x8168a39 rr_sequential__FP14st_read_record + 153
0x813e57e sub_select__FP4JOINP13st_join_tableb + 330
0x813e226 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 434
0x8134b36 exec__4JOIN + 4234
0x8135068
mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_ord
erT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 832
0x8131fde handle_select__FP3THDP6st_lexP13select_result + 174
0x81142b7 mysql_execute_command__FP3THD + 1427
0x8118d49 mysql_parse__FP3THDPcUi + 177
0x8112f3f dispatch_command__F19enum_server_commandP3THDPcUi + 1635
0x81128d1 do_command__FP3THD + 161
0x8112047 handle_one_connection + 563
0xb75bedac _end + -1358844220
0xb74ea9ea _end + -1359713534

----------------------------------------------------------------------------
----
CREATE TABLE PART (
   P_PARTKEY      int(10),
   P_NAME         varchar(55),
   P_MFGR         char(25),
   P_BRAND        char(10),
   P_TYPE         varchar(25),
   P_SIZE         int(10),
   P_CONTAINER    char(10),
   P_RETAILPRICE  dec(10,2),
   P_COMMENT      varchar(23),
      PRIMARY KEY (P_PARTKEY))
TYPE=INNODB;

CREATE TABLE PARTSUPP (
   PS_PARTKEY     int(10),
   PS_SUPPKEY     int(10),
   PS_AVAILQTY    int(10),
   PS_SUPPLYCOST  dec(10,2),
   PS_COMMENT     varchar(199),
      PRIMARY KEY (PS_PARTKEY, PS_SUPPKEY),
      INDEX PS_PARTKEY_INDX (PS_PARTKEY),
      FOREIGN KEY (PS_PARTKEY) REFERENCES PART(P_PARTKEY) ON DELETE NO
ACTION,
      INDEX PS_SUPPKEY_INDX (PS_SUPPKEY),
      FOREIGN KEY (PS_SUPPKEY) REFERENCES SUPPLIER(S_SUPPKEY) ON DELETE NO
ACTION)
TYPE=INNODB;

select
        count(*)
from
        PARTSUPP
where ps_supplycost = (
                select
                        min(ps_supplycost)
                from
                        PARTSUPP,
                        PART
                where
                        p_partkey = ps_partkey
               );

0x8106af3 handle_segfault + 423
0xb75c4df8 _end + -1358819568
0x8185e6b set__10Copy_fieldP5FieldT1b + 387
0x81783cb general_fetch__11ha_innobasePcUiUi + 75
0x817860b rnd_next__11ha_innobasePc + 79
0x8168a39 rr_sequential__FP14st_read_record + 153
0x813e57e sub_select__FP4JOINP13st_join_tableb + 330
0x813e226 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 434
0x8134b36 exec__4JOIN + 4234
0x8135068
mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_ord
erT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 832
0x8131fde handle_select__FP3THDP6st_lexP13select_result + 174
0x81142b7 mysql_execute_command__FP3THD + 1427
0x8118d49 mysql_parse__FP3THDPcUi + 177
0x8112f3f dispatch_command__F19enum_server_commandP3THDPcUi + 1635
0x81128d1 do_command__FP3THD + 161
0x8112047 handle_one_connection + 563
0xb75bedac _end + -1358844220
0xb74ea9ea _end + -1359713534

Here is the last bit of the trace file before the crash for the above
select.  The rest of the trace file consists of fetches and index reads.
MySQL reaches the end of the indexes, unlocks the tables, commits the trans,
does some other stuff, and then attempts to fetch again??? 

T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >index_read
T@36874: | | | | | | | | | | <index_read
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | | <test_if_group_changed
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | | <test_if_group_changed
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | | <test_if_group_changed
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | | <test_if_group_changed
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >index_read
T@36874: | | | | | | | | | | <index_read
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | | <test_if_group_changed
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | | <test_if_group_changed
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | | <test_if_group_changed
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | | <test_if_group_changed
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >general_fetch
T@36874: | | | | | | | | | | <general_fetch
T@36874: | | | | | | | | | | >end_send_group
T@36874: | | | | | | | | | | | >select_singlerow_subselect::send_data
T@36874: | | | | | | | | | | | <select_singlerow_subselect::send_data
T@36874: | | | | | | | | | | <end_send_group
T@36874: | | | | | | | | | | >join_free
T@36874: | | | | | | | | | | | >free_io_cache
T@36874: | | | | | | | | | | | <free_io_cache
T@36874: | | | | | | | | | | | >my_free
T@36874: | | | | | | | | | | | | my: ptr: 0
T@36874: | | | | | | | | | | | <my_free
T@36874: | | | | | | | | | | | >index_end
T@36874: | | | | | | | | | | | <index_end
T@36874: | | | | | | | | | | | >my_free
T@36874: | | | | | | | | | | | | my: ptr: 0
T@36874: | | | | | | | | | | | <my_free
T@36874: | | | | | | | | | | | >index_end
T@36874: | | | | | | | | | | | <index_end
T@36874: | | | | | | | | | | | >mysql_unlock_read_tables
T@36874: | | | | | | | | | | | | >thr_multi_unlock
T@36874: | | | | | | | | | | | | | lock: data: 867fd38  count: 3
T@36874: | | | | | | | | | | | | | >thr_unlock
T@36874: | | | | | | | | | | | | | | lock: data: 867cfe4  thread: 36874
lock: 867d6c8
T@36874: | | | | | | | | | | | | | | lock: No waiting read locks to free
T@36874: | | | | | | | | | | | | | <thr_unlock
T@36874: | | | | | | | | | | | | | >thr_unlock
T@36874: | | | | | | | | | | | | | | lock: data: 8679b74  thread: 36874
lock: 8680298
T@36874: | | | | | | | | | | | | | <thr_unlock
T@36874: | | | | | | | | | | | | | >thr_unlock
T@36874: | | | | | | | | | | | | | | lock: data: 867c284  thread: 36874
lock: 8680298
T@36874: | | | | | | | | | | | | | | lock: No waiting read locks to free
T@36874: | | | | | | | | | | | | | <thr_unlock
T@36874: | | | | | | | | | | | | <thr_multi_unlock
T@36874: | | | | | | | | | | | | >unlock_external
T@36874: | | | | | | | | | | | | | >ha_innobase::external_lock
T@36874: | | | | | | | | | | | | | | enter: lock_type: 2
T@36874: | | | | | | | | | | | | | <ha_innobase::external_lock
T@36874: | | | | | | | | | | | | | >ha_innobase::external_lock
T@36874: | | | | | | | | | | | | | | enter: lock_type: 2
T@36874: | | | | | | | | | | | | | <ha_innobase::external_lock
T@36874: | | | | | | | | | | | | | >ha_innobase::external_lock
T@36874: | | | | | | | | | | | | | | enter: lock_type: 2
T@36874: | | | | | | | | | | | | | | >innobase_commit
T@36874: | | | | | | | | | | | | | | | trans: ending transaction
T@36874: | | | | | | | | | | | | | | <innobase_commit
T@36874: | | | | | | | | | | | | | <ha_innobase::external_lock
T@36874: | | | | | | | | | | | | <unlock_external
T@36874: | | | | | | | | | | | <mysql_unlock_read_tables
T@36874: | | | | | | | | | | <join_free
T@36874: | | | | | | | | | | info: 1 records output
T@36874: | | | | | | | | | <do_select
T@36874: | | | | | | | | <JOIN::exec
T@36874: | | | | | | | <subselect_single_select_engine::exec
T@36874: | | | | | | | >rnd_next
T@36874: | | | | | | | | >general_fetch
mysqld got signal 11;


Thanks for you consideration.

Chris Jeffus
Acxiom Corporation
Conway, AR
501.342.0447
chris.jeffus@xxxxxxxxxx



**********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.


-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/bugs?unsub=gcdmb-bugs@xxxxxxxxxxx