MySQL Bugs: #13545: Server crash caused by select query

Bug #13545	Server crash caused by select query
Submitted:	27 Sep 2005 21:12	Modified:	30 Sep 2005 17:36
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.0.14-rc BK source	OS:	Linux (Linux/Windows)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
The entire server crashes with the following queries.

How to repeat:
drop table attachment;
drop table post;
drop table xbt_files;
drop table xbt_files_users;

CREATE TABLE `attachment` (
  `attachmentid` int(10) unsigned NOT NULL auto_increment,
  `userid` int(10) unsigned NOT NULL default '0',
  `dateline` int(10) unsigned NOT NULL default '0',
  `thumbnail_dateline` int(10) unsigned NOT NULL default '0',
  `filename` varchar(100) NOT NULL default '',
  `filedata` mediumblob NOT NULL,
  `visible` smallint(5) unsigned NOT NULL default '0',
  `counter` smallint(5) unsigned NOT NULL default '0',
  `filesize` int(10) unsigned NOT NULL default '0',
  `postid` int(10) unsigned NOT NULL default '0',
  `filehash` char(32) NOT NULL default '',
  `posthash` char(32) NOT NULL default '',
  `thumbnail` mediumblob NOT NULL,
  `thumbnail_filesize` int(10) unsigned NOT NULL,
  `bt_info_hash` blob,
  `bt_name` varchar(255) default NULL,
  `bt_size` bigint(20) default NULL,
  `bt_tracker` varchar(255) default NULL,
  PRIMARY KEY  (`attachmentid`),
  KEY `filesize` (`filesize`),
  KEY `filehash` (`filehash`),
  KEY `userid` (`userid`),
  KEY `posthash` (`posthash`,`userid`),
  KEY `postid` (`postid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

CREATE TABLE `post` (
  `postid` int(10) unsigned NOT NULL auto_increment,
  `threadid` int(10) unsigned NOT NULL default '0',
  `parentid` int(10) unsigned NOT NULL default '0',
  `username` varchar(50) NOT NULL default '',
  `userid` int(10) unsigned NOT NULL default '0',
  `title` varchar(250) NOT NULL default '',
  `dateline` int(10) unsigned NOT NULL default '0',
  `pagetext` mediumtext NOT NULL,
  `allowsmilie` smallint(6) NOT NULL default '0',
  `showsignature` smallint(6) NOT NULL default '0',
  `ipaddress` char(15) NOT NULL default '',
  `iconid` smallint(5) unsigned NOT NULL default '0',
  `visible` smallint(6) NOT NULL default '0',
  `attach` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`postid`),
  KEY `iconid` (`iconid`),
  KEY `userid` (`userid`),
  KEY `threadid` (`threadid`,`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

CREATE TABLE `xbt_files` (
  `fid` int(11) NOT NULL auto_increment,
  `info_hash` blob NOT NULL,
  `leechers` int(11) NOT NULL,
  `seeders` int(11) NOT NULL,
  `announced_http` int(11) NOT NULL,
  `announced_http_compact` int(11) NOT NULL,
  `announced_http_no_peer_id` int(11) NOT NULL,
  `announced_udp` int(11) NOT NULL,
  `scraped_http` int(11) NOT NULL,
  `scraped_udp` int(11) NOT NULL,
  `completed` int(11) NOT NULL,
  `started` int(11) NOT NULL,
  `stopped` int(11) NOT NULL,
  `flags` int(11) NOT NULL,
  `mtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ctime` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`fid`),
  UNIQUE KEY `info_hash` (`info_hash`(20))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

CREATE TABLE `xbt_files_users` (
  `info_hash` blob NOT NULL,
  `uid` int(11) NOT NULL,
  `active` tinyint(4) NOT NULL,
  `announced` int(11) NOT NULL,
  `completed` int(11) NOT NULL,
  `downloaded` bigint(20) NOT NULL,
  `left` bigint(20) NOT NULL,
  `uploaded` bigint(20) NOT NULL,
  `mtime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `info_hash` (`info_hash`(20),`uid`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

select ifnull(xf.completed, 0) completed
	from xbt_files xf
		inner join xbt_files_users xfu on xf.info_hash = xfu.info_hash
		inner join attachment a on a.bt_info_hash = xf.info_hash
		inner join post p using (postid)

Reduced test case:
drop table if exists attachment;
drop table if exists post;
drop table if exists xbt_files;
drop table if exists xbt_files_users;

CREATE TABLE `attachment` (
  `postid` int,
  `bt_info_hash` blob
);

CREATE TABLE `post` (
  `postid` int
);

CREATE TABLE `xbt_files` (
  `info_hash` blob
);

CREATE TABLE `xbt_files_users` (
  `info_hash` blob,
  `uid` int
);

select ifnull(xf.completed, 0) completed
from xbt_files xf
	inner join xbt_files_users xfu on xf.info_hash = xfu.info_hash
	inner join attachment a on a.bt_info_hash = xf.info_hash
	inner join post p using (postid);

5.0.12-beta-Debian_2-log is also affected, so changing version from MySQL 5.0.13-rc-nt to * and OS to *.

Thank you for the bug report.

mysql> select ifnull(xf.completed, 0) completed
    -> from xbt_files xf
    -> inner join xbt_files_users xfu on xf.info_hash = xfu.info_hash
    -> inner join attachment a on a.bt_info_hash = xf.info_hash
    -> inner join post p using (postid);
ERROR 2013 (HY000): Lost connection to MySQL server during query

050927 18:20:36 [Note] libexec/mysqld: ready for connections.
Version: '5.0.14-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld got signal 11;

Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8e637d0 = select ifnull(xf.completed, 0) completed
from xbt_files xf
inner join xbt_files_users xfu on xf.info_hash = xfu.info_hash
inner join attachment a on a.bt_info_hash = xf.info_hash
inner join post p using (postid)
thd->thread_id=2
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.
miguel@hegel:~/dbs/5.0>

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30512

fixed in 5.0.14

Noted in 5.0.14 changelog.