Subquery Crashes Server
am 10.05.2004 18:42:51 von Jeffus Chris - cjeffuHello 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_listUiRt4List1Z4Item P4ItemUiP8st_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_listUiRt4List1Z4Item P4ItemUiP8st_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: | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >test_if_group_changed
T@36874: | | | | | | | | | | | | info: idx: -1
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | |
T@36874: | | | | | | | | | | | >select_singlerow_subselect::send_data
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | | | >free_io_cache
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | | | | my: ptr: 0
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | | | | my: ptr: 0
T@36874: | | | | | | | | | | |
T@36874: | | | | | | | | | | |
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: | | | | | | | | | | | | |
T@36874: | | | | | | | | | | | | | | lock: data: 8679b74 thread: 36874
lock: 8680298
T@36874: | | | | | | | | | | | | |
T@36874: | | | | | | | | | | | | | | lock: data: 867c284 thread: 36874
lock: 8680298
T@36874: | | | | | | | | | | | | | | lock: No waiting read locks to free
T@36874: | | | | | | | | | | | | |
T@36874: | | | | | | | | | | | | | >ha_innobase::external_lock
T@36874: | | | | | | | | | | | | | | enter: lock_type: 2
T@36874: | | | | | | | | | | | | |
T@36874: | | | | | | | | | | | | | | enter: lock_type: 2
T@36874: | | | | | | | | | | | | |
T@36874: | | | | | | | | | | | | | | enter: lock_type: 2
T@36874: | | | | | | | | | | | | | | >innobase_commit
T@36874: | | | | | | | | | | | | | | | trans: ending transaction
T@36874: | | | | | | | | | | | | | |
T@36874: | | | | | | | | |
T@36874: | | | | | | | | >general_fetch
mysqld got signal 11;
Thanks for you consideration.
Chris Jeffus
Acxiom Corporation
Conway, AR
501.342.0447
chris.jeffus@acxiom.com
************************************************************ **********
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@m.gmane.org