MySQL Bugs: #13082: crash mysqld,restart mysql

Bug #13082	crash mysqld,restart mysql
Submitted:	9 Sep 2005 7:46	Modified:	13 Oct 2005 10:31
Reporter:	jo ho-sug	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.11	OS:	Linux (linux,)
Assigned to:		CPU Architecture:	Any

Description:
Description:
When executing special query ,then the server crashes.  An excerpt from the error log

follows:

mysqld got signal 11;
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=268435456
read_buffer_size=1044480
max_used_connections=250
max_connections=250
threads_connected=55
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1029142 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=0xa6920148
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...
frame pointer (ebp) is NULL, did you compile with
-fomit-frame-pointer? Aborting backtrace!
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8611528 = SELECT   ITEMID , ITEM.SHOPID , ITEM.HIGH_CATEGORYID , ITEM.MIDDLE_CATEGORYID , ITEM.LOW_CATEGORYID , ITEM_NAME , ITEM_TYPE , ITEM_COMMENT , ITEM_EFFECT_COMMENT , ITEM_DISPLAY_CYMONEY , ITEM_DISPLAY_SRC , ITEM_SRC , ITEM_SRC_L , ITEM_EXPIRE_SRC , ITEM_WIDTH , ITEM_HEIGHT , THEMEID , ARTIST , CONFIRM_TYPE , DISPLAY_YN , ITEM.ACTIVE_TYPE , CONFIRM_DATE , CONFIRM_UPDATE_DATE , SELL_CNT , date_format(ITEM.CREATE_DATE, '%Y%m%d') , DATE_FORMAT(UPDATE_DATE,'%Y%m%d') , SHOP.SHOP_NAME , HIGH.HIGH_CATEGORY_NAME , MIDDLE.MIDDLE_CATEGORY_NAME  FROM  DB_MALL.TB_ITEM ITEM  LEFT JOIN DB_MALL.TB_SHOP SHOP ON ITEM.SHOPID = SHOP.SHOPID  LEFT JOIN DB_MALL.TB_HIGH_CATEGORY HIGH ON ITEM.HIGH_CATEGORYID = HIGH.HIGH_CATEGORYID  LEFT JOIN DB_MALL.TB_MIDDLE_CATEGORY MIDDLE ON ITEM.MIDDLE_CATEGORYID = MIDDLE.MIDDLE_CATEGORYID  WHERE  ITEM.SHOPID = SHOP.SHOPID  AND  ITEM.HIGH_CATEGORYID = HIGH.HIGH_CATEGORYID  AND  ITEM.MIDDLE_CATEGORYID = MIDDLE.MIDDLE_CATEGORYID  AND  ITEMID IN (1184,1223,1132,1223,1132,1083,1001,1191,1057,1223) ORDE
thd->thread_id=3127914
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 0
050805 17:57:49  mysqld restarted
/gsp/server/mysql/libexec/mysqld: ready for connections.
Version: '4.1.11-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

How to repeat:
This bug sometimes ocurrs, when special query.
mainly we execute on join syntax

Please, describe you hardware and send the results of EXPLAIN for your special queries causing crash:

explain SELECT   ITEMID , ITEM.SHOPID ,
ITEM.HIGH_CATEGORYID , ITEM.MIDDLE_CATEGORYID , ITEM.LOW_CATEGORYID ,
ITEM_NAME , ITEM_TYPE , ITEM_COMMENT , ITEM_EFFECT_COMMENT ,
ITEM_DISPLAY_CYMONEY , ITEM_DISPLAY_SRC , ITEM_SRC , ITEM_SRC_L ,
ITEM_EXPIRE_SRC , ITEM_WIDTH , ITEM_HEIGHT , THEMEID , ARTIST ,
CONFIRM_TYPE , DISPLAY_YN , ITEM.ACTIVE_TYPE , CONFIRM_DATE ,
CONFIRM_UPDATE_DATE , SELL_CNT , date_format(ITEM.CREATE_DATE,
'%Y%m%d') , DATE_FORMAT(UPDATE_DATE,'%Y%m%d') , SHOP.SHOP_NAME ,
HIGH.HIGH_CATEGORY_NAME , MIDDLE.MIDDLE_CATEGORY_NAME  FROM 
DB_MALL.TB_ITEM ITEM  LEFT JOIN DB_MALL.TB_SHOP SHOP ON ITEM.SHOPID =
SHOP.SHOPID  LEFT JOIN DB_MALL.TB_HIGH_CATEGORY HIGH ON
ITEM.HIGH_CATEGORYID = HIGH.HIGH_CATEGORYID  LEFT JOIN
DB_MALL.TB_MIDDLE_CATEGORY MIDDLE ON ITEM.MIDDLE_CATEGORYID =
MIDDLE.MIDDLE_CATEGORYID  WHERE  ITEM.SHOPID = SHOP.SHOPID  AND 
ITEM.HIGH_CATEGORYID = HIGH.HIGH_CATEGORYID  AND 
ITEM.MIDDLE_CATEGORYID = MIDDLE.MIDDLE_CATEGORYID  AND  ITEMID IN
(1184,1223,1132,1223,1132,1083,1001,1191,1057,1223) ...

thank for your help

explain follows, but i think this bug no relation hardware, just mysql version.
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ITEM
         type: range
possible_keys: PRIMARY,IDX1_HIGH_CATEGORYID,IDX2_HIGH_CATEGORYID,IDX3_HIGH_CATEGORYID,IDX3_MIDDLE_CATEGORYID,IDX4_MIDDLE_CATEGORYID,IDX5_MIDDLE_CATEGORYID,IDX6_SHOPID,IDX7_SHOPID,IDX8_SHOPID,IDX10_ITEM_NAME,IDX11_ARTIST
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 7
        Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: SHOP
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: DB_MALL.ITEM.SHOPID
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: HIGH
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: MIDDLE
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 1
          ref: DB_MALL.ITEM.MIDDLE_CATEGORYID
         rows: 1
        Extra: Using where
4 rows in set (0.00 sec)

thanks~

I asked about hardware because the crash you got may be related to inability to allocate 1029142 K bytes of memory for mysqld, as one can see in the log.

So, how much RAM do you really have? What kind (kernel) of Linux do you use?

What are the results of "ulimit -a" command executed by the user running mysqld (mysql, I suppose)?

If you think it is a version-specific problem, please, try to repeat the behaviour you described on the newer version, 4.1.14. It is available for download from http://dev.mysql.com/downloads/mysql/4.1.html

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".