[mysql 4.0.14-nt] MySQL eats my CPU with speical SELECT statement

Hi,

During using OpenOffice.org with MySQL, it seems a user discovered a bug
in MySQL.
The details are described in
http://www.openoffice.org/issues/show_bug.cgi?id=18039.

In short: If a sample SELECT statement (attached to the issue above) is
executed against MySQL, with the sample data also attached to the above
issue, then the CPU usage of the mysdqld-nt process jumps to 100%, and
the query doesn't return.

The original bug problem was discovered while using OpenOffice.org to
access the MySQL database, but it persists if the MySQL console is used
to execute the statement, so this is clearly a MySQL problem.

Any hints anybody?

Thanks & Ciao
Frank

--
- Frank Schönheit, Software Engineer, OpenOffice.org Database Access -
- fs@openoffice.org http://dba.openoffice.org/FAQ -
- -
- - - - - disclaimer: working for Sun, speaking for myself - - - - -


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [mysql 4.0.14-nt] MySQL eats my CPU with speical SELECT statement

Frank,

Frank Schönheit wrote:
> Hi,
>
> During using OpenOffice.org with MySQL, it seems a user discovered a bug
> in MySQL.
> The details are described in
> http://www.openoffice.org/issues/show_bug.cgi?id=18039.
>
> In short: If a sample SELECT statement (attached to the issue above) is
> executed against MySQL, with the sample data also attached to the above
> issue, then the CPU usage of the mysdqld-nt process jumps to 100%, and
> the query doesn't return.

We will investigate this case, but Query in question is Cartesian Product between
3 tables!

mysql> select count(*) from albums;
+----------+
| count(*) |
+----------+
| 305 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from tracks;
+----------+
| count(*) |
+----------+
| 3638 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from artists;
+----------+
| count(*) |
+----------+
| 231 |
+----------+
1 row in set (0.00 sec)


305*3638*231 = 256315290

Query which will provide 256 million rows and which can store them in some
temporary space before sending to client will be always slow.

I don't agree with you about
"closing as INVALID, because it's an MySQL bug."

Interrim result of this Cartesian product consists of 2 columns CHAR(100) + 1 Byte
per column * 256315290 rows = 51519373290 bytes which is almost 48GB of data. This
number is huge by any means. You should not expect your machine to handle easily
this amount of data.


I see nothing strange. You are just using MySQL in a way it is not intended to be
used. Cartesian Product is almost never meaningfull.

You can knock down any server with sending huge amount of data request.


This is very good example of careless SQL usage. No matter if this query is
written by hand or generated by some programs. Processing 3 very small tables
less than 2 Mb in size and very few rows can demand huge amount of resources.

> The original bug problem was discovered while using OpenOffice.org to
> access the MySQL database, but it persists if the MySQL console is used
> to execute the statement, so this is clearly a MySQL problem.
>
> Any hints anybody?

Do you agree with me this is not a bug really?

> Thanks & Ciao
> Frank

Best regards

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [mysql 4.0.14-nt] MySQL eats my CPU with speical SELECT statement

Hello alexander,

thanks for your quick reply - see my comments below ...

> We will investigate this case, but Query in question is Cartesian Product between
> 3 tables!
>
> ...
> 305*3638*231 = 256315290

(quite impressing arithmetics, indeed :)

>
> Query which will provide 256 million rows and which can store them in some
> temporary space before sending to client will be always slow.

Ah, yes ... looking at the (wrongly generated) statement, again:

SELECT `artists`.`name`, `albums`.`name`
FROM `albums`, `tracks`, `artists`
GROUP BY `tracks`.`al_id`
HAVING 0 = 1

.... Being *that* used to seeing "WHERE 0 = 1" in OOo-generated
statements, I didn't realize that we're talking about a HAVING here,
which of course is a different thing.

> I don't agree with you about
> "closing as INVALID, because it's an MySQL bug."

You're completely right, sorry for sueing MySQL! This, indeed, is an
amount of data which the next 4 generations of my PC won't be able to
handling easily :).

I'll state this in the IZ-issue (citing you there :), and reopen it.

> This is very good example of careless SQL usage. No matter if this query is
> written by hand or generated by some programs.

Generated by OOo in this case, unfortunately :(. I already submitted an
issue for this improper generation, before I asked here, but as said:
reading the HAVING as WHERE didn't let me realized that it's also the
real problem. Sorry!

> Do you agree with me this is not a bug really?
Yes!

Thanks & Ciao
Frank



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [mysql 4.0.14-nt] MySQL eats my CPU with speical SELECT statement

Hi Frank,

Frank Schönheit wrote:
> Hello alexander,


>>Query which will provide 256 million rows and which can store them in some
>>temporary space before sending to client will be always slow.
>
>
> Ah, yes ... looking at the (wrongly generated) statement, again:
>
> SELECT `artists`.`name`, `albums`.`name`
> FROM `albums`, `tracks`, `artists`
> GROUP BY `tracks`.`al_id`
> HAVING 0 = 1
>
> ... Being *that* used to seeing "WHERE 0 = 1" in OOo-generated
> statements, I didn't realize that we're talking about a HAVING here,
> which of course is a different thing.

I want to be sure you understand what I am pointing to you. Having is not a
problem, nor is GROUP BY. What makes query "wrong by idea" is Cartesian Product.
It joins 3 tables without Join condition.

SELECT ... FROM `albums`, `tracks`;

This translates as "Combine each row in albums with each row in tracks".

Such request can hardly makes sense.

SELECT ... FROM `albums`, `tracks` WHERE albums.id = tracks.al_id;

on the other hand reads
"Combine rows from albums with their corresponding rows in tracks (and return
these rows only for which there is such relation)"

So if your query was:

SELECT `artists`.`name`, `albums`.`name`
FROM `albums`, `tracks`, `artists`
WHERE albums.id = tracks.al_id
AND tracks.ar_id = artists.id;

....

It returns

3638 rows in set (0.10 sec)

All these rows makes pefrect sense for both me and you.

Adding GROUP BY clause to this query can possibly make some sense, but not exactly
this GROUP BY clause with this SELECT part.

Your query actually utilises MySQL ANSI exiention (some can claim it is violation)
ANS SQL does not allow usage in GROUP BY of columns or aliases which don't appear
in SELECT part.

However this is beyond topic of current discussion.

>>I don't agree with you about
>>"closing as INVALID, because it's an MySQL bug."
>
>
> You're completely right, sorry for sueing MySQL! This, indeed, is an
> amount of data which the next 4 generations of my PC won't be able to
> handling easily :).


Some more info.

MySQL 4.0.14 on Linux "survived" your original query with your data:

mysql> SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`, `artists`
GROUP BY `tracks`.`al_id` HAVING 0 = 1;
Empty set (11 min 46.55 sec)


Yes it is very slow, but final result is expected given that HAVING 0 = 1 is
always false. MySQL did not hang, nor machine did. CPU usage remain at usual level
- no noticeable slowdown caused by MySQL.

I don't have these ~48GB memory. The reason that MySQL was able to process
original query is that it sucessfully optimizes GROUP BY clause and make usage of
Indexes in tables.

mysql> explain SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`,
`artists` GROUP BY `tracks`.`al_id` HAVING 0 = 1;
+---------+-------+---------------+------------------+------ ---+------+------+------------------------------------------ ----+|
table | type | possible_keys | key | key_len | ref | rows | Extra

|+---------+-------+---------------+------------------+----- ----+------+------+----------------------------------------- -----+|
albums | index | NULL | albums_webindex | 100 | NULL | 305 | Using
index; Using temporary; Using filesort || tracks | ALL | NULL | NULL
| NULL | NULL | 3638 |
|| artists | index | NULL | artists_webindex | 100 | NULL | 231 |
Using index
|+---------+-------+---------------+------------------+----- ----+------+------+----------------------------------------- -----+



Trying to run:
SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`, `artists`;

simply leads to error 28: No space left on device


Running original query without using indexes looks it will take forever to finish.
When I'm writing it it works for 1.5 hours.


> I'll state this in the IZ-issue (citing you there :), and reopen it.

I have no idea what IZ is.


>>This is very good example of careless SQL usage. No matter if this query is
>>written by hand or generated by some programs.
>
>
> Generated by OOo in this case, unfortunately :(. I already submitted an
> issue for this improper generation, before I asked here, but as said:
> reading the HAVING as WHERE didn't let me realized that it's also the
> real problem. Sorry!

No need to apologize.
Whenever you think you have hit MySQL bug don't hesitate to report it. Preferred
way is to fill report in http://bugs.mysql.com.

You don't need to be advised what repeatable test case means. Your


> Thanks & Ciao
> Frank

You are welcome

Best regards

--
Are you MySQL certified? -> http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [mysql 4.0.14-nt] MySQL eats my CPU with speical SELECT statement

Hi Alexander,

>>Ah, yes ... looking at the (wrongly generated) statement, again:
>>
>> SELECT `artists`.`name`, `albums`.`name`
>> FROM `albums`, `tracks`, `artists`
>> GROUP BY `tracks`.`al_id`
>> HAVING 0 = 1
>>
>>... Being *that* used to seeing "WHERE 0 = 1" in OOo-generated
>>statements, I didn't realize that we're talking about a HAVING here,
>>which of course is a different thing.
>
> I want to be sure you understand what I am pointing to you. Having is not a
> problem, nor is GROUP BY. What makes query "wrong by idea" is Cartesian Product.
> It joins 3 tables without Join condition.
> ...

Yes, I see this clearly.
What I wanted (means: failed :) to express was that I associated the "0
= 1" I saw with the "WHERE 0 = 1" which OOo regularily uses to determine
the structure of a result set. And since "WHERE 0 = 1" can be (and
probably is, in MySQL?) implemented without actually retrieving any data
- since it's obvious that the result set will always be empty -, I
assumed the same for the statement I saw there. This lead me to thinking
it's a MySQL problem.

Speaking strictly, it would probably be possible to optimize a "HAVING 0
= 1", too, since there the same arguing leads to an empty result set
immediately. But for HAVING this does not really sound as ... natural to
me as for WHERE.

> So if your query was:

(I have to mention here that the original problem, together with all the
sample data/queries, was reported by some other user of OOo :)

> SELECT `artists`.`name`, `albums`.`name`
> FROM `albums`, `tracks`, `artists`
> WHERE albums.id = tracks.al_id
> AND tracks.ar_id = artists.id;
> ...
> Adding GROUP BY clause to this query can possibly make some sense, but not exactly
> this GROUP BY clause with this SELECT part.

Yep.

> Some more info.
>
> MySQL 4.0.14 on Linux "survived" your original query with your data:
>
> mysql> SELECT `artists`.`name`, `albums`.`name` FROM `albums`, `tracks`, `artists`
> GROUP BY `tracks`.`al_id` HAVING 0 = 1;
> Empty set (11 min 46.55 sec)
>
> Yes it is very slow, but final result is expected given that HAVING 0 = 1 is
> always false. MySQL did not hang, nor machine did. CPU usage remain at usual level
> - no noticeable slowdown caused by MySQL.

Hmm. You mean I have migrate to Linux? :) Okay, that's becoming
off-topic ... :)


> Running original query without using indexes looks it will take forever to finish.
> When I'm writing it it works for 1.5 hours.

That's the time neither me nor the original reporter waited - as said,
on Windows it's terribly slowing down the whole machine, the MySQL
server process takes 99-100% of the CPU, and even terminating it takes
half a minute because everything (including consoles, which are normally
the last thing working) is extremly unresponsive.

>>I'll state this in the IZ-issue (citing you there :), and reopen it.
>
> I have no idea what IZ is.

Sorry. IZ = IssueZilla, the bug tracking system of OpenOffice.org.
What I wanted to say is that I will (and meanwhile did) state what you
said in the respective bug in IssueZilla, to not leave the "this is a
MySQL bug" there :).


Thanks & Ciao
Frank




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [mysql 4.0.14-nt] MySQL eats my CPU with speical SELECT statement

Hi,

Frank Schönheit wrote:
> Hi Alexander,


> What I wanted (means: failed :) to express was that I associated the "0
> = 1" I saw with the "WHERE 0 = 1" which OOo regularily uses to determine
> the structure of a result set.

Really?
I would never expect this!
With MySQL there is much nicer way to check if query syntax is Ok and it works
with all queries and all possible WHERE, GROUP BY and HAVING clauses.

.... LIMIT 0;

Seeing LIMIT 0 MySQL will just return empty set.

I agree with you about 1 = 0 optimization. It works as you say for WHERE clauses.
EXPLAIN on such query you will see it qualifies it a "Impossible WHERE"

>>Yes it is very slow, but final result is expected given that HAVING 0 = 1 is
>>always false. MySQL did not hang, nor machine did. CPU usage remain at usual level
>>- no noticeable slowdown caused by MySQL.
>
>
> Hmm. You mean I have migrate to Linux? :) Okay, that's becoming
> off-topic ... :)

No! I mean that I gave it a try and there were nothing wrong with CPU usage.


> Thanks & Ciao
> Frank

Best regards

--
Are you MySQL certified? -- http://www.mysql.com/certification
For technical support contracts, visit https://order.mysql.com/?ref=msal
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
<___/ www.mysql.com




--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: [mysql 4.0.14-nt] MySQL eats my CPU with speical SELECT statement

Hi Alexander,

>> What I wanted (means: failed :) to express was that I associated the "0
>> = 1" I saw with the "WHERE 0 = 1" which OOo regularily uses to determine
>> the structure of a result set.
>
> Really?
> I would never expect this!
> With MySQL there is much nicer way to check if query syntax is Ok and it
> works with all queries and all possible WHERE, GROUP BY and HAVING clauses.
>
> ... LIMIT 0;

The problem is that OpenOffice.org does not rely on a fixed backend - it
should work with a lot more backends than MySQL - at least until MySQL
(or any other database, for that matter :) reaches world domination and
there will be no other databases anymore ...

"WHERE 0 = 1" is the least common denominator, it's expected that most
halfway modern databases have an optimization for it.


>> Hmm. You mean I have migrate to Linux? :) Okay, that's becoming
>> off-topic ... :)
>
> No! I mean that I gave it a try and there were nothing wrong with CPU
> usage.

just kidding ...

Thanks & Ciao
Frank


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org