| Bug #2120 | derived table query causes server to go 100 percent cpu | ||
|---|---|---|---|
| Submitted: | 15 Dec 2003 13:14 | Modified: | 20 Feb 2004 5:55 |
| Reporter: | Edmund Mierzwa | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.1.1 alpha | OS: | Windows (winxp) |
| Assigned to: | Oleksandr Byelkin | CPU Architecture: | Any |
[15 Dec 2003 13:23]
Dean Ellis
Verified against 4.1.2. EXPLAIN on this query forces mysqld to restart (with nothing in the error log other than mentioning that mysqld restarted). (The issue in MySQLCC is due to MySQLCC issuing an EXPLAIN against the query.)
[15 Dec 2003 16:42]
Alexander Keremidarski
Further tests revealed that bug happens only when mysqld is compiled without debug options. Debug binaries return expected result from both query and EXPLAIN
[20 Dec 2003 2:06]
Alexey Botchkov
bk commit - 4.1 tree (hf:1.1701) (20 Dec 2003)
[1 Feb 2004 5:36]
Oleksandr Byelkin
ChangeSet 1.1683 04/02/01 15:30:32 bell@sanja.is.com.ua +18 -0 now all tables of query are locked in one place (including derived tables) fixed BUG#2120 and other problem with EXPLAINing derived tables
[1 Feb 2004 10:13]
Oleksandr Byelkin
as addition: ChangeSet 1.1689 04/02/01 20:07:44 bell@sanja.is.com.ua +6 -0 make JOIN::prepare, JOIN::optimize only once for EXPLAIN of derived table (BUG#2120 sfter merge)
[20 Feb 2004 5:55]
Oleksandr Byelkin
thank you for bug report. Putch for this bug is pushed to our internal source repository and will be present in next 4.1 release
Description: This derived table query causes server to go to 100 percent cpu. I then have to kill it using taskmgr. Funny thing is after I kill it, my client(mysqlcc 0.9.3 beta) displays the correct result set. If I run this query from the C API or "mysql" client it works just fine. This bug could of been reported against mysqlcc as well, but I believe mysqld should not lockup because of a client. Using mysqld-nt Alpha 4.1.1 on WinXP, Dell 2.6ghz 600mg ram. Thanks, Ed How to repeat: DROP TABLE IF EXISTS data; CREATE TABLE data ( name char(1) default NULL, val int(5) default NULL ) TYPE=MyISAM; INSERT INTO data VALUES ('a',1), ('a',2), ('a',2), ('a',2), ('a',3), ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5); SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM data x, data y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name #Expected results, and the results that gets displayed after killing mysqld-nt. +--------+--------+ | name | median | +--------+--------+ | a | 7.0000 | | b | 3.5000 | +--------+--------+ Suggested fix: I can create a temporary table first, to avoid using derived tables or use a different client.