MySQL Bugs: #61805: MySQL crashes running query

Bug #61805	MySQL crashes running query
Submitted:	9 Jul 2011 5:20	Modified:	9 Sep 2011 23:14
Reporter:	Trevor Murphy	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.0.51a-3ubuntu5.4	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	3 million rows, crash, query, self join

Description:
MySql is crashing unpredictably. The following query works for some users, and not for others. Some users with 1000's of rows have no problem, others do, including users with just 4 rows. This crashes the server. Running on linux. Table has 3 million plus rows. Query does a self join. Query is against a message table and returns the latest row for each thread

SELECT 'text_messages'.* FROM 'text_messages'  LEFT OUTER JOIN text_messages as m2 ON (text_messages.sms_thread_id = m2.sms_thread_id AND m2.queue_name = 'rachelesmith' AND (m2.is_deleted IS NULL OR m2.is_deleted = 0) AND (m2.hide_from_chat IS NULL OR m2.hide_from_chat = 0) AND text_messages.created_at < m2.created_at) WHERE (m2.sms_thread_id is NULL) AND (((text_messages.queue_name = 'rachelesmith') AND (text_messages.is_deleted IS NULL OR text_messages.is_deleted = 0)) AND (text_messages.hide_from_chat IS NULL OR text_messages.hide_from_chat = 0)) AND ( ('text_messages'.'type' = 'SmsTextMessage' ) )  ORDER BY text_messages.updated_at DESC LIMIT 0, 25

Not sure if it is a database config problem, a corrupted table, index or how to trouble shoot. But in general one would not expect their database to crash. Perform poorly sure, but crash?

How to repeat:
SELECT 'text_messages'.* FROM 'text_messages'  LEFT OUTER JOIN text_messages as m2 ON (text_messages.sms_thread_id = m2.sms_thread_id AND m2.queue_name = 'rachelesmith' AND (m2.is_deleted IS NULL OR m2.is_deleted = 0) AND (m2.hide_from_chat IS NULL OR m2.hide_from_chat = 0) AND text_messages.created_at < m2.created_at) WHERE (m2.sms_thread_id is NULL) AND (((text_messages.queue_name = 'rachelesmith') AND (text_messages.is_deleted IS NULL OR text_messages.is_deleted = 0)) AND (text_messages.hide_from_chat IS NULL OR text_messages.hide_from_chat = 0)) AND ( ('text_messages'.'type' = 'SmsTextMessage' ) )  ORDER BY text_messages.updated_at DESC LIMIT 0, 25

Hi Trevor,

There are thousands of crashing bugs in 5.0.51.  You should consider upgrade to
5.1.58 or 5.5.14 and check if stability improves.  However, feel free to upload the mysql error log (attached in a file) here so we can check if the stack trace gives us any clues about which bug you hit.

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".

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".