Bug #49835 sometime, 5.0.77 crash.
Submitted: 21 Dec 2009 2:15 Modified: 5 Mar 2010 6:42
Reporter: wang xu Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.77 OS:Linux (64)
Assigned to: CPU Architecture:Any
Tags: crash, mysqlserver

[21 Dec 2009 2:15] wang xu
Description:
Hi!
I have a trouble. we often find the question. I need your help.
============================================================================
Number of processes running now: 0
091220 18:07:38  mysqld restarted
091220 18:07:38  InnoDB: Started; log sequence number 0 9590762
091220 18:07:38 [Warning] 'user' entry 'root@f5db001.follow5.com' ignored in --skip-name-resolve mode.
091220 18:07:38 [Warning] 'user' entry '@f5db001.follow5.com' ignored in --skip-name-resolve mode.
091220 18:07:38 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.77-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
091220 18:55:14 - 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=1073741824
read_buffer_size=2097152
max_used_connections=28
max_connections=400
threads_connected=27
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 5144576 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
 
thd=0x2aaaac5e6620
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=0x45a89fb0, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
Stack trace seems successful - bottom reached
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.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 0xdaf5110 = select n.*,r.PUBLISH_TIME,r.receiver_id from note_01_info as n left join (NOTE_RECEIVER_15_INFO as r )  on n.note_i
d = r.note_id where  ( (r.receiver_id = '137415' )  or (r.SEND_ID in ('2311118','2254924','160801','164710','2267727','2275320','1252806','22
79407','2284007','1272008','2272908','2295408','2261409','1277809','2276610','1282609','1289010','2290110','2241820','1293210','2281808','118
3520','2192201','1254116','2243116','181015','2262506','166703','224512','1237605','153606','2221627','141928','2251918','1200402','2203902',
'1242101','2218903','1221605','1217303','2268927','1214103','1278116','151618','202515','154207','1264006','1243704','2250701','2243404','122
6003','1257705','158604','164128','1241126','2263205','191116','142608','160304','1198502','2233327','139804','175708','1268515','2229720','1
243109','2176931','157613','145715','2216420','173907','1282324','2216429','1213529','209614','2238810','2200030','143124','140402','1268720'
,'174218','130424','1264005','1203302','2234228','2268423','223
thd->thread_id=1
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
============================================================================

How to repeat:
I am not sure, It will be repeat sometime. it is random.
[21 Dec 2009 2:20] wang xu
My my.cnf:

key_buffer_size = 1024M            
max_allowed_packet = 1024M
table_cache = 2048
 
net_read_timeout=120          

sort_buffer_size = 8M              
read_buffer_size = 2M            
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 128M
thread_cache_size = 500
tmp_table_size=384M        
tmpdir=/home/mysqldata/tmp 
thread_stack=384K       
query_cache_size= 128M   
query_cache_limit= 2M 
join_buffer_size=8M       
interactive_timeout=86400 
wait_timeout=86400        
max_connect_errors = 10000000  
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
set-variable=max_connections=400
[21 Dec 2009 2:24] wang xu
OS:CentOS release 5.2 (Final)
Cpu Cores:4
Memory:16G
[21 Dec 2009 4:23] Valeriy Kravchuk
If crash always happen on the same/similar SELECT, please, identify its entire text and send EXPLAIN results for it.
[21 Dec 2009 5:48] wang xu
explain select * from note_01_info as n ,NOTE_RECEIVER_15_INFO as r where n.note_id = r.note_id and ( (r.receiver_id = '137415' )  or (r.SEND_ID in ('1279418','2311118','2254924','160801','164710','2267727','2275320','1252806',......'135610','169818','197009','141018','158403','203125','155615','167917','167920','177920','195728','200304','201906','138220','147623','137811','184410','132924','140018','131611','147019','151714','148621','135024','154118','133122','1227025','1191420','101825','218415','152708','190623','1267127','187629','185926','2254006','2223413','1267306','2246304','200905','190109','1244215','100409','174126','183605','240914','1220428','152205','2247410','101925','1197021','2248917','190813','156917','156417','151518','103024','1201921','155114','105624','144325','204511','2218029','155918','1284018','102125','129431','102726','2217128','177726','145610','1254424','2235423','143702','1241017','154510','1210209','1266806','137415' ) and r.RECEIVER_TYPE = 6) )  order by n.PUBLISH_TIME desc  LIMIT 0 , 21;

+----+-------------+-------+-------------+-----------------------------------------------+---------------------------------+---------+--------------------+--------+-------------------------------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                 | key                             | key_len | ref                | rows   | Extra                                                                                           |
+----+-------------+-------+-------------+-----------------------------------------------+---------------------------------+---------+--------------------+--------+-------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | r     | index_merge | INDEX_NOTE_ID,INDEX_SEND_ID,INDEX_RECEIVER_ID | INDEX_RECEIVER_ID,INDEX_SEND_ID | 9,9     | NULL               | 187044 | Using sort_union(INDEX_RECEIVER_ID,INDEX_SEND_ID); Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | n     | eq_ref      | PRIMARY                                       | PRIMARY                         | 8       | followme.r.NOTE_ID |      1 |                                                                                                 | 
+----+-------------+-------+-------------+-----------------------------------------------+---------------------------------+---------+--------------------+--------+-------------------------------------------------------------------------------------------------+
2 rows in set (0.08 sec)
[21 Dec 2009 5:49] wang xu
logs for explain results.

Attachment: explain_results.txt (text/plain), 11.10 KiB.

[21 Dec 2009 5:50] wang xu
The sql is too long, so please get the sql and the explain resutls from the att files.
[21 Dec 2009 7:09] Valeriy Kravchuk
OK, so temporary table is used. Please, send the results of

free

Linux command and the results of

show global status;
show global variables like '%heap%';
[21 Dec 2009 7:29] wang xu
>free
>Linux command and the results of
>show global status;
>show global variables like '%heap%';
thank for your help .
The comment is too long. please get the results from attach file(variables.txt).
[21 Dec 2009 7:31] wang xu
free & global status

Attachment: variables.txt (text/plain), 13.39 KiB.

[19 Jan 2010 8:19] wang xu
I am waiting ...
[5 Feb 2010 6:42] Sveta Smirnova
Thank you for the feedback.

Please also send output of SHOW CREATE TABLE note_01_info, SHOW CREATE TABLE NOTE_RECEIVER_15_INFO and SHOW TABLE STATUS for both tables.
[6 Mar 2010 0:00] Bugs System
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".