0

I'm looking for a way to protect my database against runaway queries issued by an application. I have control over both the application and the database. It occurs to me that I can protect against two specific things relatively easily:

  1. Set a max_statement using GRANT ... MAX_STATEMENT_TIME for the application user.
  2. Set sql_select_limit for each connection.

When testing, I can see that if I set a max_statement_time of 5 (seconds), if I execute a SELECT SLEEP(10), I end up with an error like this:

Query execution was interrupted (max_statement_time exceeded)

But if I perform a SELECT which should return a number of rows that exceeds some value (e.g. 1000), I get this result:

mysql> set sql_select_limit = 1000;
Query OK, 0 rows affected (0.000 sec)

mysql> SELECT * FROM largetable;

....
....
1000 rows in set (0.010 sec)

mysql> show warnings;
Empty set (0.000 sec)

So while I get the "protection" of limiting my query results to 1000 rows, I think what I really want is an error to be thrown in these cases, so we can identify them in the application and fix them.

Is there anything in MariaDB that is like sql_select_limit but throws an error rather than simply limiting the size of the result set?

9
  • FWIW, trying to do this by limiting the number of rows being returned is not a good way to go about it. The same query can be just as resource intensive with and without a row limit, because it requires a lot of the same heavy steps to actually locate the data on disk, and process it (such as filtering and transformations), before the final SELECT of those rows at the end of the execution. If your query elicits a table scan against a 1 million row table, it's still going to have to scan 1 million rows, even if limited to return only 1,000 rows. The actual SELECT step is the easiest part.
    – J.D.
    Jul 24, 2023 at 19:16
  • What is the actual goal? Limit network bandwidth -- check bytes, not rows. Limit CPU -- you mentioned how to do that. Avoid overrunning some IOPs limit? Intermediate temp table could chew up that for what otherwise looks like a 'short' query.
    – Rick James
    Jul 26, 2023 at 1:11
  • @J.D. I agree, which is why we are coupling this with a query-execution-time limit. The idea is that we also want to protect against fast-running queries that generate huge numbers of result rows (e.g. SELECT * from bigtable which should take essentially zero time to execute, but a long time to stream). Jul 26, 2023 at 21:14
  • @RickJames The goal for some quasi-sql_select_limit is exactly what I have said at the end of my question: what I really want is an error to be thrown in these cases, so we can identify them in the application and fix them. Jul 26, 2023 at 21:15
  • "I agree, which is why we are coupling this with a query-execution-time limit." - Yea but it's redundant then and doesn't actually protect you from resource consumption problems. Essentially the SQL engine doesn't know how many rows it's actually going to return until after it's already done all the heavy lifting, so it wouldn't really be possible to do what you're looking for.
    – J.D.
    Jul 26, 2023 at 21:39

1 Answer 1

0

The purpose of MAX_STATEMENT_TIME is to protect the system, because MySQL does not know how much time it will take to execute this statement, and the system may be in an unknown state at this time, so it is reasonable to design it as “return error”.

And sql_select_limit is a clear query statement, and it is safe to return directly to the client when the number of rows (such as 1000) is met, so returning is more reasonable than reporting an error.

Of course, in practice, this parameter setting is not friendly to the business code. Imagine that another programmer takes over this system and may think that 1000 is the actual correct return result, which may cause problems with the subsequent processing logic.

If you have a business logic that is to achieve “report an error when exceeding sql_select_limit”, it is recommended to use "select id from largetable limit sql_select_limit+1;"

Then judge whether the "returned row_num" > sql_select_limit, and the business layer code reports an error by itself.

2
  • Thank you for your answer, but the returned row_count / max(row_num) would never exceed sql_select_limit, so it's not possible for the application to determine if it hit or exceeded the limit. I don't believe a single query can override the sql_select_limit by specifying a LIMIT clause in the SELECT, though I haven't actually tried it. But that would require that I modify every query in my application and the goal of this small change (setting sql_select_limit) is to avoid having to re-evaluate every query in the entire application. Jul 26, 2023 at 21:21
  • “ but the returned row_count / max(row_num) would never exceed sql_select_limit”, I meant "limit 1000+1" in your query Jul 28, 2023 at 2:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.