Bug #2098 Sporadic crash when executing SELECT query

• Submitted: 11 Dec 2003 10:41
• Modified: 11 Dec 2003 11:29
• Reporter: Rick Selby
• Status: Can't repeat
• Category: MySQL Server: MyISAM storage engine
• Severity: S1 (Critical)
• Version: 4.0.16-nt
• OS: Windows (Windows XP)
• CPU Architecture: Any

[11 Dec 2003 10:41] Rick Selby

Description:
When executing the given query:

SELECT
  fteam.id,
  fteam.name,
  fteam.owner,
  SUM(pp.points) AS totalpoints
FROM
  fteam, provpoints pp
WHERE
    (fteam.driver1id = pp.id AND pp.type = 'driver')
 OR (fteam.driver2id = pp.id AND pp.type = 'driver')
 OR (fteam.teamid = pp.id AND pp.type = 'team')
GROUP BY id
ORDER BY totalpoints DESC

mySQL will sometimes complete the query with no problems, and sometimes will crash (causing Windows to bring up the "mysqld-nt.exe has encountered a problem and needs to close.  We are sorry for the inconvenience." dialogue box).

Running the same query on a different server (4.0.15-standard on 'Linux' - webserver) only seems to cause a non-returned result from the query rather than a crash.

How to repeat:
CREATE TABLE fteam (
  id smallint(5) unsigned NOT NULL auto_increment,
  name tinytext NOT NULL,
  owner smallint(5) unsigned NOT NULL default '0',
  driver1id smallint(5) unsigned NOT NULL default '0',
  driver2id smallint(5) unsigned NOT NULL default '0',
  teamid smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY owners (owner)
) TYPE=MyISAM;

CREATE TABLE provpoints (
  race tinyint(3) unsigned NOT NULL default '0',
  type enum('driver','team') NOT NULL default 'driver',
  id smallint(5) unsigned NOT NULL default '0',
  points float NOT NULL default '0',
  PRIMARY KEY  (type,id),
  KEY id (id)
) TYPE=MyISAM;

SELECT
  fteam.id,
  fteam.name,
  fteam.owner,
  SUM(pp.points) AS totalpoints
FROM
  fteam, provpoints pp
WHERE
    (fteam.driver1id = pp.id AND pp.type = 'driver')
 OR (fteam.driver2id = pp.id AND pp.type = 'driver')
 OR (fteam.teamid = pp.id AND pp.type = 'team')
GROUP BY id
ORDER BY totalpoints DESC

[11 Dec 2003 11:04] MySQL Verification Team

I tried more than 50 queries without to repeat the crash.

[11 Dec 2003 11:14] Rick Selby

Thanks for trying.

I seemed to actually have the same affect on the web server - came back with the error "Lost connection to MySQL server during query".

I shall rework my query.

[11 Dec 2003 11:29] MySQL Verification Team

If you are getting: "Lost connection to MySQL server during query"
verify that your client it isn't using 4.0.15 client library.
If yes see the notes about bugs fixed in the 4.0.16 release.