MySQL Bugs: #12623: MYSQL CRASH AND HANG THE MACHINE WHILE RUNNING THIS QUERY

Bug #12623	MYSQL CRASH AND HANG THE MACHINE WHILE RUNNING THIS QUERY
Submitted:	17 Aug 2005 13:50	Modified:	19 Aug 2005 1:09
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	>4.1.10	OS:	Linux (LINUX 9.0)
Assigned to:		CPU Architecture:	Any

Description:
Hi all,

While running the following query :
SELECT Count(DISTINCT p.pid) AS post, Count(DISTINCT t.tid) AS threads FROM cdb_posts p
LEFT JOIN cdb_threads t ON t.tid > 0
WHERE 1 GROUP BY p.pid;

The mysql became hang and stop reponse, then will eat all the avaliable swap and ram and cpu and totally hang the system, the one way to restart the system if just go to the data center and make a hard reboot. Do anyone know how to solve this??Or this is a bugs of MYSQL?

IF install MYSQL-MAX, it aslo will be hang with  following the query :
SELECT Count(*) FROM cdb_threads where 1;

My system config :
REDHAT LINUX 9.0
MYSQL-SERVER - Now is 5.0.10-0  <<this also happens in version > 4.1.0
MySQL-bench 5.0.10-0
MySQL-client 5.0.10-0
MySQL-devel 5.0.10-0
MySQL-embedded 5.0.10-0
MySQL-shared 5.0.10-0
PHP 5.1.0b3
HTTPD 2.1.6alpha

Those to tables fields list below:

---------------------------------------------------------------------
TABLE cdb_posts   37185  RECORDS
---------------------------------------------------------------------

DROP TABLE IF EXISTS cdb_posts;
CREATE TABLE cdb_posts (
  pid int(10) unsigned NOT NULL auto_increment,
  fid smallint(6) unsigned NOT NULL default '0',
  tid mediumint(8) unsigned NOT NULL default '0',
  aid tinyint(1) NOT NULL default '0',
  author varchar(15) NOT NULL default '',
  authorid mediumint(8) unsigned NOT NULL default '0',
  subject varchar(80) NOT NULL default '',
  dateline int(10) unsigned NOT NULL default '0',
  message mediumtext NOT NULL,
  useip varchar(15) NOT NULL default '',
  usesig tinyint(1) NOT NULL default '0',
  bbcodeoff tinyint(1) NOT NULL default '0',
  smileyoff tinyint(1) NOT NULL default '0',
  parseurloff tinyint(1) NOT NULL default '0',
  rate smallint(6) NOT NULL default '0',
  ratetimes tinyint(3) unsigned NOT NULL default '0',
  watermarkon tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (pid),
  KEY fid (fid),
  KEY dateline (dateline),
  KEY authorid (authorid),
  KEY tid (tid,dateline)
);
---------------------------------------------------------------------
TABLE cdb_threads:  7967 RECORDS
---------------------------------------------------------------------

DROP TABLE IF EXISTS cdb_threads;
CREATE TABLE cdb_threads (
  tid mediumint(8) unsigned NOT NULL auto_increment,
  fid smallint(6) unsigned NOT NULL default '0',
  cate VARCHAR( 2 ) NOT NULL DEFAULT '0',
  creditsrequire smallint(6) unsigned NOT NULL default '0',
  iconid smallint(6) unsigned NOT NULL default '0',
  author char(15) NOT NULL default '',
  authorid mediumint(8) unsigned NOT NULL default '0',
  subject char(80) NOT NULL default '',
  dateline int(10) unsigned NOT NULL default '0',
  lastpost int(10) unsigned NOT NULL default '0',
  lastposter char(15) NOT NULL default '',
  views mediumint(8) unsigned NOT NULL default '0',
  replies smallint(6) unsigned NOT NULL default '0',
  displayorder tinyint(1) NOT NULL default '0',
  highlight tinyint(1) NOT NULL default '0',
  digest tinyint(1) NOT NULL default '0',
  poll tinyint(1) NOT NULL default '0',
  attachment tinyint(1) NOT NULL default '0',
  closed mediumint(8) unsigned NOT NULL default '0',
  threads_moderatetime char(15) NOT NULL default '',
  threads_moderate tinyint(1) NOT NULL default '0',
  threads_moderator char(15) NOT NULL default '',
  threads_moderatorid char(8) NOT NULL default '',
  isserect tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (tid),
  KEY displayorder (fid,displayorder,lastpost),
  KEY digest (digest)
);

How to repeat:
#TABLE cdb_posts   37185  RECORDS
#--------------------------------------------------------------------

DROP TABLE IF EXISTS cdb_posts;
CREATE TABLE cdb_posts (
  pid int(10) unsigned NOT NULL auto_increment,
  fid smallint(6) unsigned NOT NULL default '0',
  tid mediumint(8) unsigned NOT NULL default '0',
  aid tinyint(1) NOT NULL default '0',
  author varchar(15) NOT NULL default '',
  authorid mediumint(8) unsigned NOT NULL default '0',
  subject varchar(80) NOT NULL default '',
  dateline int(10) unsigned NOT NULL default '0',
  message mediumtext NOT NULL,
  useip varchar(15) NOT NULL default '',
  usesig tinyint(1) NOT NULL default '0',
  bbcodeoff tinyint(1) NOT NULL default '0',
  smileyoff tinyint(1) NOT NULL default '0',
  parseurloff tinyint(1) NOT NULL default '0',
  rate smallint(6) NOT NULL default '0',
  ratetimes tinyint(3) unsigned NOT NULL default '0',
  watermarkon tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (pid),
  KEY fid (fid),
  KEY dateline (dateline),
  KEY authorid (authorid),
  KEY tid (tid,dateline)
);
#---------------------------------------------------------------------
#TABLE cdb_threads:  7967 RECORDS
#---------------------------------------------------------------------

DROP TABLE IF EXISTS cdb_threads;
CREATE TABLE cdb_threads (
  tid mediumint(8) unsigned NOT NULL auto_increment,
  fid smallint(6) unsigned NOT NULL default '0',
  cate VARCHAR( 2 ) NOT NULL DEFAULT '0',
  creditsrequire smallint(6) unsigned NOT NULL default '0',
  iconid smallint(6) unsigned NOT NULL default '0',
  author char(15) NOT NULL default '',
  authorid mediumint(8) unsigned NOT NULL default '0',
  subject char(80) NOT NULL default '',
  dateline int(10) unsigned NOT NULL default '0',
  lastpost int(10) unsigned NOT NULL default '0',
  lastposter char(15) NOT NULL default '',
  views mediumint(8) unsigned NOT NULL default '0',
  replies smallint(6) unsigned NOT NULL default '0',
  displayorder tinyint(1) NOT NULL default '0',
  highlight tinyint(1) NOT NULL default '0',
  digest tinyint(1) NOT NULL default '0',
  poll tinyint(1) NOT NULL default '0',
  attachment tinyint(1) NOT NULL default '0',
  closed mediumint(8) unsigned NOT NULL default '0',
  threads_moderatetime char(15) NOT NULL default '',
  threads_moderate tinyint(1) NOT NULL default '0',
  threads_moderator char(15) NOT NULL default '',
  threads_moderatorid char(8) NOT NULL default '',
  isserect tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (tid),
  KEY displayorder (fid,displayorder,lastpost),
  KEY digest (digest)
);

#Command
# I thing you should add something  into above table then test.

SELECT Count(DISTINCT p.pid) AS post, Count(DISTINCT t.tid) AS threads FROM cdb_posts p
LEFT JOIN cdb_threads t ON t.tid > 0
WHERE 1 GROUP BY p.pid;

#Command
#when I installed mysql-max , MYSQL was also hang in the followinf query:
SELECT Count(*) FROM cdb_threads where 1;

Could you please provide a test case with some data I was
unable to reproduce on my own.

Thanks in advance.

For security reason , I'can hive you access to my servers, I just can dump the same datas for you to make the test.

Is ok. You can upload the dump file at:

ftp://ftp.mysql.com/pub/mysql/upload/

use a file name which identified this bug report.

Thanks in advance.

you can download from here ->

http://sqldump.fbi.com.tw/sql.sql    (22.24MB)

Please tell me if you have downloaded that I must del this backup, thx a lot.

P.S this backup ihas messages that wrote in Big5 , It may apear that there is errors in it when you restore. If have, just del it.

OK~ I'm uploading, 2minutes left...

I have finished upload the file as /pub/mysql/upload/12623.sql

Thank you for the feedback. I was unable to repeat the
crash and looks it is a tuning schema/optimization issue:

<cut>

|    1 |    8115 |
|    1 |    8115 |
|    1 |    8115 |
+------+---------+
38216 rows in set (1 hour 29 min 32.08 sec)

mysql> 
mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.14-debug-log |
+------------------+
1 row in set (0.07 sec)