query kills mySQL db

[starman7]

I have a query that seems to kill the db for our website (causes the
'Too many connections' error).

I would like to avoid this! Is there a way to test the query before the
db goes down? I do not have access to a staging environment. I can
access the db via Linux command line, or phpMyAdmin - however I can
only select, update, and delete.

I've heard a user can identify a bad query and kill it, how is this
done? Would my user have rights to do it (kill his own query)? How can
I tell?

Thanks -
S7

P.S. Here is the query, in-case there is something glaringly wrong:

SELECT i.id, m.name, i.weight
FROM mods m, inv i, data d
WHERE m.id = i.id AND i.id = d.id
AND d.active = 'y'
AND i.weight < 1
OR i.weight > 50

[Gordon Burditt]

>I have a query that seems to kill the db for our website (causes the

'Too many connections' error).
This is a strange use of the term 'kill'. If you get a 'too many
connections error trying to do the query itself, you never even
got to the query. It is possible that it loads the database
enough that it bottlenecks other queries. But then you get the error
on OTHER queries.

I would like to avoid this! Is there a way to test the query before the
db goes down? I do not have access to a staging environment. I can
access the db via Linux command line, or phpMyAdmin - however I can
only select, update, and delete.
You could try logging in from the command line BEFORE running the
query (that way you won't run out of connections). Do a 'show
processlist'. Use the same login info your query is using - you
can show and kill your own processes. Then run the query from
another connection. Keep doing 'show processlist' to see what it's
doing and if the database is still responding.
I've heard a user can identify a bad query and kill it, how is this
done? Would my user have rights to do it (kill his own query)? How can
I tell?
"show processlist" to see the ID numbers for your own processes (or
all of them with sufficient privs). "kill <id>" to kill one of
your processes. You'll get an error message if you don't have the
privs to do that. Use the same login (or an administrative one
with wide privileges) for watching the process as for running it,
that way you can see and kill it.
SELECT i.id, m.name, i.weight
FROM mods m, inv i, data d
WHERE m.id = i.id AND i.id = d.id
AND d.active = 'y'
AND i.weight < 1
OR i.weight > 50
What is your intent here? This query is equivalent to:
SELECT i.id, m.name, i.weight
FROM mods m, inv i, data d
WHERE ((((((((((((((( (m.id = i.id) AND (i.id = d.id)
AND (d.active = 'y')
AND (i.weight < 1 ))))))))))))))) )
OR (i.weight > 50)
If you have M mods, D datas, and II weights greater than 50, you'll
get every possible combination of them, which is at least M*D*II
results, which could be a really large number. (If M = 10,000; D=
10,000, and II = 10,000, that's a million million records, which
may not finish in your lifetime) I suspect you really want:
SELECT i.id, m.name, i.weight
FROM mods m, inv i, data d
WHERE (m.id = i.id) AND (i.id = d.id)
AND (d.active = 'y')
AND ((((((((((((((( (i.weight < 1 )
OR (i.weight > 50))))))))))))) )))

except you probably want more "unnecessar y" parentheses.

Assuming that m.id and i.id and d.id are primary keys for the tables,
this would retrieve at most the number of records in the inv table.

Gordon L. Burditt

[starman7]

Hi Gordon,

Thanks for your quick response, and suggestions for monitoring/killing
the process. That makes sense.

I think my query was loading the DB, it ran, and ran. And finally the
"Too many connections" error began to show.

I had no idea my query was so bad! My intent is to find the smallest
set, where all the id's are the same (they are a sort of key), and to
show the weights where < 0 or > 50, and where they are active. Thanks
for the example - I don't understand the multiple parentheses though,
e.g.

AND ((((((((((((((( (i.weight < 1 )
OR (i.weight > 50))))))))))))) )))

do I need all those?

[Bill Karwin]

st******@hotmai l.com wrote:

I don't understand the multiple parentheses though,
do I need all those?

The point is that AND binds tighter than OR, so you need to use
parentheses to make the conditions combine in the way you intend.
Gordon was being ironic by using so many nested parentheses.

Explanation of "binds tighter":

An expression of the form X AND Y OR Z is equivalent to (X AND Y) OR Z.
So the whole expression returns true every time Z is true, regardless of
the result of X AND Y.

Since your join conditions are in the position like X in this example,
they are effectively factored out, and you get every row in your tables
joined to every row in the other tables. This is called a Cartesian
product; it's rarely what you want when you're joining tables (but it is
a legitimate type of join, and has some rare good uses).

You need to parenthesize the expression like this:
X AND (Y OR Z).

I like to join using the SQL-92 JOIN syntax. It's easier to avoid
Cartesian product queries if I keep the join conditions separate from
row-restriction conditions. Your query would be written as follows:

SELECT i.id, m.name, i.weight
FROM mods m INNER JOIN inv i ON m.id = i.id
INNER JOIN data d ON i.id = d.id
WHERE d.active = 'y'
AND (i.weight < 1 OR i.weight > 50)

Regards,
Bill K.

[starman7]

Bill Karwin wrote:

st******@hotmai l.com wrote:

I don't understand the multiple parentheses though,
do I need all those?

The point is that AND binds tighter than OR, so you need to use
parentheses to make the conditions combine in the way you intend.
Gordon was being ironic by using so many nested parentheses.

Explanation of "binds tighter":

An expression of the form X AND Y OR Z is equivalent to (X AND Y) OR Z.
So the whole expression returns true every time Z is true, regardless of
the result of X AND Y.

Since your join conditions are in the position like X in this example,
they are effectively factored out, and you get every row in your tables
joined to every row in the other tables. This is called a Cartesian
product; it's rarely what you want when you're joining tables (but it is
a legitimate type of join, and has some rare good uses).

You need to parenthesize the expression like this:
X AND (Y OR Z).

I like to join using the SQL-92 JOIN syntax. It's easier to avoid
Cartesian product queries if I keep the join conditions separate from
row-restriction conditions. Your query would be written as follows:

SELECT i.id, m.name, i.weight
FROM mods m INNER JOIN inv i ON m.id = i.id
INNER JOIN data d ON i.id = d.id
WHERE d.active = 'y'
AND (i.weight < 1 OR i.weight > 50)

Regards,
Bill K.

Thanks so much for that Bill. It's hard to spot irony when you don't
know what's going on to begin with! But I did suspect it as a
possibility ((((( ))))) just looks a little crazy. Anyway, your inner
joins worked great. The data came back quick, no cartesian products
here. Thank you both again.
S7