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.