Bug #14098 Mysql server crashes when a query is issued
Submitted: 18 Oct 2005 0:37 Modified: 18 Oct 2005 2:05
Reporter: Jason Kirk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.13-rc-standard OS:Linux (Linux/Windows)
Assigned to: MySQL Verification Team CPU Architecture:Any

[18 Oct 2005 0:37] Jason Kirk
Description:
Mysql will restart itself when an certain type of query is issued.

How to repeat:
Create a database called "Policy" with the following tables:

CREATE TABLE `reviews` (
  `reviewId` bigint(10) unsigned NOT NULL auto_increment,
  `clientAccnum` int(10) unsigned NOT NULL default '0',
  `clientSubacc` smallint(4) unsigned zerofill NOT NULL default '0000',
  `reviewTriggerId` tinyint(3) unsigned NOT NULL default '0',
  `reviewStatusId` tinyint(3) unsigned NOT NULL default '0',
  `reviewStateId` tinyint(3) unsigned NOT NULL default '1',
  `violationLevelId` tinyint(3) unsigned NOT NULL default '0',
  `uid` bigint(20) unsigned NOT NULL default '0',
  `datestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `nextStepDays` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`reviewId`),
  KEY `account` (`clientAccnum`,`clientSubacc`),
  KEY `status` (`reviewStatusId`,`reviewStateId`),
  KEY `state` (`reviewStateId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `reviewStatuses` (
  `reviewStatusId` tinyint(3) unsigned NOT NULL auto_increment,
  `reviewStatus` char(40) NOT NULL default '',
  `priority` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`reviewStatusId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `reviewAreas` (
  `reviewId` bigint(20) unsigned NOT NULL default '0',
  `areaReviewId` bigint(20) unsigned NOT NULL default '0',
  `active` tinyint(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`reviewId`,`areaReviewId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `areaReviews` (
  `areaReviewId` bigint(10) unsigned NOT NULL auto_increment,
  `areaId` int(10) unsigned NOT NULL default '0',
  `reviewId` bigint(20) unsigned NOT NULL default '0',
  `areaReviewStatusId` tinyint(3) unsigned NOT NULL default '0',
  `areaReviewStateId` tinyint(3) unsigned NOT NULL default '1',
  `violationLevelId` tinyint(3) unsigned NOT NULL default '0',
  `datestamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `uid` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`areaReviewId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `areas` (
  `areaId` int(10) unsigned NOT NULL auto_increment,
  `urlId` bigint(20) unsigned NOT NULL default '0',
  `areaTypeId` tinyint(3) unsigned NOT NULL default '0',
  `concernLevelId` tinyint(3) unsigned NOT NULL default '3',
  `lastReviewDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `adult` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`areaId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
 
CREATE TABLE `fullURLs` (
  `urlId` bigint(20) unsigned NOT NULL default '0',
  `fullURL` text NOT NULL,
  PRIMARY KEY  (`urlId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000

Then issue the following query:

SELECT distinct(r.reviewId),
                r.clientAccnum,
                r.clientSubacc,
                r.reviewStatusId,
                r.reviewTriggerId,
                r.violationLevelId,
                r.datestamp,
                a.urlId,
                fullURL
          FROM Policy.reviews r
               INNER JOIN Policy.reviewStatuses rs USING (reviewStatusId)
               INNER JOIN Policy.reviewAreas ra ON (ra.reviewId = r.reviewId AND ra.active = 1)
               INNER JOIN Policy.areaReviews ar on (ar.areaReviewId = ra.areaReviewId)
               INNER JOIN Policy.areas a ON (a.areaId = ar.areaId AND
                                                    areaTypeId = 1)
               INNER JOIN Policy.fullURLs USING (urlId)
          WHERE r.reviewStateId = 2
          ORDER BY
                (ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(r.datestamp)) / 3600) + 1) * rs.priority DESC
          LIMIT 10

Mysql will crash and restart itself. This is caused by the command "INNER JOIN Policy.fullURLs USING (urlId)". If you use ON instead of USING it will work. However this is not possible in all situations. Please help. Thanks.
[18 Oct 2005 2:05] MySQL Verification Team
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

mysql> SELECT distinct(r.reviewId),
    ->                 r.clientAccnum,
    ->                 r.clientSubacc,
    ->                 r.reviewStatusId,
    ->                 r.reviewTriggerId,
    ->                 r.violationLevelId,
    ->                 r.datestamp,
    ->                 a.urlId,
    ->                 fullURL
    ->           FROM Policy.reviews r
    ->                INNER JOIN Policy.reviewStatuses rs USING (reviewStatusId)
    ->                INNER JOIN Policy.reviewAreas ra ON (ra.reviewId = r.reviewId AND
    -> ra.active = 1)
    ->                INNER JOIN Policy.areaReviews ar on (ar.areaReviewId =
    -> ra.areaReviewId)
    ->                INNER JOIN Policy.areas a ON (a.areaId = ar.areaId AND
    ->                                                     areaTypeId = 1)
    ->                INNER JOIN Policy.fullURLs USING (urlId)
    ->           WHERE r.reviewStateId = 2
    ->           ORDER BY
    ->                 (ROUND((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(r.datestamp)) /
    -> 3600) + 1) * rs.priority DESC
    ->           LIMIT 10;
Empty set (0.01 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.16-debug |
+--------------+
1 row in set (0.01 sec)