MySQL Bugs: #18122: Mysql server crashes

Bug #18122	Mysql server crashes
Submitted:	9 Mar 2006 23:35	Modified:	18 Apr 2006 16:39
Reporter:	Simon Teller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19	OS:	Linux (Fedora Core 4)
Assigned to:		CPU Architecture:	Any

Description:
Running Fedora Core 4 with Apache 2.2, Mysql 5.0.18, PHP 5.12

Using a forum software called SMF (Simple Machines Forum).
Using two queries always crashes the MySQL server with the error "Database Error: Lost connection to MySQL server during query". Both queries are doing pretty much the same, just in different pages. The first problem is documented with the full query on the SMF forum and explains what the forum software developer did to get the query working. This topic is viewable at this url:
http://www.simplemachines.org/community/index.php?topic=63232.msg439212#msg439212
The query that caused the mysql server to crash was:

SELECT
a.filename, a.attachmentType, a.ID_ATTACH, a.ID_MEMBER, a.ID_MSG,
IFNULL(thumb.ID_ATTACH, 0) AS ID_THUMB, thumb.filename AS thumb_filename, thumb_parent.ID_ATTACH AS ID_PARENT
FROM (cvssmf_attachments AS a)
LEFT JOIN cvssmf_attachments AS thumb ON (thumb.ID_ATTACH = a.ID_THUMB)
LEFT JOIN cvssmf_attachments AS thumb_parent ON (a.attachmentType = 3 AND thumb_parent.ID_THUMB = a.ID_ATTACH)
WHERE a.attachmentType = 0
AND a.ID_MSG = 2230

I've been in touch with the forum writers about this issue and they tell me that this must be a mysql bug. They got around this crash error by escaping the integer, something they tell me should not be necessary.

The other query that is still crashing the server now is present in this excerpt from the mysql error log. It is a query to edit/delete posts and topics on the forum and is as follows.
_____________________________________________________________
060309 21:50:35 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.18-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL)
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=16777216
read_buffer_size=2093056
max_used_connections=5
max_connections=500
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2062380 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x92b3808
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=0x3ebcf8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815bbb0
0xc20420
0x816f4a3
0x8178318
0x816f4a3
0x816efdd
0x816e52e
0x886b80
0x7999ce
New value of fp=(nil) failed sanity check, terminating stack trace!
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 0x93295b8 = SELECT
a.filename, a.attachmentType, a.ID_ATTACH, a.ID_MEMBER, a.ID_MSG,
IFNULL(thumb.ID_ATTACH, 0) AS ID_THUMB, thumb.filename AS thumb_filename, thumb_parent.ID_ATTACH AS ID_PARENT
FROM (smf_attachments AS a)
LEFT JOIN smf_attachments AS thumb ON (thumb.ID_ATTACH = a.ID_THUMB)
LEFT JOIN smf_attachments AS thumb_parent ON (a.attachmentType = 3 AND thumb_parent.ID_THUMB = a.ID_ATTACH)
WHERE a.attachmentType = 0 AND a.ID_MSG = 3620 AND a.ID_ATTACH NOT IN (0, 212)
thd->thread_id=68
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
060309 21:51:44 mysqld restarted
-------------------------------------------
I really hope that you can advise on this. This never happened with MySQL4 at all, only upon upgrading to 5. I am using the official mysql rpm's for generic linux.
Packages installed are
MySQL-shared-5.0.18-0.glibc23
MySQL-client-5.0.18-0.glibc23
MySQL-server-5.0.18-0.glibc23
MySQL-devel-5.0.18-0.glibc23

How to repeat:
Run MySQL5 on fedora core 4, and php 5.12. Install SMF forum software.

Set up a forum and try to delete the topic and the reply to a topic. This error wll occur then, and the database will die.

Thank you for a problem report. Please, try to repeat with a newer version, 5.0.19, and, in case of the same crash, send the SHOW CREATE TABLE and SHOW TABLE STATUS results for the table in that crashing self-join query, 
smf_attachments.

Hi there,
Upgraded to mysql 5.0.19 using same rpm's, just newer versions.
Same error occurred, error log segment below.

----------------------------------------------------------------
Error Log

060323 14:24:34 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.19-standard'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)
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=16777216
read_buffer_size=2093056
max_used_connections=3
max_connections=500
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2062380 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8c0cb18
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=0x521cf8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x815d800
0xf76420
0x8171443
0x817a1e8
0x8171443
0x8170f7d
0x81704c0
0x886b80
0x7999ce
New value of fp=(nil) failed sanity check, terminating stack trace!
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 0x8c02b50 = SELECT
                        a.filename, a.attachmentType, a.ID_ATTACH, a.ID_MEMBER, a.ID_MSG,
                        IFNULL(thumb.ID_ATTACH, 0) AS ID_THUMB, thumb.filename AS thumb_filename, thumb_parent.ID_ATTACH AS ID_PARENT
                FROM (smf_attachments AS a)
                        LEFT JOIN smf_attachments AS thumb ON (thumb.ID_ATTACH = a.ID_THUMB)
                        LEFT JOIN smf_attachments AS thumb_parent ON (a.attachmentType = 3 AND thumb_parent.ID_THUMB = a.ID_ATTACH)
                WHERE a.attachmentType = 0 AND a.ID_MSG = 3620 AND a.ID_ATTACH NOT IN (0, 212)
thd->thread_id=22
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
------------------------------------

Create Tabe Syntax

-- --------------------------------------------------------

-- 
-- Table structure for table `smf_attachments`
-- 

CREATE TABLE `smf_attachments` (
  `ID_ATTACH` int(10) unsigned NOT NULL auto_increment,
  `ID_THUMB` int(10) unsigned NOT NULL default '0',
  `ID_MSG` int(10) unsigned NOT NULL default '0',
  `ID_MEMBER` mediumint(8) unsigned NOT NULL default '0',
  `attachmentType` tinyint(3) unsigned NOT NULL default '0',
  `filename` tinytext NOT NULL,
  `size` int(10) unsigned NOT NULL default '0',
  `downloads` mediumint(8) unsigned NOT NULL default '0',
  `width` mediumint(8) unsigned NOT NULL default '0',
  `height` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ID_ATTACH`),
  UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`ID_ATTACH`),
  KEY `ID_MSG` (`ID_MSG`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=215 ;

-----------------------------------------
Show table status output
| Name                     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length  | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
| smf_attachments          | MyISAM | 10      | Dynamic    | 163  | 41             | 6768        | 281474976710655  | 11264        | 0         | 215            | 2005-12-31 21:38:55 | 2006-03-23 14:28:59 | 2006-03-17 12:52:58 | latin1_swedish_ci | NULL     |                |         |

Hope this helps :)
Regards
Simon

i can verify this bug on MySQL 5.0.19 as well, running SMF software version 1.1 RC2.  

to bypass this problem, i have had to disable all attachments on the forums, which is not an awful workaround... but i would love if this were in fact fixed ASAP if this is indeed a MySQL bug.

i'm sorry, mine is on a different OS as well -- Debian 3 (Sarge) server with all the latest apt-get upgrades.

this has apparently been fixed in 5.0.20, just verified on my SMF installation.  thanks!!

Myself and 2 other people have tested this issue with the new, MySQL 5.0.20. The bug appears to have disappeared.
However, the changelog for 5.0.20 doesn't specifically address this bug it appears, so I ask that you ensure that you are aware of what the problem was between MySQL < 5.0.20 compared with 5.0.20, so that these changes will not be reversed in subsequent versions of MySQL.

I'll close this issue now, but do ask that you are aware of this for the future releases.

Many thanks for your efforts !
Regards
Simon

Thank you for the additional tests. Please, send a comment to this bug report if you will see the crash again (I had not been able to get it in 5.0.21-BK), including the resolved stack trace and dump of the smallest set of data to demonstrate the crash.

tdzdubs sen wjvoqj lyzzo groj dze <a href="http://www.joomlacart.1hwy.com/">erbjudanden</a> ymcfiah ynb qmwoxo mvlth ohoy cdw     
    
http://exorbitance.snn.gr d