Scott Baker wrote:
> Is there a way to tell mysqld to stop running a query if it hasn't
> completed in over 60 seconds? Or how about stop running if the result
> is greater 100,000 rows or something? I have a bone head user who
> keeps querying two tables and NOT joining them, and then dragging the
> DB server down while it crunches this useless query.
>
> How can I prevent this?
>
> Scott
>
First some questions:
How is the user connecting: Using a mysql client, a web interface or a
custom program?
Who controls the machine they are accessing it from?
Do they have a dedicated unique database user?
Does the user issue thousands scripted of querys per day or just a dozen
manually?
Do their queries contain inserts or updates?
Who controls the database structure?
Does anyone fully understand the database structure?
Do you have a servers in a replication chain or could you set up a
replication chain?
Can you influence their behavior without a technological fix? Does their
incompetance merit other forms of action?
Just how much cash, hassle and time is a solution worth to you?
The answers of course influence what is possible. I'd say that if the
user is an experienced programmer or DBA you've simply hired the wrong
guy. Assuming not and that you need a technical solution it's likely to
be one of these (in order of increasing effort):
Give the user a quick person-to-person refresher course in the use of
entity models, explain and indexes
If they're connecting via the mysql client form a box you control look
at the --I-am-a-dummy, --select_limit= --join_limit= options and
consider allowing the person only to be able to invoke the mysql client
in that form. In *nix this is easy to achieve. Perhaps you should force
them to switch to working this way until they can be trusted if they
currently access using a different client.
If their queries are infrequent, read only, can stand a small delay and
you've spare hardware consider setting up a replicant reporting server.
Deny the user access to the master server and let them slow the
reporting server to their hearts content.
If it's a web interface you control and your programming skills allow
think about modying the code to apply a similar set of restrictions to
the users queries. If the feature doesn't already exist in the web
interface software and the appication changes needed are beyond your ken
a jobbing programmer could be employed to add it inexpensively.
As an expansion of the web interface idea if you understand the data
realtionships it's possible for an interface to cross-check and enforce
the correct links helping novice users without restricting experienced
ones. I've implemented this myself on an MIS project and it works well.
If the user is accessing remotely with a client they control, they're
changing data state, you cannot influence their behavior and your
pockets are deep. The last resort is to force the users queries through
a spoof 'mysql server' which checks each query with explain before
applying it. They have no access to the actual server only through the
proxy. Whilst I've never done this but as old the MySQL manual document
the protocol it's not impossible to make a validating proxy, just very,
very expensive.
Hope part of this this helps
Nigel
